MYSQL 业务上碰到的 SQL 问题整理集合

前言

身为一名前端工程师，对于 SQL了解程度并不是很深刻，盘点一些个人工作遇到的问题，给大家普及下知识，以及记录自己如何解决这些问题的。

导航

• SELECT 语句不区分大小写?

• SELECT IN 语句顺序不符合传入时要求?

• SELECT 存储查询生僻汉字, 结果乱码?

• SELECT LOCATE 与 LIKE 区别使用

SELECT 查询语句不区分字母大小写？

相信这是一个非常常见的问题了，而这个问题的原因主要还是表字符集引起的。

假设存在 config 表结构：

表内数据如下：

执行语句为：

SELECT `key`,`value` FROM config WHERE `key` = 'version' LIMIT 1;

期待结果：

执行结果：

为什么会有这种现象?

mysql 默认对字符匹配排序大小写不敏感，字段包括 varchar ， char ， text 内容。如果要确实要区分大小写，则在建表或者查表的时候使用 BINARY 属性。二进制的 A 与 a 还是有区别的 ~~

解决方案1 ：修改sql语句

SELECT `key`,`value` FROM config WHERE `key` = binary('version') LIMIT 1;

或者

SELECT `key`,`value` FROM config WHERE binary `key` = 'version' LIMIT 1;

解决方案2 ：修改表结构

建表语句

CREATE TABLE `config` (
  `key` BINARY varchar(255) NOT NULL,
  `value` BINARY varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

修改表语句

ALTER TABLE `config` MODIFY COLUMN `key` varchar(255) BINARY NOT NULL;

SELECT IN 语句顺序不符合传入时要求？

以 config 表为例，表内数据：

SQL语句：

SELECT `key`, `value` FROM `config` WHERE `key` IN ('username', 'email');




    

执行结果：

明明是 username 优先于 email ，结果却是 email 优先于 username 。原因在于 IN 查询只负责查询，不负责排序，而默认排序是用 id asc ，所以得到了一个不符合 IN 查询的结果。

解决方案

1. 使用 ORDER BY FIELD()

2. 使用 ORDER BY FIND_IN_SET()

SELECT `key`, `value` FROM `config` WHERE `key` IN ('username', 'email') ORDER BY FIELD('key','username', 'email');

或者

SELECT `key`, `value` FROM `config` WHERE `key` IN ('username', 'email') ORDER BY FIND_IN_SET(`key`,'username,email');

最终执行结果：

注意： FIND IN SET 第二个参数 strlist 逗号之间不需要空格

SELECT 存储查询生僻汉字, 结果乱码？

前提，数据库和表都是采用的是 utf8 字符集。生僻字比如 𠂤 ：

INSERT INTO `config` (`key`,`value`,`id`) VALUES ('word', '𠂤', 7);

查询SQL：

SELECT * FROM `config` WHERE `key` = 'word' LIMIT 1;

执行结果：

出现了 ???? 这种情况，难道说 utf8 字符集没有记录这个生僻字么?

mysql 支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

引用一段 关于 MySQL UTF8 编码下生僻字符插入失败/假死问题的分析 内容。

解决方案：

修改字符集：

//修改数据库字符集
ALTER DATABASE test CHARACTER SET = utf8mb4;
//修改表字符集
alter table `config` convert to character set utf8mb4;
//修改字符字符集
ALTER TABLE `config` CHANGE COLUMN `value` `value` varchar(12) CHARACTER SET utf8mb4;

最终执行sql：

//设置连接 socket 使用字符集
SET NAMES utf8mb4;
//修改表字段字符集
ALTER TABLE `config` CHANGE COLUMN `value` `value` varchar(12) CHARACTER SET utf8mb4;
//更新值
UPDATE `config` SET `value` = '𠂤' WHERE `key` = 'word';
//查询
SELECT * FROM `config` WHERE `key` = 'word';

执行结果：

SELECT LOCATE 与 LIKE 区别使用

同样再使用 config