如何解决排序规则的非法混合(latin1_general_ci，IMPLICIT)错误 [英] How to solve Illegal mix of collations (latin1_general_ci,IMPLICIT) error

问题描述

我正在尝试更新MySQL表中的记录，但遇到以下错误.

I am trying to update records in MySQL table but I am running into the following error.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' 

这是我的查询

$db->processQuery('UPDATE account_mids SET price_sheet_file = ? WHERE mid = ? AND price_sheet_file IS NULL', array($filename, $mid));   

我尝试添加整理，但是没有用.这就是我所做的

I have tried to add collate but did not work. This is what I have done

$db->processQuery('UPDATE account_mids SET price_sheet_file = ?  COLLATE utf8_general_ci    WHERE mid = ? COLLATE latin1_general_ci AND price_sheet_file IS NULL', array($filename, $mid)); 

但是我遇到了以下错误

Syntax error or access violation: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8''

这是我的演出创建表

CREATE TABLE `account_mids` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `program_name` varchar(60) DEFAULT NULL,
 `mid` char(16) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `stage` enum('Engaged','Pricing','Application Sent','Application Incomplete','No Sale','Pending Admin Approval','Admin Rejected','Admin Approved','Submit to PTC','Waiting PTC Decision','Approved','Declined','On Hold','Withdrawn','Cancelled','Change of Program') NOT NULL,
 `type` enum('Existing Business','New Business','New Prospect','Change of Ownership','Program Transfer') NOT NULL,
 `chapter_name` varchar(80) DEFAULT NULL,
 `account_id` int(11) unsigned NOT NULL,
 `agent_code` varchar(8) DEFAULT NULL COMMENT 'the 10th and 11th char in mid value plus 00 it will equal out 4 chars',
 `prin_code` varchar(8) DEFAULT NULL COMMENT 'the 7th 8th and 9th char in the mid plus 0 on the right which is equal to 4 chars',
 `price_sheet_file` varchar(30) DEFAULT NULL,
 `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `status` tinyint(1) NOT NULL DEFAULT '1',
 `closed_reason` varchar(1000) DEFAULT NULL,
 `closed_on` datetime DEFAULT NULL,
 `approved_on` datetime DEFAULT NULL,
 `enrolled_on` date DEFAULT NULL,
 `training_requested_on` datetime DEFAULT NULL,
 `first_sale_on` datetime DEFAULT NULL,
 `speciality` varchar(255) DEFAULT NULL,
 `created_by` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `mid` (`mid`),
 KEY `account_id` (`account_id`,`status`),
 CONSTRAINT `am_account_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=384010 DEFAULT CHARSET=utf8

如何解决此问题?

谢谢

推荐答案

我建议尽可能通过Sequel Pro查看表结构，因为它更容易调试/比较.我创建了一个新表，并且没有使用以前创建的表中使用的CHARSET和COLLATE值...

I recommend viewing the table structure via Sequel Pro if possible since it's much easier to debug/compare. I created a new table and didn't use the CHARSET and COLLATE values used in previously created tables...

因此，当使用两个具有不同CHARSET和COLLATE值的表运行查询时，这导致我们在上面看到的错误...

So this led to the error we are seeing above when running a query using both tables with different CHARSET and COLLATE values...

通过表格信息"或结构"标签比较设置，并确保CHARSET和COLLATE的值匹配.

Compare the settings via the "Table Info" or the "Structure" tab and make sure the values for CHARSET AND COLLATE match.

要进行更深入的调试，请运行SHOW CREATE TABLE tablename并比较表CHARSET/COLLATE值和表列COLLATE值.

To debug deeper run SHOW CREATE TABLE tablename and compare table CHARSET/COLLATE values and the table columns COLLATE values.

示例:column1 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

如果您不能使用续集专业版，请查看此mysql文档并运行更改... https://dev.mysql.com/doc/refman/5.7/en/charset-column.html

If you can't use sequel pro review this mysql doc and run the alters... https://dev.mysql.com/doc/refman/5.7/en/charset-column.html

这篇关于如何解决排序规则的非法混合(latin1_general_ci，IMPLICIT)错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！