MySQL 中的一些坑

发表于 分类于 阅读次数： 本文字数： 2.3k
MySQL 使用过程中遇到的一些坑

Format

在 MySQL 中，使用 format 函数可以将一个数字保留指定的小数位数，例如：

1
2
SELECT format(123456.12645, 2);
-- 124556.13

但是，如果将 format 嵌套进 SUM 中使用，就会出现问题：

1
2
3
4
SELECT SUM(format(123456.12345, 2));
-- 123
SELECT SUM(format(1234567.12345, 2));
-- 1

该情况从 MySQL 5.6 - 8.0.27 测试都存在这个问题，尚未知是某种特性还是 bug，观察其特征或许是将 format 输出的结果中的千位分隔符当作小数点处理了。

order by limit 造成优化器选择索引错误

参考：MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误

该问题在 5.7 及以上的版本中已经修复，但是在 5.6 版本中仍然存在。

在 MySQL 中，优化器会先选择 where 条件中字段的索引，该索引过滤性较好； 但当 SQL 中同时存在有 order by limit 时，优化器会优先选择 order by 后面的字段作为索引，导致查询效率降低

例如：

存在一个 user 表，有 id, name, age 三个字段，id 为主键，name 为普通索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain select * from user where name = 'test' order by id limit 1;

-- ***************************[ 1. row ]***************************
-- id            | 1
-- select_type   | SIMPLE
-- table         | user
-- partitions    | <null>
-- type          | ref
-- possible_keys | idx_name
-- key           | PRIMARY
-- key_len       | 130
-- ref           | const
-- rows          | 8
-- filtered      | 100.0
-- Extra         | <null>

解决办法通常使用强制索引，例如：

1
select * from user FORCE INDEX (idx_name) where name = 'test' order by id limit 1;

Binary

在 MySQL 中，大小写是不敏感的，在做查询的时候，可能就会出现一些问题，例如：

1
2
3
4
5
6
SELECT id FROM `user` WHERE name='test';
-- id	name
-- 1	test
-- 2	TeSt
-- 2	TEsT
-- 3	TEST

此时，为了达到精确查询的效果，可以使用 BINARY 关键字，例如：

1
2
3
4
5
6
SELECT id FROM `user` WHERE BINARY name='test';
-- id	name
-- 1	test
SELECT id FROM `user` WHERE name= BINARY 'test';
-- id	name
-- 1	test

但上面两种都能在线上环境中使用吗，笔者用教训回答是不可以的，因为如果恰好 name 字段上有索引，那么第一种方法是走不了索引的，只能用第二种方法，故只推荐以下方式

1
2
3
SELECT id FROM `user` WHERE name= BINARY 'test';
-- id	name
-- 1	test

时间格式化

MySQL 中的时间格式化占位符和 Python 语言中的时间格式化方式有区别，导致经常会得到不想要的结果

占位符 含义
%Y 4位的年份
%y 2位的年份
%M 英文月份, 如 January
%b 缩写月名, 如 Jan
%m 数字月份, 00-12, 如 01
%c 数字月份, 0-12, 如 1
%d 每月的第几天, 00-31, 如 01
%D 带有英文前缀的月中的天, 如 1st
%e 每月的第几天, 0-31, 如 1
%j 每年的第几天, 000-366, 如 001
%W 星期名, 如 Monday
%a 缩写星期名, 如 Mon
%w 每周的第几天, 0=星期日, 6=星期六, 如 1
%H 小时, 00-23, 如 01
%h 小时, 01-12, 如 01
%I 小时, 01-12, 如 01
%k 小时, 1-23, 如 1
%l 小时, 1-12, 如 1
%p 上午或下午, AM/PM, 如 AM
%i 分钟, 00-59, 如 04
%S 秒钟, 00-59, 如 04
%s 秒钟, 00-59, 如 04
%f 微秒, 000000-999999, 如 000000
%r 时间, 12小时制表示, hh:mm:ss AM/PM, 如 03:04:05 PM
%T 时间, 24小时制表示, hh:mm:ss, 如 15:04:05
%U (不知道啥用) 每年的第几周, 星期日是一周的第一天, 00-53, 如 01
%u (不知道啥用) 每年的第几周, 星期一是一周的第一天, 00-53, 如 01
%V (不知道啥用) 每年的第几周, 星期日是一周的第一天, 01-53, 如 01
%v (不知道啥用) 每年的第几周, 星期一是一周的第一天, 01-53, 如 01
%X (不知道啥用) 年, 其中的星期日是周的第一天, 与 %V 使用
%x (不知道啥用) 年, 其中的星期一是周的第一天, 与 %v 使用

示例

1
2
3
SELECT DATE_FORMAT("2006-01-02 15:04:05.123456", "%Y-%Y %M-%M-%B-%C %D-%D-%E-%J %W-%A-%W %H-%H-%I-%K-%L-%P %I %S-%S %F %R %T");

-- 2006-06 January-01-Jan-1 02-2nd-2-002 Monday-Mon-1 15-03-03-15-3-PM 04 05-05 123456 03:04:05 PM 15:04:05