MySQL主键值被我用完了

简介

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

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

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

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

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

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

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

数值类型的边界值

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

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

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

不积跬步，无以至千里；不积小流，无以成江海。