环境：CentOS7.6 MySQL5.7.26（RPM安装）
测试一：创建含有自增序列的表
mysql> create table test01 (id int unsigned auto_increment, name varchar(8));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table test02 (id int unsigned primary key, num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table test03 (id int unsigned auto_increment primary key, num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table test05 (id int unsigned auto_increment primary key, num int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table test06 (id int unsigned, num int auto_increment unique key);
Query OK, 0 rows affected (0.12 sec)
结论:设定自增的列必须定义为一个key，比如主键、唯一健等。
测试二：插入数据
mysql> insert into test05(id,num) values(2,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05(num) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05(id,num) values(5,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05(id,num) values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test05;
±—±-----+
| id | num |
±—±-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 5 | 1 |
±—±-----+
4 rows in set (0.00 sec)
结论:插入数据时，如果指定数据，只要不重复、满足类型范围就可以。如果不指定数据，会从最大值开始自增长。
测试三、删除数据，观察自增长列
1、delete
mysql> delete from test05;
Query OK, 5 rows affected (0.00 sec)
mysql> select * from test05;
Empty set (0.00 sec)
mysql> insert into test05(num) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05(id,num) values(3,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test05;
±—±-----+
| id | num |
±—±-----+
| 3 | 2 |
| 7 | 3 |
±—±-----+
2 rows in set (0.00 sec)
2、truncate
mysql> truncate test05;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test05(id,num) values(3,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test05(num) values(3);
Query OK, 1 row affected (0.50 sec)
mysql> select * from test05;
±—±-----+
| id | num |
±—±-----+
| 3 | 2 |
| 4 | 3 |
±—±-----+
2 rows in set (0.00 sec)
结论：使用delete后，再插入数据时，会从删除前的最大值开始自增长。
使用truncate后，再插入数据时，会从头开始自增长。
