建站
咨詢
售后
建站咨詢
新聞中心
前言
網(wǎng)站的建設(shè)創(chuàng)新互聯(lián)建站專注網(wǎng)站定制,經(jīng)驗(yàn)豐富,不做模板,主營(yíng)網(wǎng)站定制開(kāi)發(fā).小程序定制開(kāi)發(fā),H5頁(yè)面制作!給你煥然一新的設(shè)計(jì)體驗(yàn)!已為發(fā)電機(jī)租賃等企業(yè)提供專業(yè)服務(wù)。
身為一名前端工程師, 對(duì)于 SQL了解程度并不是很深刻, 盤點(diǎn)一些個(gè)人工作遇到的問(wèn)題，給大家普及下知識(shí), 以及記錄自己如何解決這些問(wèn)題的。
SELECT 查詢語(yǔ)句不區(qū)分字母大小寫?
相信這是一個(gè)非常常見(jiàn)的問(wèn)題了, 而這個(gè)問(wèn)題的原因主要還是表字符集引起的。
假設(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í)行語(yǔ)句為:
- 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 |
為什么會(huì)有這種現(xiàn)象?
mysql 默認(rèn)對(duì)字符匹配排序大小寫不敏感, 字段包括 varchar, char, text 內(nèi)容. 如果要確實(shí)要區(qū)分大小寫, 則在建表或者查表的時(shí)候使用 BINARY 屬性. 二進(jìn)制的 A 與 a 還是有區(qū)別的 ~~
解決方案1 : 修改sql語(yǔ)句
- 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)
建表語(yǔ)句
- 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;
修改表語(yǔ)句
- ALTER TABLE `config` MODIFY COLUMN `key` varchar(255) BINARY NOT NULL;
SELECT IN 語(yǔ)句順序不符合傳入時(shí)要求?
以config表為例, 表內(nèi)數(shù)據(jù):
| key | value | id |
|---|---|---|
| 295697141@qq.com | 1 | |
| username | 我 | 2 |
SQL語(yǔ)句:
- SELECT `key`, `value` FROM `config` WHERE `key` IN ('username', 'email');
執(zhí)行結(jié)果:
| key | value |
|---|---|
| 295697141@qq.com | |
| username | 我 |
明明是username 優(yōu)先于 email, 結(jié)果卻是 email優(yōu)先于 username. 原因在于 IN 查詢只負(fù)責(zé)查詢, 不負(fù)責(zé)排序, 而默認(rèn)排序是用 id asc, 所以得到了一個(gè)不符合IN查詢的結(jié)果
解決方案
- 使用 ORDER BY FIELD()
- 使用 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 | 我 |
| 295697141@qq.com |
注意： FIND_IN_SET 第二個(gè)參數(shù) strlist 逗號(hào)之間不需要空格
SELECT 存儲(chǔ)查詢生僻漢字, 結(jié)果亂碼 ?
前提，數(shù)據(jù)庫(kù)和表都是采用的是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)了???? 這種情況，難道說(shuō) utf8字符集沒(méi)有記錄這個(gè)生僻字么?
mysql 支持的 utf8 編碼***字符長(zhǎng)度為 3 字節(jié)，如果遇到 4 字節(jié)的寬字符就會(huì)插入異常了。三個(gè)字節(jié)的 UTF-8 ***能編碼的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文種平面（BMP）。也就是說(shuō)，任何不在基本多文本平面的 Unicode字符，都無(wú)法使用 Mysql 的 utf8 字符集存儲(chǔ)。包括 Emoji 表情（Emoji 是一種特殊的 Unicode 編碼，常見(jiàn)于 ios 和 android 手機(jī)上），和很多不常用的漢字，以及任何新增的 Unicode 字符等等。
引用一段 關(guān)于 MySQL UTF8 編碼下生僻字符插入失敗/假死問(wèn)題的分析 內(nèi)容
解決方案：
修改字符集
- --修改數(shù)據(jù)庫(kù)字符集
- 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表舉一個(gè)例子, 假如有以下的行數(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語(yǔ)句
如果我們想要查詢以app 或 h5 開(kāi)頭的命名空間的所有配置項(xiàng), 可以使用LIKE語(yǔ)句
- 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 |
| test@outlook.com |
LIKE 在字符串全匹配，以及前置查詢?nèi)?h5.%的時(shí)候, 如果存在索引會(huì)有一定的優(yōu)化作用。不會(huì)進(jìn)行全表掃描
LOCATE 函數(shù)
LOCATE是一種查詢匹配字符串出現(xiàn)次數(shù)的函數(shù)
執(zhí)行語(yǔ)句:
- 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ò)相關(guān)資料的學(xué)習(xí), 最終認(rèn)為L(zhǎng)IKE的效率與LOCATE的效率是無(wú)法對(duì)比誰(shuí)快誰(shuí)慢，相關(guān)文章推薦閱讀 MySQL LIKE vs LOCATE
總結(jié)
mysql, sql 里面的知識(shí)確實(shí)讓人感覺(jué)深?yuàn)W. 此時(shí)此刻我只是解決了我遇到問(wèn)題, 一會(huì)也會(huì)遇到更多不一樣的問(wèn)題, 而這也是學(xué)習(xí)sql, 計(jì)算機(jī)的魅力. 以后遇到更多的關(guān)于SQL的問(wèn)題, 會(huì)不斷更新...
歡迎大家收藏和點(diǎn)贊?。?！
新聞標(biāo)題：【MYSQL】業(yè)務(wù)上碰到的SQL問(wèn)題整理集合
當(dāng)前路徑：http://www.dlmjj.cn/article/dghhejp.html
