125. MySQL操作时的相关坑和处理技巧

坑

一、字段长度

我们在创建表的时候，给字段定义完类型之后，紧接着需要指定字段的长度，比如：varchar(20)，biginit(20)等。那么问题来了，varchar代表的是字节长度，还是字符长度呢？

create table test_varchar(a varchar(20));
insert into test_varchar values('程序员百里');
select length(a),CHARACTER_LENGTH(a) from test_varchar;

执行后的结果：

我们看到中文的5个字length函数统计后长度为15，代表占用了15个字节，而使用charcter_length函数统计长度是5，代表有5个字符。所以varchar代表的是字符长度，因为有些复杂的字符或者中文，一个字节表示不了，utf8编码格式的一个中文汉字占用3个字节。不同的数据库编码格式，占用不同的字节数对照表如下：

mysql除了varchar和char是代表字符长度之外，其余的类型都是代表字节长度。

int(n) 这个n表示什么意思呢？从一个列子出发：

create table test_bigint (a bigint(4) ZEROFILL);
insert into test_bigint values(1);
insert into test_bigint values(123456);
select * from test_bigint;

ZEROFILL表示长度不够填充0

执行结果：

mysql常用数字类型字段占用字节数对照表：

从表中可以看出bigint实际长度是8个字节，但是我们定义的a显示4个字节，所以在不满4个字节时前面填充0。满了4个字节时，按照实际的长度显示，比如：123456。但是，需要注意的是，有些mysql客户端即使满了4个字节，也可能只显示4个字节的内容，比如显示：1234。

所以bigint(4)，这里的4表示显示的长度为4个字节，实际长度还是8个字节。

二、忽略大小写

我们知道在英文字母中有大小写问题，比如：a 和 A 是一样的吗？我们认为肯定是不一样的，但是数据库是如何处理的呢？

create table test_a(a varchar(20));
insert into test_a values('a');
insert into test_a values('A');
select * from test_a where a = 'a';

执行结果是什么呢？

本以为只会返回a，但是实际上把A也返回了，这是为什么呢？

该表默认的Collation是utf8_general_ci，这种Collation会忽略大小写，所以才会出现查询小写字母a的值，意外把大写字母A的值也查询出来了。

那么如果我们只想查询出小写a的值该怎么办？先看看mysql支持哪些Collation？

show collation;

从上图中我们可以找到utf8_bin，这个表示二进制格式的数据，我们设置成种类型的试试。

修改一下字段类型

ALTER TABLE test_a MODIFY COLUMN a VARCHAR(20) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

再查看一下数据

select * from test_a where a = 'a';

执行结果：

果然，结果对了。

三、特殊字符

笔者之前做项目的时候，提供过一个留言的功能，结果客户端用户输入了一个emoji表情，直接导致接口报错了。

最后定位原因是由于当时数据库和表的字符编码都是用的utf8，mysql的utf8编码的一个字符最多3个字节，但是一个emoji表情为4个字节，所以utf8不支持存储emoji表情。

该如何解决这个问题呢？

将字符编码改成utf8mb4，utf8mb4最多能有4字节，不过，在mysql5.5.3或更高的版本才支持。

在mysql 的配置文件 my.cnf 或 my.ini 配置文件中修改如下：

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_general_ci

重启MySQL，然后使用以下命令查看编码，应该全部为utf8mb4，这是修改整个数据库的编码方式。

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

结果为：

也可以单独修改某张表的编码方式：

alter table test_a convert to character set utf8mb4 collate utf8mb4_bin;

以及修改某个字段的编码方式：

ALTER TABLE test_a CHANGE a a VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

此外，建议同学们在创建数据库和表的时候字符编码都定义成utf8mb4，避免一些不必要的问题。

处理技巧

一、操作数据之前先select一下

需要特别说明的是：本文的操作数据主要指修改和删除数据。

很多时候，由于我们人为失误，把where条件写错了。但没有怎么仔细检查，就把sql语句直接执行了。影响范围小还好，如果影响几万、几十万，甚至几百万行数据，我们可能要哭了。

针对这种情况，在操作数据之前，把sql先改成select count(*)语句，比如：

update order set status=1 where status=0;

改成：

select count(*) from order where status=0;

查一下该sql执行后影响的记录行数，做到自己心中有数。也给自己一次测试sql是否正确，确认是否执行的机会。

二、操作数据sql加limit

即使通过上面的select语句确认了sql语句没有问题，执行后影响的记录行数是对的。

也建议你不要立刻执行，建议在正在执行的时候，加上limit + select出的记录行数。例如：

update order set status=1 where status=0 limit 1000;

假设有一次性更新的数据太多，所有相关记录行都会被锁住，造成长时间的锁等待，而造成用户请求超时。

此外，加limit可以避免一次性操作太多数据，对服务器的cpu造成影响。

还有一个最重要的原因：加limit后，操作数据的影响范围是完全可控的。

三、update时更新修改人和修改时间

很多人写update语句时，如果要修改状态，就只更新状态，不管其他的字段。比如：

update order set status=1 where status=0;

这条sql会把status等于0的数据，全部更新成1。

后来发现业务逻辑有问题，不应该这么更新，需要把status状态回滚。

这时你可能会很自然想到这条sql：

update order set status=0 where status=1;

但仔细想想又有些不对。

这样不是会把有部分以前status就是1的数据更新成0？

这回真的要哭了，呜呜呜。

这时，送你一个好习惯：在更新数据的时候，同时更新修改人和修改时间字段。

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

这样在恢复数据时就能通过修改人和修改时间字段过滤数据了。

后面需要用到的修改时间通过这条sql语句可以轻松找到：

select edit_user ,edit_date from `order` order by edit_date desc limit 50;

当然，如果是高并发系统不建议这种批量更新方式，可能会锁表一定时间，造成请求超时。

有些同学可能会问：为什么要同时更新修改人，只更新修改时间不行吗？

主要有如下的原因：

• 为了标识非正常用户操作，方便后面统计和定位问题。
• 有些情况下，在执行sql语句的过程中，正常用户产生数据的修改时间跟你的可能一模一样，导致回滚时数据查多了。

四、多用逻辑删除，少用物理删除

在业务开发中，删除数据是必不可少的一种业务场景。

有些人开发人员习惯将表设计成物理删除，根据主键只用一条delete语句就能轻松搞定。

他们给出的理由是：节省数据库的存储空间。

想法是好的，但是现实很残酷。

如果有条极重要的数据删错了，想恢复怎么办？

此时只剩八个字：没有数据，恢复不了。（PS：或许通过binlog二进制文件可以恢复）

如果之前设计表的时候用的逻辑删除，上面的问题就变得好办了。删除数据时，只需update删除状态即可，例如：

update order set del_status=1,edit_date=now(),edit_user='admin' where id=123;

假如出现异常，要恢复数据，把该id的删除状态还原即可，例如：

update order set del_status=0,edit_date=now(),edit_user='admin' where id=123;

五、操作数据之前先做备份

如果只是修改了少量的数据，或者只执行了一两条sql语句，通过上面的修改人和修改时间字段，在需要回滚时，能快速的定位到正确的数据。

但是如果修改的记录行数很多，并且执行了多条sql，产生了很多修改时间。这时，你可能就要犯难了，没法一次性找出哪些数据需要回滚。

为了解决这类问题，可以将表做备份。

可以使用如下sql备份：

create table order_bak_2021031721 like`order`;
insert into order_bak_2021031721 select * from`order`;

先创建一张一模一样的表，然后把数据复制到新表中。

也可以简化成一条sql：

create table order_bak_2021031722 select * from`order`;

创建表的同时复制数据到新表中。

此外，建议在表名中加上bak和时间，一方面是为了通过表名快速识别出哪些表是备份表，另一方面是为了备份多次时好做区分。因为有时需要执行多次sql才能把数据修复好，这种情况建议把表备份多次，如果出现异常，把数据回滚到最近的一次备份，可以节省很多重复操作的时间。

恢复数据时，把sql语句改成select语句，先在备份库找出相关数据，每条数据对应一条update语句，还原到老表中。

六、中间结果写入临时表

有时候，我们要先用一条sql查询出要更新的记录的id，然后通过这些id更新数据。

批量更新之后，发现不对，要回滚数据。但由于有些数据已更新，此时使用相同的sql相同的条件，却查不出上次相同的id了。

这时，我们开始慌了。

针对这种情况，我们可以先将第一次查询的id存入一张临时表，然后通过临时表中的id作为查询条件更新数据。

如果要恢复数据，只用通过临时表中的id作为查询条件更新数据即可。

修改完，3天之后，如果没有出现问题，就可以把临时表删掉了。

七、表名前面一定要带库名

我们在写sql时为了方便，习惯性不带数据库名称。比如：

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

假如有多个数据库中有相同的表order，表结构一模一样，只是数据不一样。

由于执行sql语句的人一个小失误，进错数据库了。

use trade1;

然后执行了这条sql语句，结果悲剧了。

有个非常有效的预防这类问题的方法是加数据库名：

update `trade2`.`order` set status=1,edit_date=now(),edit_user='admin' where status=0;

这样即使执行sql语句前进错数据库了，也没什么影响。

八、字段增删改的限制

很多时候，我们少不了对表字段的操作，比如：新加、修改、删除字段，但每种情况都不一样。

1. 新加的字段一定要允许为空

新加的字段一定要允许为空。为什么要这样设计呢？

正常情况下，如果程序新加了字段，一般是先在数据库中加字段，然后再发程序的最新代码。

为什么是这种顺序？

因为如果先发程序，然后在数据库中加字段。在该程序刚部署成功，但数据库新字段还没来得及加的这段时间内，最新程序中，所有使用了新加字段的增删改查sql都会报字段不存在的异常。

好了，就按先在数据库中加字段，再发程序的顺序。

如果数据库中新加的字段非空，最新的程序还没发，线上跑的还是老代码，这时如果有insert操作，就会报字段不能为空的异常。因为新加的非空字段，老代码是没法赋值的。

所以说新加的字段一定要允许为空。

除此之外，这种设计更多的考虑是为了程序发布失败时的回滚操作。如果新加的字段允许为空，则可以不用回滚数据库，只需回滚代码即可，是不是很方便？

2. 不允许删除字段

删除字段是不允许的，特别是必填字段一定不能删除。

为什么这么说？

假设开发人员已经把程序改成不使用删除字段了，接下来如何部署呢？

如果先把程序部署好了，还没来得及删除数据库相关表字段。当有insert请求时，由于数据库中该字段是必填的，会报必填字段不能为空的异常。

如果先把数据库中相关表字段删了，程序还没来得及发。这时所有涉及该删除字段的增删改查，都会报字段不存在的异常。

所以，线上环境必填字段一定不能删除的。

3. 根据实际情况修改字段

修改字段要分为这三种情况：

1.修改字段名称
修改字段名称也不允许，跟删除必填字段的问题差不多。

如果把程序部署好了，还没来得及修改数据库中表字段名称。这时所有涉及该字段的增删改查，都会报字段不存在的异常。

如果先把数据库中字段名称改了，程序还没来得及发。这时所有涉及该字段的增删改查，同样也会报字段不存在的异常。

所以，线上环境字段名称一定不要修改。

2. 修改字段类型
修改字段类型时一定要兼容之前的数据。例如：

tinyint改成int可以，但int改成tinyint要仔细衡量一下。
varchar改成text可以，但text改成varchar要仔细衡量一下。

3.修改字段长度
字段长度建议改大，通常情况下，不建议改小。如果一定要改小，要先确认该字段可能会出现的最大长度，避免insert操作时出现字段太长的异常。

此外，建议改大也需要设置一个合理的长度，避免数据库资源浪费。