MariaDB 10.3字符集设置 中文排序

1.MariaDB 10.3 的配置文件：
/etc/my.cnf.d/mariadb-server.cnf

2.MariaDB 10.3 的经典配置

#提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件
innodb_flush_log_at_trx_commit=2
#该参数控制着二进制日志写入磁盘的过程，每写1000次操作系统缓冲就执行一次刷新操作
sync_binlog=1000

character-set-client-handshake = FALSE
#字符集设置
character_set_client    = utf8mb4
character_set_server = utf8mb4
#collation_server = utf8mb4_unicode_ci

3. 查询字符集：

   SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'

   MariaDB 10.3字符集如下：
   Variable_name	Value
   character_set_client	utf8
   character_set_connection	utf8
   character_set_database	gbk
   character_set_filesystem	binary
   character_set_results	utf8
   character_set_server	latin1
   character_set_system	utf8
   character_sets_dir	/usr/share/mariadb/charsets/
   collation_connection	utf8_general_ci
   collation_database	gbk_chinese_ci
   collation_server	latin1_swedish_ci

4. 如果需要进行中文字符按拼音排序，需要在创建数据库时指定字符集GBK，默认按拼音排序。

   create database 数据库名 DEFAULT CHARACTER SET gbk

5. 如果某个数据库的字符集为utf8,需要先对字段进行转码然后排序：

   SELECT * FROM 表名
   ORDER BY CONVERT(排序字段名 using gbk);

6. 测试案例：
   create database test DEFAULT CHARACTER SET gbk
   create table aa(id int , name varchar(20));

insert into aa(id,name) values(1,'张三');
insert into aa(id,name) values(2,'李四');
insert into aa(id,name) values(3,'王五');
insert into aa(id,name) values(4,'陈六');
insert into aa(id,name) values(5,'赵明');
insert into aa(id,name) values(6,'魃ba明');
insert into aa(id,name) values(7,'魈xiao明');
insert into aa(id,name) values(8,'鬾qi明');
insert into aa(id,name) values(9,'bb');
insert into aa(id,name) values(10,'aa');

select * from aa ordy by name;