前言

身為一名前端工程師, 對于 SQL了解程度并不是很深刻, 盤點(diǎn)一些個人工作遇到的問題，給大家普及下知識, 以及記錄自己如何解決這些問題的。

SELECT 查詢語句不區(qū)分字母大小寫?

相信這是一個非常常見的問題了, 而這個問題的原因主要還是表字符集引起的。

假設(shè)存在config表結(jié)構(gòu):

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

表內(nèi)數(shù)據(jù)如下:

key	value	id
VERSION	1.0.1	1
version	2.0.1	2

執(zhí)行語句為: 

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

期待結(jié)果:

key	value
version	2.0.1

執(zhí)行結(jié)果:

key	value
VERSION	1.0.1

為什么會有這種現(xiàn)象?

mysql 默認(rèn)對字符匹配排序大小寫不敏感, 字段包括 varchar, char, text 內(nèi)容. 如果要確實(shí)要區(qū)分大小寫, 則在建表或者查表的時(shí)候使用 BINARY 屬性. 二進(jìn)制的 A 與 a 還是有區(qū)別的 ~~

解決方案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 : 修改表結(jié)構(gòu)

建表語句

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 語句順序不符合傳入時(shí)要求?

以config表為例, 表內(nèi)數(shù)據(jù):

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

SQL語句:

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

執(zhí)行結(jié)果:

key	value
email	295697141@qq.com
username	我

明明是username 優(yōu)先于 email, 結(jié)果卻是 email優(yōu)先于 username. 原因在于 IN 查詢只負(fù)責(zé)查詢, 不負(fù)責(zé)排序, 而默認(rèn)排序是用 id asc, 所以得到了一個不符合IN查詢的結(jié)果

解決方案

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

最終執(zhí)行結(jié)果：

key	value
username	我
email	295697141@qq.com

注意： FIND_IN_SET 第二個參數(shù) strlist 逗號之間不需要空格

SELECT 存儲查詢生僻漢字, 結(jié)果亂碼 ?

前提，數(shù)據(jù)庫和表都是采用的是utf8字符集.

生僻字比如: 𠂤

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

查詢SQL:

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

執(zhí)行結(jié)果：

value	key	id
word	????	7

出現(xiàn)了???? 這種情況，難道說 utf8字符集沒有記錄這個生僻字么?

mysql 支持的 utf8 編碼***字符長度為 3 字節(jié)，如果遇到 4 字節(jié)的寬字符就會插入異常了。三個字節(jié)的 UTF-8 ***能編碼的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文種平面（BMP）。也就是說，任何不在基本多文本平面的 Unicode字符，都無法使用 Mysql 的 utf8 字符集存儲。包括 Emoji 表情（Emoji 是一種特殊的 Unicode 編碼，常見于 ios 和 android 手機(jī)上），和很多不常用的漢字，以及任何新增的 Unicode 字符等等。

引用一段 關(guān)于 MySQL UTF8 編碼下生僻字符插入失敗/假死問題的分析 內(nèi)容

解決方案：

修改字符集

--修改數(shù)據(jù)庫字符集 
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; 

最終執(zhí)行sql

- 設(shè)置連接 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'; 

執(zhí)行結(jié)果

key	value	id
word	𠂤	7

SELECT LOCATE 與 LIKE 區(qū)別使用

同樣再使用config表舉一個例子, 假如有以下的行數(shù)據(jù):

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 開頭的命名空間的所有配置項(xiàng), 可以使用LIKE語句

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

執(zhí)行結(jié)果:

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

如果想去掉h5命名空間前綴, 可以使用 substring 函數(shù)

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

執(zhí)行結(jié)果:

key	value
version	1.0.1
email	test@outlook.com

LIKE 在字符串全匹配，以及前置查詢?nèi)?h5.%的時(shí)候, 如果存在索引會有一定的優(yōu)化作用。不會進(jìn)行全表掃描

LOCATE 函數(shù)

LOCATE是一種查詢匹配字符串出現(xiàn)次數(shù)的函數(shù)

執(zhí)行語句:

 

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

執(zhí)行結(jié)果:

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

經(jīng)過相關(guān)資料的學(xué)習(xí), 最終認(rèn)為LIKE的效率與LOCATE的效率是無法對比誰快誰慢，相關(guān)文章推薦閱讀 MySQL LIKE vs LOCATE

總結(jié)

mysql, sql 里面的知識確實(shí)讓人感覺深奧. 此時(shí)此刻我只是解決了我遇到問題, 一會也會遇到更多不一樣的問題, 而這也是學(xué)習(xí)sql, 計(jì)算機(jī)的魅力. 以后遇到更多的關(guān)于SQL的問題, 會不斷更新...

歡迎大家收藏和點(diǎn)贊！?。?nbsp;