成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

【MYSQL】業(yè)務(wù)上碰到的SQL問(wèn)題整理集合

YPHP / 1979人閱讀

摘要：不會(huì)進(jìn)行全表掃描函數(shù)是一種查詢匹配字符串出現(xiàn)次數(shù)的函數(shù)執(zhí)行語(yǔ)句執(zhí)行結(jié)果經(jīng)過(guò)相關(guān)資料的學(xué)習(xí)最終認(rèn)為的效率與的效率是無(wú)法對(duì)比誰(shuí)快誰(shuí)慢，相關(guān)文章推薦閱讀查詢結(jié)果中文亂碼原因主要是的編碼字符集與數(shù)據(jù)庫(kù)的字符集不一致導(dǎo)致的。

前言 
身為一名前端工程師, 對(duì)于 SQL了解程度并不是很深刻, 盤(pán)點(diǎn)一些個(gè)人工作遇到的問(wèn)題，給大家普及下知識(shí), 以及記錄自己如何解決這些問(wèn)題的.
導(dǎo)航

SELECT 語(yǔ)句不區(qū)分大小寫(xiě)?

SELECT IN 語(yǔ)句順序不符合傳入時(shí)要求?

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

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

SELECT 查詢結(jié)果中文亂碼?

SHOW 查詢表字段的詳細(xì)描述?

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

相信這是一個(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ì)字符匹配排序大小寫(xiě)不敏感, 字段包括 varchar, char, text 內(nèi)容. 如果要確實(shí)要區(qū)分大小寫(xiě), 則在建表或者查表的時(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
email [email protected] 1
username 我 2

SQL語(yǔ)句:

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

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

key value
email [email protected]
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 我
email [email protected]

注意： 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 [email protected] 10
h5.email [email protected] 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 [email protected]

如果想去掉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 [email protected]

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 [email protected]

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

SELECT 查詢結(jié)果中文亂碼?

原因：主要是 mysql client 的編碼字符集與數(shù)據(jù)庫(kù)的字符集不一致導(dǎo)致的。

查看字符集方式

mysql> show variables like "%char%";

+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

解決方案

1. 修改字符集

mysql> set charset utf8;

mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

2. 直接在client連接時(shí)指定字符集

mysql --default-character-set=utf8-h 127.0.0.1 -uroot -p123456 -P3306 -D test
查詢表字段的詳細(xì)描述?

查看表的字段描述主要有以下兩種方式

desc table_name;

show full columns/fields from table_name;

1.查看簡(jiǎn)單的表字段描述

mysql> desc table_name;

+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| id                    | bigint(20)       | NO   | PRI | NULL    | auto_increment |
+-----------------------+------------------+------+-----+---------+----------------+

2.查看具體的表字段信息

mysql> show full fields from table_name;
+-----------------------+------------------+--------------------+------+-----+---------+----------------+----------------------+------------------+
| Field                 | Type             | Collation          | Null | Key | Default | Extra          | Privileges           | Comment          |
+-----------------------+------------------+--------------------+------+-----+---------+----------------+----------------------+------------------+
| id                    | bigint(20)       | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update | 自增主鍵          |
+-----------------------+------------------+--------------------+------+-----+---------+----------------+----------------------+------------------+
總結(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)贊?。?！

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/17652.html

相關(guān)文章

  • Java開(kāi)發(fā) 大廠面試整理

    摘要：用戶態(tài)不能干擾內(nèi)核態(tài)所以指令就有兩種特權(quán)指令和非特權(quán)指令不同的狀態(tài)對(duì)應(yīng)不同的指令。非特權(quán)指令所有程序均可直接使用。用戶態(tài)常態(tài)目態(tài)執(zhí)行非特權(quán)指令。 這是我今年從三月份開(kāi)始，主要的大廠面試經(jīng)過(guò)，有些企業(yè)面試的還沒(méi)來(lái)得及整理，可能有些沒(méi)有帶答案就發(fā)出來(lái)了，還請(qǐng)各位先思考如果是你怎么回答面試官？這篇文章會(huì)持續(xù)更新，請(qǐng)各位持續(xù)關(guān)注，希望對(duì)你有所幫助！ 面試清單 平安產(chǎn)險(xiǎn) 飛豬 上汽大通 浩鯨科...

    Scorpion 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<