|
|
|
|
移动端

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

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

作者:liyanlong来源:segmentfault|2017-12-20 13:39

有奖调研 | 1TB硬盘等你拿 AI+区块链的发展趋势及应用调研


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

前言

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

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

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

假设存在config表结构:

Field Type Allow Null Default Value
key varchar(255) No  
value varchar(255) No  
id int(11) No  

表内数据如下:

key value id
VERSION 1.0.1 1
version 2.0.1 2


执行语句为:

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

期待结果:

key value
version 2.0.1


执行结果:

key value
VERSION 1.0.1


为什么会有这种现象?

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

解决方案1 : 修改sql语句

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

或者

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

解决方案2 : 修改表结构

建表语句

  1. CREATE TABLE `config` ( 
  2.  
  3.   `keyBINARY varchar(255) NOT NULL
  4.  
  5.   `value` BINARY varchar(255) DEFAULT NULL
  6.  
  7.   `id` int(11) NOT NULL
  8.  
  9.   PRIMARY KEY (`id`) 
  10.  
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

修改表语句

  1. ALTER TABLE `config` MODIFY COLUMN `keyvarchar(255) BINARY NOT NULL

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

以config表为例, 表内数据:

key value id
email 295697141@qq.com 1
username 2


SQL语句:

  1. SELECT `key`, `value` FROM `config` WHERE `keyIN ('username''email'); 

执行结果:

key value
email 295697141@qq.com
username


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

解决方案

  1.     使用 ORDER BY FIELD()
  2.     使用 ORDER BY FIND_IN_SET() 
  1. SELECT `key`, `value` FROM `config` WHERE `keyIN ('username''email'ORDER BY FIELD('key','username''email'); 

或者

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

最终执行结果:

key value
username
email 295697141@qq.com

注意: FIND_IN_SET 第二个参数 strlist 逗号之间不需要空格

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

前提,数据库和表都是采用的是utf8字符集.

生僻字比如: 𠂤

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

查询SQL:

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

执行结果:

value key id
word ???? 7


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

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

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

解决方案:

修改字符集

  1. --修改数据库字符集 
  2. ALTER DATABASE test CHARACTER SET = utf8mb4; 
  3. --修改表字符集 
  4. alter table `config` convert to character set utf8mb4; 
  5. --修改字符字符集 
  6. ALTER TABLE `config` CHANGE COLUMN `value` `value` varchar(12) CHARACTER SET utf8mb4; 

最终执行sql

  1. - 设置连接 socket 使用字符集 
  2.  
  3. SET NAMES utf8mb4; 
  4.  
  5. - 修改表字段字符集 
  6.  
  7. ALTER TABLE `config` CHANGE COLUMN `value` `value` varchar(12) CHARACTER SET utf8mb4; 
  8.  
  9. - 更新值 
  10.  
  11. UPDATE `config` SET `value` = '𠂤' WHERE `key` = 'word'
  12.  
  13. - 查询 
  14.  
  15. SELECT * FROM `config` WHERE `key` = 'word'

执行结果

key value id
word 𠂤 7

SELECT LOCATE 与 LIKE 区别使用

同样再使用config表举一个例子, 假如有以下的行数据:

key value id
app.version 1.0.0 8
h5.version 1.0.1 9
app.email test@gmail.com 10
h5.email test@outlook.com 11

LIKE语句

如果我们想要查询以app 或 h5 开头的命名空间的所有配置项, 可以使用LIKE语句

  1. SELECT `key`, `value` FROM `config` WHERE `keyLIKE 'h5.%';

执行结果:

key value
h5.version 1.0.1
h5.email test@outlook.com


如果想去掉h5命名空间前缀, 可以使用 substring 函数

  1. SELECT substring(`key`, length('h5.') + 1), `value` FROM `config` WHERE `keyLIKE 'h5.%'

执行结果:

key value
version 1.0.1
email test@outlook.com


LIKE 在字符串全匹配,以及前置查询如 h5.%的时候, 如果存在索引会有一定的优化作用。不会进行全表扫描

LOCATE 函数

LOCATE是一种查询匹配字符串出现次数的函数

执行语句:

  1. SELECT `key`, `value` FROM `config` WHERE LOCATE('app',`key`) > 0; 

执行结果:

key value
app.version 1.0.0
app.email test@gmail.com


经过相关资料的学习, 最终认为LIKE的效率与LOCATE的效率是无法对比谁快谁慢,相关文章推荐阅读 MySQL LIKE vs LOCATE

总结

mysql, sql 里面的知识确实让人感觉深奥. 此时此刻我只是解决了我遇到问题, 一会也会遇到更多不一样的问题, 而这也是学习sql, 计算机的魅力. 以后遇到更多的关于SQL的问题, 会不断更新...

欢迎大家收藏和点赞!!!

【编辑推荐】

  1. SQL优化器究竟帮你做了哪些工作?
  2. 这五大开源MySQL管理工具,将DBA从 “删库跑路”中 解救出来!
  3. 数据库的优化,要从这几方面着手!
  4. 记一次MySQL死锁排查过程
  5. 一篇文章读懂阿里企业级数据库最佳实践
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

非常网管——网络应用

在网络应用越来越复杂的今天,传统的网络应用已经不能满足企业和用户的需要,这就对网络管理员、信息管理部门提出了更高的要求。本书介绍了...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊