В связи с тем, что довольно много людей обращается с просьбой помочь исправить проблему с кодировками MySQL, решил написать статью с описанием, как «лечить» наиболее часто встречающиеся случаи.
В статье описывается не то, как первоначально правильно настроить кодировки MySQL (об этом уже довольно много написано), а о случаях, когда есть довольно большие таблицы с неправильными кодировками и нужно всё исправить.
Самое плохое в неправильно настроенных кодировках — то, что зачастую проблему сложно обнаружить, и с первого взгляда может казаться, что сайт работает правильно, и никаких проблем нет.
Небольшое отступление. Sypex Viewer
В какой-то момент надоело отправлять людей в громоздкий phpMyAdmin, и была написана крошечная утилитка Sypex Viewer. Она представляет собой один PHP-файл, использует современные Web 2.0 технологии AJAX, JSON и другие. Основные задачи, которые ставилась при создании — минимальный вес, и максимальное удобство и скорость работы. В дальнейшем в примерах будут скриншоты из неё, но все те же действия можно сделать и в phpMyAdmin.
Данные в cp1251 таблицы в latin1
Наверное, самая популярная проблема. Когда данные в кодировке cp1251 (Windows-1251), а у таблиц указана кодировка по умолчанию latin1. Такие ситуации возникают в следующих случаях:
- при неграмотном обновлении с версии MySQL меньше 4.1 на более новые;
- очень часто возникает в «буржуйских» скриптах, которых вполне устраивает кодировка по умолчанию, и они «забывают», что неплохо бы указывать кодировку, как таблиц, так и соединения;
- также бывают случаи, когда переезжают с одного сервера (у которого установлена дефолтная кодировка cp1251, в частности, так сделано в Денвере) на другой (у которого стоит стандартная кодировка latin1).
В результате на сайте вроде как всё нормально, но если посмотреть в Sypex Viewer, то русские символы будут выглядеть как «кракозябры» (как их обычно называют пользователи).
В статье я рассмотрю один из вариантов преобразование кодировок с помощью бесплатного php-скрипта Sypex Dumper, в качестве готового решения.
- На вкладке «Экспорт» выбираем нужные таблицы.
- Кодировка должна быть auto (остальные параметры неважны, можно комментарий добавить, например, «Дамп перед исправлением кодировки»).
- Нажимаем «Выполнить». Теперь у нас есть бэкап (его в любом случае желательно делать при любых преобразованиях базы данных).
- Переходим на вкладку «Импорт»
- Выбираем только что сделанный файл бэкапа.
- Выбираем кодировку cp1251 и помечаем опцию «Коррекция кодировки».
- Нажимаем «Выполнить».
- Вот и всё заходим в Sypex Viewer, чтобы убедиться, что русские символы выводятся корректно.
Данные и таблицы в utf8, но кодировка соединения latin1
Теперь рассмотрим более запущенный случай. Набирающая популярность в последнее время проблема, в связи с повальным увлечением UTF-8. Создатели софта стали переводить свои детища на UTF-8, но и тут не всё так гладко, как хотелось бы.
Возникает проблема в основном в случае, когда у таблиц указана кодировка UTF-8, данные в UTF-8, но кодировка соединения установлена по умолчанию latin1 (типичный пример, vBulletin 4, хоть там и есть в конфигах настройка кодировки соединения, но она закомментирована по умолчанию).
В результате в MySQL присылаются данные в UTF-8, но поскольку указана кодировка соединения latin1, то MySQL пытается преобразовать данные из latin1 в UTF-8. В итоге русские символы выглядят так:
Ситуация более запущенная, но исправляется проблема почти также, как в первом случае, только в пункте 2 нужно выбрать кодировку latin1, а в пункте 6 нужно выбрать utf8 кодировку.
Изменение кодировки
Также часто встречающаяся проблема преобразования кодировки из cp1251 в UTF-8. До выполнения этого шага обязательно убедитесь, что русские символы у вас правильно показываются в Sypex Viewer или phpMyAdmin, если это не так, то предварительно исправьте кодировку.
Итак, опять заходим в Sypex Dumper.
- Во вкладке «Экспорт» выбираем нужные таблицы.
- Устанавливаем кодировку, в которую хотите преобразовать таблицы, в данном случае utf8.
- Нажимаем «Выполнить».
- После чего заходим в «Импорт» и выбираем нужный файл.
- Выставляем кодировку utf8 и опцию «Коррекция кодировки».
- Нажимаем «Выполнить».
- Вот и всё таблицы в UTF-8. Не забываем, что нужно еще установить кодировку соединения, сконвертировать ваши скрипты и шаблоны в UTF-8, выставить правильную кодировку в заголовках.
Кодировка соединения
Не забываем, что после исправлений кодировки, нужно убедиться, что ваши скрипты используют правильную кодировку соединения (в принципе, это будет сразу видно, они будут неправильно показывать русские символы без нужной кодировки соединения). У некоторых она выставляется в настройках, в некоторых придется добавить самостоятельно.
Для чего достаточно пройтись поиском по файлам, и найти где вызывается функция mysql_connect (или mysqli_connect). После этой строки нужно добавить строку которая укажет кодировку соединения.
mysql_query("SET NAMES 'cp1251'");Где вместо cp1251, указать нужную кодировку соединения.
Не забывайте перед преобразованиями кодировок делать бэкап, тут как с презервативами, лучше пусть он будет и не понадобится, чем когда понадобится — его не будет.
P.S. Спасибо Шортикам за веселый контент для примеров.
|
Пользователь 76561 Посетитель Сообщений: 69 |
Продукт — «Портал государственной организации» Кодировка таблицы b_perf_cluster (cp1251) отличается от кодировки базы (utf8) Еще есть 7 строк вида: Сравнение (Collation) для таблицы «b_perf_cluster» (cp1251_general_ci) отличается от значения для базы (utf8_general_ci) и 266 строк вида: Сравнение (Collation) для таблицы «b_adv_banner» (utf8_unicode_ci) отличается от значения для базы (utf8_general_ci) Всего таблиц 275 b_iblock_offers_tmp когда возникли ошибки не известно Вопросы Заранее спасибо |
|
Пользователь 76561 Посетитель Сообщений: 69 |
|
|
Администратор Сообщений: 137 |
обратитесь к хостерам, пусть выполнят необходимые запросы и перекодируют все таблиц в нужную кодировку. Руководитель службы технической поддержки |
|
Пользователь 76561 Посетитель Сообщений: 69 |
Сервер у меня свой, перекодировать таблицы я могу сам. Хотелось бы понять что вызывает смену кодировки во ВСЕХ таблицах. Устанавливался продукт под кодировкой UTF-8. |
|
Пользователь 76561 Посетитель Сообщений: 69 |
Продукт недавно развернули, сейчас идет настройка. Не поздно переустановить. Но есть ли смысл? |
|
Пользователь 12014 Эксперт Сообщений: 1000 |
Достаточно перекодироват таблицы Не жмись, кликай «Мне нравится» на сообщении |
|
Пользователь 76561 Посетитель Сообщений: 69 |
Перекодировать таблицы достаточно, а что необходимо сделать или чего нельзя делать, чтобы кодировка произвольно не изменялась |
|
Пользователь 90944 Посетитель Сообщений: 79 |
Мужики подскажите как перекодировать базу,у меня такая же ерунда вышла.Переносил сайт с веб окружения на виртуалку и вот тебе на Сравнение (Collation) для таблицы «b_adv_banner» (utf8_unicode_ci) отличается от значения для базы (utf8_general_ci) и так далее……. |
|
Администратор Сообщений: 1193 |
ALTER DATABASE XXX DEFAULT COLLATE utf8_unicode_ci; |
|
Пользователь 34561 Постоянный посетитель Сообщений: 287 |
#10 5 29.07.2011 10:45:40 Было то же самое. Вот ответы техподдержки: Посмотреть параметры кодировки БД можно следующим запросом (Настройки — Инструменты — SQL запрос): Все параметры, кроме параметра character_set_filesystem необходимо установить в соответствие с кодировкой сайта. Это можно сделать в файле after_connect.php (bitrixphp_interfaceafter_connect.php), например: Затем проверить кодировку в проверке сайта в «Тестировании конфигурации» (Настройки — Инструменты — Проверка сайта). Для смены кодировки таблиц выполните, пожалуйста, запрос для каждой таблицы: Проблема решается добавлением: Если к таблицам «b_search_phrase» и «b_search_suggest» будут ошибки, которые нельзя будет исправить (например восстановить таблицы), тогда мы рекомендуем Вам удалить модуль Поиск (Настройки — Настройки продукта — Модули) без сохранения данных в таблицах базы данных. Затем заново установите модуль Поиск, проведя переиндексацию сайта. После выполнения всех процедур все выровнялось. Рекомендую. |
|
Пользователь 90944 Посетитель Сообщений: 79 |
#11 0 29.07.2011 13:24:24
Большое спасибо,Помогло. |
||
|
Пользователь 90944 Посетитель Сообщений: 79 |
#12 0 29.07.2011 13:35:03
У меня еще одна проблема возникла,я думаю что данного характера,перенес сайт веб окружения на вертуалку,сначала были ошибки с кодировкой(это исправил)теперь обнаружил что часть портала а именно Интранет встал коряво,шаблон не тот что был,да и информация которая была отображается всего лишь на 30 процентов…было в базе забито около 100 сотрудников а после переноса стало человек 10 и т.п. |
||
|
Пользователь 871 Посетитель Сообщений: 70 |
#13 3 16.09.2011 15:17:58 QODA, спасибо
помогло ) |
||
|
Пользователь 49702 Заглянувший Сообщений: 10 |
#14 0 19.09.2011 10:46:50
Огромное спасибо! Решение помогло! |
||
|
Пользователь 109077 Заглянувший Сообщений: 1 |
#15 0 30.11.2011 15:49:58 При проверке сайта после восстановления из бэкапа вылезли следующие ошибки: Кодировка соединения (check_mysql_connection_charset): Fail Дописывание $DB->Query(‘SET collation_connection = «utf8_unicode_ci»‘); в after_connect приводит к тому, что нельзя зайти даже на морду сайта. Где ещё можно копать? |
|
Пользователь 85035 Заглянувший Сообщений: 32 |
#16 0 14.12.2011 03:00:16 попробуй еще добавить в начало Вот мой файл after_connect.php
4, повторяем Тестирование конфигурации. Все ок! Удачи:) |
||||||
|
Пользователь 99542 Заглянувший Сообщений: 7 |
#17 0 15.12.2011 17:35:57 Скачал виртуальную машину. Сравнение соединения с базой данных должно быть utf8_unicode_ci, текущее значение: utf8_general_ci. Раньше такого не было. |
|
Пользователь 63180 Эксперт Сообщений: 618 |
#18 3 02.08.2012 17:04:10 а можно сменить кодировку всем таблицам сразу ) SELECT CONCAT(‘ALT ER TABLE `’, t.`TABLE_SCHEMA`, ‘`.`’, t.`TABLE_NAME`, ‘` CONVERT TO CHARACTER SET cp1251 COLLATE cp1251_general_ci;’) as sqlcode FROM `information_schema`.`TABLES` t (вместо db_name) имя вашей базы в результате запроса вы увидете пару сотен строк )) типа
затем ответ копируете и все эти строки бахаете как новый запрос ) вот и всё) для утф посдатвляете utf8 COLLATE utf8_general_ci; |
||
|
Пользователь 576649 Заглянувший Сообщений: 1 |
#19 0 29.01.2017 22:58:19 Кодировки таблиц имеют ошибки 2017-Jan-29 22:51:26 Кодировки таблиц в БД (check_mysql_table_charset): Fail помогите разобраться |
|
Пользователь 34550 Эксперт Сообщений: 1579 |
#20 0 30.01.2017 10:09:39 вам же все написано. |
|
Пользователь 307442 Заглянувший Сообщений: 10 |
#21 0 12.02.2019 11:46:47 А у меня тоже проблем но на команду ALT ER DATABASE XXX DEFAULT COLLATE utf8_unicode_ci; приходит ответ [1044] Access denied for user ‘логин-базы_bitrix’@’localhost’ to database ‘XXX’! доступ запрещен как мне быть |
Mysql поддерживает много кодировок и это нередко является головной болью для программистов. Самая частая проблема — кракозяблы вместо русского текста. Это происходит из за того, что текст либо лежит на сервере, либо отдается клиенту в неверной кодировке. Последнее(а иногда и первое) решается проще всего. Устанавливаем кодировку соединения (в utf8 в примере) сразу после установления соединения
|
mysql_set_charset(‘utf8’); // или mysql_query(‘SET NAMES «utf8″‘); |
Хуже, когда скрипт отдает в базу, данные в верной кодировке, а в ответ получаем кракозяблы или вопросики. Или когда часть таблиц в верной кодировке, часть нет.. В таких случаях придется разбираться детально.
|
mysql_query(«SHOW VARIABLES LIKE ‘char%'» ); /* character_set_client: latin1 character_set_connection: latin1 character_set_database: utf8 character_set_filesystem: binary character_set_results: latin1 character_set_server: cp1251 character_set_system: utf8 character_sets_dir: usrlocalmysql-5.1sharecharsets */ |
Этот запрос обязательно проверять в самом скрипте, а не в phpmyadmin, где могут быть установлены другие параметры
character_set_client— кодировка, в которой данные будут поступать от клиентаcharacter_set_connection— по умолчанию для всего, что в рамках соединения не имеет кодировкиcharacter_set_database— кодировка по умолчанию для базcharacter_set_filesystem— кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT … INTO OUTFILE, и т.д.)character_set_results— кодировка, в которой будет выбран результатcharacter_set_server— кодировка, в которой работает серверcharacter_set_system— идентификаторы MySQL, всегда UTF8character_sets_dir— папка с кодировками
По умолчанию после установки mysql сервер, который устанавливается ленивым хостеромадмином имеет кодировку latin1. Соответственно указанные выше глобальные переменные будут в latin1. Базы соответственно по умолчанию и таблицы так же. И именно на это стоит обратить в самом начале обратить внимание, чтобы проблемы не всплывали позднее.
В идеальном варианте, нам следуетпривести все отмеченные цветом кодировки к единому значению. Тогда мы просто будем избавлены от мелких ошибок с кодировкой. Фактически, если мы работаем с хостингом, то на (3) и (6) мы повлиять не сможем. Но и это не страшно если настроены остальные три параметра. Mysql умет перекодировать на лету если правильно настроена кодировка соединения.
Ну и наконец, основной вопрос, что делать если одна из mysql таблиц(или несколько) в неверной кодировке и на сайте видны кракозяблывопросики?
1. Выяснить кодировку таблицы.
|
mysql > SHOW CREATE TABLE `files` ————————————————————————— CREATE TABLE `files` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `iNode` int(10) unsigned NOT NULL, `pid` int(10) unsigned NOT NULL, `sName` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `sTitle` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`), KEY `iNode` (`iNode`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
В этой таблице поле sName в кодировке latin1, если у нас соединение в другой кодировке, то мы увидим кракозябры.
2. Поэтому дальше проверим кодировку соединения, sql запросом SHOW VARIABLES LIKE ‘character_set_client’. Замечу, что php функция mysqli_client_encoding(), нам не подойдет, так как она отображает кодировку только на момент соединения.
3. Если кодировка соединения не совпала с кодировкой одного из полей таблицы, то 2 очевидных варианта.
Если у нас все таблицы в одной кодировке, то проще поменять кодировку соединения .
А как исправить неверную кодировку поля таблицы?
Для этого выполним 2 запроса
|
ALTER TABLE files CHANGE sName sName BLOB; ALTER TABLE files CHANGE sName sName VARCHAR(128) CHARACTER SET utf8; |
Нельзя обойтись только вторым запросом. Важно выполнить оба. Первый преобразовывает данные в двоичные, второй запрос, преобразовывает данные в строковые сменив кодировку.. Т.е. по сути мы не измениили двоичные данные, мы изменили правило формирования символов. Если бы мы попробовали обойтись только вторым запросом, то получили бы ошибочный набор.
I’m designing a php web-app and have some difficulties understanding a meaning of Mysql variables related to encoding and how they interact between each other. The encoding of the server is set to latin1 but the client’s is utf8mb4.
Running the mysql query inside a database
SHOW VARIABLES
WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'
gives the following:
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = latin1
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = latin1
character_set_system = utf8
collation_connection = utf8mb4_unicode_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci
I’m afraid running into issues with the older databases which are in latin1 if I change the character set of the mysql server to utf8mb4, but I certainly want to use utf8mb4 for the new databases I create. To correctly serve and retrieve data from the database should server’s and client’s encoding and collation always be the same? Any insight would be appreciated?
asked Dec 21, 2017 at 2:18
1
Some of those VARIABLES must agree with what encoding is used in the client.
CREATE TABLE ... specifies how they are to be stored in the tables.
If those two differ, then MySQL will convert «on the wire» between the client encoding an the table encoding.
If that means converting, say, Korean characters (encoding in utf8 or utf8mb4) to latin1 encoding, it will not be possible. On the other hand, all accented letters in Western Europe have encodings in both latin1 and utf8, so there is no problem.
Read this for common screwups:
Trouble with UTF-8 characters; what I see is not what I stored
See ALTER TABLE .. CONVERT TO .. for converting all character columns in one table to a different encoding (assuming it was correctly stored to begin with).
answered Dec 27, 2017 at 1:42
Rick JamesRick James
131k11 gold badges126 silver badges214 bronze badges
I’m designing a php web-app and have some difficulties understanding a meaning of Mysql variables related to encoding and how they interact between each other. The encoding of the server is set to latin1 but the client’s is utf8mb4.
Running the mysql query inside a database
SHOW VARIABLES
WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'
gives the following:
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = latin1
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = latin1
character_set_system = utf8
collation_connection = utf8mb4_unicode_ci
collation_database = latin1_swedish_ci
collation_server = latin1_swedish_ci
I’m afraid running into issues with the older databases which are in latin1 if I change the character set of the mysql server to utf8mb4, but I certainly want to use utf8mb4 for the new databases I create. To correctly serve and retrieve data from the database should server’s and client’s encoding and collation always be the same? Any insight would be appreciated?
asked Dec 21, 2017 at 2:18
1
Some of those VARIABLES must agree with what encoding is used in the client.
CREATE TABLE ... specifies how they are to be stored in the tables.
If those two differ, then MySQL will convert «on the wire» between the client encoding an the table encoding.
If that means converting, say, Korean characters (encoding in utf8 or utf8mb4) to latin1 encoding, it will not be possible. On the other hand, all accented letters in Western Europe have encodings in both latin1 and utf8, so there is no problem.
Read this for common screwups:
Trouble with UTF-8 characters; what I see is not what I stored
See ALTER TABLE .. CONVERT TO .. for converting all character columns in one table to a different encoding (assuming it was correctly stored to begin with).
answered Dec 27, 2017 at 1:42
Rick JamesRick James
131k11 gold badges126 silver badges214 bronze badges