Mysql 主键自增长(AUTO_INCERMENT)

二、主键自增长（AUTO_INCREMENT）

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

 字段名 数据类型 AUTO_INCREMENT

• 默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。

• 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

• AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。

• AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。

• AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

示例1:定义数据表tb_student,指定id字段递增,SQL语句和运行结果如下:

 mysql> CREATE TABLE IF NOT EXISTS tb_student(
     -> id INT(4) PRIMARY KEY AUTO_INCREMENT,
     -> name VARCHAR(25) NOT NULL);
 Query OK, 0 rows affected (0.02 sec)
 ​

上述语句执行成功后，会创建名为 tb_student 的数据表。其中，id 为主键，每插入一条新记录，id 的值就会在前一条记录的基础上自动加 1。name 为非空字段，该字段的值不能为空值（NULL）。

向 tb_student 表中插入数据，并使用 SELECT 命令查询表中记录，SQL 语句如下所示：

 mysql> INSERT INTO tb_student(name) VALUES ('Java'),('MySQL'),('Python');
 Query OK, 3 rows affected (0.01 sec)
 Records: 3  Duplicates: 0  Warnings: 0
 ​
 #查看记录
 mysql> SELECT * FROM tb_student;
 +----+--------+
 | id | name   |
 +----+--------+
 |  1 | Java   |
 |  2 | MySQL  |
 |  3 | Python |
 +----+--------+
 3 rows in set (0.00 sec)

1.指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加。

示例2: 下面创建表 tb_student2，指定主键从 100 开始自增长。SQL 语句和运行结果如下：

 mysql> CREATE TABLE tb_student2 (
     -> id INT NOT NULL AUTO_INCREMENT,
     -> name VARCHAR(20) NOT NULL,
     -> PRIMARY KEY(ID)
     -> )AUTO_INCREMENT=100;
 Query OK, 0 rows affected (0.03 sec)

向 tb_student2 表中插入数据，并使用 SELECT 命令查询表中记录，SQL 语句如下所示：

 mysql> INSERT INTO tb_student2 (name) VALUES ('JAVA'),('PYTHON');
 Query OK, 2 rows affected (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 0
 ​
 mysql> SELECT * FROM tb_student2;
 +-----+--------+
 | id  | name   |
 +-----+--------+
 | 100 | JAVA   |
 | 101 | PYTHON |
 +-----+--------+
 2 rows in set (0.00 sec)

2.自增字段值不连续

1）唯一键冲突导致自增字段值不连续

示例1：创建数据表tb_student3，插入导致唯一键冲突的记录后，在插入数据

 mysql> CREATE TABLE tb_student3(
     -> id INT PRIMARY KEY AUTO_INCREMENT,
     -> name VARCHAR(20) UNIQUE KEY,
     -> age INT DEFAULT NULL);
 Query OK, 0 rows affected (0.02 sec)
 ​
 mysql> INSERT INTO tb_student3 VALUES(1,'1','1');
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> INSERT INTO tb_student3 VALUES(NULL,'1','1');
 ERROR 1062 (23000): Duplicate entry '1' for key 'name'
 ​
 ERROR 1062 (23000): Duplicate entry '1' for key 'name'
 mysql> INSERT INTO tb_student3 VALUES(NULL,'2','1');
 Query OK, 1 row affected (0.01 sec)
 ​
 mysql> SELECT * FROM tb_student3;
 +----+------+------+
 | id | name | age  |
 +----+------+------+
 |  1 | 1    |    1 |
 |  3 | 2    |    1 |
 +----+------+------+
 2 rows in set (0.00 sec)

由于name字段有唯一键约束，当插入相同内容的字段时，会报 Duplicate key error（唯一键冲突）。

在这之后，在插入新数据时， ，自增 id 就是 3，这样就出现了自增字段值不连续的情况。

2）删除字段导致自增字段值不连续

示例2:创建数据表tb_student4，删除新增的数据后，再次新增数据

 #创建新表
 mysql> CREATE TABLE IF NOT EXISTS tb_student4(
     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
     -> name VARCHAR(10) NOT NULL);
 Query OK, 0 rows affected (0.02 sec)
 ​
 #新增字段
 mysql> INSERT INTO tb_student4(name) VALUES('JAVA'),('PYTHON');
 Query OK, 2 rows affected (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 0
 ​
 #查看表内容
 mysql> SELECT * FROM tb_student4;
 +----+--------+
 | id | name   |
 +----+--------+
 |  1 | JAVA   |
 |  2 | PYTHON |
 +----+--------+
 2 rows in set (0.00 sec)
 ​
 #删除字段 name='PYTHON'
 mysql> DELETE FROM tb_student4 WHERE name='PYTHON';
 Query OK, 1 row affected (0.01 sec)
 ​
 #查看表内容
 mysql> SELECT * FROM tb_student4;
 +----+------+
 | id | name |
 +----+------+
 |  1 | JAVA |
 +----+------+
 1 row in set (0.00 sec)
 ​
 #插入表数据
 mysql> INSERT INTO tb_student4(name) VALUES('MYSQL'),('HTML');
 Query OK, 2 rows affected (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 0
 ​
 #查看表内容
 mysql> SELECT * FROM tb_student4;
 +----+-------+
 | id | name  |
 +----+-------+
 |  1 | JAVA  |
 |  3 | MYSQL |
 |  4 | HTML  |
 +----+-------+
 3 rows in set (0.00 sec)

可以看出，删除字段后，自增字段不会补齐而是按照既定数值继续向下排列，会导致自增数字不连续。