之前在论坛上看到一个有意思的问题,如果有一个字段的值超过bigint,会发生什么。
然后就看到有人喷,说0.1秒插入一个值,将bigint设置为无符号,插入到极限需要多久,根本不可能遇到如何如何。
(接下来会做个测试)
好了,进入正文:
首先需要知道一个概念,如果在定义的时候,设置UNSIGNED可以将范围扩大一倍,该值意味着此字段无符号(即不包含负数)。
根据文档可知,数据类型有五种,分别为
TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER),BIGINT。

其值的范围,同样查阅官方文档可知
TINYINT为 -128~127 无符号数则为127-(-128)=255(2^8-1),因为非负,所以从0开始即0~255,以此类推:
SMALLINT为 -32768~32767 无符号数则为0~65535(2^16-1)
MEDIUMINT为 -8388608~8388607 无符号数则为0~16777215(2^24-1)
INT为-2147483648~2147483647 无符号数则为0~4294967295(2^32-1)
BIGINT为-9223372036854775808~9223372036854775807 无符号数则为0~18446744073709551615(2^64-1)
当然光是INT就已经达到了20亿以上的数量级,十分够用。
下表来自MySQL 5.6官方手册
其存储所需容量也可以看出,由小到大分别为
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BIGINT 8字节
当然上述数据类型及范围好像在sql server中也是适用的,同时也包括一些程序设计语言。
回到开始的话题,如果超过BIGINT的范围怎么办?
其实mysql的处理方法和超过TINYINT一样:
此处测试均为MySQL 5.6版本(社区版),存储引擎为默认的InnoDB。
首先插入255上限值,然后我做了如下两个操作:
1、插入256,报错ERROR 1264,即超过ID列的上限值。
2、插入NULL,此处应该自增,报错ERROR 1062,即由于主键约束,故无法重复存在。
其报错结果可以轻易看出,256是不能够被插入的(废话)
处理AUTO_INCREMENT约束时,即便遇到数值类型的上限值,仍然会尝试插入,此时插入的值是上限值,即255。
此处可以看出此处的AUTO_INCREMENT=255,而不是256(即便255已经存在)。
注,此处(ENGINE=InnoDB后面)的AUTO_INCREMENT为MySQL认为该表的下一个自增字段的值。
才
当然既然开始了,还是要做一下BIGINT的实验。
同样插入BIGINT UGSIGNED的上限值,然后继续做如下两个操作:
1、插入18446744073709551616,报错ERROR 1264,即超过ID列的上限值。(与实验1的报错结果一致)
2、插入NULL,此处报错与之前不同,为ERROR 1467。
ERROR 1467官方解释为:
搜了一下google,好像还真有人在实际运用中遇到这种问题,有个小哥提出了如下的方法:
After some searching i found the answer and it solved my problem.
run this sql query it will fix the problem
ALTER TABLE `YOUR_TABLE` AUTO_INCREMENT = 1
我也做了如下操作,但是…… 发现并未生效。
额外两个tips:
【1】由于TIMESTAMP用的其实是INT来存储,故从1970年1月1日开始往后顺延2147483647s,约24855天,即68年(到2038年)。
关于TIMESTAMP,官方给出的范围是:
'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.
【2】
MySQL提供了一个叫SERIAL的”类型“
实际上是unsigned NOT NULL AUTO_INCREMENT UNIQUE的别名:
然后就看到有人喷,说0.1秒插入一个值,将bigint设置为无符号,插入到极限需要多久,根本不可能遇到如何如何。
(接下来会做个测试)
好了,进入正文:
首先需要知道一个概念,如果在定义的时候,设置UNSIGNED可以将范围扩大一倍,该值意味着此字段无符号(即不包含负数)。
根据文档可知,数据类型有五种,分别为
TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER),BIGINT。

其值的范围,同样查阅官方文档可知
TINYINT为 -128~127 无符号数则为127-(-128)=255(2^8-1),因为非负,所以从0开始即0~255,以此类推:
SMALLINT为 -32768~32767 无符号数则为0~65535(2^16-1)
MEDIUMINT为 -8388608~8388607 无符号数则为0~16777215(2^24-1)
INT为-2147483648~2147483647 无符号数则为0~4294967295(2^32-1)
BIGINT为-9223372036854775808~9223372036854775807 无符号数则为0~18446744073709551615(2^64-1)
当然光是INT就已经达到了20亿以上的数量级,十分够用。
下表来自MySQL 5.6官方手册
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BIGINT 8字节
当然上述数据类型及范围好像在sql server中也是适用的,同时也包括一些程序设计语言。
回到开始的话题,如果超过BIGINT的范围怎么办?
其实mysql的处理方法和超过TINYINT一样:
此处测试均为MySQL 5.6版本(社区版),存储引擎为默认的InnoDB。
- mysql> CREATE TABLE a (id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT);
- Query OK, 0 rows affected (0.25 sec)
- mysql> INSERT INTO a SELECT 255;
- Query OK, 1 row affected (0.04 sec)
- Records: 1 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO a SELECT 256;
- ERROR 1264 (22003): Out of range value for column 'id' at row 1
- mysql> INSERT INTO a SELECT NULL;
- ERROR 1062 (23000): Duplicate entry '255' for key 'PRIMARY'
- mysql> SELECT * FROM a;
- +-----+
- | id |
- +-----+
- | 255 |
- +-----+
- 1 row in set (0.00 sec)
1、插入256,报错ERROR 1264,即超过ID列的上限值。
2、插入NULL,此处应该自增,报错ERROR 1062,即由于主键约束,故无法重复存在。
其报错结果可以轻易看出,256是不能够被插入的(废话)
处理AUTO_INCREMENT约束时,即便遇到数值类型的上限值,仍然会尝试插入,此时插入的值是上限值,即255。
此处可以看出此处的AUTO_INCREMENT=255,而不是256(即便255已经存在)。
注,此处(ENGINE=InnoDB后面)的AUTO_INCREMENT为MySQL认为该表的下一个自增字段的值。
才
- mysql> SHOW CREATE TABLE a\G
- *************************** 1. row ***************************
- Table: a
- Create Table: CREATE TABLE `a` (
- `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
当然既然开始了,还是要做一下BIGINT的实验。
- mysql> CREATE TABLE b (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT);
- Query OK, 0 rows affected (0.26 sec)
- mysql> INSERT INTO b SELECT 18446744073709551615;
- Query OK, 1 row affected (0.05 sec)
- Records: 1 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO b SELECT 18446744073709551616;
- ERROR 1264 (22003): Out of range value for column 'id' at row 1
- mysql> INSERT INTO b SELECT NULL;
- ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
- mysql> SHOW CREATE TABLE b\G
- *************************** 1. row ***************************
- Table: b
- Create Table: CREATE TABLE `b` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
1、插入18446744073709551616,报错ERROR 1264,即超过ID列的上限值。(与实验1的报错结果一致)
2、插入NULL,此处报错与之前不同,为ERROR 1467。
ERROR 1467官方解释为:
Error: 1467 SQLSTATE: HY000 (ER_AUTOINC_READ_FAILED)
Message: Failed to read auto-increment value from storage engine
字面意思为“从存储引擎读取自增字段失败”。搜了一下google,好像还真有人在实际运用中遇到这种问题,有个小哥提出了如下的方法:
After some searching i found the answer and it solved my problem.
run this sql query it will fix the problem
ALTER TABLE `YOUR_TABLE` AUTO_INCREMENT = 1
我也做了如下操作,但是…… 发现并未生效。
- mysql> ALTER TABLE b AUTO_INCREMENT = 1;
- Query OK, 0 rows affected (0.04 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO b SELECT NULL;
- ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
- mysql> SHOW CREATE TABLE b\G
- *************************** 1. row ***************************
- Table: b
- Create Table: CREATE TABLE `b` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
额外两个tips:
【1】由于TIMESTAMP用的其实是INT来存储,故从1970年1月1日开始往后顺延2147483647s,约24855天,即68年(到2038年)。
关于TIMESTAMP,官方给出的范围是:
'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.
【2】
MySQL提供了一个叫SERIAL的”类型“
实际上是unsigned NOT NULL AUTO_INCREMENT UNIQUE的别名:
- mysql> CREATE TABLE test(a SERIAL);
- Query OK, 0 rows affected (0.41 sec)
- mysql> show create table test\G
- *************************** 1. row ***************************
- Table: test
- Create Table: CREATE TABLE `test` (
- `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- UNIQUE KEY `a` (`a`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)