mysql - ejb 中的非法混合排序规则

Question

在数据库排序规则为 latin1_general_ci 的 MySQL 5.1 上，我在 UPDATE 时遇到此异常:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseExceptionInternal Exception: java.sql.SQLException: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='Error Code: 1267Call: UPDATE medicaltestdetails, TL_medicaltestdetails SET medicaltestdetails.Status = TL_medicaltestdetails.Status WHERE medicaltestdetails.TestDate = TL_medicaltestdetails.TestDate AND medicaltestdetails.TestSerial = TL_medicaltestdetails.TestSerial AND medicaltestdetails.ConsultantId = TL_medicaltestdetails.ConsultantId AND medicaltestdetails.TestTime = TL_medicaltestdetails.TestTimeQuery: UpdateAllQuery(referenceClass=Medicaltestdetails sql="CREATE TEMPORARY TABLE IF NOT EXISTS TL_medicaltestdetails (Chg BIGINT, Remarks VARCHAR(255), Result VARCHAR(255), Status VARCHAR(255), TestDesc VARCHAR(255), TestType VARCHAR(255), TestDate DATE NOT NULL, TestSerial VARCHAR(255) NOT NULL, ConsultantId VARCHAR(255) NOT NULL, TestTime INTEGER NOT NULL, PRIMARY KEY (TestDate, TestSerial, ConsultantId, TestTime))")

请帮我解决这个问题。

Answer 1

您可能已将“&characterEncoding=UTF-8”添加到我的 JDBC 连接字符串中。如果

mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_general_ci |
    +--------------------+-----------------+

不是这样的和

mysql> show table status;
    +--------------+-----------------+
    | Name         | Collation       |
    +--------------+-----------------+
    | my_view      | NULL            |
    | my_table     | utf8_unicode_ci |
    ...

不是这样的

做:

ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;

并更改字段的编码:

ALTER TABLE my_table MODIFY COLUMN my_column column_type CHARACTER SET utf8 COLLATE utf8_unicode_ci [NOT NULL];

或者改变表格的编码:

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

再见

卢卡