MySQL 线上2个小案例
MySQL 线上2个小案例
今天在线上遇到2个很有意思的MySQL案例，都是比较经典的问题，拿出来跟大家分享一下。为了对库表名称进行脱敏，我把问题抽象出来两个小的例子，且看分享。
01
索引长度导致的更新报错
来看这个案例：
首先我们有一张表：
CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar() DEFAULT NULL,
`col2` varchar() DEFAULT NULL,
`col3` varchar() DEFAULT NULL,
`col4` varchar() DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c1_c2_c3_c4` (`col1`,`col2`,`col3`,`col4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表t有4个字段，分别是col1~col4；
唯一索引是由这4个字段组成的联合索引。
mysql> select * from t;
+----+------+------+------+------+
| id | col1 | col2 | col3 | col4 |
+----+------+------+------+------+
| 1 | a | b | c | d |
| 2 | aa | bb | cc | dd |
| 3 | aaa | bbb | ccc | ddd |
| 4 | aaaa | bbbb | cccc | dddd |
+----+------+------+------+------+
rows in set (. sec)
表t中有4条记录，如上。
现在的问题是，col4需要更新一个数据，但是在更新数据的时候，发现长度太短了。报错如下：
mysql> update t set col4='ddddd' where id=;
ERROR (): Data too long for column 'col4' at row
这个其实不难理解，就是表t的col4这个字段设置的是varchar(4), 但是插入的数据是'ddddd'包含5个字符，当然会报一个Data too long的错误了。
业务同学看到这个报错之后，想着将这个表的col4字段改成varchar(5), 这样不就能进行更新了么？
mysql> alter table t modify col4 varchar();
ERROR (): Specified key was too long; max key length is bytes
可以看到，当我们尝试修改表t的col4字段的时候，发生了第二个报错，说定义的key太长了，最大的key长度是3072 bytes。
看起来我们也无法修改col4的定义了。我们看看是哪里出的问题，翻阅MySQL的官方文档，上面对这个现象有介绍：
For string columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format.
写的也很明白：
对于string类型的列，可以使用前缀字符来进行索引，
对于redundant或者compact行格式的记录，最大的索引前缀是767个字节；
对于dynamic和compressed的行格式的记录，最大的索引前缀长度是3072个字节
那我们看看当前表的行格式吧：
select * from information_schema.tables where table_schema='test' and table_name='t'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: t
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION:
ROW_FORMAT: Dynamic
TABLE_ROWS:
AVG_ROW_LENGTH:
DATA_LENGTH:
MAX_DATA_LENGTH:
INDEX_LENGTH:
DATA_FREE:
AUTO_INCREMENT:
CREATE_TIME: 2022-07-07 ::
UPDATE_TIME: 2022-07-07 ::
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
row in set (0.00 sec)
可以看到，我们的行格式是dynamic，当然，索引的最大限制字节数就是3072了，我们再来看看当前索引的字节数：
col1---varchar(500)---utf8编码---1500字节
col2---varchar(500)---utf8编码---1500字节
col3---varchar(20)---utf8编码---60字节
col4---varchar(4)---utf8编码---12字节
可以看到，加起来一共就是3072字节。
因此当我们执行：
alter table t modify col4 varchar(5)的时候，索引idx_c1_c2_c3_c4就会超过3072字节，就会直接报错
如何解决这个更新问题？
既然我们无法修改c4这个列的varchar长度，那么我们如何实现我们修改字段内容的需求呢？
索引idx_c1_c2_c3_c4是为了保证唯一，其实对于col1这个字段来说，它的长度是500，其实没有必要把500个字段全部索引起来，只需要索引它的前若干个字符，例如前200个字符如果能保证唯一的区分度，那么索引前200个即可：
mysql> alter table t add UNIQUE KEY `idx_c1_c2_c3_c4` (`col1`(200),`col2`,`col3`,`col4`);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(500) DEFAULT NULL,
`col2` varchar(500) DEFAULT NULL,
`col3` varchar(20) DEFAULT NULL,
`col4` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c1_c2_c3_c4` (`col1`(200),`col2`,`col3`,`col4`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
mysql> alter table t modify col4 varchar(5);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
通过这种办法，就能通过绕弯的方式实现索引区分的目的。
02
有Null值的列
首先创建一个包含default null字段的表：
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL,
`name` mediumtext COLLATE utf8mb4_general_ci,
`sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | aaa | male |
| 2 | aaa | male |
| 3 | aaa | NULL |
+----+------+------+
3 rows in set (0.00 sec)
我们可以看到id=3的记录，sex列是NULL值，当我们对这个表进行修改，将sex列改成not null属性的时候：
mysql> alter table t1 modify sex varchar() COLLATE utf8mb4_general_ci NOT NULL;
Query OK, rows affected, warning (. sec)
Records: Duplicates: Warnings:
## 查看下这个报警
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | | Data truncated for column 'sex' at row 3 |
+---------+------+------------------------------------------+
row in set (. sec)
mgrm5562:mysqlha_common@10.182.17. [test] 23:01:42> select * from t1;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | aaa | male |
| 2 | aaa | male |
| 3 | aaa | |
+----+------+------+
rows in set (. sec)
可以看到一条警告，数据被truncate了，其实数据上发生的变化就是id=3的记录，NULL值被改成了空字符串。
这里的warning有一定的迷惑性。
这个例子告诉我们，当你的字段从default null属性改为not null属性的时候，出现的Data truncate操作不一定是数据被截断了，还有可能是数据从NULL值，被修改成了默认的空字符串。
总结
今天的文章，讲了2个小的例子，后续大家如果看到诸如此类的报错，希望可以有个灵感。
1、对于string类型，可以索引前几位来保证一定的区分度，又可以让索引的长度变小，索引的数据量也会对应变小；
2、default null字段的属性变为not null的时候，虽然报错Data truncate，但是你的数据没有丢失，只是将null值进行了修改
MySQL里面的细节非常多，时常留意，多积累，出现问题的时候才能游刃有余。
PS：文中的MySQL实验版本是5.7.24.
腾讯云开发者
