Chinaunix首页 | 论坛 | 博客
  • 博客访问: 528584
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2017-07-11 20:59:03

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址: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!
阅读(1101) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~