MySQL 排序规则简介-白红宇的个人博客

MySQL 排序规则简介

发布日期：2021-05-14 06:27:18 浏览次数：17 分类：博客文章

本文共 15545 字，大约阅读时间需要 51 分钟。

��MySQL��MySQL��

��

��MySQL��

��Collation ��Charset �� utf8 �� utf8 ��

��

��

��, ��SQL��

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)

��utf8��

utf8�� utf8_general_ci ��3��

utf8mb4 �� utf8mb4_general_ci ��4��

��

MySQL�� /etc/my.cnf �� [mysqld] ��

��MySQL�� utf8, �� utf8_general_ci, �� /etc/my.cnf �� [mysqld] ��

character-set-server=utf8collation-server=utf8_general_ci

��MySQL��

��: ��MySQL�� 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)

�� at �� MySQL��

��SQL�� at ��MySQL��

��

��

��

��utf8_general_ci �� utf8_bin �� utf8��, latin1_swedish_ci �� latin1��

�� binary ��,��, �� binary

��

��

_ci : ������������������, Case-insensitive���������_cs : ������������������Case-sensitive���������_ai : ������������������Accent-insensitive���������_as : ���������������Accent-sensitive���������_bin : ���������

1: ��

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)

�� utf8_unicode_ci �� a �� A ��

2: ��

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)

�� latin1_general_cs �� 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)

�� utf8_bin ��utf8_bin ��

4: ��

�� �� �� a�� ����e���� �� n���� ��

��(�� _bin)��, ��

_ai �� _as, �� _ci ��_ai, _cs��_as

��: utf8_unicode_ci��

latin1_general_cs ��

��

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)

��

��

��1: INFORMATION_SCHEMA.SCHEMATA�� DEFAULT_COLLATION_NAME ��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          | latin1                     | latin1_swedish_ci      |+-------------+----------------------------+------------------------+1 row in set (0.00 sec)

�� at ��at��latin1_swedish_ci

��2�� collation_database ��

mysql> use at;Database changedmysql> show variables like 'collation_database';+--------------------+-------------------+| Variable_name      | Value             |+--------------------+-------------------+| collation_database | latin1_swedish_ci |+--------------------+-------------------+1 row in set (0.00 sec)

�� use at SQL�� at �� show variables like 'collation_database'; �� at ��

��

��1�� INFORMATION_SCHEMA.TABLES �� TABLE_COLLATION ��

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�� show create table �� ��

mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=utf81 row in set (0.00 sec)

��, show create table tc\G �� tc ��tc �� utf8 , �� utf8_general_ci

��

�� 2 ��

mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=utf81 row in set (0.00 sec)

��,�� tc��utf8

�� a ��utf8��utf8_general_ci

�� b �� utf8_bin

�� c �� latin1_general_cs

��

��

�� alter database �� collate �� 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 �� utf8 �� utf8_general_ci

SQL��: alter database at collate utf8_bin; �� utf8 �� utf8_bin

SQL��: alter database at collate latin1_swedish_ci; �� latin1 �� latin1_swedish_ci

��latin1_swedish_ci�� latin1�� at �� latin1_swedish_ci �� utf8 �� latin1

��

�� alter table �� collate ��; ��

��

mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=utf81 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: 0mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=latin11 row in set (0.00 sec)

��tc��utf8,��utf8_general_ci �� alter table tc collate latin1_swedish_ci;SQL��latin1_swedish_ci

��latin1_swedish_ci��latin1��latin1

��a��utf8_general_ci��

��a��

��

mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=latin11 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: 0mysql> show create table tc\G*************************** 1. row ***************************       Table: tcCreate 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=latin11 row in set (0.00 sec)

�� b �� utf8��utf8_bin

�� alter table tc modify b char(32) not null collate latin1_general_cs;�� b �� latin1_general_cs, ��latin1_general_cs ��latin1��, �� b ��

��

��MySQL��MySQL��

上一篇：有问题找男人帮忙- Linux下man命令

下一篇：再也不用担心 SSH 断开了 - tmux 命令

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

��

��

��

��

��

��

发表评论

最新留言

关于作者

推荐文章

������

������������������

������������������������������������

������������������

������������������

������

发表评论

最新留言

关于作者

推荐文章

��

��

��

��

��

��