Information Functions
962
The ID that was generated is maintained in the server on a per-connection basis. This means that
the value returned by the function to a given client is the first
AUTO_INCREMENT
value generated for
most recent statement affecting an
AUTO_INCREMENT
column by that client. This value cannot be
affected by other clients, even if they generate
AUTO_INCREMENT
values of their own. This behavior
ensures that each client can retrieve its own ID without concern for the activity of other clients, and
without the need for locks or transactions.
The value of
LAST_INSERT_ID()
[961]
is not changed if you set the
AUTO_INCREMENT
column
of a row to a non-“magic” value (that is, a value that is not
NULL
and not
0
).
Important
If you insert multiple rows using a single
INSERT
statement,
LAST_INSERT_ID()
[961]
returns the value generated for the first inserted
row only. The reason for this is to make it possible to reproduce easily the
same
INSERT
statement against some other server.
For example:
mysql>
USE test;
Database changed
mysql>
CREATE TABLE t (
->
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
->
name VARCHAR(10) NOT NULL
->
);
Query OK, 0 rows affected (0.09 sec)
mysql>
INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql>
SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)
mysql>
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql>
INSERT INTO t VALUES
->
(NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql>
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
