2012-05-05 00:22
2018-08-15 13:06


  • 属性 serial 可以用作 bigint unsigned not null auto_increment unique 的别名。

官方文档摘录 - 13.1.18 CREATE TABLE Syntax

Source: 13.1.18 CREATE TABLE Syntax

AUTO_INCREMENT applies only to integer and floating-point types.
auto_increment 只适用于 整型 与 浮点类型。

An integer or floating-point column can have the additional attribute AUTO_INCREMENT.

When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value.
向一个已编入索引的 AUTO_INCREMENT 列中插入 null (建议) 或 0 时,此列被设置为下一个序列的值。

Typically this is value+1, where value is the largest value for the column currently in the table.
通常情况下为 value+1,此处 value 是当前在表中的列的最大值。

AUTO_INCREMENT sequences begin with 1.
AUTO_INCREMENT 序列 默认 从 1 开始。

To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.
插入行之后 要检索 AUTO_INCREMENT 值,使用 LAST_INSERT_ID() SQL 函数 或者 mysql_insert_id() C API 函数。

If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value.
如果 NO_AUTO_VALUE_ON_ZERO SQL 模式被启用,则可以将 0 存储在 AUTO_INCREMENT 列中作为 0,而无需生成新的序列值。

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.
每个表中只能有一个 AUTO_INCREMENT 列,它必须被索引,它不能有默认值。

An AUTO_INCREMENT column works properly only if it contains only positive values.
AUTO_INCREMENT 列 仅包含正值 才能 正确工作。

Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers "wrap" over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.
插入负数 被当做是 插入一个非常大的正数。这样做是为了 当数值 "包装" 从正到负时 避免精度问题,也确保你不小心 得到 一个 包含 0AUTO_INCREMENT 列。

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, "Using AUTO_INCREMENT".
对于 MyISAM 表,可以在 多列键中 指定一个 AUTO_INCREMENT 辅助列。

To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query: SELECT * FROM tbl_name WHERE auto_col IS NULL. This method requires that sql_auto_is_null variable is not set to 0.

For information about InnoDB and AUTO_INCREMENT, see Section, 'AUTO_INCREMENT Handling in InnoDB'. For information about AUTO_INCREMENT and MySQL Replication, see Section, "Replication and AUTO_INCREMENT".

The initial AUTO_INCREMENT value for the table. In MySQL 5.7, this works for MyISAM, MEMORY, InnoDB, and ARCHIVE tables.

To set the first auto-increment value for engines that do not support the AUTO_INCREMENT table option, insert a "dummy" row with a value one less than the desired value after creating the table, and then delete the dummy row.
为 不支持 AUTO_INCREMENT 表选项 的引擎 设置第一个 AUTO_INCREMENT 值,创建表之后 插入一个 值小于期望值的 "虚拟" 行,然后删除 该虚拟行。

For engines that support the AUTO_INCREMENT table option in CREATE TABLE statements, you can also use ALTER TABLE tbl_name AUTO_INCREMENT = N to reset the AUTO_INCREMENT value.

The value cannot be set lower than the maximum value currently in the column.

last_insert_id() C# 应用 代码片段

public uint Ins(TplGroup_Po po, bool isHandleNullOrDbNull = false)
    string cmdText = @"
insert into tpl_group (
) values (
select last_insert_id()
    Parameter[] prmts = {
    ulong id = DbHelper.ExeScalar<ulong>(cmdText, prmts, isHandleNullOrDbNull);
    return (uint)id;

last_insert_id() 函数

  • select last_insert_id()
  • 返回 单个会话中最近一次执行的 insert 语句时表的自增 id 的值。不受其他的会话插入影响。
  • 注意: 当一次插入多条记录时,只是获得第一次插入的id值
  • 该函数与表无关。这意味着 如果向表 a 插入数据后,再向表 b 插入数据,last_insert_id() 会改变。
  • 该函数基于连接,只要每个线程都使用独立的连接对象,该函数将返回该连接对 auto_increment 列最新的 insert or update 所生成的第一个记录的 id。不会被其它 客户端/连接 影响,不需要加锁。
  • 一个 insert 语句插入多条记录时 该函数 返回一个列表。


  • 服务器系统变量 last_insert_id & identity 的值 从 LAST_INSERT_ID() 返回。
  • 服务器系统变量 sql_auto_is_null 被设置为 1 时,当 成功插入 一个自动生成 AUTO_INCREMENT 值 的 语句 之后,执行某语句返回 1 行,该返回值 与 你调用 LAST_INSERT_id() 函数 相同。
  • 2018-01-16 测试记录: 对于 Id int unsigned auto_increment primary key comment '主键' 列,用 object obj = _cmd.ExecuteScalar(); 获取 select last_insert_id() 的值,其数据类型为 ulong


  • 该函数基于连接。这意味着 该函数返回的 自增列的值,在同一连接中是确定的、不 在同一连接中 是不确定的。
  • 该函数与表无关。这意味着 向多个表插入行之后,该函数返回最后被插入的表的自增列的值。
  • 单个插入语句插入多行,该函数返回第一行的 自增列的值。

官方文档摘录 - 信息函数 LAST_INSERT_ID(), LAST_INSERT_ID(expr)

12.14 Information Functions

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.
无参 LAST_INSERT_ID() 返回一个 BIGINT UNSIGNED (64-bit) 值,该值 代表 第一个 被自动生成的值 成功被插入 一个 AUTO_INCREMENT 列,作为 最近被执行的 INSERT 语句 的 结果。

The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.
如果 没有行 被成功插入,LAST_INSERT_ID() 的值 保持不变。

With an argument, LAST_INSERT_ID() returns an unsigned integer.
有参 LAST_INSERT_ID() 返回一个无符号整数。

For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

        -> 195

The currently executing statement does not affect the value of LAST_INSERT_ID().
当前正在执行的语句不影响 LAST_INSERT_ID() 的值。

Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column.
假设 你用一个语句 生成一个 AUTO_INCREMENT 值,然后 适用 LAST_INSERT_ID() 在 一个多行插入语句 (它插入多行至一个表,该表 有它自已的 AUTO_INCREMENT 列)。

The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions.
LAST_INSERT_ID() 的值 将保持 在第二个语句; 第二 与 随后的行 的值 不被 早前插入的行 影响。

(However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)
然而,如果你 混合引用 LAST_INSERT_ID()LAST_INSERT_ID(expr),影响 是 未定义的。

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined.
如果 先前的 语句 返回 一个错误,LAST_INSERT_ID() 的值 是 未定义的。

For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined.
对于 事务表,如果 语句 由于错误 被回滚,LAST_INSERT_ID() 的值 是 未定义的。

For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.
对于 手动 ROLLBACKLAST_INSERT_ID() 的值 不被恢复 到 事务前; 它保持 在回滚点。

The ID that was generated is maintained in the server on a per-connection basis.
基于每个连接,生成的 ID 被维持 在服务器里。

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.
这意味着 该函数返回给指定的客户端的值 是 被客户端 最近的语句 影响的 AUTO_INCREMENT 列 生成 第一个 AUTO_INCREMENT 值。

This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own.
这个值 不能 被 其它客户端 影响,即使 他们自己生成 AUTO_INCREMENT 值。

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.
这种行为 确保 每个客户端 能 检索 它自已的 ID,不担心 其它客户端 的 活动,不需要 锁 或 事务。

The value of LAST_INSERT_ID() 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).
LAST_INSERT_ID() 的值 不被改变,如果你设置 某行的 AUTO_INCREMENT 列 为 "非魔法值" (即 一个值 它 非 NULL 且 非 0)。

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() 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.
如果使用单个插入语句 插入多行,LAST_INSERT_ID() 仅 返回 为 第一个被插入行 生成的值。这样做的原因是 为了能够 对其它服务器 容易地复制 相同的 INSERT 语句。

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:
如果 expr 作为参数 提供给 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(); -- 返回 "id+1"

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.
你可以 不调用 LAST_INSERT_ID() 生成序列,但是 使用这个函数的功用 这种方法 这个 ID 值 被 被维持 在服务器里 作为最后自动生成的值。它是 多用户 安全的 因为 多个客户端 能执行 UPDATE 语句 而且 用 SELECT 语句 (或者 mysql_insert_id()) 获得 它们自已的序列值,不 被其它客户端 影响或被影响 生成它们自已的序列值。

官方文档摘录 - 服务器系统变量 last_insert_id

Source: last_insert_id

The value to be returned from LAST_INSERT_ID().
该变量的值 从 LAST_INSERT_ID() 返回。

This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table.
当你 在 更新表的语句中 使用 LAST_INSERT_ID() 时,这将被存储 在 二进制日志中。

Setting this variable does not update the value returned by the mysql_insert_id() C API function.
设置此变量 不会更新 mysql_insert_id() C API 函数 返回的值。

TonyRemark: 服务器系统变量 last_insert_id & identity 的作用域 不是 全局 (global) 而是 会话 (session)。

@@identity 变量

  • select @@identity
  • 需要在插入后调用。
  • last_insert_id() 功能相似,优缺点相同。
  • 表示 最近一次向具有 identity 属性 (即自增列) 的表插入数据时对应的自增列的值。
  • 该变量是系统定义的全局变量。通常系统定义的全局变量 均以 @@ 开头,用户自定义变量 均以 @ 开头。
  • 使用 @@identity 的前提是 insert 后连接尚未关闭,否则 select @@identity 将返回 null
  • 例如 某个表有一个自增列,当向该表插入一行数据后,如果该自增列的值自动增加至 101,那么 select @@identity 的返回值就是 101

官方文档摘录 - 服务器系统变量 identity

Source: identity

This variable is a synonym for the last_insert_id variable.
该变量 是 "last_insert_id 服务器系统变量" 的同义词。

It exists for compatibility with other database systems.
它的存在 是为了 与 其它数据库系统 兼容。

You can read its value with SELECT @@identity, and set it using SET identity.
你可以用 SELECT @@identity 读取其值,并使用 SET identity 设置它。

TonyRemark: 服务器系统变量 last_insert_id & identity 的作用域 不是 全局 (global) 而是 会话 (session)。

官方文档摘录 - 服务器系统变量 sql_auto_is_null

Source: sql_auto_is_null

变量作用域 为 全局 (global) 或者 会话 (session)。
允许值 的数据类型为 boolean、默认值为 0

If this variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:
如果该变量被设置为 1,则 成功插入 一个自动生成 AUTO_INCREMENT 值 的 语句 之后,你可以 通过执行 如下形式 的语句 找到 那个值:
SELECT * FROM tbl_name WHERE auto_col IS NULL

If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function.
如果该语句返回 1 行,该返回值 与 你调用 LAST_INSERT_id() 函数 相同。

For details, including the return value after a multiple-row insert, see Section 12.14, "Information Functions".
有关详细信息,包括 多行插入后的 返回值,参见 Section 12.14, "Information Functions"。

If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.
如果 没有 AUTO_INCREMENT 值 被成功插入,该 SELECT 语句返回 0 行。

The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison is used by some ODBC programs, such as Access.
通过使用 "IS NULL 比较" 检索 AUTO_INCREMENT 值 的 行为,被用于 ODBC 程序,例如 Access。

This behavior can be disabled by setting sql_auto_is_null to 0.
通过 设置 sql_auto_is_null0,该行为 能被禁用。

The default value of sql_auto_is_null is 0.

Tony 对该变量的理解

8.1.1 Obtaining Auto-Increment Values 中提及:

获得 自增列的值 有几种不同的方法。

方法一: 从 MySQL 服务器 返回 自增列的值,供 客户端代码 使用。

INSERT INTO tbl (auto,text) VALUES(NULL,'text');

方法二: 客户端程序中不需要 自增列的值,但是 MySQL 服务器端 需要使用该值。

INSERT INTO tbl (auto,text) VALUES(NULL,'text');
INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');

方法三: 与 sql_auto_is_null 有关
Certain ODBC applications (including Delphi and Access) may have trouble obtaining the auto-increment value using the previous examples. In this case, try the following statement as an alternative:
某些 ODBC 应用程序 (包括 Delphi 和 Access) 可能难以 使用前面的示例 获得 自增值。在这种情况下,尝试以下语句作为替代:


This alternative method requires that sql_auto_is_null variable is not set to 0.
此替代方法 要求 该变量 不设置为 0

max(id) 函数

select max(id) from tb_name
返回指定表的最大 id 值。
获取的不是真正的自增 id,是表中最大的 id,如果有删除数据的话,那么该值和自增 id 相差比较大。如果有连表数据,有可能导致数据错乱。
高并发情况下,必须加上 'X' 锁才能获取会话当前的最大 id,显然加锁会严重影响并发性能。


  • 数据量大时,select max(id) 语句效率较低。
  • 并发操作时,select max(id) 返回的不一定是刚才插入的记录,有可能是另一个插入的记录。


show table status 命令

能够准确的查到自增 id。而且可以在语句后面加上 where 语句或者 like 语句来过滤。

Example - 显示 表的属性 - show table status

结果中有一个 auto_increment 字段:

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-16 15:13:31
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
1 row in set (0.00 sec)


MySQL 表的相关信息存储在 information_schema 中。
所以我们参考 SHOW TABLE STATUS来构建查询语句。如:
select auto_increment from information_schema.tables where table_schema=‘db_name’ and table_name=‘table_name’

select auto_increment           from information_schema.tables where table_name='a' --获取下一自增列值
select (auto_increment-1) as id from information_schema.tables where table_name='a' --获取当前自增列值

auto_increment 重置

truncate table

删除表的数据,释放空间,并且重置字自增 id,但不会删除表的定义。

alter table tb_name auto_increment = n

不会清空数据,有可能会出现重复 key,因此仅适用于清空表之后重置自增 id 或者大量删除后修改自增 id。

使用修改标识:dbcc checkident (‘table_name’ , reseed, new_reseed_value)
说明:new_reseed_value是设置的当前标识,下次插入则从new_reseed_value+1开始。如果new_reseed_value小于表中当前标识的值,有可能会产生重复key,会产生 2627 号错误信息。

InnoDB 存储引擎 的 自动增长列

  • 该列可以手工插入,但是插入的值如果是空或者 0,则实际插入的将是自动增长后的值。
  • 该列的初始值默认从 1 开始。
  • 可以通过 alter table tb_name auto_increment = n 强制设置该列的初始值。
  • 该列必须是索引。如果是组合索引,也必须是组合索引的第一列。但 MyISAM 表的自动增长列可以是组合索引的其它列。

《_MySQL_5.5中文参考手册.pdf》 摘录 AUTO_INCREMENT列在InnoDB里如何工作

InnoDB使用下列算法来为包含一个名为ai_col的AUTO_INCREMENT列的表T初始化自动增长计数器:服务器启动之后,当一个用户对表T做插入之时,InnoDB执行等价如下语句的动作:SELECT MAX(ai_col) FROM T FOR UPDATE;
语句取回的值逐次加一,并被赋给列和自动增长计数器。如果表是空的,值1被赋予该列。如果自动增长计数器没有被初始化,而且用户调用为表T显示输出的SHOW TABLE STATUS语句,则计数器被初始化(但不是增加计数)并被存储以供随后的插入使用。注意,在这个初始化中,我们对表做一个正常的独占读锁定,这个锁持续到事务的结束。
在CREATE TABLE和ALTER TABLE语句中,InnoDB支持AUTO_INCREMENT = n 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因,这个选项的影响在服务器重启后就无效了。


你可以使用LAST_INSERT_ID()SQL函数或mysql_insert_id() C API函数来查询最新的AUTO_INCREMENT值。
对于MyISAM和BDB表,你可以在第二栏指定AUTO_INCREMENT以及多列索引。此时,AUTO_INCREMENT列生成的值的计算方法为:MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。如果想要将数据放入到排序的组中可以使用该方法。

13.1.5 CREATE TABLE语法" 书摘

整型列 可以有附加属性 AUTO_INCREMENT。AUTO_INCREMENT 列的数据类型必须为整型。向一个已编入索引的 AUTO_INCREMENT 列中插入 NULL (建议) 或 0 时,此列被设置为下一个序列的值。通常情况下为 value + 1,此处 value 是当前在表中的列的最大值。AUTO_INCREMENT 序列从 1 开始。

Resource - Official Manual

  1. 13.1.18 CREATE TABLE Syntax
  2. 3.6.9 Using AUTO_INCREMENT - You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. These functions are connection-specific, so their return values are not affected by another connection which is also performing inserts.
  3. AUTO_INCREMENT Handling in InnoDB
  4. Replication and AUTO_INCREMENT
  5. 4.6.4 Retrieving AUTO_INCREMENT Column Values through JDBC
  6. mysql_insert_id() - mysql_insert_id(), LAST_INSERT_ID()
  7. LAST_INSERT_ID(), LAST_INSERT_ID(expr) - Value of the AUTOINCREMENT column for the last INSERT. SELECT LAST_INSERT_ID()
  8. How to Get the Unique ID for the Last Inserted Row
  9. 23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
  10. Obtaining Auto-Increment Values


  1. MySQL auto_increment 详解
  2. 修改 auto_increment 值时遇到的问题 - 如果修改的目标值小于最大值,在 InnoDB 引擎下 数据库不会有任何变化,在 MyISAM 引擎下 AUTO_INCREMENT 会被设置为该最大值。
  4. MySQL 获取最大自增 ID (auto_increment) 的五种方式及其特点