为什么我看到"COLLATION'xxx'对CHARACTER SET'yyy'无效"； [英] Why am I seeing "COLLATION 'xxx' is not valid for CHARACTER SET 'yyy'"

问题描述

我在Amazon RDS上使用MySQL 5.6.22(InnoDB).我试图将所有表，列，连接和数据库字符集以及排序规则设置都设置为utf8mb4/utf8mb4_unicode_ci.我找不到任何证据表明任何东西都有字符集 latin1 ，但是当我执行以下代码时(通过node-mysql或直接在Mac上的"Sequel Pro"应用中):

I am on MySQL 5.6.22 (InnoDB) on Amazon RDS. I have attempted to set all of my tables, columns, connection and database charset and collation settings to utf8mb4 / utf8mb4_unicode_ci. I can find no evidence anywhere that anything has charset latin1, yet when I execute the following code (either via node-mysql, or directly in "Sequel Pro" app on my Mac):

update MyTable m 
set m.Column8 = 1
where m.Column3 = 26 and m.Column4 = 76
collate utf8mb4_unicode_ci

我收到此错误消息:

COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'

在我的配置中找不到设置为 latin1 的任何内容.

I cannot find anything set to latin1 in my configuration.

的输出显示类似"char％"的变量:

character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    utf8mb4
character_set_results       utf8mb4
character_set_server        utf8mb4
character_set_system        utf8
character_sets_dir          /rdsdbbin/mysql-5.6.22.R1/share/charsets/

的输出显示诸如排序规则％"之类的变量:

collation_connection        utf8mb4_unicode_ci
collation_database          utf8mb4_unicode_ci
collation_server            utf8mb4_unicode_ci

MyTable的CREATE TABLE信息是:

MyTable's CREATE TABLE info is:

CREATE TABLE `MyTable` (
  `Column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Column2` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column3` bigint(20) unsigned NOT NULL,
  `Column4` bigint(20) unsigned NOT NULL,
  `Column5` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Column6` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Column7` varchar(112) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `Column8` tinyint(1) unsigned NOT NULL,
  `Column9` decimal(16,14) DEFAULT NULL,
  `Column10` decimal(17,14) DEFAULT NULL,
  `Column11` bigint(20) unsigned DEFAULT NULL,
  `Column12` bigint(20) unsigned DEFAULT NULL,
  `Column13` timestamp(6) NULL DEFAULT NULL,
  `Column14` timestamp(6) NULL DEFAULT NULL,
  `Column15` tinyint(4) NOT NULL DEFAULT '1',
  `Column16` tinyint(4) NOT NULL DEFAULT '1',
  `Column17` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column18` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Column19` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`Column1`),
  KEY `IX_Reevues_Column3` (`Column3`),
  KEY `IX_Reevues_Column4` (`Column4`),
  KEY `IX_Reevues_Column6` (`Column6`),
  KEY `IX_Reevues_Column8` (`Column8`),
  KEY `IX_Reevues_Column2` (`Column2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

推荐答案

1. 在将BIGINT与文字数字进行比较时，为什么要使用COLLATE子句?删除UPDATE语句中的COLLATE子句.-根据OP的注释，这是主要的解决方案.

1. Why have a COLLATE clause when comparing a BIGINTs compared to literal numbers? Remove the COLLATE clause in the UPDATE statement. -- This is the main solution, as per OP's comments.

使用latin1构建的存储例程中的代码是吗?请执行SHOW CREATE PROCEDURE(或FUNCTION)来查看是否是这种情况.如果是这样，则请删除并使用有效的utf8mb4重新创建它.

Is the code inside a Stored Routine that was build with latin1? Do SHOW CREATE PROCEDURE (or FUNCTION) to see if that were the case. If so, then DROP and reCREATE it with utf8mb4 in force.

更改character_set_filesystem和character_set_server是有风险的.改回来.

It is risky to change character_set_filesystem and character_set_server. Change them back.

这篇关于为什么我看到"COLLATION'xxx'对CHARACTER SET'yyy'无效"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！