Неверное смешение параметров сортировки MySQL Error

Question

Я получаю эту странную ошибку при обработке большого количества данных...

Error Number: 1267

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

SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€'

Что я могу сделать, чтобы решить эту проблему? Могу ли я каким-либо образом экранировать строку, чтобы эта ошибка не возникала, или мне нужно как-то изменить кодировку таблицы, и если да, то на что мне следует ее изменить?

Answer 1

SET collation_connection = 'utf8_general_ci';

тогда для ваших баз данных

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

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

Answer 2

CONVERT(column1 USING utf8)

Решает мою проблему. Где column1 является столбцом, который дает мне эту ошибку.

Answer 3

Вы должны установить кодировку таблицы и кодировки соединения на UTF-8:

ALTER TABLE keywords CHARACTER SET UTF8; -- run once

а также

SET NAMES 'UTF8';
SET CHARACTER SET 'UTF8';

Answer 4

Используйте следующее утверждение для ошибки

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

 ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 5

Моя таблица изначально была создана с CHARSET = latin1. После преобразования таблицы в utf8 некоторые столбцы не были преобразованы, однако это было неочевидно. Вы можете попробовать запустить SHOW CREATE TABLE my_table; и посмотрите, какой столбец не был преобразован, или просто исправьте неправильный набор символов в проблемном столбце с помощью запроса ниже (измените длину varchar и CHARSET и COLLATE в соответствии с вашими потребностями):

 ALTER TABLE `my_table` CHANGE `my_column` `my_column` VARCHAR(10) CHARSET utf8 
 COLLATE utf8_general_ci NULL;

Answer 6

Я обнаружил, что используя cast() было для меня лучшим решением:

cast(Format(amount, "Standard") AS CHAR CHARACTER SET utf8) AS Amount

Также есть convert()функция. Подробнее об этом здесь

Другой ресурс здесь

Answer 7

Измените набор символов таблицы на utf8

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8

Answer 8

В общем, лучший способ - изменить параметры сортировки таблицы. Однако у меня есть старое приложение, и я не могу точно оценить результат, имеет ли это побочные эффекты. Поэтому я попытался как-то преобразовать строку в какой-то другой формат, который решил проблему с сортировкой. Я обнаружил, что работа заключается в сравнении строк путем преобразования строк в шестнадцатеричное представление их символов. В базе данных это делается с HEX(column). Для PHP вы можете использовать эту функцию:

public static function strToHex($string)
{
    $hex = '';
    for ($i=0; $i<strlen($string); $i++){
        $ord = ord($string[$i]);
        $hexCode = dechex($ord);
        $hex .= substr('0'.$hexCode, -2);
    }
    return strToUpper($hex);
}

При выполнении запроса к базе данных ваша исходная строка UTF8 должна быть сначала преобразована в строку iso (например, используя utf8_decode() в PHP), прежде чем использовать его в БД. Из-за типа сортировки в базе данных не может быть символов UTF8 внутри, поэтому сравнение должно работать, хотя это изменяет исходную строку (преобразование символов UTF8, которых нет в кодировке ISO, приводит к? Или они полностью удаляются). Просто убедитесь, что когда вы записываете данные в базу данных, вы используете то же преобразование UTF8 в ISO.

Answer 9

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

Если, как и я, вас не волнует сопоставление символов (вы используете оператор '='), вы можете применить обратное исправление. Запустите это перед вашим SELECT:

SET collation_connection = 'latin1_swedish_ci';

Answer 10

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

В вашем "/etc/my.cnf.d/server.cnf" или где бы он ни находился, добавьте значения по умолчанию в раздел [mysqld], чтобы он выглядел так:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Источник: https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html