三十七、MySQL 自增序列 AUTO_INCREMENT

文章永久连接：https://tech.souyunku.com/?p=4931

MySQL 自增序列是一组整数：1, 2, 3, …

一张数据表只能有一个自增主键

如果你想实现其它字段也实现自动增加，可以使用 MySQL 序列来实现

AUTO_INCREMENT

MySQL 定义序列最简单的方法就是使用 AUTO_INCREMENT 来定义列

比如我们前面创建 tbl_language 表的语句中就把 id 设定为一个自增主键

CREATE TABLE IF NOT EXISTS `tbl_language`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(64) NOT NULL,
   `url` VARCHAR(128) NOT NULL,
   `founded_at` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

那么在插入数据时无需指定字段 id 的值，每插一条数据，它就会自增 1

MariaDB [souyunku]> TRUNCATE tbl_language;
Query OK, 0 rows affected (0.02 sec)

MariaDB [souyunku]> INSERT INTO `tbl_language` (name,url,founded_at) VALUES ('Python','https://tech.souyunku.com','1991-2-20');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> INSERT INTO `tbl_language` (name,url,founded_at) VALUES ('PHP','http://www.php.net','1994-1-1');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> INSERT INTO `tbl_language` (name,url,founded_at) VALUES ('Ruby','https://www.ruby-lang.org/','1996-12-25');
Query OK, 1 row affected (0.01 sec)

MariaDB [souyunku]> SELECT * FROM tbl_language;
+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://tech.souyunku.com        | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
+----+--------+----------------------------+------------+
3 rows in set (0.00 sec)

可以看到每条数据的 id 值都会自增

重置序列

如果删除了数据表中的多条记录，并希望对剩下数据的 AUTO_INCREMENT 列进行重新排列，那么可以通过删除自增的列，然后重新添加来实现

不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱

MariaDB [souyunku]> ALTER TABLE tbl_language DROP id;
MariaDB [souyunku]> ALTER TABLE tbl_language
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

这个操作方法非常危险，我们不推荐使用，尤其是多表情况下，会造成数据混乱

但是，处女座的我们，真的，真的是不能容忍有没有

设置序列的开始值

默认情况下，AUTO_INCREMENT 列的默认初始值为 1

如果需要指定其它值，比如 1000, 那么可以通过添加 auto_increment=1000 来实现，就像下面这样

CREATE TABLE IF NOT EXISTS `tbl_language`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(64) NOT NULL,
   `url` VARCHAR(128) NOT NULL,
   `founded_at` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB auto_increment=1000 DEFAULT CHARSET=utf8mb4;

或者可以在表创建成功后，通过运行下面的语句来实现

ALTER TABLE tbl_language AUTO_INCREMENT = 100;

AUTO_INCREMENT 保存在哪里 ?

AUTO_INCREMENT 是表的元数据，表的元数据都保存在 information_schema.tables 表里

我们使用下面的语句查看下 information_schema.tables 表有什么字段

MariaDB [souyunku]> DESC information_schema.tables;
+-----------------+---------------------+------+---------|
| Field           | Type                | Null | Default |
+-----------------+---------------------+------+---------+
| TABLE_CATALOG   | varchar(512)        | NO   |         |
| TABLE_SCHEMA    | varchar(64)         | NO   |         |
| TABLE_NAME      | varchar(64)         | NO   |         |
| TABLE_TYPE      | varchar(64)         | NO   |         |
| ENGINE          | varchar(64)         | YES  | NULL    |
| VERSION         | bigint(21) unsigned | YES  | NULL    |
| ROW_FORMAT      | varchar(10)         | YES  | NULL    |
| TABLE_ROWS      | bigint(21) unsigned | YES  | NULL    |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  | NULL    |
| DATA_LENGTH     | bigint(21) unsigned | YES  | NULL    |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  | NULL    |
| INDEX_LENGTH    | bigint(21) unsigned | YES  | NULL    |
| DATA_FREE       | bigint(21) unsigned | YES  | NULL    |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  | NULL    |
| CREATE_TIME     | datetime            | YES  | NULL    |
| UPDATE_TIME     | datetime            | YES  | NULL    |
| CHECK_TIME      | datetime            | YES  | NULL    |
| TABLE_COLLATION | varchar(32)         | YES  | NULL    |
| CHECKSUM        | bigint(21) unsigned | YES  | NULL    |
| CREATE_OPTIONS  | varchar(2048)       | YES  | NULL    |
| TABLE_COMMENT   | varchar(2048)       | NO   |         |
+-----------------+---------------------+------+---------+

可以看到 TABLE_NAME 和 AUTO_INCREMENT 两列

我们使用下面的语句来查看下 tbl_language 表的元数据

SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'tbl_language'\G;

运行结果如下

MariaDB [souyunku]> SELECT * FROM information_schema.tables WHERE TABLE_NAME = 'tbl_language'\G;
*********************** 1. row ***********************
  TABLE_CATALOG: def
   TABLE_SCHEMA: souyunku
     TABLE_NAME: tbl_language
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 5461
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 4
    CREATE_TIME: 2018-04-09 08:58:12
    UPDATE_TIME: 2018-04-09 09:09:10
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
1 rows in set (0.01 sec)

可以看到自增值为 4 ，而数据只有 3，哈哈，还记得创建表时默认为 1 吗？

它其实是先取值，然后再自增 +1

干货推荐

本站推荐：精选优质专栏

附录：MySQL 教程：系列文章

• 一、MySQLMariaDB 基础教程
• 二、MySQL 简介
• 三、MySQL MariaDB 安装
• 四、MySQL 管理
• 五、MySQL 日常管理
• 六、MySQL PHP 语法
• 七、MySQL 创建连接
• 八、MySQL 获取数据库列表
• 九、MySQL 创建数据库
• 十、MySQL 删除数据库
• 十一、MySQL 选择数据库
• 十二、MySQL 数据类型
• 十三、MySQL 列出数据表
• 十四、MySQL 创建数据表
• 十五、MySQL 删除表
• 十六、MySQL 插入数据
• 十七、MySQL 获取插入数据的 ID
• 十八、MySQL SELECT FROM 查询数据
• 十九、MySQL WHERE 子句有条件的查询数据
• 二十、MySQL UPDATE 更新数据
• 二十一、MySQL DELETE FROM 语句删除数据
• 二十二、MySQL 返回删改查受影响的行数
• 二十三、MySQL LIKE 子句模糊查询数据
• 二十四、MySQL UNION 操作符查询多张表
• 二十五、MySQL ORDER BY 排序
• 二十六、MySQL GROUP BY 分组查询数据
• 二十七、MySQL JOIN 进行多表查询
• 二十八、MySQL NULL 值处理
• 二十九、MySQL REGEXP 子句正则表达式查询
• 三十、MySQL 数据库事务
• 三十一、MySQL ALTER 命令
• 三十二、MySQL 索引
• 三十三、CREATE TEMPORARY TABLE 创建临时表
• 三十四、MySQL DROP TABLE 删除临时表
• 三十五、MySQL INSERT INTO SELECT 复制表
• 三十六、MySQL 获取服务器元数据
• 【当前读到】三十七、MySQL 自增序列 AUTO_INCREMENT
• 三十八、MySQL 处理重复数据
• 三十九、MySQL 安全及防止 SQL 注入攻击
• 四十、MySQL 导出数据
• 四十一、MySQL 导入数据