Битрикс mysql query error 1271 illegal mix of collations for operation union 400

Ответ 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;

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