阅读 58

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

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 个字节,有符号的取值范围是 (-32 768,32 767),无符号的取值范围是 (0,65 535)。

看下创建的表情况

 DESC table0;
复制代码

1.png

插入数据

INSERT INTO table0 (name) VALUES ('veryitman');
复制代码

2.png

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

INSERT INTO table0 (name) VALUES ('.com');
复制代码

3.png

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

不设置主键无自增

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

4.png

强力推荐给你-不要错过,万一能帮助到自己呢?
朋友做了一个关于 人工智能的教程,教程不仅是零基础,通俗易懂,而且非常风趣幽默,像看小说一样!点 这里 可以跳转到教程,对人工智能感兴趣的朋友可以去了解一下。


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

文章分类
后端
文章标签