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

前言

网站的建设创新互联建站专注网站定制,经验丰富,不做模板,主营网站定制开发.小程序定制开发,H5页面制作!给你焕然一新的设计体验!已为发电机租赁等企业提供专业服务。

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

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表举一个例子, 假如有以下的行数据:

LIKE语句

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

 
 
 
 

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

执行结果:

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

 
 
 
 

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

执行结果:

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

LOCATE 函数

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

执行语句:

 
 
 
 

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

执行结果:

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

总结

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

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

本文名称：【MYSQL】业务上碰到的SQL问题整理集合
文章地址：http://www.shufengxianlan.com/qtweb/news32/523082.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联