简介
本篇主要说明下面两个问题:
- 主键值范围越界了会发生什么?
- 不设置主键行吗?
看完正文之后,可以在小结部分找到答案。
数值类型的边界值
C 标准库 limits.h
中定义了一些数据类型的值的范围、限制值,也就是说该类型的值不可以大于该上界或者小于该类型值的下界。
咱们先看下下面这个 C 语言例子。
#include <stdio.h>
#include <limits.h>
int main() {
// 65535
printf("unsigned short max value: %u\n", USHRT_MAX);
unsigned short yy = USHRT_MAX;
printf("xx = %hu\n", yy); //65535
printf("xx + 1 = %hu\n", yy+1); //0
printf("xx + 2 = %hu\n", yy+2); //1
printf("unsigned short sizeof: %u\n", sizeof(unsigned short));
return 0;
}
编译运行,可以得到如下结果:
unsigned short max value: 65535
xx = 65535
xx + 1 = 0
xx + 2 = 1
unsigned short sizeof: 2
从结果来看,无符号的 short 数据类型的最大值是 65535,当然最小值就是 0,即范围是 0 ~ 65535。
在这里,还是要再提一下关于格式化打印里面的一些说明:
i%
和d%
,都可以用来表示有符号的十进制整数,一般ld%
用来表示long
类型的;o%
用来表示八进制整数,x%
(X%
)用来表示十六进制整数;- 对
short
类型使用h
前缀,因此%hd
表示以十进制显示short
整数,%ho
表示以八进制显示short
整数; %u
用来表示无符号整数,即unsigned int
类型数据;h%
和l%
前缀都可以同u%
结合使用表示无符号整数。如%lu
表示打印unsigned long
类型、%hu
表示打印unsigned short
类型;- 打印
long
数值,%ld
打印long
数值,%lx
表示以十六进制格式打印长整数,%lo
表示以八进制格式打印长整数;
实践
经过上面的说明之后,接下来我们就可以进入今天的正题了。
创建数据库 play_db
CREATE DATABASE play_db;
USE play_db;
设置主键并自增
创建表 table0
CREATE TABLE table0(id SMALLINT unsigned AUTO_INCREMENT PRIMARY KEY, name char(10)) AUTO_INCREMENT=65535;
SMALLINT 是 MySQL 支持的一种数据类型,占用 2 个字节,有符号的取值范围是 (-32768,32767),无符号的取值范围是 (0,65535)。
看下创建的表情况
DESC table0;
插入数据
INSERT INTO table0 (name) VALUES ('veryitman');
可见,此时 id 默认值已经是 65535了,试图再插入一条数据。
INSERT INTO table0 (name) VALUES ('.com');
那么另外一个问题来了,如果我们不设置主键呢?
不设置主键无自增
创建表 table1
TINYINT unsigned,表示范围 0 ~ 255
CREATE TABLE table1(id TINYINT unsigned, name char(10));
INSERT INTO table1 (id, name) VALUES (255, 'veryitman');
INSERT INTO table1 (id, name) VALUES (256, 'haha');
此时会报错:ERROR 1264 (22003): Out of range value for column 'id' at row 1
如果不设置主键, InnoDB 会自动帮你创建一个不可见的、长度为 6 字节的 row_id
,而且这个 row_id
是由 InnoDB 维护全局的 dictsys.row_id
,每次插入一条数据时都会让全局 row_id
加一(未定义主键的表会使用全局 row_id
作为主键 id
)。
如果全局 row_id
一直涨,直到涨到2的48次方-1时,这时候再加一就会让低 48 位的 row_id
都为 0,此时如果再插入一条数据,它拿到的 row_id
就是 0,这样的话就有可能存在主键冲突的。
所以创建表的时候,最好设置主键。
设置自增但无主键
创建表 table2
CREATE TABLE table2(id TINYINT unsigned AUTO_INCREMENT, name char(10)) AUTO_INCREMENT=255;
报错信息如下:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
错误信息的意思是,只能有一个自增列,它必须被定义为键。
小结
1、在设计数据表时,尽量根据业务需求来选择合适的字段类型。数据库表的自增 ID 达到上限之后,再申请时它的值就不会在改变了,继续插入数据时会导致报主键冲突错误。
一个库或者表数据尽量不要太多,根据自己的业务合适设置即可。如果数据较多要进行分库分表,分布式环境下要注意主键生成问题,做到主键唯一;
2、为了避免一些不必要的麻烦和隐性错误,设计表都应该设置主键。
附录
MySQL支持的数值类型