Information Functions
963
1 row in set (0.00 sec)
Although the second
INSERT
statement inserted three new rows into
t
, the ID generated for the
first of these rows was
2
, and it is this value that is returned by
LAST_INSERT_ID()
[961]
for the
following
SELECT
statement.
If you use
INSERT IGNORE
and the row is ignored, the
AUTO_INCREMENT
counter is not
incremented and
LAST_INSERT_ID()
[961]
returns
0
, which reflects that no row was inserted.
If
expr
is given as an argument to
LAST_INSERT_ID()
[961]
, the value of the
argument is returned by the function and is remembered as the next value to be returned by
LAST_INSERT_ID()
[961]
. This can be used to simulate sequences:
1. Create a table to hold the sequence counter and initialize it:
mysql>
CREATE TABLE sequence (id INT NOT NULL);
mysql>
INSERT INTO sequence VALUES (0);
2. Use the table to generate sequence numbers like this:
mysql>
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql>
SELECT LAST_INSERT_ID();
The
UPDATE
statement increments the sequence counter and causes the next call to
LAST_INSERT_ID()
[961]
to return the updated value. The
SELECT
statement retrieves
that value. The
mysql_insert_id()
C API function can also be used to get the value. See
Section 20.6.6.37, “
mysql_insert_id()
”
.
You can generate sequences without calling
LAST_INSERT_ID()
[961]
, but the utility of using the
function this way is that the ID value is maintained in the server as the last automatically generated
value. It is multi-user safe because multiple clients can issue the
UPDATE
statement and get their
own sequence value with the
SELECT
statement (or
mysql_insert_id()
), without affecting or
being affected by other clients that generate their own sequence values.
Note that
mysql_insert_id()
is only updated after
INSERT
and
UPDATE
statements, so you
cannot use the C API function to retrieve the value for
LAST_INSERT_ID(expr)
[961]
after
executing other SQL statements like
SELECT
or
SET
.
•
ROW_COUNT()
[963]
ROW_COUNT()
[963]
returns the number of rows changed, deleted, or inserted by the last
statement if it was an
UPDATE
,
DELETE
, or
INSERT
. For other statements, the value may not be
meaningful.
For
UPDATE
statements, the affected-rows value by default is the number of rows actually changed.
If you specify the
CLIENT_FOUND_ROWS
flag to
mysql_real_connect()
when connecting to
mysqld
, the affected-rows value is the number of rows “found”; that is, matched by the
WHERE
clause.
For
REPLACE
statements, the affected-rows value is 2 if the new row replaced an old row, because in
this case, one row was inserted after the duplicate was deleted.
For
INSERT ... ON DUPLICATE KEY UPDATE
statements, the affected-rows value is 1 if the row
is inserted as a new row and 2 if an existing row is updated.
The
ROW_COUNT()
[963]
value is similar to the value from the
mysql_affected_rows()
C API
function and the row count that the
mysql
client displays following statement execution.
mysql>
INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
SELECT ROW_COUNT();
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 ...
