MySQL 中有哪些排序規(guī)則

本篇文章為大家展示了MySQL 中有哪些排序規(guī)則，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

簡述

說起排序規(guī)則就離不開字符集，嚴格來說，排序規(guī)則是依賴于字符集的。

字符集是用來定義MySQL存儲不同字符的方式，而排序規(guī)則一般指對字符集中字符串之間的比較、排序制定的規(guī)則。一種字符集可以對應(yīng)多種排序規(guī)則，但是一種排序規(guī)則只能對應(yīng)指定的一種字符集，兩個不同的字符集不能有相同的排序規(guī)則。

上圖中，Collation 列表示排序方式，Charset 列表示字符集，可以看出 utf8  字符集對應(yīng)著許多的排序方式，排序方式那一列每一項的值都不一樣，并且每一項都對應(yīng)唯一一種字符集，在這里是 utf8 字符集。

默認排序規(guī)則

• 字符集的默認排序規(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)

每種字符集都有一個默認的排序規(guī)則, 可以通過下面的SQL語句查詢指定字符集的默認排序規(guī)則

上面的例子是查詢字符集前綴包含utf8的默認排序方式，從中可以得知：

utf8字符集的默認排序方式是 utf8_general_ci 字符集中字符最大長度占3個字節(jié)

utf8mb4 字符集的默認排序方式是 utf8mb4_general_ci 字符集中字符最大長度占4個字節(jié)

• 數(shù)據(jù)庫的默認排序規(guī)則

MySQL服務(wù)器的默認字符集可以在 /etc/my.cnf 配置中的 [mysqld] 下修改

例如：現(xiàn)需要把MySQL服務(wù)器的默認字符集設(shè)置為 utf8, 默認排序規(guī)則設(shè)置為 utf8_general_ci, 只需要在 /etc/my.cnf  配置文件的 [mysqld] 下添加以下子項

character-set-server=utf8 collation-server=utf8_general_ci

創(chuàng)建數(shù)據(jù)庫的時候如果沒有指定字符集，會使用MySQL服務(wù)器默認字符集和默認排序規(guī)則

假如: 在下面例子中，MySQL服務(wù)器的默認字符集和默認排序規(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ù)庫，創(chuàng)建數(shù)據(jù)庫的時候沒有為數(shù)據(jù)庫指定字符集和排序規(guī)則，此時會使用 MySQL服務(wù)器的默認字符集和排序規(guī)則

通過SQL語句查詢 at 數(shù)據(jù)庫的默認字符集和默認排序規(guī)則，結(jié)果和MySQL服務(wù)器的默認字符集和默認排序規(guī)則是一樣的

排序規(guī)則命名以及名字后綴

• 命名

排序規(guī)則的命名是以和它自身關(guān)聯(lián)的字符集名字開頭的，后面再接一個或多個后綴來表示指定字符集的一種排序規(guī)則

例如：utf8_general_ci 和 utf8_bin 就是 utf8字符集的兩種排序規(guī)則, latin1_swedish_ci 是  latin1字符集的排序規(guī)則

注意： binary 字符集只有一種排序規(guī)則,并且它的排序規(guī)則名字和字符集名字一樣, 也是 binary

• 后綴

排序規(guī)則名字的后綴是有特殊意義的，根據(jù)后綴可以知道排序規(guī)則是否區(qū)分大小寫，是否區(qū)分重音，是否是二進制等等，下面列出了部分后綴的說明

_ci : 不區(qū)分大小寫, Case-insensitive的縮寫  _cs : 區(qū)分大小寫，Case-sensitive的縮寫  _ai : 不區(qū)分重音，Accent-insensitive的縮寫  _as : 區(qū)分重音，Accent-sensitive的縮寫  _bin : 二進制

1: 不區(qū)分大小寫

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ū)分大小寫的，所以字符 a 和字符 A 會被當做相同字符處理

2: 區(qū)分大小寫

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 是會區(qū)分大小寫的，所以字符 a 和字符 A 會被認為是兩個不同的字符

3: 二進制

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 'à' = 'a'; +------------+ | 'à' = 'a'  | +------------+ |          0 | +------------+ 1 row in set (0.00 sec)

上面例子使用的排序規(guī)則是 utf8_bin 從結(jié)果可以得知：utf8_bin 排序規(guī)則區(qū)分大小寫，也區(qū)分重音字符

4: 是否區(qū)分重音

重音字符是類似 à、ě、ň 的字符，不區(qū)分重音是指字符 a和 à、e和ě 以及 n和ň 被認為是同一個字符

對于非二進制(后綴為 _bin)的排序規(guī)則, 如果排序規(guī)則名字后綴不包含 _ai 和 _as, 則排序規(guī)則名稱中的 _ci 默認隱含了_ai,  _cs默認隱含了_as

例如: utf8_unicode_ci排序規(guī)則是不區(qū)分大小寫并且隱含不區(qū)分重音的

latin1_general_cs 排序規(guī)則是區(qū)分大小寫并且隱含區(qū)分重音的

具體的請查看下面的例子

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; Query OK, 0 rows affected (0.02 sec)  mysql> select 'à' = 'a'; +------------+ | 'à' = '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 'à' = 'a'; +------------+ | 'à' = 'a'  | +------------+ |          0 | +------------+ 1 row in set (0.01 sec)

查看排序規(guī)則

• 查看數(shù)據(jù)庫數(shù)據(jù)庫的排序規(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ù)庫的字符集和排序規(guī)則，從結(jié)果可以得知：at數(shù)據(jù)庫的排序規(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)

實例中 use at SQL語句切換到 at 數(shù)據(jù)庫，然后使用 show variables like 'collation_database';  語句查詢 at 數(shù)據(jù)庫的排序規(guī)則

• 查看表的排序規(guī)則

方法1：根據(jù)數(shù)據(jù)庫名和表名查看 INFORMATION_SCHEMA.TABLES 表中的 TABLE_COLLATION  字段，可以得到指定數(shù)據(jù)庫中指定表的排序規(guī)則，具體的實例如下所示：

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 表名 語句查看

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)建語句，一般創(chuàng)建表的時候會指定排序規(guī)則，例子中沒有顯示指定表的排序規(guī)則，這是因為使用的是字符集的默認排序規(guī)則，tc 表的字符集是 utf8 ,  默認的排序規(guī)則是 utf8_general_ci

• 查看字段的排序規(guī)則

上面 查看表的排序規(guī)則 小節(jié)的 方法2  是查看表的創(chuàng)建語句，字段的排序規(guī)則也可以從表創(chuàng)建語句中查看到，如果沒有顯示指定字段的排序規(guī)則，創(chuàng)建表的語句中是看不到排序規(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

字段 a 沒有顯示指定排序規(guī)則，則它使用的是utf8字符集的默認排序規(guī)則utf8_general_ci

字段 b 的排序規(guī)則是 utf8_bin

字段 c 的排序規(guī)則是 latin1_general_cs

修改排序規(guī)則

修改數(shù)據(jù)庫的排序規(guī)則

數(shù)據(jù)庫的默認排序規(guī)則可以通過 alter database 數(shù)據(jù)庫名 collate 新的排序規(guī)則名字 SQL語句進行修改

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ù)庫初始的字符集和排序規(guī)則分別是 utf8 和 utf8_general_ci

SQL語句: alter database at collate utf8_bin; 把字符集和排序規(guī)則修改為 utf8 和 utf8_bin

SQL語句: alter database at collate latin1_swedish_ci; 把字符集和排序規(guī)則修改為 latin1 和  latin1_swedish_ci

由于latin1_swedish_ci排序規(guī)則 是屬于latin1字符集，所以 at 數(shù)據(jù)庫排序規(guī)則修改成 latin1_swedish_ci  之后，字符集相應(yīng)的也會由 utf8 變成 latin1

• 修改表的排序規(guī)則

通過 alter table 表名 collate 新的排序規(guī)則名字; 語句可以修改表的排序規(guī)則

注意：上面的語句修改表排序規(guī)則，對現(xiàn)有字段的排序規(guī)則沒影響，只影響后續(xù)新添加字段的默認排序規(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)  mysql> alter table tc collate latin1_swedish_ci; Query OK, 0 rows affected (0.00 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 utf8 COLLATE utf8_bin NOT NULL,   `c` char(32) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

上面例子中，tc表初始的字符集是utf8,默認排序規(guī)則是utf8_general_ci 執(zhí)行 alter table tc collate  latin1_swedish_ci;SQL語句把表的排序規(guī)則修改成latin1_swedish_ci

由于latin1_swedish_ci排序規(guī)則是屬于latin1字符集的，所以此時表的字符集也會修改成latin1

修改表排序規(guī)則之前，字段a的排序規(guī)則是默認的utf8_general_ci，在創(chuàng)建表的語句中沒有顯示出來

修改排序規(guī)則之后，由于表默認的排序規(guī)則發(fā)生的變化，所以字段a會顯示出它原本的字符集

• 修改字段的排序規(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

語句 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ī)則，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。