mysql这几个坑你踩过几个？

女爷i 2024-04-07 14:56 27阅读 0赞

### 一、前言 ###

对于从事互联网开发的同学来说，mysql可谓是再熟悉不过的了。无论是DBA、开发或测试，基本上天天要跟它打交道，很多同学可能已经身经百战了。但是，笔者遇到过的这些坑不知道你们都经历过没？

### 二、有符号和无符号 ###

以前我们公司在项目开发之初制定开发规范时，对mysql的int类型字段定义成有符号，还是无符号问题专门讨论过。

观点一：

对于能够确定里面存的值一定是正数的字段，定义成UNSIGNED无符号的，可以节省一半的存储空间。创建无符号字段的语句如下：

create table test_unsigned(a int UNSIGNED, b int UNSIGNED);

观点二：

建议都定义成有符号的，使用起来比较简单，mysql默认int类型就是有符号的，创建有符号字段的语句如下：

create table test_signed(a int);insert into test_signed values(-1);

执行结果：

![ce082f68c89d434d9d88565e1bacd7d2.png][]

在字段a中插入-1，我们看到是可以操作成功的。

这两个方案，经过我们激烈讨论之后，选择了使用有符号定义int类型字段。为什么呢？

create table test_unsigned(a int UNSIGNED, b int UNSIGNED);insert into test_unsigned values(1,2);

先创建test\_unsigned表，里面包含两个无符号字段a和b，再插入一条数据a=1，b=2

select b - a from test_unsigned;

没有问题，返回1

但是如果sql改成这样：

select a - b from test_unsigned;

执行结果：

![b3da3fc6f8ac44c9adf2bf29ed748af0.png][]

报错了。。。

所以，在使用无符号字段时，千万要注意字段相减出现负数的坑，建议还是使用有符号字段，避免不必要的问题。

### 三、自动增长 ###

建过表的同学都知道，对于表的主键可以定义成自动增长的，这样一来，就可以交给数据库自己生成主键值，而无需在代码中指定，而且生成的值是递增的。一般情况下，创建表的语句如下：

create table test_auto_increment (a int auto_increment primary key);

但如果改成这样的会怎样？

create table test_auto_increment (a int auto_increment);

执行结果：

![867c5e8f94bd4ed9a22822e59399023d.png][]

报错了。。。

截图中没有全部显示出来，完整的提示语是这样的：

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key, Time: 0.006000

意思是自动增长字段，必须被定义成key，所以我们需要加上primary key。

此外，还有一个有趣的实验：

insert into test_auto_increment(a) values (null),(50),(null),(8),(null);

大家猜猜执行结果会是什么样的？

![87b8587232ea43bcab4737f50a1a3e03.png][]

第一个null插入1，然后按真实的数字大小排序后插入，后面两个null，是在最大的数字上面加1。

再看看这条sql主键中插入负数，能执行成功吗？

insert into test_auto_increment values(-3);

答案是可以，主键可以插入负数。

![7ffe80735196427ebd8f94a68b09df2b.png][]

还有这条sql呢，主键中插入0？

insert into test_auto_increment values(0);

执行结果：

![d120c81ee6c54dcdbc568db263df5b43.png][]

也可以执行成功，但是没有插入数据

### 四、字段长度 ###

我们在创建表的时候，给字段定义完类型之后，紧接着需要指定字段的长度，比如：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;

执行后的结果：

![518b1598e0ce438e89727c6d16f3ad5a.png][]

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

![453d76c38c204db4aaff5a0b52b7458f.png][]

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

执行结果：

![5a0f5be558de4ecdb83f4ccfaf877eb9.png][]

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

![02c1c4eba91a4067a6aaf97a5a6f4d38.png][]

从表中可以看出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';

执行结果是什么呢？

![a962ddfb8539453e85da9b85f8602f62.png][]

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

![aa59b8054a91401aae3363c30d9d414b.png][]

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

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

show collation;

![236a4af8296644edbf986cbe0b544e81.png][]

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

![f79a42073edd4a3cb1c7a16e3fd2214c.png][]

修改一下字段类型

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';

执行结果：

![e9ebecec196141a6bb2d39c0d51f226f.png][]

## 果然，结果对了。 ##

### 六、特殊字符 ###

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

![61fc689dd1114f9b82c89f4abbbc9de3.png][]

最后定位原因是由于当时数据库和表的字符编码都是用的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 = utf8mb4collation-server     = utf8mb4_general_ci

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

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

结果为：

![95f956d8cf474cbe87730c1e9bc18504.png][]

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

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，避免一些不必要的问题。

如果这篇文档对您有所帮助的话，麻烦关注一下

[ce082f68c89d434d9d88565e1bacd7d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/89ff624ad1ab4b63a857cdbe85f8cd18.png
[b3da3fc6f8ac44c9adf2bf29ed748af0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/2a275a32a85f43748898cf24816de465.png
[867c5e8f94bd4ed9a22822e59399023d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/6758aa7a896743ffa6553ba22d9ad39d.png
[87b8587232ea43bcab4737f50a1a3e03.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/de5a90832bdb4118afc51d27cf5f2344.png
[7ffe80735196427ebd8f94a68b09df2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/fc5c92e089ba4c23b6c8069494b431c0.png
[d120c81ee6c54dcdbc568db263df5b43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/05dadaf703cf4c69b3575a58f0cfcfa1.png
[518b1598e0ce438e89727c6d16f3ad5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/7be7fdd2280b434eb85d976f2baeac3b.png
[453d76c38c204db4aaff5a0b52b7458f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/3f3af554a1964a66ab50aac8892ca64d.png
[5a0f5be558de4ecdb83f4ccfaf877eb9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/9fb1c10c91d14f44a5314de5a507a48c.png
[02c1c4eba91a4067a6aaf97a5a6f4d38.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/62257f21119a420e9b4d5c5f685d380b.png
[a962ddfb8539453e85da9b85f8602f62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/37ede60d04e84a23b696e980064e3f57.png
[aa59b8054a91401aae3363c30d9d414b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/030d7e214b924a8e91f68f3531c148ed.png
[236a4af8296644edbf986cbe0b544e81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/42116d1f7ff5471a8aa8cd253cc97311.png
[f79a42073edd4a3cb1c7a16e3fd2214c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/dc2c3831a9eb493b814b87476a556910.png
[e9ebecec196141a6bb2d39c0d51f226f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/b512d27a9d414bb59e8964fa0d50446c.png
[61fc689dd1114f9b82c89f4abbbc9de3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/b3eaf8c727f4458ea89ec6d14b946d76.png
[95f956d8cf474cbe87730c1e9bc18504.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/03f227fcaf134609b76d34f2f5b92e02.png