mysql 自增列 性能_MySQL自增列测试

MySQL自增列测试(DB:5.7.27 OS:CentOS7.6)

情景1：创建包含自增列的新表测试

mysql> create table test05 (id int unsigned auto_increment);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

结论：自增列必须为主键

情景2：创建包含自增列的新表，自增值直接从最大开始，添加新值

mysql> create table test06 (id int unsigned primary key auto_increment ) auto_increment=4294967295;

Query OK, 0 rows affected (0.02 sec)

或者

create table test06 (id int unsigned unique auto_increment ) auto_increment=4294967295;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test06 values();

Query OK, 1 row affected (0.00 sec)

mysql> insert into test06 values();

ERROR 1062 (23000): Duplicate entry ‘4294967295’ for key ‘PRIMARY’

mysql> select * from test06;

±-----------+

| id |

±-----------+

| 4294967295 |

±-----------+

1 row in set (0.00 sec)

mysql>

结论：自增值超过范围后会报主键重复，无法插入数据。