mysql查询默认排序规则_记一次MySQL库 SQL error: #1271解决思路

概述

最近在做一次从mysql5.7到mysql8的结构同步，发现报错，具体信息如下：

MySQL SQL error: #1271 - Illegal mix of collations for operation 'UNION'

下面简单记录下解决的过程及思路。

---

思路：

MySQL在使用UNION查询时或者创建视图时报异常：SQL error: #1271 - Illegal mix of collations for operation 'UNION'，此类问题是由于UNION Mysql的Table的时候对应的字段Collation字符序不同导致的，如果这时候用MySQL在使用UNION查询时或者创建视图时就会报异常：SQL error: #1271 - Illegal mix of collations for operation 'UNION'

解决过程如下：

---

1、查看表详细信息

可以发现排序规则不一致

SELECTc.TABLE_SCHEMA '数据库',c.TABLE_NAME '表',c.COLUMN_NAME '字段',c.COLUMN_DEFAULT '默认值',c.IS_NULLABLE '是否为空',c.DATA_TYPE '字段类型',c.character_set_name '原字符集',c.collation_name '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',CASEWHEN c.is_nullable = 'NO' THEN'NOT NULL' ELSE 'NULL' END,CASEWHEN c.COLUMN_DEFAULT = '' THEN' DEFAULT ''''' WHEN c.COLUMN_DEFAULT IS NULL THEN' DEFAULT NULL' ELSE concat( ' DEFAULT ', '''', c.COLUMN_DEFAULT, '''' ) END,' comment ','''',c.COLUMN_COMMENT,'''',';' ) '修正SQL' FROMinformation_schema.`COLUMNS` c WHERETABLE_SCHEMA='xxx';

2、通过修正sql统一调整字符集规则

应用修正sql做调整：

ALTER TABLE xxx MODIFY COLUMN xx CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' comment '';...

3、调整后检查

可以发现已经没有不一致的字符排序规则了。

SELECT DISTINCTcharacter_set_name FROMinformation_schema.`COLUMNS` c WHERETABLE_SCHEMA = 'xxxx'

4、重新结构同步

测试后没有问题。

---

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下~