LAST_INSERT_ID and CRC32

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=buLL@pubbs.net

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@isfahan.at 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=johnny@pixelated.net
>
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

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@isfahan.at 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=johnny@pixelated.net
>>
>>
>>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=buLL@pubbs.net

I'm so sorry for the mistake...
I mean:
I think it is to slow when I make a SELECT later in this form:
> 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@isfahan.at 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=johnny@pixelated.net
>>
>>
>>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=buLL@pubbs.net

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=3Dcrctable.id
Where crctable.crcval=3D'xxxxxxx'
Just be sure to index the crcval column.
On Sunday, May 3, 2009, thunder@isfahan.at 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 =3D "2532552"
> 0.0005 sec.
>
> SELECT id FROM `testtable` WHERE CRC32(id) =3D "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. Sin=
ce
> LAST_INSERT_ID() is set to the last insert id of the connection... and th=
e
> 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 =A0 =A0CRC32ID
> 1 =A0 =A0 =A0 0
> 2 =A0 =A0 =A0 1
>
> You could run an update immediately after the insert to set the CRC32
> column:
>
> UPDATE testtable SET id-crc=3DCRC32(LAST_INSERT_ID()) WHERE
> id=3DLAST_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@isfahan.at wr=
ote:
>
>
>
> 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` (
> =A0`id` bigint(20) unsigned NOT NULL auto_increment,
> =A0`id-crc` bigint(20) unsigned NOT NULL,
> =A0PRIMARY KEY =A0(`id`),
> =A0UNIQUE KEY `id-crc` (`id-crc`)
> ) ENGINE=3DMyISAM =A0DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D1 ;
>
>
> `id` is the unique autoincrement
>
> in `id-crc` I would like save the CRC32 from `id` (the coloumn is unique)
>
> E.G.
>
> id =A0 =A0 =A0id-crc
> --------------------
> 1 =A0 =A0 =A0 2212294583 =A0 =A0 =A0--> CRC32('1')
> 2 =A0 =A0 =A0 450215437 =A0 =A0 =A0 --> CRC32('2')
> 3 =A0 =A0 =A0 1842515611 =A0 =A0 =A0--> 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>
>
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>
--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3DbuLL@pubbs.net

Johnny Withers schrieb:
> 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.
From my understanding, a TRIGGER might do exactly what Thunder needs.
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
Ciao,
Thomas Pundt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=bull@pubbs.net

Yes, Triggers... I so rarely use them I forget they exist.
On Tue, May 5, 2009 at 10:22 AM, Thomas Pundt wrote:
> Johnny Withers schrieb:
>
>> 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.
>>
>
> From my understanding, a TRIGGER might do exactly what Thunder needs.
>
> http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
>
> Ciao,
> Thomas Pundt
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

Thank you very much for all answers!!!!
I will trying Triggers and the example with the update after an INSERT.
Ant then, I use the best for me;-)
Thunder
> Yes, Triggers... I so rarely use them I forget they exist.
>
> On Tue, May 5, 2009 at 10:22 AM, Thomas Pundt wrote:
>
>
>> Johnny Withers schrieb:
>>
>>
>>> 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.
>>>
>>>
>> From my understanding, a TRIGGER might do exactly what Thunder needs.
>>
>> http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
>>
>> Ciao,
>> Thomas Pundt
>>
>>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=buLL@pubbs.net