MYSQL排序规则很坑人

海鲨数据库架构师 2023-08-17

290

MYSQL 建表没有给字符字段指定字符集和排序规则. 建完后MYSQL 给它加上了utf8mb4_general_ci . 日后要对字段修改备注导致排序规则变成了utf8mb4_0900_ai_ci . 这不是坑人啊

会话参数SESSION

show variables like '%coll%';

mysql> show variables like '%coll%';


+-------------------------------+--------------------+


| Variable_name | Value              |


+-------------------------------+--------------------+


| collation_connection | utf8mb4_0900_ai_ci |


| collation_database | utf8mb4_general_ci |


| collation_server | utf8mb4_general_ci |


| default_collation_for_utf8mb4 | utf8mb4_general_ci |


+-------------------------------+--------------------+
复制

全局参数

show global variables like '%coll%';


collation_connection utf8_general_ci


collation_database utf8_general_ci


collation_server utf8_general_ci


default_collation_for_utf8mb4 utf8mb4_0900_ai_ci
复制

CREATE TABLE`dic_table_filed_value1` (


`ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100)  NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45)  NOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100)  NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35)  DEFAULT NULL COMMENT '字段的值',


`VAULE_MEMO` varchar(100)  DEFAULT NULL COMMENT '字段值说明',


`is_valid` char(1)  DEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15)  DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',
`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20  COMMENT='表字段取值说明表'
复制

show create table  dic_table_filed_value1;


CREATE TABLE`dic_table_filed_value1` (


`ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100) NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45) NOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100) NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35) DEFAULT NULL COMMENT '字段的值',


`VAULE_MEMO` varchar(100) DEFAULT NULL COMMENT '字段值说明',


`is_valid` char(1) DEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15) DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',


`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciCOMMENT='表字段取值说明表'
复制

采用默认的是不会给排序规则的

show create database sk_history;


CREATE DATABASE `sk_history` 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
/*!80016 DEFAULT ENCRYPTION='N' */
复制

从RDS的DMS 管理数据库来看是0900_AI_CI

修改数据库默认排序规则

在历史库再次创建2号表

CREATE TABLE`dic_table_filed_value2` (


`ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100)  NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45)  NOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100)  NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35)  DEFAULT NULL COMMENT '字段的值',


`VAULE_MEMO` varchar(100)  DEFAULT NULL COMMENT '字段值说明',


`is_valid` char(1)  DEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15)  DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',


`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20  COMMENT='表字段取值说明表'
复制

创建后就会被特殊指定,表和字符字段

CREATE TABLE`dic_table_filed_value2` (


`ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) COLLATEutf8mb4_general_ci NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100) COLLATEutf8mb4_general_ci NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45) COLLATEutf8mb4_general_ci NOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100) COLLATEutf8mb4_general_ci NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35) COLLATEutf8mb4_general_ci DEFAULT NULL COMMENT '字段的值',


`VAULE_MEMO` varchar(100) COLLATEutf8mb4_general_ci DEFAULT NULL COMMENT '字段值说明',


`is_valid` char(1) COLLATE utf8mb4_general_ciDEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15) COLLATEutf8mb4_general_ci DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',


`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciCOMMENT='表字段取值说明表'
复制

如果数据库SHCEMA 不是默认的排序规则0900_ci的话创建表它就好给表和字段指定字符集和排序规则

如果修改字段没有指定排序规则

ALTER TABLE `dic_table_filed_value2`


CHANGE COLUMN `FIELD_VALUE` `FIELD_VALUE` VARCHAR(35) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '字段的值这个是字符的,数字也是存字符' ;
复制

惊天发现了排序规则变了

CREATE TABLE`dic_table_filed_value2` (


`ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) COLLATEutf8mb4_general_ci NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100) COLLATEutf8mb4_general_ci NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45) COLLATEutf8mb4_general_ci NOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100) COLLATEutf8mb4_general_ci NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '字段的值这个是字符的,数字也是存字符',


`VAULE_MEMO` varchar(100) COLLATEutf8mb4_general_ci DEFAULT NULL COMMENT '字段值说明',


`is_valid` char(1) COLLATEutf8mb4_general_ci DEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15) COLLATEutf8mb4_general_ci DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',


`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciCOMMENT='表字段取值说明表'
复制

字符集又是什么呢?

show  session variables like '%char%';


character_set_client utf8mb4


character_set_connection utf8mb4


character_set_database utf8mb4


character_set_filesystem binary


character_set_results utf8mb4


character_set_server utf8mb3


character_set_system utf8mb3


character_sets_dir /u01/mysql/share/charsets/
复制

show  global variables like '%char%';


character_set_client utf8mb3


character_set_connection utf8mb3


character_set_database utf8mb3


character_set_filesystem binary


character_set_results utf8mb3


character_set_server utf8mb3


character_set_system utf8mb3


character_sets_dir/u01/mysql/share/charsets/
复制

看来跟字符集没啥关系,还是COLLATION才是关键

修改全局参数

set global collation_connection=utf8mb4_general_ci;


set global collation_database=utf8mb4_general_ci;


set global colltion_server = utf8mb4_general_ci;


set global default_collation_for_utf8mb4=utf8mb4_general_ci;
复制

RDS 不给权限

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
当前数据库账号无权限执行该SQL。
一般为数据库账号权限不足，请检查您的权限后更换账号，或对账号赋权后重试执行。
如果您本人无权限操作该账号，请联系您的管理员。
如果报错提示需要super或者root权限，由于RDS并不提供root账号，也无法授权super权限，请删除该SQL再提交变更。
TraceId : 0abb7edf16910325819806646e7113

set session collation_connection=utf8mb4_general_ci;


set session collation_database=utf8mb4_general_ci;


set session colltion_server = utf8mb4_general_ci;


set session default_collation_for_utf8mb4=utf8mb4_general_ci;
复制

这方法不适合RDS

其实社区版是可以修改的我在测试库和开发库都修改后,再也没有发生此问题.

这是治标的方法

最简单的方法，批量修改成一样的字符集，修改完 session 重连就行了，balabala一大堆的

mysql5.7 升级到mysql8.0的时候，批量修改成一样的字符集。

SELECT CONCAT('ALTER TABLE',' ', table_name, ' convert to character setutf8mb4 COLLATE utf8mb4_0900_ai_ci' ';') from information_schema.`TABLES` where TABLE_SCHEMA = 'dbname' and table_collation ='utf8mb4_general_ci';
复制

每次修改字符一定要添加排序规则

ALTER TABLE`dic_table_filed_value2`


CHANGE COLUMN  `VAULE_MEMO` `VAULE_MEMO` varchar(100) CHARACTER SET 'utf8mb4' COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '字段值说明x';
复制

CREATE TABLE`dic_table_filed_value2` (


 `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',


`TABLE_NAME` varchar(45) COLLATE utf8mb4_general_ci NOT NULL COMMENT '表名',


`TABLE_COMMENT` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '表说明',


`FIELD_NAME` varchar(45) COLLATE utf8mb4_general_ciNOT NULL COMMENT '字段名',


`FIELD_COMMENT` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '字段说明',


`FIELD_VALUE` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '字段的值这个是字符的,数字也是存字符',


`VAULE_MEMO` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '字段值说明x',


`is_valid` char(1) COLLATE utf8mb4_general_ci DEFAULT '1' COMMENT '是否生效',


`OPREATE_NAME` varchar(15) COLLATE utf8mb4_general_ci DEFAULT '曾凡坤' COMMENT '操作人名',


`CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',


`UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',


PRIMARY KEY (`ID`)


) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ciCOMMENT='表字段取值说明表'
复制