Tips:MySQL表主键自增增长问题

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5767466.html


1.首先来看，表对自增列的增长定义

mysql> CREATE TABLE `t1` (
    ->   `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `F_INFO_FULLNAME` varchar(150) DEFAULT '' COMMENT '名称\n',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=86519 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)


mysql> insert into t1 values('zhangsan');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> 
mysql> insert into t1 values('','zhangsan');
Query OK, 1 row affected, 1 warning (0.00 sec)

观察到
mysql> select * from t1;
+-------+-----------------+
| ID    | F_INFO_FULLNAME |
+-------+-----------------+
| 86519 | zhangsan        |
+-------+-----------------+
1 row in set (0.00 sec)


mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `F_INFO_FULLNAME` varchar(150) DEFAULT '' COMMENT '名称\n',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=86520 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

以上说明：show create table的时候，AUTO_INCREMENT指的是自增的下一次值

2.我们来寻找自增的最大值

mysql> insert into t1 values('2147483647','lee');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t1;
+------------+-----------------+
| ID         | F_INFO_FULLNAME |
+------------+-----------------+
|      86519 | zhangsan        |
| 2147483647 | lee             |
+------------+-----------------+
2 rows in set (0.00 sec)


mysql> insert into t1 values('2147483648','lie');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t1;
+------------+-----------------+
| ID         | F_INFO_FULLNAME |
+------------+-----------------+
|      86519 | zhangsan        |
| 2147483647 | lee             |
| 2147483648 | le              |
+------------+-----------------+
3 rows in set (0.00 sec)


mysql> insert into t1 values('8147483648','yang');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t1;
+------------+-----------------+
| ID         | F_INFO_FULLNAME |
+------------+-----------------+
|      86519 | zhangsan        |
| 2147483647 | lee             |
| 2147483648 | le              |
| 4294967295 | yang            |
+------------+-----------------+
4 rows in set (0.00 sec)

这里我找到了最大值，我们再继续插入：
mysql> insert into t1 values('4294967296','yang');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
mysql> insert into t1 values('4294967297','yang');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

在插入比最大id还要大的值的时候报错信息，为主键重复。但是可以插入比自增列最大值小的值。
mysql> insert into t1 values('7','yd');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t1;
+------------+-----------------+
| ID         | F_INFO_FULLNAME |
+------------+-----------------+
|          7 | yd              |
|      86519 | zhangsan        |
| 2147483647 | lee             |
| 2147483648 | le              |
| 4294967295 | yang            |
+------------+-----------------+
5 rows in set (0.00 sec)


3.对表进行清理后自增列值的变化
在自增列的增长值达到了最大值，清理表数据，如果使用delete的话
mysql> insert into t1 values('','yy');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
mysql> delete from t1;  ---删除表所有数据
Query OK, 5 rows affected (0.00 sec)


mysql> select * from t1;
Empty set (0.00 sec)


mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `F_INFO_FULLNAME` varchar(150) DEFAULT '' COMMENT '名称\n',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 AUTO_INCREMENT的值并没有变化，事实上

mysql> insert into t1 values('','yd');
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'ID' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select * from t1;
+------------+-----------------+
| ID         | F_INFO_FULLNAME |
+------------+-----------------+
| 4294967295 | yd              |
+------------+-----------------+
1 row in set (0.00 sec)


mysql> 
mysql> insert into t1 values('','yang');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

说明：delete数据并不能改变自增列的自增值

那么换成truncate进行清理表的数据的话。

mysql> truncate table t1;
Query OK, 0 rows affected (0.03 sec)


mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `F_INFO_FULLNAME` varchar(150) DEFAULT '' COMMENT '名称\n',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> insert into t1 values('','yang');
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> select * from t1;
+----+-----------------+
| ID | F_INFO_FULLNAME |
+----+-----------------+
|  1 | yang            |
+----+-----------------+
1 row in set (0.00 sec)


mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `F_INFO_FULLNAME` varchar(150) DEFAULT '' COMMENT '名称\n',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> select * from t1;
+----+-----------------+
| ID | F_INFO_FULLNAME |
+----+-----------------+
|  1 | yang            |
|  2 | yy              |
+----+-----------------+
2 rows in set (0.00 sec)

以上可见自增从头开始了。


总之，我们建议：
1.如果表的数据增长很频繁的话，建议自增列数据类型使用bigint。这是因为
  int范围：1-4294967295
  bigint的范围：1-18446744073709551615
2.某些特殊业务场景下，使用truncate代替delete可以重置自增初始值。
3.还可以直接重置自增起始值：AUTO_INCREMENT，为想要的值(有限制)。

--The end!