Mysql获取最大自增ID(auto_increment)的五种方式及其特点

在关系型数据库的表结构中,一般情况下,都会定义一个具有‘AUTO_INCREMENT’扩展属性的‘ID’字段,以确保数据表的每一条记录都有一个唯一标识。
而实际应用中,获取到最近最大的ID值是必修课之一,针对于该问题,实践整理如下:

一、新建测试数据表get_max_id

mysql>CREATETABLE `get_max_id` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '业务主键',
    `content` char(25) DEFAULT NULL COMMENT '业务内容',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

二、未初始化表获取最大自增ID

创建完数据表之后,我们知道,表中的内容暂时为空,
此时,查询max(id)获取到的内容将是NULL;

方式1 - max(id):

该方式的优点是简单粗暴,直奔主题;
同时,它无视其它客户端连接(db_connection)的影响,可以直奔第3点位置;

mysql>select max(id) from get_max_id;
 +---------+
 | max(id) |
 +---------+
 |    NULL |
 +---------+
 1 row in set (0.00 sec)

方式2 - LAST_INSERT_ID()函数:

LAST_INERT_ID(),返回最后一个INSERT或 UPDATE 查询中, AUTO_INCREMENT列设置的第一个表的值。

这玩意儿的使用还是有些限制的:
1、同一个Connection连接对象(同一客户端)中,SELECT的结果为最后一次INSERT的AUTO_INCREMENT属性列的ID。这句话的重点在于“同一个”,即其他连接的客户端不对其查询的结果造成影响。假设客户端A和B,表ta原自增ID为3,在A中插入记录后产生自增ID为4,在客户端A中通过该函数查询的结果为4,但在客户端B中查询的结果值仍为3;(已验证)
2、与表无关,即假设ta表和tb表,向ta插入记录后,再向tb插入记录,结果值为tb的max(id)值;(已验证)
3、使用非魔术方法('magic')来INSERT或UPDATE一条记录时,即使用非0/非NULL值作为插入的字段,则LAST_INSERT_ID()返回值不会发生变化;(已验证)
4、同一条INSERT语句中,传入多个VALUES值,则LAST_INSERT_ID()返回值为该查询第一条记录的ID;(已验证)
5、在进阶方面,可运用作分表ID的唯一性。

初始化查询的结果,得到的是0,这点和max(id)还是有区别的;

mysql>select LAST_INSERT_ID();
 +------------------+
 | LAST_INSERT_ID() |
 +------------------+
 |                0 |
 +------------------+
 1 row in set (0.00 sec)

方式3 - 查看表状态show table status

该方式提供了当前DB(use db_name;)下每个表的基本信息;
可以通过where条件获取到Auto_increment属性的值;
下述提供的结果值,为下一个自增ID的数值。

mysql> show table status where Name='get_max_id';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options| Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| get_max_id | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |  10485760 |              1 | 2015-04-20 11:49:07 | NULL        | NULL       | utf8_general_ci |     NULL |     |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

方式4 - information_schema.tables

提供关于数据库中的表(包括视图)的信息。
详细描述了某个表属于哪个schema,表类型,表引擎等等信息;
下述提供的结果值,为下一个自增ID的数值。

mysql> select table_name, AUTO_INCREMENT from information_schema.tables where table_name="get_max_id";
+------------+----------------+
| table_name | AUTO_INCREMENT |
+------------+----------------+
| get_max_id |              1 |
+------------+----------------+
1 row in set (0.01 sec)

方式5 - @@IDENTITY全局变量

基础:以@@开头的变量为全局变量,而以@开头的变量为用户自定义的变量。

此处 @@IDENTITY表示最近一次向具有identity属性(auto_increment)的表INSERT数据时对应的自增列的值。此处得到的值是0。

1、类似于LAST_INSERT_ID()函数,该方式必须在同一个客户端内进行的INSERT与SELECT,且不受其他客户端影响;(已验证)
2、与表无关;(已验证)
3、非魔术方法插入不影响结果值;(已验证)
4、同一INSERT插入多条记录,取第一条记录的ID值为结果;(已验证)

mysql> select @@IDENTITY;
+------------+
| @@IDENTITY |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

本文转载自:Mysql获取最大自增ID(auto_increment)的五种方式及其特点

冯奎博客
请先登录后发表评论
  • latest comments
  • 总共0条评论