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

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

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

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

latin1_general_ci таблицы – latin1_general_ci а столбец в предложении latin1_general_cslatin1_general_cs .

13 Solutions collect form web for “Устранение ошибки «Недопустимое смешение коллажей» в mysql”

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

Предложение 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 (str AS BINARY).

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

 SELECT * FROM table WHERE BINARY a = BINARY b; 

или,

 SELECT * FROM table ORDER BY BINARY a; 

TL; DR

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


  1. Что это за «сортировка»?

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

    Набор символов представляет собой набор символов и кодировок. Сопоставление – это набор правил для сравнения символов в наборе символов. Давайте сделаем различие понятным на примере мнимого набора символов.

    Предположим, что у нас есть алфавит с четырьмя буквами: « 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 » в одном из двух германских сопоставлений).

    Другие примеры приведены в примерах влияния сортировки .

  2. Хорошо, но как 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 разрешает такие вопросы, используя то, что раньше называлось правилами «принудительности».

      [ удаление ] 

    MySQL использует значения коэрцитивности со следующими правилами для устранения неоднозначностей:

    • Используйте сопоставление с наименьшим значением коэрцитивности.

    • Если обе стороны имеют одну и ту же совместимость, то:

      • Если обе стороны являются Unicode, или обе стороны не являются Unicode, это ошибка.

      • Если одна из сторон имеет набор символов Unicode, а другая сторона имеет набор символов, отличных от Юникода, выигрывает сторона с символьным набором Unicode, а автоматическое преобразование набора символов применяется к стороне, отличной от Юникода. Например, следующий оператор не возвращает ошибку:

         SELECT CONCAT(utf8_column, latin1_column) FROM t1; 

        Он возвращает результат, который имеет набор символов utf8 и ту же сортировку, что и utf8_column . Значения latin1_column автоматически преобразуются в utf8 перед конкатенацией.

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

  3. Итак, что такое «незаконное сочетание сортировок»?

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

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

  4. Это все очень хорошо, но как решить такие ошибки?

    Как показывают приведенные выше выдержки из руководства, эту проблему можно решить несколькими способами, из которых два являются разумными и рекомендуемыми:

    • Измените сортировку одной (или обеих) строк так, чтобы они совпадали, и больше не существует какой-либо двусмысленности.

      Как это можно сделать, зависит от того, откуда взялась строка: Буквенные выражения принимают сопоставление, указанное в системной переменной collation_connection ; значения из таблиц принимают сопоставление, указанное в их метаданных столбцов.

    • Настройте одну строку, чтобы она не была принудительной.

      Я пропустил следующую цитату из вышесказанного:

      MySQL присваивает значения коэрцитивности следующим образом:

      • Явное предложение COLLATE имеет коэффициент совместимости 0. (Не допустимо вообще).

      • Конкатенация двух строк с разными сопоставлениями имеет коэрцитивность 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 сопоставления _bin .

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

Я изучал аналогичную проблему, когда я получил следующую ошибку при использовании пользовательских функций, которые получили параметр 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; 

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

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

 eg : WHERE binary table1.column1 = binary table2.column1 

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

У меня была аналогичная проблема, я пытался использовать процедуру 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 рядом с объявлением переменной, сопоставление переменных соответствовало настройке сопоставления для таблицы.

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

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

Я использую Pentaho Data Integration и не могу указать синтаксис sql. Использование очень простого поиска в БД дало ошибку «Недопустимое сочетание сортировок (cp850_general_ci, COERCIBLE) и (latin1_swedish_ci, COERCIBLE) для операции« = »

Сгенерированный код был «SELECT DATA_DATE AS latest_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 для обоих столбцов, и ошибка исчезла. 🙂

Если у столбцов, с которыми у вас возникают проблемы, есть «хеши», тогда рассмотрим следующее …

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

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

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

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

Возможным решением является преобразование всей базы данных в UTF8 (см. Также этот вопрос ).

Другим источником проблемы с сопоставлениями является таблица 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 .

Я использовал 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 .

Этот код необходимо поместить внутри Запустить 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; 

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

Interesting Posts
Давайте будем гением компьютера.