MySQL中COLLATE是用來做什麼的?

語言: CN / TW / HK

theme: awesome-green

📢📢📢📣📣📣\ 哈嘍!大家好,我是【IT邦德】,江湖人稱jeames007,10年DBA工作經驗\ 中國DBA聯盟(ACDU)成員,目前從事DBA及程式程式設計😜😜😜\ 擅長Oracle、MySQL、PG 運維開發,備份恢復,安裝遷移,效能優化、故障應急處理等。\ ❤️❤️❤️感謝各位大可愛小可愛!❤️❤️❤️

⛳️ 1.COLLATE介紹

COLLATE即使校驗規則, 會影響到 ORDER BY 語句的順序, 會影響到 WHERE 條件中大於小於號篩選出來的結果, 會影響DISTINCT、GROUP BY、HAVING語句的查詢結果。 另外,mysql 建索引的時候,如果索引列是字元型別, 也會影響索引建立,只不過這種影響我們感知不到。 總之,凡是涉及到字元型別比較或排序的地方,都會和 COLLATE 有關

在mysql中執行show create table 指令,可以看到一張表的建表語句

image.png

image.png

所謂所謂utf8_unicode_ci,其實是用來排序的規則, 對於mysql中那些字元型別的列, 如VARCHAR,CHAR,TEXT型別的列, 都需要有一個COLLATE型別來告知mysql如何對該列進行排序和比較。

⛳️ 2.COLLATE的區別

COLLATE 通常是和資料編碼(CHARSET )相關的 ,\ 一般來說每種 CHARSET 都有多種它所支援的 COLLATE,\ 並且每種 CHARSET 都指定一種 COLLATE 為預設值。

latin1 編碼的預設 COLLATE 為 latin1_swedish_ci GBK 編碼的預設 COLLATE 為 gbk_chinese_ci utf8mb4 編碼的預設值為 utf8mb4_general_ci 另外需要注意的一點是, 從 mysql 8.0 開始, mysql 預設的 CHARSET 已經不再是 Latin1 了, 改為了 utf8mb4,並且預設的 COLLATE 也改為了 utf8mb4_0900_ai_ci

mysql> show charset where charset in (‘gbk’,‘utf8mb4’,‘latin1’);

image.png

⛳️ 3.COLLATE的影響

🌈 3.1 大小寫影響

create table case_bin_test (word VARCHAR(10));\ INSERT INTO case_bin_test VALUES (‘Frank’),(‘Google’),(‘froogle’),(‘flickr’),(‘FlicKr’);

image.png

mysql查詢預設是不區分大小寫的,如下所示

image.png

如果想區分大小寫,則要加binary,如下所示\

image.png

image.png

🌈 3.2 排序影響

MySQL預設的order by是不區分大小寫的

建表

create table test_collate (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, text varchar(40) NOT NULL )

插入資料

insert into test_collate(text) values(‘aaa’),(‘bbb’),(‘AAA’),(‘BBB’); 現在,如果我們按照text排序:\ mysql> select * from test_collate order by text;

image.png

結果表明,大寫的AAA和小寫的aaa被認為是一樣的, 都排在BBB和bbb前面 現在,如果我們按照text排序,使用utf8mb4_bin:\ select * from test_collate order by text collate utf8mb4_bin;

image.png

相關推薦閱讀: MySQL8.0新特性搶先看,效能又雙叒提升了\ MySQL武林祕籍,SQL學廢必過考試\ Linux7.6原始碼安裝Mysql8\ Oracle巡檢指令碼大全,伺服器可直接部署\ MySQL root密碼忘記找回妙招\ 監控神器Zabbix,從部署到應用\ Oracle監聽日誌清除

大家可以點贊、收藏、關注、評論我啦 、有資料庫相關的問題隨時聯絡我或交流喲~!

image.png