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?
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
______________________________________________________
The contents of this e-mail are privileged and/or confidential to the
named recipient and are not to be used by any other person and/or
organisation. If you have received this e-mail in error, please notify
the sender and delete all material pertaining to this e-mail.
______________________________________________________
---------------------------------------------------------------------
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
