Недопустимое сочетание сортировок (utf8_unicode_ci, IMPLICIT) и (utf8_general_ci, IMPLICIT) для операции '='

Ошибка msg на MySql: Недопустимое сочетание сортировок (utf8_unicode_ci, IMPLICIT) и (utf8_general_ci, IMPLICIT) для операции '='

Я прошел несколько других сообщений и не смог решить эту проблему. Часть затронутая - это нечто похожее на это:

CREATE TABLE users (
    userID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(24) NOT NULL,
    lastName VARCHAR(24) NOT NULL,
    username VARCHAR(24) NOT NULL,
    password VARCHAR(40) NOT NULL,
    PRIMARY KEY (userid)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE products (
    productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(104) NOT NULL,
    picturePath VARCHAR(104) NULL,
    pictureThumb VARCHAR(104) NULL,
    creationDate DATE NOT NULL,
    closeDate DATE NULL,
    deleteDate DATE NULL,
    varPath VARCHAR(104) NULL,
    isPublic TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (productID)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE productUsers (
    productID INT UNSIGNED NOT NULL,
    userID INT UNSIGNED NOT NULL,
    permission VARCHAR(16) NOT NULL,
    PRIMARY KEY (productID,userID),
    FOREIGN KEY (productID) REFERENCES products (productID) ON DELETE RESTRICT ON UPDATE NO ACTION,
    FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE RESTRICT ON UPDATE NO ACTION
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Хранимая процедура, которую я использую, такова:

CREATE PROCEDURE updateProductUsers (IN rUsername VARCHAR(24),IN rProductID INT UNSIGNED,IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

Я тестировал php, но такая же ошибка дается с помощью SQLyog. Я также тестировал воссоздание всей БД, но ничего хорошего.

Любая помощь будет очень оценена.

Ответ 1

Существует четыре варианта:

Вариант 1: добавьте COLLATE в вашу переменную ввода:

SET @rUsername = ‘aname COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

Вариант 2: добавить COLLATE в WHERE:

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24),
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername COLLATE utf8_unicode_ci -- COLLATE added
        AND productUsers.productID = rProductID;
END

Вариант 3: добавьте его в определение параметра IN:

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
    IN rProductID INT UNSIGNED,
    IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

Вариант 4: изменить поле:

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

поскольку настройка сортировки по умолчанию для параметров хранимой процедуры - utf8_general_ci и вы не можете смешивать сортировки.

Если вам не нужно сортировать данные в порядке Unicode, я бы предложил изменить все ваши таблицы на использование сопоставления utf8_general_ci, так как он не требует изменений кода, и будет быстро utf8_general_ci.

UPDATE: utf8mb4/utf8mb4_unicode_ci теперь является предпочтительным методом набора символов/сортировки. utf8_general_ci не рекомендуется, так как улучшение производительности незначительно. См. fooobar.com/questions/12120/...

Ответ 2

Я потратил полдня на поиск ответов на идентичную ошибку "Недопустимое смешение коллажей" с конфликтами между utf8_unicode_ci и utf8_general_ci.

Я обнаружил, что некоторые столбцы в моей базе данных не были специально сопоставлены с utf8_unicode_ci. Кажется, mysql неявно сопоставил эти столбцы utf8_general_ci.

В частности, выполнение запроса "SHOW CREATE TABLE table1" выводит примерно следующее:

| table1 | CREATE TABLE 'table1' (
'id' int(11) NOT NULL,
'col1' varchar(4) CHARACTER SET utf8 NOT NULL,
'col2' int(11) NOT NULL,
PRIMARY KEY ('col1','col2')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Обратите внимание на строку 'col1' varchar (4) CHARACTER SET utf8 NOT NULL не имеет указанной сортировки. Затем я выполнил следующий запрос:

ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

Это решило мою ошибку "Недопустимое смешение коллажей". Надеюсь, это поможет кому-то другому.

Ответ 3

У меня была аналогичная проблема, но она возникла у меня внутри процедуры, когда мой параметр запроса был задан с использованием переменной, например. SET @value='foo'.

Что вызвало это, было несоответствие collation_connection и сопоставление базы данных. Изменен collation_connection для соответствия collation_database, и проблема исчезла. Я думаю, что это более элегантный подход, чем добавление COLLATE после param/value.

Подводя итог: все сопоставления должны совпадать. Используйте SHOW VARIABLES и убедитесь, что collation_connection и collation_database соответствуют (также проверьте сортировку таблицы с помощью SHOW TABLE STATUS [table_name]).

Ответ 4

Немного похоже на ответ @bpile, мой случай был установкой записи my.cnf collation-server = utf8_general_ci. После того, как я понял, что (и после попытки все выше), я принудительно переключил свою базу данных на utf8_general_ci вместо utf8_unicode_ci, и это было так:

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci;

Ответ 5

В моем случае у меня есть следующая ошибка

Недопустимое сочетание сортировок (utf8_general_ci, IMPLICIT) и (utf8_unicode_ci, IMPLICIT) для операции '='

$this-> db-> select ("users.username as matric_no, CONCAT (users.surname, '', users.first_name, '', users.last_name) как fullname") → join ('users', 'users.username = classroom_students.matric_no ',' left ') → где (' classroom_students.session_id ', $ session) → где (' classroom_students.level_id ', $ level) → где (' classroom_students.dept_id ', $ dept );

После нескольких недель поиска в Google я заметил, что два поля, которые я сравниваю, состоит из другого имени сортировки. Первое, то есть имя пользователя, относится к utf8_general_ci, а второе относится к utf8_unicode_ci, поэтому я вернулся к структуре второй таблицы и изменил второе поле (matric_no) на utf8_general_ci, и он работал как шарм.

Ответ 6

Несмотря на то, что мы обнаружили огромное количество вопросов по одной и той же проблеме (1, 2, 3, 4), я даже не нашел ответа, который учитывал эффективность, даже здесь.

Несмотря на то, что уже было предложено несколько рабочих решений, я хотел бы рассмотреть вопрос об эффективности.

EDIT: Благодаря Manatax за указание, что вариант 1 не страдает от проблем с производительностью.

Использование вариантов ~~1 и~~ 2, также как и метод COLLATE, может привести к потенциальному узкому месту, потому что любой индекс, определенный в столбце, не будет использоваться, что приведет к полному сканированию.

Несмотря на то, что я не пробовал вариант 3, я подозреваю, что он перенесет те же последствия, что и варианты ~~1 и~~ 2.

Наконец, вариант 4 является лучшим вариантом для очень больших таблиц, когда он является жизнеспособным. Я имею в виду, что нет другого использования, которое полагалось бы на исходную сортировку.

Рассмотрим этот упрощенный запрос:

SELECT 
    *
FROM
    schema1.table1 AS T1
        LEFT JOIN
    schema2.table2 AS T2 ON T2.CUI = T1.CUI
WHERE
    T1.cui IN ('C0271662' , 'C2919021')
;

В моем первоначальном примере у меня было много других объединений. Конечно, таблица1 и таблица2 имеют разные сопоставления. Используя оператор сортировки для создания, это приведет к тому, что индексы не будут использоваться.

См. Пояснения на рисунке ниже.

Visual Query Explanation при использовании COLLATE

С другой стороны, вариант 4 может использовать преимущества возможного индекса и приводить к быстрым запросам.

На приведенном ниже рисунке вы можете увидеть тот же запрос, который запускается после применения Option 4, также изменяя сортировку схемы/таблицы/столбца.

Visual Query Explanation после изменения сортировки и, следовательно, без сортировки

В заключение, если производительность важна, и вы можете изменить сортировку таблицы, перейдите к варианту 4. Если вам нужно действовать в одном столбце, вы можете использовать что-то вроде этого:

ALTER TABLE schema1.table1 MODIFY 'field' VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ответ 7

Это происходит, когда столбцу явно задано другое сопоставление или сопоставление по умолчанию отличается в запрашиваемой таблице.

если у вас много таблиц, вы хотите изменить параметры сортировки при запуске этого запроса:

select concat('ALTER TABLE ', t.table_name , ' CONVERT TO CHARACTER 
SET utf8 COLLATE utf8_unicode_ci;') from (SELECT table_name FROM 
information_schema.tables where table_schema='SCHRMA') t;

это выведет запросы, необходимые для преобразования всех таблиц, чтобы использовать правильное сопоставление для столбца