共計(jì) 10648 個(gè)字符，預(yù)計(jì)需要花費(fèi) 27 分鐘才能閱讀完成。
自動(dòng)寫(xiě)代碼機(jī)器人，免費(fèi)開(kāi)通
本篇文章為大家展示了 MySQL 中有哪些排序規(guī)則，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。
簡(jiǎn)述
說(shuō)起排序規(guī)則就離不開(kāi)字符集，嚴(yán)格來(lái)說(shuō)，排序規(guī)則是依賴于字符集的。
字符集是用來(lái)定義 MySQL 存儲(chǔ)不同字符的方式，而排序規(guī)則一般指對(duì)字符集中字符串之間的比較、排序制定的規(guī)則。一種字符集可以對(duì)應(yīng)多種排序規(guī)則，但是一種排序規(guī)則只能對(duì)應(yīng)指定的一種字符集，兩個(gè)不同的字符集不能有相同的排序規(guī)則。
上圖中，Collation 列表示排序方式，Charset 列表示字符集，可以看出 utf8 字符集對(duì)應(yīng)著許多的排序方式，排序方式那一列每一項(xiàng)的值都不一樣，并且每一項(xiàng)都對(duì)應(yīng)唯一一種字符集，在這里是 utf8 字符集。
默認(rèn)排序規(guī)則
字符集的默認(rèn)排序規(guī)則
mysql show character set like utf8% +---------+---------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +---------+---------------+--------------------+--------+ 2 rows in set (0.00 sec)每種字符集都有一個(gè)默認(rèn)的排序規(guī)則, 可以通過(guò)下面的 SQL 語(yǔ)句查詢指定字符集的默認(rèn)排序規(guī)則
上面的例子是查詢字符集前綴包含 utf8 的默認(rèn)排序方式，從中可以得知：
utf8 字符集的默認(rèn)排序方式是 utf8_general_ci 字符集中字符最大長(zhǎng)度占 3 個(gè)字節(jié)
utf8mb4 字符集的默認(rèn)排序方式是 utf8mb4_general_ci 字符集中字符最大長(zhǎng)度占 4 個(gè)字節(jié)
數(shù)據(jù)庫(kù)的默認(rèn)排序規(guī)則
MySQL 服務(wù)器的默認(rèn)字符集可以在 /etc/my.cnf 配置中的 [mysqld] 下修改
例如：現(xiàn)需要把 MySQL 服務(wù)器的默認(rèn)字符集設(shè)置為 utf8, 默認(rèn)排序規(guī)則設(shè)置為 utf8_general_ci, 只需要在 /etc/my.cnf 配置文件的 [mysqld] 下添加以下子項(xiàng)
character-set-server=utf8 collation-server=utf8_general_ci創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候如果沒(méi)有指定字符集，會(huì)使用 MySQL 服務(wù)器默認(rèn)字符集和默認(rèn)排序規(guī)則
假如: 在下面例子中，MySQL 服務(wù)器的默認(rèn)字符集和默認(rèn)排序規(guī)則分別是 utf8 和 utf8_general_ci
mysql create database at; Query OK, 1 row affected (0.00 sec) mysql select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME= at +-------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | at | utf8 | utf8_general_ci | +-------------+----------------------------+------------------------+ 1 row in set (0.00 sec)在上面的例子中，創(chuàng)建了 at 數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候沒(méi)有為數(shù)據(jù)庫(kù)指定字符集和排序規(guī)則，此時(shí)會(huì)使用 MySQL 服務(wù)器的默認(rèn)字符集和排序規(guī)則
通過(guò) SQL 語(yǔ)句查詢 at 數(shù)據(jù)庫(kù)的默認(rèn)字符集和默認(rèn)排序規(guī)則，結(jié)果和 MySQL 服務(wù)器的默認(rèn)字符集和默認(rèn)排序規(guī)則是一樣的
排序規(guī)則命名以及名字后綴
命名
排序規(guī)則的命名是以和它自身關(guān)聯(lián)的字符集名字開(kāi)頭的，后面再接一個(gè)或多個(gè)后綴來(lái)表示指定字符集的一種排序規(guī)則
例如：utf8_general_ci 和 utf8_bin 就是 utf8 字符集的兩種排序規(guī)則, latin1_swedish_ci 是 latin1 字符集的排序規(guī)則
注意：binary 字符集只有一種排序規(guī)則, 并且它的排序規(guī)則名字和字符集名字一樣, 也是 binary
后綴
排序規(guī)則名字的后綴是有特殊意義的，根據(jù)后綴可以知道排序規(guī)則是否區(qū)分大小寫(xiě)，是否區(qū)分重音，是否是二進(jìn)制等等，下面列出了部分后綴的說(shuō)明
_ci : 不區(qū)分大小寫(xiě), Case-insensitive 的縮寫(xiě) _cs : 區(qū)分大小寫(xiě)，Case-sensitive 的縮寫(xiě) _ai : 不區(qū)分重音，Accent-insensitive 的縮寫(xiě) _as : 區(qū)分重音，Accent-sensitive 的縮寫(xiě) _bin : 二進(jìn)制1: 不區(qū)分大小寫(xiě)
mysql SET NAMES utf8 COLLATE utf8_unicode_ci Query OK, 0 rows affected (0.02 sec) mysql select a = A +-----------+ | a = A | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)例子中排序規(guī)則為 utf8_unicode_ci 是不區(qū)分大小寫(xiě)的，所以字符 a 和字符 A 會(huì)被當(dāng)做相同字符處理
2: 區(qū)分大小寫(xiě)
mysql SET NAMES latin1 COLLATE latin1_general_cs Query OK, 0 rows affected (0.00 sec) mysql select a = A +-----------+ | a = A | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)例子中排序規(guī)則為 latin1_general_cs 是會(huì)區(qū)分大小寫(xiě)的，所以字符 a 和字符 A 會(huì)被認(rèn)為是兩個(gè)不同的字符
3: 二進(jìn)制
mysql SET NAMES utf8 COLLATE utf8_bin Query OK, 0 rows affected (0.00 sec) mysql select a = A +-----------+ | a = A | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) mysql select agrave; = a +------------+ | agrave; = a | +------------+ | 0 | +------------+ 1 row in set (0.00 sec)上面例子使用的排序規(guī)則是 utf8_bin 從結(jié)果可以得知：utf8_bin 排序規(guī)則區(qū)分大小寫(xiě)，也區(qū)分重音字符
4: 是否區(qū)分重音
重音字符是類似 agrave;、ě、ň 的字符，不區(qū)分重音是指字符 a 和 agrave;、e 和ě 以及 n 和ň 被認(rèn)為是同一個(gè)字符
對(duì)于非二進(jìn)制 (后綴為 _bin) 的排序規(guī)則, 如果排序規(guī)則名字后綴不包含 _ai 和 _as, 則排序規(guī)則名稱中的 _ci 默認(rèn)隱含了_ai, _cs 默認(rèn)隱含了_as
例如: utf8_unicode_ci 排序規(guī)則是不區(qū)分大小寫(xiě)并且隱含不區(qū)分重音的
latin1_general_cs 排序規(guī)則是區(qū)分大小寫(xiě)并且隱含區(qū)分重音的
具體的請(qǐng)查看下面的例子
mysql SET NAMES utf8 COLLATE utf8_unicode_ci Query OK, 0 rows affected (0.02 sec) mysql select agrave; = a +------------+ | agrave; = a | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql SET NAMES latin1 COLLATE latin1_general_cs Query OK, 0 rows affected (0.00 sec) mysql select agrave; = a +------------+ | agrave; = a | +------------+ | 0 | +------------+ 1 row in set (0.01 sec)查看排序規(guī)則
查看數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)的排序規(guī)則
mysql select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME= at +-------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | at | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+ 1 row in set (0.00 sec)上面的例子是查看 at 數(shù)據(jù)庫(kù)的字符集和排序規(guī)則，從結(jié)果可以得知：at 數(shù)據(jù)庫(kù)的排序規(guī)則是 latin1_swedish_ci
方法 2：直接查詢 collation_database 變量值
mysql use at; Database changed mysql show variables like collation_database +--------------------+-------------------+ | Variable_name | Value | +--------------------+-------------------+ | collation_database | latin1_swedish_ci | +--------------------+-------------------+ 1 row in set (0.00 sec)實(shí)例中 use at SQL 語(yǔ)句切換到 at 數(shù)據(jù)庫(kù)，然后使用 show variables like collation_database 語(yǔ)句查詢 at 數(shù)據(jù)庫(kù)的排序規(guī)則
查看表的排序規(guī)則
方法 1：根據(jù)數(shù)據(jù)庫(kù)名和表名查看 INFORMATION_SCHEMA.TABLES 表中的 TABLE_COLLATION 字段，可以得到指定數(shù)據(jù)庫(kù)中指定表的排序規(guī)則，具體的實(shí)例如下所示：
mysql SELECT TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA= test and TABLE_NAME = tc +--------------+------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | +--------------+------------+-----------------+ | test | tc | utf8_general_ci | +--------------+------------+-----------------+ 1 row in set (0.00 sec)方法 2：執(zhí)行 show create table 表名 語(yǔ)句查看
mysql show create table tc\G *************************** 1. row *************************** Table: tc Create Table: CREATE TABLE `tc` ( `id` int(11) NOT NULL, `a` char(32) NOT NULL, `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)上面的例子中, show create table tc\G 是查看 tc 表的創(chuàng)建語(yǔ)句，一般創(chuàng)建表的時(shí)候會(huì)指定排序規(guī)則，例子中沒(méi)有顯示指定表的排序規(guī)則，這是因?yàn)槭褂玫氖亲址哪J(rèn)排序規(guī)則，tc 表的字符集是 utf8 , 默認(rèn)的排序規(guī)則是 utf8_general_ci
查看字段的排序規(guī)則
上面 查看表的排序規(guī)則 小節(jié)的 方法 2 是查看表的創(chuàng)建語(yǔ)句，字段的排序規(guī)則也可以從表創(chuàng)建語(yǔ)句中查看到，如果沒(méi)有顯示指定字段的排序規(guī)則，創(chuàng)建表的語(yǔ)句中是看不到排序規(guī)則的，這表示該字段使用字符集的默認(rèn)排序方式
mysql show create table tc\G *************************** 1. row *************************** Table: tc Create Table: CREATE TABLE `tc` ( `id` int(11) NOT NULL, `a` char(32) NOT NULL, `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)上面例子中, 表 tc 的字符集是 utf8
字段 a 沒(méi)有顯示指定排序規(guī)則，則它使用的是 utf8 字符集的默認(rèn)排序規(guī)則 utf8_general_ci
字段 b 的排序規(guī)則是 utf8_bin
字段 c 的排序規(guī)則是 latin1_general_cs
修改排序規(guī)則
修改數(shù)據(jù)庫(kù)的排序規(guī)則
數(shù)據(jù)庫(kù)的默認(rèn)排序規(guī)則可以通過(guò) alter database 數(shù)據(jù)庫(kù)名 collate 新的排序規(guī)則名字 SQL 語(yǔ)句進(jìn)行修改
mysql select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME= at +-------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | at | utf8 | utf8_general_ci | +-------------+----------------------------+------------------------+ 1 row in set (0.00 sec) mysql alter database at collate utf8_bin; Query OK, 1 row affected (0.00 sec) mysql select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME= at +-------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | at | utf8 | utf8_bin | +-------------+----------------------------+------------------------+ mysql alter database at collate latin1_swedish_ci ; Query OK, 1 row affected (0.00 sec) mysql select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME= at +-------------+----------------------------+------------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +-------------+----------------------------+------------------------+ | at | latin1 | latin1_swedish_ci | +-------------+----------------------------+------------------------+ 1 row in set (0.00 sec)
上面的例子中，at 數(shù)據(jù)庫(kù)初始的字符集和排序規(guī)則分別是 utf8 和 utf8_general_ci
SQL 語(yǔ)句: alter database at collate utf8_bin; 把字符集和排序規(guī)則修改為 utf8 和 utf8_bin
SQL 語(yǔ)句: alter database at collate latin1_swedish_ci; 把字符集和排序規(guī)則修改為 latin1 和 latin1_swedish_ci
由于 latin1_swedish_ci 排序規(guī)則 是屬于 latin1 字符集，所以 at 數(shù)據(jù)庫(kù)排序規(guī)則修改成 latin1_swedish_ci 之后，字符集相應(yīng)的也會(huì)由 utf8 變成 latin1
修改表的排序規(guī)則
通過(guò) alter table 表名 collate 新的排序規(guī)則名字; 語(yǔ)句可以修改表的排序規(guī)則
注意：上面的語(yǔ)句修改表排序規(guī)則，對(duì)現(xiàn)有字段的排序規(guī)則沒(méi)影響，只影響后續(xù)新添加字段的默認(rèn)排序規(guī)則
mysql show create table tc\G *************************** 1. row *************************** Table: tc Create Table: CREATE TABLE `tc` ( `id` int(11) NOT NULL, `a` char(32) NOT NULL, `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)上面例子中，tc 表初始的字符集是 utf8, 默認(rèn)排序規(guī)則是 utf8_general_ci 執(zhí)行 alter table tc collate latin1_swedish_ci;SQL 語(yǔ)句把表的排序規(guī)則修改成 latin1_swedish_ci
由于 latin1_swedish_ci 排序規(guī)則是屬于 latin1 字符集的，所以此時(shí)表的字符集也會(huì)修改成 latin1
修改表排序規(guī)則之前，字段 a 的排序規(guī)則是默認(rèn)的 utf8_general_ci，在創(chuàng)建表的語(yǔ)句中沒(méi)有顯示出來(lái)
修改排序規(guī)則之后，由于表默認(rèn)的排序規(guī)則發(fā)生的變化，所以字段 a 會(huì)顯示出它原本的字符集
修改字段的排序規(guī)則
mysql show create table tc\G *************************** 1. row *************************** Table: tc Create Table: CREATE TABLE `tc` ( `id` int(11) NOT NULL, `a` char(32) CHARACTER SET utf8 NOT NULL, `b` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, `d` char(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql alter table tc modify b char(32) not null collate latin1_general_cs; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show create table tc\G *************************** 1. row *************************** Table: tc Create Table: CREATE TABLE `tc` ( `id` int(11) NOT NULL, `a` char(32) CHARACTER SET utf8 NOT NULL, `b` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, `d` char(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)上面例子中，字段 b 原始的字符集和排序規(guī)則分別是 utf8、utf8_bin
語(yǔ)句 alter table tc modify b char(32) not null collate latin1_general_cs; 把字段 b 的排序規(guī)則修改成 latin1_general_cs, 由于 latin1_general_cs 排序規(guī)則是屬于 latin1 字符集, 所以修改之后字段 b 的字符集和排序規(guī)則都發(fā)生了變化
上述內(nèi)容就是 MySQL 中有哪些排序規(guī)則，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。
向 AI 問(wèn)一下細(xì)節(jié)
