List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:May 3 2009 5:08pm
Subject:Re: LAST_INSERT_ID and CRC32
View as plain text  
I don't think its possible to do what you want in a single statement. Since
LAST_INSERT_ID() is set to the last insert id of the connection... and the
row you are inserting doesn't exist.. well.. until you create it, it will
always either be zero or the record BEFORE your next insert, ie:

INSERT INTO testtable(NULL,LAST_INSERT_ID());
INSERT INTO testtable(NULL,LAST_INSERT_ID());

would produce
ID    CRC32ID
1       0
2       1

You could run an update immediately after the insert to set the CRC32
column:

UPDATE testtable SET id-crc=CRC32(LAST_INSERT_ID()) WHERE
id=LAST_INSERT_ID();

Not quite sure why you need the CRC32 value of the ID, will it not always be
the same value for the given ID number? Wouldn't it be easier to do it on
the select side of the equation?

SELECT id,CRC32(id) AS id-crc... FROM testtable...

-jw
On Sun, May 3, 2009 at 7:16 AM, thunder@stripped <thunder@stripped>wrote:

> Hello,
> I have a questions and I hope, that is possible in MySQL.
>
> I have the following short Table.
> CREATE TABLE IF NOT EXISTS `testtable` (
>  `id` bigint(20) unsigned NOT NULL auto_increment,
>  `id-crc` bigint(20) unsigned NOT NULL,
>  PRIMARY KEY  (`id`),
>  UNIQUE KEY `id-crc` (`id-crc`)
> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
>
>
> `id` is the unique autoincrement
>
> in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
>
> E.G.
>
> id      id-crc
> --------------------
> 1       2212294583      --> CRC32('1')
> 2       450215437       --> CRC32('2')
> 3       1842515611      --> CRC32('3')
>
> I would like insert the CRC32 directly when I make a new Insert. E.G.
>
> INSERT INTO `db283796092`.`testtable` (
> `id` , `id-crc`
> )
> VALUES (
> NULL , LAST_INSERT_ID()
> );
>
> But LAST_INSERT_ID() is 0!!!! How can I make that, that he use the actual
> INSERT-ID?
>
> Best regards
>
> Thunder
>
>
>
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
LAST_INSERT_ID and CRC32thunder@isfahan.at3 May
  • Re: LAST_INSERT_ID and CRC32Johnny Withers3 May
    • Re: LAST_INSERT_ID and CRC32thunder@isfahan.at3 May
      • Re: LAST_INSERT_ID and CRC32Johnny Withers4 May
        • Re: LAST_INSERT_ID and CRC32Thomas Pundt5 May
          • Re: LAST_INSERT_ID and CRC32Johnny Withers6 May
            • Re: LAST_INSERT_ID and CRC32thunder@isfahan.at6 May
    • Re: LAST_INSERT_ID and CRC32thunder@isfahan.at3 May