Устранение ошибки "Недопустимое смешение коллажей" в mysql

Получаем ошибку ниже при попытке выполнить выбор в хранимой процедуре в MySQL.

Нелегальное сочетание сортировок (latin1_general_cs, IMPLICIT) и (latin1_general_ci, IMPLICIT) для операции '='

Любая идея о том, что здесь может быть неправильным?

Сравнение таблицы latin1_general_ci, а столбца в предложении where - latin1_general_cs.

Ответы

Ответ 1

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

Предложение COLLATE позволяет указать параметры сортировки, используемые в запросе.

Например, следующее предложение WHERE всегда будет содержать сообщение об ошибке, которую вы опубликовали:

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs

Ваше решение состоит в том, чтобы указать общее сопоставление для двух столбцов в запросе. Вот пример, который использует предложение COLLATE:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Другой вариант - использовать оператор BINARY:

BINARY str - сокращение от CAST (st как AS BINARY).

Ваше решение может выглядеть примерно так:

SELECT * FROM table WHERE BINARY a = BINARY b;

или

SELECT * FROM table ORDER BY BINARY a;

Ответ 2

TL; DR

Либо измените сопоставление одного (или обоих) строк так, чтобы они соответствовали, либо добавили в выражение выражение COLLATE.

Что это за "сортировка" в любом случае?

Как описано в Наборы символов и сортировки в целом:

A набор символов представляет собой набор символов и кодировок. сопоставление - это набор правил для сравнения символов в наборе символов. Позвольте сделать различие понятным на примере мнимого набора символов.

Предположим, что у нас есть алфавит с четырьмя буквами: "A" , "B" , "A" , "B" . Мы даем каждой букве число: "A" = 0, "B" = 1, "A" = 2, "B" = 3. Буква "A" является символом, число 0 является кодировкой для "A" , а комбинация всех четырех букв и их кодировок - это набор символов .

Предположим, что мы хотим сравнить два строковых значения: "A" и "B" . Самый простой способ сделать это - посмотреть кодировки: 0 для "A" и 1 для "B" . Поскольку 0 меньше 1, мы говорим: "A" меньше "B" . Мы только что применили сопоставление с нашим набором символов. Сопоставление - это набор правил (только одно правило в этом случае): "сравнить кодировки". Мы называем это простейшее из всех возможных сопоставлений двоичным сопоставлением.

Но что, если мы хотим сказать, что строчные и прописные буквы эквивалентны? Тогда у нас будет по крайней мере два правила: (1) обрабатывать строчные буквы "A" и "B" как эквивалентные "A" и "B" ; (2), затем сравните кодировки. Мы называем это нечувствительным к регистру сопоставлением. Это немного сложнее, чем двоичная сортировка.

В реальной жизни большинство наборов символов имеют много символов: не только "A" и "B" , но целые алфавиты, иногда несколько алфавитов или восточные системы письма с тысячами символов, а также множество специальных символов и знаков препинания Метки. Кроме того, в реальной жизни большинство коллайлов имеют много правил, а не только для того, чтобы различать буквенный регистр, но также и для того, чтобы отличить акценты ( "акцент" - это знак, прикрепленный к персонажу, как на немецком языке "Ö" ), и для многосимвольные сопоставления (например, правило "Ö" = "OE" в одном из двух германских сопоставлений).

Другие примеры приведены в примерах эффекта сортировки.
Хорошо, но как MySQL решает, какое сопоставление использовать для данного выражения?

Как описано в Сочетание выражений:
В подавляющем большинстве утверждений очевидно, что используется MySQL для сопоставления операции сравнения. Например, в следующих случаях должно быть ясно, что сортировка - это сортировка столбца charset_name:
```
SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;
```
Однако с несколькими операндами может быть неоднозначность. Например:
```
SELECT x FROM T WHERE x = 'Y';
```
Если сравнение использует сортировку столбца x или строкового литерала 'Y'? Оба x и 'Y' имеют сопоставления, так что сопоставление имеет приоритет?

Стандартный SQL разрешает такие вопросы, используя то, что раньше называлось правилами "принуждаемости".
```
[ deletia ]
```
MySQL использует значения коэрцитивности со следующими правилами для устранения неоднозначностей:
- Используйте сопоставление с наименьшим значением принуждения.
- Если обе стороны имеют одну и ту же коэрцитивность, то:
  - Если обе стороны являются Unicode или обе стороны не являются Unicode, это ошибка.
  - Если одна из сторон имеет набор символов Unicode, а другая сторона имеет набор символов, отличных от Юникода, выигрывает сторона с символьным набором Unicode, а автоматическое преобразование набора символов применяется к стороне, отличной от Юникода. Например, следующий оператор не возвращает ошибку:
```
SELECT CONCAT(utf8_column, latin1_column) FROM t1;
```
    Он возвращает результат, имеющий набор символов utf8 и ту же сортировку, что и utf8_column. Значения latin1_column автоматически преобразуются в utf8 перед конкатенацией.
  - Для операции с операндами из того же набора символов, но которые смешивают сортировку _bin и a _ci или _cs, используется сортировка _bin. Это похоже на то, как операции, в которых смешиваются недвоичные и двоичные строки, оценивают операнды как двоичные строки, за исключением того, что они предназначены для сопоставлений, а не для типов данных.
Итак, что такое "незаконное сочетание сортировок"?

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

Конкретная ошибка, заданная в вопросе Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=', говорит нам о том, что было проведено сравнение равенства между двумя строками, не относящимися к Unicode, с равной совместимостью. Кроме того, он говорит нам, что сопоставления не были указаны явно в заявлении, а скорее подразумевались из источников строк (например, метаданных столбца).
Все это очень хорошо, но как решить такие ошибки?

Как показывают приведенные выше выдержки из руководства, эта проблема может быть решена несколькими способами, из которых два являются разумными и рекомендуемыми:
- Измените сортировку одной (или обеих) строк так, чтобы они совпадали, и больше не существует двусмысленности.
  
  Как это можно сделать, зависит от того, откуда пришла строка: Литеральные выражения принимают сопоставление, указанное в системной переменной collation_connection; значения из таблиц берут сопоставление, указанное в их метаданных столбцов.
- Настроить одну строку, чтобы она не была принудительной.
  
  Я пропустил следующую цитату из вышеперечисленного:
  MySQL присваивает значения коэрцитивности следующим образом:
  - Явное предложение COLLATE обладает способностью к нулю (не является коэрцитивной).
  - Конкатенация двух строк с разными сопоставлениями имеет коэрцитивность 1.
  - Сопоставление столбца или параметра хранимой процедуры или локальной переменной имеет совместимость с 2.
  - "Системная константа" (строка, возвращаемая такими функциями, как USER() или VERSION()) обладает способностью 3.
  - Сопоставление литерала имеет коэрцитивность 4.
  - NULL или выражение, полученное из NULL, имеет коэрцитивность 5.
  Таким образом, просто добавление предложения COLLATE в одну из строк, используемых при сравнении, заставит использовать эту сортировку.
В то время как другие были бы ужасно плохой практикой, если бы они были развернуты только для устранения этой ошибки:
- Принудите одну (или обе) строки к некоторым другим значениям коэрцитивности, чтобы иметь преимущество.
  
  Использование CONCAT() или CONCAT_WS() приведет к тому, что строка с способностью 1; и (если в хранимой процедуре) использование параметров/локальных переменных приведет к строкам с способностью 2.
- Измените кодировку одной (или обеих) строк так, чтобы она была Unicode, а другая - не.
  
  Это можно сделать путем перекодирования с помощью CONVERT(expr USING transcoding_name); или путем изменения базового набора символов (например, изменение столбца, изменение character_set_connection для литеральных значений или отправка их с клиента в другую кодировку и изменение character_set_client/добавление средства ввода символов). Обратите внимание, что изменение кодировки приведет к другим проблемам, если некоторые желаемые символы не могут быть закодированы в новом наборе символов.
- Измените кодировки одной (или обеих) строк так, чтобы они были одинаковыми и изменили одну строку, чтобы использовать соответствующую сортировку _bin.
  
  Методы изменения кодировок и сопоставлений были подробно описаны выше. Этот подход был бы малопригодным, если бы на самом деле требовалось применять более сложные правила сопоставления, чем предлагалось с помощью сортировки _bin.

Ответ 3

Добавление моего 2c к обсуждению будущих googlers.

Я изучал аналогичную проблему, когда я получил следующую ошибку при использовании пользовательских функций, которые получили параметр varchar:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and 
(utf8_general_ci,IMPLICIT) for operation '='

Используя следующий запрос:

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

Я смог сказать, что БД использовала utf8_general_ci, а таблицы были определены с помощью utf8_unicode_ci:

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

Обратите внимание, что представления имеют NULL-сопоставление. Похоже, что представления и функции имеют определения сортировки, хотя этот запрос показывает null для одного представления. Используемая сортировка - это сортировка БД, которая была определена при создании представления/функции.

Печальное решение заключалось в том, чтобы изменить сортировку db и воссоздать представления/функции, чтобы заставить их использовать текущую сортировку.

Изменение сортировки db:

ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;

Надеюсь, это поможет кому-то.

Ответ 4

Иногда бывает сложно конвертировать кодировки, особенно в базы данных с огромным количеством данных. Я думаю, что лучший вариант - использовать "двоичный" оператор:

e.g : WHERE binary table1.column1 = binary table2.column1

Ответ 5

У меня была аналогичная проблема, я пытался использовать процедуру FIND_IN_SET со строкой переменной.

SET @my_var = 'string1,string2';
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

и получал ошибку

Код ошибки: 1267. Недопустимое сочетание сортировок (utf8_unicode_ci, IMPLICIT) и (utf8_general_ci, IMPLICIT) для операции "find_in_set"

Короткий ответ:

Не нужно менять переменные collation_YYYY, просто добавьте правильную сортировку рядом с объявлением переменной, т.е.

SET @my_var = 'string1,string2' COLLATE utf8_unicode_ci;
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

Длинный ответ:

Сначала я проверил переменные сортировки:

mysql> SHOW VARIABLES LIKE 'collation%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    +----------------------+-----------------+
    | collation_database   | utf8_general_ci |
    +----------------------+-----------------+
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+

Затем я проверил сортировку таблицы:

mysql> SHOW CREATE TABLE my_table;

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_name` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Это означает, что моя переменная была настроена со значением по умолчанию utf8_general_ci, тогда как моя таблица была настроена как utf8_unicode_ci.

Добавив команду COLLATE рядом с объявлением переменной, сопоставление переменных соответствовало настройке сопоставления для таблицы.

Ответ 6

Вы можете попробовать этот script, который преобразует все ваши базы данных и таблицы в utf8.

Ответ 7

Решение, если речь идет о литералах.

Я использую интеграцию данных Pentaho и не могу указать синтаксис sql. Использование очень простого поиска в БД дало ошибку Msgstr "Недействительное сочетание сортировок (cp850_general_ci, COERCIBLE) и (latin1_swedish_ci, COERCIBLE) для операции '='"

Сгенерированный код был "SELECT DATA_DATE AS last_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY =?"

Сокращение истории сократило поиск до представления, и когда я выпустил

mysql> show full columns from hr_cc_normalised_data_date_v;
+------------+------------+-------------------+------+-----+
| Field      | Type       | Collation         | Null | Key |
+------------+------------+-------------------+------+-----+
| PSEUDO_KEY | varchar(1) | cp850_general_ci  | NO   |     |
| DATA_DATE  | varchar(8) | latin1_general_cs | YES  |     |
+------------+------------+-------------------+------+-----+

который объясняет, откуда берется "cp850_general_ci".

Вид был просто создан с помощью 'SELECT' X ',......' В соответствии с такими литералами, как это, следует наследовать их набор символов и сортировку из настроек сервера, которые были правильно определены как "latin1" и "latin1_general_cs", так как этого явно не случилось, я заставил его создать вид

CREATE OR REPLACE VIEW hr_cc_normalised_data_date_v AS
SELECT convert('X' using latin1) COLLATE latin1_general_cs        AS PSEUDO_KEY
    ,  DATA_DATE
FROM HR_COSTCENTRE_NORMALISED_mV
LIMIT 1;

теперь он показывает latin1_general_cs для обоих столбцов, и ошибка исчезла.:)

Ответ 8

MySQL действительно не любит смешивать сортировки, если только он не может принудить их к одному (что явно не возможно в вашем случае). Не можете ли вы просто заставить ту же сортировку использовать предложение COLLATE? (или более простой BINARY ярлык, если применимо...).

Ответ 9

Если столбцы, с которыми у вас возникают проблемы, являются "хэшами", тогда рассмотрим следующее...

Если "хэш" является двоичной строкой, вы действительно должны использовать тип данных BINARY(...).

Если "хеш" - это шестнадцатеричная строка, вам не нужно utf8, и этого следует избегать из-за проверки символов и т.д. Например, MySQL MD5(...) дает 32-байтовую строку с фиксированной длиной. SHA1(...) дает 40-байтовую шестую строку. Это можно сохранить в CHAR(32) CHARACTER SET ascii (или 40 для sha1).

Или, еще лучше, сохраните UNHEX(MD5(...)) в BINARY(16). Это уменьшает половину размера столбца. (Тем не менее, это делает его непечатаемым.) SELECT HEX(hash) ..., если вы хотите, чтобы он был читабельным.

Сравнение двух столбцов BINARY не имеет проблем с сортировкой.

Ответ 10

Возможное решение: конвертировать всю базу данных в UTF8 (см. также question).

Ответ 11

Другим источником проблемы с сопоставлениями является таблица mysql.proc. Проверьте сортировки ваших процедур хранения и функций:

SELECT
  p.db, p.db_collation, p.type, COUNT(*) cnt
FROM mysql.proc p
GROUP BY p.db, p.db_collation, p.type;

Также обратите внимание на столбцы mysql.proc.collation_connection и mysql.proc.character_set_client.

Ответ 12

Если у вас установлен phpMyAdmin, вы можете следовать инструкциям, приведенным в следующей ссылке: https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation Необходимо сопоставить сопоставление базы данных с сопоставлением всех таблиц, а также полей таблиц, а затем перекомпилировать все сохраненные данные. процедуры и функции. С этим все должно работать снова.

Ответ 13

Я использовал ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;, но не работал.

В этом запросе:

Select * from table1, table2 where table1.field = date_format(table2.field,'%H');

Эта работа для меня:

Select * from table1, table2 where concat(table1.field) = date_format(table2.field,'%H');

Да, только concat.

Ответ 14

Этот код необходимо поместить внутри Запустить SQL-запрос/запросы в базе данных

SQL QUERY WINDOW

ALTER TABLE `table_name` CHANGE `column_name` `column_name`   VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

Пожалуйста, замените имя_таблицы и имя_столбца соответствующим именем.