mysql数据库编码

字符编码校对规则（排序规则）：

在进行字符编码设置时，还需要设置校对规则信息，校对规则是什么？

主要可以根据校对规则定义或设置的不同，在存储数据信息时，影响数据信息的排序效果；

# 查看数据库服务中可用的校对规则信息
mysql> show collation;
+----------------------------+------------+------+----------+-------------+-----------+------------------+
| Collation                        | Charset   | Id    | Default | Compiled | Sortlen   | Pad_attribute |
+----------------------------+------------+------+----------+-------------+-----------+------------------+
| utf8mb4_0900_ai_ci     | utf8mb4  | 255 | Yes        | Yes            |            0   | NO PAD            |
| utf8mb4_0900_bin        | utf8mb4  | 309 |              | Yes            |            1   | NO PAD            |
​
# 设置数据库服务中可用的校对规则信息
mysql> create database xiaoA charset utf8 collate utf8_general_mysql500_ci;
mysql> show create database xiaoA;
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                    |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| xiaoA    | CREATE DATABASE `xiaoA` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
--- 数据库设置字符编码校对规则
​
mysql> use xiaoA;
Database changed
mysql> create table t1 (id int) charset utf8 collate utf8_german2_ci;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
​
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_german2_ci |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--- 数据表设置字符编码校对规则