故障分析 | 关于 MySQL 5.7 升级 8.0 时 INT 显示问题分析

2024年 6月 19日 72.2k 0

1背景

MySQL 5.7 已经停止更新了,最新的版本是 5.7.44。很多客户为了安全或者新特性的需求要对存量的 MySQL 5.7 进行大版本升级。

本案例中的客户是从 MySQL 5.7.36 升级到 MySQL 8.0.35,升级完成后业务反馈表结构有变化,升级前某个表的字段数据类型为 INT(10)
,升级后变成了 INT
,客户咨询有什么影响没有?

故事开始前,先问个小问题,MySQL 中 INT(num)
的作用是什么?能够限制插入的数据范围吗?如何正确的使用呢?

阅读之前大家先把答案默念一遍,然后根据下面的测试结果对比一下,是不是我们想的那样?

2本地复现

升级前的表结构和数据

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int(4) unsigned DEFAULT NULL,
  `id2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
 
mysql> insert into t2 values(1,1),(10,10),(100,100),(1000,1000),(12345,12345);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
 
mysql> select * from t2;
+-------+-------+
| id1   | id2   |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+
5 rows in set (0.00 sec)

升级后的表结构和数据

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int unsigned DEFAULT NULL,
  `id2` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
 
 
mysql> select * from t2;
+-------+-------+
| id1   | id2   |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+
5 rows in set (0.00 sec)

结论

MySQL 升级到 8.0.35 之后,t2
表的 id1
id2
字段的数据类型统一变成了 INT
。只是表结构的 INT 类型显示发生了变化,对数据没有影响。

3讨论:MySQL 整数类型扩展

MySQL 支持的整数类型所需要的存储(字节)和范围[1] 如下:

故障分析 | 关于 MySQL 5.7 升级 8.0 时 INT 显示问题分析-1

如何选择整数类型?

  1. 是否使用负数?

    如果使用负数,需要查看对应的整数类型允许插入的最大值是否满足要求;如果不使用负数,建议使用无符号整数类型;

  2. 如何选择性价比高的整数类型?

    每个整数类型的插入值都有对应的上限,占用的存储(字节)越大,能够插入的数值也就越大,越消耗内存且计算效率越低,建议根据插入值的上限来选择合适的整数类型。

  3. INT(num)
    并不能限制插入数值的范围!

int(num) 的使用场景

数据类型:INT(num)
CHAR(num)
的含义不同,很容易混淆。CHAR(num)
中的 num
是指插入字符的最大个数。

mysql> show create table tv\G
*************************** 1. row ***************************
       Table: tv
Create Table: CREATE TABLE `tv` (
  `name` char(2) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
 
mysql> insert into tv values('a');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into tv values('aa');
Query OK, 1 row affected (0.01 sec)
 
//超出 char(2) 的限制,无法插入
mysql> insert into tv values('aaa');
ERROR 1406 (22001): Data too long for column 'name' at row 1   

INT(num)
中的 num
需要配合 zerofill
一起使用才能生效,表示显示宽度,不足这个宽度的用0补齐。单独使用 INT(num)
并没有什么效果,也并不会限制插入的数据范围发生变化,所以不建议单独使用 INT(num)
INT(num)
配合 zerofill
一起使用时,MySQL 升级过程中不会对表结构做修改。

//升级前
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id1` int(4) unsigned zerofill DEFAULT NULL,
  `id2` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
 
 
mysql> insert into t1 values(1,1),(10,10),(100,100),(1000,1000),(12345,12345);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> select * from t1;
+-------+------------+
| id1   | id2        |
+-------+------------+
|  0001 | 0000000001 |
|  0010 | 0000000010 |
|  0100 | 0000000100 |
|  1000 | 0000001000 |
| 12345 | 0000012345 |
+-------+------------+
5 rows in set (0.00 sec)
 
 
//升级后
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id1` int(4) unsigned zerofill DEFAULT NULL,
  `id2` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
 
 
mysql> select * from t1;
+-------+------------+
| id1   | id2        |
+-------+------------+
|  0001 | 0000000001 |
|  0010 | 0000000010 |
|  0100 | 0000000100 |
|  1000 | 0000001000 |
| 12345 | 0000012345 |
+-------+------------+
5 rows in set (0.00 sec)

直接使用 INT

不同版本的 MySQL,直接使用 INT 类型时,表结构显示的 INT 也不同,MySQL 5.7.25 默认显示是 INT(11)
,如果配合 zerofill
使用,默认显示是 INT(10)
。MySQL 8.0.35 默认还是 INT,配合 zerofill
默认显示是 int(10)

参考资料

[1]

整数类型存储限制: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论