MySQL字符集和排序規(guī)則詳解(推薦)
前言
MySQL中,字符集和排序規(guī)則在處理文本數(shù)據(jù)時(shí)起著至關(guān)重要的作用,本文將詳細(xì)介紹 MySQL中的字符集和排序規(guī)則。
簡(jiǎn)介
字符集(Character Set):
- 字符集定義了數(shù)據(jù)庫(kù)中可以存儲(chǔ)的字符的集合。在MySQL中,常見(jiàn)的字符集包括utf8、utf8mb4、latin1等。
- utf8mb4字符集是用于在MySQL中存儲(chǔ)Unicode字符集(包括Emoji等特殊字符)的一種字符集,提供更廣泛的字符支持。
排序規(guī)則(Collation):
- 排序規(guī)則定義了如何對(duì)字符進(jìn)行比較和排序。不同的排序規(guī)則會(huì)影響字符比較的結(jié)果。
- 排序規(guī)則由字符集和排序方式組成,例如utf8_general_ci、utf8mb4_unicode_ci等。
字符集
常見(jiàn)字符集 以及 說(shuō)明
- utf8:
UTF-8 是一種變長(zhǎng)字符編碼,可以表示世界上幾乎所有的字符。utf8是MySQL中常用的字符集,但在處理某些特殊字符(如 Emoji)時(shí)可能會(huì)有問(wèn)題。
- utf8mb4:
utf8mb4 是 utf8 的超集,支持存儲(chǔ)更廣泛的字符范圍(包括 Emoji 等)。通常用于支持更廣泛的語(yǔ)言和符號(hào)。
- latin1:
Latin1 是一種較老的字符集,適用于大多數(shù)西歐語(yǔ)言和部分其他語(yǔ)言的字符。
- latin2:
Latin2 是擴(kuò)展的 Latin1 字符集,支持中東歐語(yǔ)言中的額外字符。
- cp1251:
CP1251 是常用于俄語(yǔ)的字符集。
- utf16:
UTF-16 是一種固定長(zhǎng)度字符編碼,用于表示 Unicode 字符。每個(gè)字符占兩個(gè)字節(jié)。
- utf32:
UTF-32 是一種固定長(zhǎng)度字符編碼,用于表示 Unicode 字符。每個(gè)字符占四個(gè)字節(jié)。
- binary:
Binary 字符集以二進(jìn)制方式存儲(chǔ)數(shù)據(jù),并且對(duì)存儲(chǔ)的數(shù)據(jù)進(jìn)行大小寫(xiě)敏感的比較。
- ascii:
ASCII 字符集只支持 ASCII 字符集中的字符,范圍較窄。
大多數(shù)選擇 utf8mb4 字符集 同時(shí)也支持表情存儲(chǔ)
排序規(guī)則
常見(jiàn)的排序規(guī)則
以字符集 utf8mb4 為例
- utf8mb4_bin:這是一個(gè)大小寫(xiě)敏感的二進(jìn)制排序規(guī)則,直接根據(jù)字符的二進(jìn)制值進(jìn)行比較,區(qū)分大小寫(xiě)。
- utf8mb4_croatian_ci:克羅地亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于克羅地亞語(yǔ)文本的比較和排序。
- utf8mb4_czech_ci:捷克語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于捷克語(yǔ)文本的比較和排序。
- utf8mb4_danish_ci:丹麥語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于丹麥語(yǔ)文本的比較和排序。
- utf8mb4_esperanto_ci:世界語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于世界語(yǔ)文本的比較和排序。
- utf8mb4_estonian_ci:愛(ài)沙尼亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于愛(ài)沙尼亞語(yǔ)文本的比較和排序。
- utf8mb4_general_ci:這是一個(gè)通用的大小寫(xiě)不敏感排序規(guī)則,適用于大多數(shù)情況下的文本比較和排序。
- utf8mb4_german2_ci:德語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于德語(yǔ)文本的比較和排序。
- utf8mb4_hungarian_ci:匈牙利語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于匈牙利語(yǔ)文本的比較和排序。
- utf8mb4_icelandic_ci:冰島語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于冰島語(yǔ)文本的比較和排序。
- utf8mb4_latvian_ci:拉脫維亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于拉脫維亞語(yǔ)文本的比較和排序。
- utf8mb4_lithuanian_ci:立陶宛語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于立陶宛語(yǔ)文本的比較和排序。
- utf8mb4_persian_ci:波斯語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于波斯語(yǔ)文本的比較和排序。
- utf8mb4_polish_ci:波蘭語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于波蘭語(yǔ)文本的比較和排序。
- utf8mb4_roman_ci:羅馬尼亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于羅馬尼亞語(yǔ)文本的比較和排序。
- utf8mb4_romanian_ci:羅馬尼亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于羅馬尼亞語(yǔ)文本的比較和排序。
- utf8mb4_sinhala_ci:僧伽羅語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于僧伽羅語(yǔ)文本的比較和排序。
- utf8mb4_slovak_ci:斯洛伐克語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于斯洛伐克語(yǔ)文本的比較和排序。
- utf8mb4_slovenian_ci:斯洛文尼亞語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于斯洛文尼亞語(yǔ)文本的比較和排序。
- utf8mb4_spanish2_ci:西班牙語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于西班牙語(yǔ)文本的比較和排序。
- utf8mb4_spanish_ci:西班牙語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于西班牙語(yǔ)文本的比較和排序。
- utf8mb4_swedish_ci:瑞典語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于瑞典語(yǔ)文本的比較和排序。
- utf8mb4_turkish_ci:土耳其語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于土耳其語(yǔ)文本的比較和排序。
- utf8mb4_unicode_520_ci:Unicode 5.20版本的大小寫(xiě)不敏感排序規(guī)則,適用于支持 Unicode 5.20字符集的文本比較和排序。
- utf8mb4_unicode_ci:Unicode的大小寫(xiě)不敏感排序規(guī)則,適用于支持Unicode字符集的文本比較和排序。
- utf8mb4_vietnamese_ci:越南語(yǔ)的大小寫(xiě)不敏感排序規(guī)則,適用于越南語(yǔ)文本的比較和排序。
比較常用的應(yīng)該就是以下4個(gè)
- utf8mb4_general_ci: 不區(qū)分大小寫(xiě),一般用于一般文本比較,適合非敏感數(shù)據(jù)。
- utf8mb4_unicode_ci: 不區(qū)分大小寫(xiě),支持多語(yǔ)言,適合需要多語(yǔ)言支持的應(yīng)用。
- utf8mb4_bin: 區(qū)分大小寫(xiě),按照二進(jìn)制順序比較,適合敏感數(shù)據(jù)比較。
選擇排序規(guī)則的考慮因素:
- 大小寫(xiě)敏感性:
_ci(Case Insensitive):不區(qū)分大小寫(xiě)。
_cs(Case Sensitive):區(qū)分大小寫(xiě)。 選擇對(duì)大小寫(xiě)敏感的排序規(guī)則,如utf8mb4_bin。
- 多語(yǔ)言支持:
_unicode_ci:提供對(duì)多語(yǔ)言的支持,適用于需要處理不同語(yǔ)言字符的場(chǎng)景。
- 排序方式:
_ci(Case Insensitive):按照不區(qū)分大小寫(xiě)的方式進(jìn)行排序。
_bin(Binary):按照二進(jìn)制方式進(jìn)行排序,嚴(yán)格按照字符的編碼值進(jìn)行比較。
- 性能考慮:
不同排序規(guī)則對(duì)性能有影響,一些排序規(guī)則可能比其他規(guī)則更快。
選擇合適的排序規(guī)則有助于優(yōu)化查詢性能。
實(shí)踐
我們來(lái)測(cè)試下 區(qū)分大小寫(xiě)和不區(qū)分大小寫(xiě)的排序規(guī)則 查詢有何不同
新建表
新建bs表 同時(shí) name 排序規(guī)則設(shè)置為utf8mb4_general_ci 大小寫(xiě)不敏感
CREATE TABLE `bs` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
測(cè)試數(shù)據(jù)寫(xiě)入
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('1','a');
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('2','A');
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('3','aA');
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('4','Aa');
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('5','AA');
INSERT INTO `test`.`bs` (`id`,`name`) VALUES ('6','aa');排序規(guī)則 不區(qū)分大小寫(xiě) 數(shù)據(jù)測(cè)試
select * from bs where name= 'A';

select * from bs where name= 'a';

select * from bs where name= 'aa';

select * from bs where name= 'AA';

select * from bs where name= 'aA';

select * from bs where name= 'Aa';

select * from bs where name LIKE '%A';

select * from bs where name LIKE '%a';

select * from bs where name= '%A%';

select * from bs where name= '%a%';

排序規(guī)則 區(qū)分大小寫(xiě) 數(shù)據(jù)測(cè)試
修改表name 字段為 排序規(guī)則為 utf8mb4_bin
ALTER TABLE `bs` MODIFY COLUMN `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ;
select * from bs where name= 'A';

select * from bs where name= 'a';

select * from bs where name= 'aa';

select * from bs where name= 'AA';

select * from bs where name= 'aA';

select * from bs where name= 'Aa';

select * from bs where name LIKE '%A';

select * from bs where name LIKE '%a';

select * from bs where name LIKE '%A%';

select * from bs where name LIKE '%a%';

總結(jié)
可以看到在排序規(guī)則區(qū)分大小寫(xiě)和不區(qū)分大小寫(xiě) 對(duì)SQL查詢的結(jié)果 影響還是比較大的。
所以在選擇字符集時(shí),需要考慮使用的語(yǔ)言、特殊字符的需求以及數(shù)據(jù)存儲(chǔ)的具體情況。確保所選字符集能夠覆蓋項(xiàng)目中的所有字符需求,并選擇合適的排序規(guī)則以確保數(shù)據(jù)的正確比較和排序。
到此這篇關(guān)于MySQL字符集和排序規(guī)則詳解的文章就介紹到這了,更多相關(guān)MySQL字符集和排序規(guī)則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
設(shè)計(jì)性能更優(yōu)MySQL數(shù)據(jù)庫(kù)schema
這篇文章主要介紹了設(shè)計(jì)性能更優(yōu)MySQL數(shù)據(jù)庫(kù)schema,文章通過(guò)數(shù)據(jù)類型優(yōu)化展開(kāi)對(duì)主題內(nèi)容的詳細(xì)介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下2022-09-09
MySQL8新特性之降序索引底層實(shí)現(xiàn)詳解
這篇文章主要介紹了MySQL8新特性之降序索引底層實(shí)現(xiàn)詳解,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-05-05
win8.1安裝mysql5.6時(shí)遇到問(wèn)題解決方案
本文主要記錄的是作者在win8.1安裝mysql5.6時(shí)遇到問(wèn)題的解決方案,網(wǎng)上查了很多方法都沒(méi)能解決,這里把最后的方法分享給大家2016-10-10
使用Canal和Kafka解決MySQL與緩存的數(shù)據(jù)一致性問(wèn)題
這篇文章主要介紹了使用Canal和Kafka解決MySQL與緩存的數(shù)據(jù)一致性問(wèn)題,文中通過(guò)圖文結(jié)合的方式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-07-07
MySql 5.7.17壓縮包免安裝的配置過(guò)程圖解
這篇文章主要介紹了MySql 5.7.17壓縮包免安裝的配置過(guò)程圖解,本文圖文并茂給大家介紹的非常詳細(xì),感興趣的朋友跟隨腳本之家小編一起學(xué)習(xí)吧2018-05-05

