At 11:44 AM +1200 10/1/01, marcus davy wrote:
>If you specify the keyword IGNORE in an INSERT, any rows that duplicate
>an existing PRIMARY or UNIQUE key in the table are ignored and are not
>inserted.
>But the last_insert_id() function still appears to increment by one
>in situations when the query is not adding any new information to
>the table.
>
>This looks like a bug to me can anyone enlighten me to this anomaly?
Why is it a bug?
INSERT IGNORE can't fully assess whether the record is to be ignored
until the record's contents have been generated.
>
>I am using 3.23.42-log on red hat 7.1 (also same results on 3.23.40-log).
>I have searched the list archive for this topic but havent found anything yet.
>
>
>mysql commands to test this below
>
>CREATE DATABASE test;
>use test;
>CREATE TABLE organism (
> OM varchar(100) NOT NULL unique,
> OM_ID tinyint(1) NOT NULL auto_increment,
> PRIMARY KEY (OM_ID)
> ) TYPE=MyISAM;
>
>INSERT IGNORE INTO organism VALUES('foo', NULL);
>INSERT IGNORE INTO organism VALUES('fodda', NULL);
>SELECT * FROM organism;
>
>#+-------+-------+
>#| OM | OM_ID |
>#+-------+-------+
>#| foo | 1 |
>#| fodda | 2 |
>#+-------+-------+
>#2 rows in set (0.00 sec)
>
>SELECT last_insert_id();
>
>#+------------------+
>#| last_insert_id() |
>#+------------------+
>#| 2 |
>#+------------------+
>#1 row in set (0.00 sec)
>
>INSERT IGNORE INTO organism VALUES('fodda', NULL);
>
>#Query OK, 0 rows affected (0.00 sec)
># NO NEW INFORMATION ADDED
>
>SELECT * FROM organism;
>#+-------+-------+
>#| OM | OM_ID |
>#+-------+-------+
>#| foo | 1 |
>#| fodda | 2 |
>#+-------+-------+
>#2 rows in set (0.01 sec)
>
>SELECT last_insert_id();
>
>#+------------------+
>#| last_insert_id() |
>#+------------------+
>#| 3 |
>#+------------------+
>#1 row in set (0.01 sec)
>
># last_insert_id has incremented by 1, by adding a further new field,
># last_insert_id() will correctly show the last inserted id again
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
