List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:May 3 2009 10:48pm
Subject:Re: LAST_INSERT_ID and CRC32
View as plain text  
Well, I think an update after insert is the only way. Other than
perpopulating another table with possibe crc values then usinga join:

Select id from testtable
Inner join crctable on testtable.id=crctable.id
Where crctable.crcval='xxxxxxx'

Just be sure to index the crcval column.



On Sunday, May 3, 2009, thunder@stripped <thunder@stripped> wrote:
> Hi Johnny,
>
> I need the CRC32 for a unique URL-ID...
>
> I think it isn't to slow when I make a SELECT later in this form:
>
> Rows in Table: 825,984
>
> Search for id: 2532552 (CRC32: 46316330)
>
> SELECT id FROM `testtable` WHERE id = "2532552"
> 0.0005 sec.
>
> SELECT id FROM `testtable` WHERE CRC32(id) = "46316330"
> 0.5712 sec.
>
> OK, I can make an UPDATE after an INSERT but then I can't use UNIQUE for the Coloumn
> with the CRC32...
> Before I can make an Update, the Value is 0...
>
>
>
> 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
>
>
>
>
>
>
>
>
>
>
> --
> 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