Mysql 自动递增(AUTO INCREMENT) 资料

使用

AUTO_INCREMENT属性可用于为新行生成唯一标识

没有为该AUTO_INCREMENT 列指定值，因此 MySQL 自动分配了序列号。NO_AUTO_VALUE_ON_ZERO 除非启用 SQL 模式，否则还可以显式为列分配 0 以生成序列号 。

INSERT INTO animals (id,name) VALUES(0,'groundhog');
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

当将任何其他值插入 AUTO_INCREMENT列时，该列将设置为该值并重置序列，以便下一个自动生成的值从最大的列值开始按顺序排列。

INSERT INTO animals (id,name) VALUES(100,'rabbit');
-- 从101开始
INSERT INTO animals (id,name) VALUES(NULL,'mouse');

检索最近自动生成AUTO_INCREMENT值

可以使用SQL 函数或C API 函数检索

SELECT LAST_INSERT_ID();

设置1以外的值开始

更新现有AUTO_INCREMENT列值也会重置AUTO_INCREMENT 序列，
只能将自增计数器值更改为大于当前最大值的值

ALTER TABLE tbl AUTO_INCREMENT = 100;

若要重新设置值小于当前最大值，可先

• TRUNCATE TABLE
  • 完全清空一张桌子
• DELETE FROM
  • 删除部分或全部记录

InnoDB AUTO_INCREMENT 计数器初始化

ALTER TABLE … AUTO_INCREMENT = N只能将自增计数器值更改为大于当前最大值的值。

5.7版本及之前

自动增量计数器存储在主内存中，而不是磁盘上。 要在服务器重新启动后初始化自动增量计数器，InnoDB将在第一次插入包含 AUTO_INCREMENT列的表时执行与以下语句等效的语句。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

服务器重新启动会取消AUTO_INCREMENT = Ntable 选项的效果，该选项可在CREATE TABLEor ALTER TABLE语句中分别用于设置初始计数器值或更改现有计数器值。

在ROLLBACK 操作后立即重新启动服务器可能会导致重用先前分配给回滚事务的自动增量值，从而有效地回滚当前最大的自动增量值。

8.0版本

服务器重启不会取消 AUTO_INCREMENT = Ntable 选项的效果。如果将自动增量计数器初始化为特定值，或者将自动增量计数器值更改为更大的值，则新值会在服务器重新启动时保持不变。

在ROLLBACK 操作后当前最大的自动增量值被持久化，防止重复使用以前分配的值。

查询表的大量信息

-- db_name：数据库名
-- 'pattern'：数据表名
SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

查询有关数据库中表的信息

INFORMATION_SCHEMA TABLES 表

-- 'db_name'：数据库名
-- 'wild'：数据表名
SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

查询下一个自动递增的值

SELECT
    AUTO_INCREMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db1'
  [AND table_name LIKE 'test']