发布于 

MySQL主键值被我用完了

简介

对于关系表,有个很重要的约束,就是任意两条记录不能重复。不能重复是指能够通过某个字段唯一区分出不同的记录,这个字段被称为 主键

对表的主键要求比较关键的一点是:记录一旦插入到表中,主键最好不要再修改。

选取表的主键一个基本原则是:没有特殊情况,不使用任何业务相关的字段(如证件号码、手机号码等)作为主键。

本篇主要说明下面两个问题:

  • 主键值范围越界了会发生什么?
  • 不设置主键行吗?

看完正文之后,可以在本篇的【小结】部分找到答案。

推荐下面几篇关于 MySQL 的文章:

数值类型的边界值

C 标准库 limits.h 中定义了一些数据类型的值的范围、限制值,也就是说该类型的值不可以大于该上界或者小于该类型值的下界。

咱们先看下下面这个 C 语言例子。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#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;
}

编译运行,可以得到如下结果:

1
2
3
4
5
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

1
2
3
CREATE DATABASE play_db;

USE play_db;

设置主键并自增

创建表 table0

1
CREATE TABLE table0(id SMALLINT unsigned AUTO_INCREMENT PRIMARY KEY, name char(10)) AUTO_INCREMENT=65535;

SMALLINT 是 MySQL 支持的一种数据类型,占用 2 个字节,有符号的取值范围是 (-32 768,32 767),无符号的取值范围是 (0,65 535)。

看下创建的表情况

1
DESC table0;

插入数据

1
INSERT INTO table0 (name) VALUES ('veryitman');

可见,此时 id 默认值已经是 65535了,试图再插入一条数据。

1
INSERT INTO table0 (name) VALUES ('.com');

那么另外一个问题来了,如果我们不设置主键呢?

不设置主键无自增

创建表 table1

TINYINT unsigned,表示范围 0 ~ 255

1
2
3
4
5
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

1
CREATE TABLE table2(id TINYINT unsigned AUTO_INCREMENT, name char(10)) AUTO_INCREMENT=255;

报错信息如下:

1
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支持的数值类型


不积跬步,无以至千里;不积小流,无以成江海。


本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议,转载请注明出处。

veryitman