mysql出现1051错误（记一次MySQL库SQLerror:）

概述

最近在做一次从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'

解决过程如下：

可以发现排序规则不一致

SELECT c.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 ', CASE WHEN c.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END, CASE WHEN 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' FROM information_schema.`COLUMNS` c WHERE TABLE_SCHEMA='xxx';

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

应用修正sql做调整：

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

3、调整后检查

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

SELECT DISTINCT character_set_name FROM information_schema.`COLUMNS` c WHERE TABLE_SCHEMA = 'xxxx'

4、重新结构同步

测试后没有问题。

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