LAST_INSERT_ID(), LAST_INSERT_ID(expr)
来源:互联网 发布:问道手游辅助软件 编辑:程序博客网 时间:2024/05/21 17:30
原文:https://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
使用不带参数的LAST_INSERT_ID将返回第一个insert对应AUTO_INCREMENT字段的值,从MySQL 5.5.29开始该值返回为BIGINT UNSIGNED,以前的版本返回BIGINT (signed) 。如果insert执行成功但没有数据插入,则LAST_INSERT_ID返回值不变。
在MySQL 5.5.29中带参数的LAST_INSERT_ID()返回值类型为无符号整型,以前的版本返回整型。
例如:在插入一条数据后AUTO_INCREMENT字段会生成一个值,你可以使用下面的SQL拿到这个值:
mysql> SELECT LAST_INSERT_ID();
-> 195
当前正在执行的语句并不能影响LAST_INSERT_ID()的返回值。假如你通过一个语句生成了AUTO_INCREMENT的值,此时执行LAST_INSERT_ID()获取到一个值,然后执行一条插入多行数据的INSERT语句,该语句设置了AUTO_INCREMENT列的值,再次通过LAST_INSERT_ID()获取的值与前一次相同。(无论使用LAST_INSERT_ID()还是LAST_INSERT_ID(expr),都不会起效)
如果前一个语句产生了错误,通过LAST_INSERT_ID()获取的值是undefined.在事务中,如果一个语句执行错误并回滚,LAST_INSERT_ID()值将是undefined.未产生异常的回滚中(手工回滚),LAST_INSERT_ID()的值将是事务中最后一个AUTO_INCREMENT的值,而不会重置为事务开始前的初始值。
在MySQL 5.5.35前,使用MySQL 5.5.35过滤规则的函数不能获取正确的值。(Bug #17234370, Bug #69861)
在stored routine (存储过程或函数)或触发器(trigger)的方法体中,LAST_INSERT_ID()值的变化规则与在方法体外执行的规则一致。在routine执行完毕后,routine类型不同对LAST_INSERT_ID()值也会不同:
如果一个存储过程执行完后改变了LAST_INSERT_ID()的值,接下来执行的存储过程能够获取到这个值.
在存储函数和触发器中改了LAST_INSERT_ID值的话,在方法体执行完毕后LAST_INSERT_ID()的值会被重置,所以后续的语句将不能获取到这个值。
在服务器端基于每一个连接维护了一个对应的ID(LAST_INSERT_ID的结果)值。这意味着调用LAST_INSERT_ID()将返回这个连接产生的最后一个AUTO_INCREMENT值,并不会影响其它连接使用该函数的返回值,即使其它连接也生成了AUTO_INCREMENT的值。这个行为确保了每个客户端能够获取自己生成AUTO_INCREMENT的值,而不用关心其它客户端对AUTO_INCREMENT的值的影响,也不用使用锁或者是事务。
如果你将AUTO_INCREMENT列的值设为非“magic”(非NULL和0)的值,LAST_INSERT_ID()的值将不会变。
For example:
mysql>USE test;
Database changedmysql>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: 0mysql> 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 |+------------------+1 row in set (0.00 sec)
尽管第二条INSERT语句成功插入了三条数据,但是ID却是指向本次插入的第一行数据2.
如果你使用了INSERT IGNORE并且插入行被忽略,LAST_INSERT_ID()的值将不变(如果这个连接尚未成功插入数据返回值将为0),在非事务操作中,AUTO_INCREMENT计数器将不增长。在InnoDB表中,innodb_autoinc_lock_mode的值如果是1或2,AUTO_INCREMENT计数器将增长,下面举一个例子:
mysql> USE test;Database changedmysql> SELECT @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 1 |+----------------------------+1 row in set (0.00 sec)mysql> CREATE TABLE `t` (`id` INT(11) NOT NULL AUTO_INCREMENT,`val` INT(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `i1` (`val`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.02 sec)-- Insert two rowsmysql> INSERT INTO t (val) VALUES (1),(2);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0-- With auto_increment_offset=1, the inserted rows-- result in an AUTO_INCREMENT value of 3mysql> SHOW CREATE TABLE t\G*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `i1` (`val`)) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin11 row in set (0.00 sec)-- LAST_INSERT_ID() returns the first automatically generated-- value that is successfully inserted for the AUTO_INCREMENT columnmysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+| 1 |+------------------+1 row in set (0.00 sec)-- The attempted insertion of duplicate rows fail but errors are ignored mysql> INSERT IGNORE INTO t (val) VALUES (1),(2);Query OK, 0 rows affected (0.00 sec)Records: 2 Duplicates: 2 Warnings: 0-- With innodb_autoinc_lock_mode=1, the AUTO_INCREMENT counter-- is incremented for the ignored rowsmysql> SHOW CREATE TABLE t\G*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `i1` (`val`)) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)-- The LAST_INSERT_ID is unchanged becuase the previous insert was unsuccessfulmysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+| 1 |+------------------+1 row in set (0.00 sec)
更多信息查看14.11.6, “AUTO_INCREMENT Handling in InnoDB”
如果LAST_INSERT_ID()的参数是一个表达式,返回值就是表达式的结果,也是LAST_INSERT_ID()的值。我们使用下面的步骤模拟这一过程:
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);
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 toLAST_INSERT_ID()
to return the updated value. TheSELECT
statement retrieves that value. Themysql_insert_id()
C API function can also be used to get the value. See Section 23.8.7.37, “mysql_insert_id()”.
You can generate sequences without calling LAST_INSERT_ID()
, 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.
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
)
after executing other SQL statements like SELECT
or SET
.- LAST_INSERT_ID(), LAST_INSERT_ID(expr)
- LAST_INSERT_ID()
- MYSQL LAST_INSERT_ID()
- mysql last_insert_id();
- 【原创】LAST_INSERT_ID
- 关于last_insert_id
- last_insert_id函数
- mysql的last_insert_id()用法
- mysql中的LAST_INSERT_ID
- 使用MySQL的LAST_INSERT_ID
- 使用MySQL的LAST_INSERT_ID
- 使用MySQL的LAST_INSERT_ID
- MYSQL 小技巧 -- LAST_INSERT_ID
- MySQL的LAST_INSERT_ID用法
- mysql中的last_insert_id()
- MySQL的LAST_INSERT_ID用法
- mysql的 last_insert_id
- MySQL的LAST_INSERT_ID用法
- centos7 ceph安装
- java反射机制详解 及 Method.invoke解释
- Swift Json 解析异常处理
- Python装饰器
- linux挂载ISO光盘镜像
- LAST_INSERT_ID(), LAST_INSERT_ID(expr)
- 抓包Fidder
- TabLayout+Fragment
- tomcat配置,通过域名访问指定项目
- shiro整合springmvc
- [转]如何更改AD域安全策略-密码必须符合复杂性要求
- jQuery学习总结
- CDH集群迁移
- listener TNS-01189 问题