本文主要是介绍mysql 自增键的上限后异常处理,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
一般情况下对于自增键 会使用 int ,自增键上限 :2的32次方 -1(4294967295)
达到自增上限后,数据就无法继续插入,报“ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'” 错误
mysql [localhost:5724] {root} (test) > insert into t values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
mysql [localhost:5724] {root} (test) > show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=latin1
1 row in set (0.07 sec)
解决办法:修改自增键数据类型为:bigint 占8个字节,自增上限 为2的64-1
mysql [localhost:5724] {root} (test) > alter table t modify column id bigint unsigned NOT NULL AUTO_INCREMENT;
Query OK, 1 row affected (0.54 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql [localhost:5724] {root} (test) > show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [localhost:5724] {root} (test) > insert into t values(null);
Query OK, 1 row affected (0.06 sec)
mysql [localhost:5724] {root} (test) > show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967297 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
这篇关于mysql 自增键的上限后异常处理的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!