MySQL: Изменение кодировки таблиц в MySQL
Начиная с версии 4.1 ,СУБД MySQL умеет работать с кодировками. Для многих работа с кодировками в MySQL – это большая проблема. Проблемы, связанные с кодировками неплохо освещены в Википедии на сайте http://phpclub.ru. Тем, кто плавает в вопросах кодировки я рекомендую посетить ресурс и читать статью до полного понимания. В основном в статье освещаются проблемы, связанные с настройкой связки PHP vs MySQL. Главное, что нужно запомнить, что кодировка содержимого БД и кодировка соединения должны совпадать. Ну, мы немного отклонились от темы.
Итак, все началось с того, что вы установили MySQL, создали таблицы, наполнили их данными, а потом обнаружили, что запросы к таблицам, в которых есть поиск или сортировка по полям типа CHAR, VARCHAR, TEXT выдают непредсказуемые результаты. Если это происходит, то, скорее всего, у вас некорректно указаны кодировки для данных. Попытаюсь объяснить, в чем проблема. Надеюсь, вы понимаете, что такое кодировка. Если нет, то прогугилите вопрос и получите хотя бы первичные знания.
Итак. Проследим, как получилось так, что у вас появились проблемы с кодировкой. MySQL при установке по умолчанию указывает кодировку для БД latin1. Большинство клиентов, при подключении, настроены на кодировку latin1. Вы, пользуясь клиентами, вносите данные, просматриваете результаты и т.д. При этом везде используете кодировку latin1. Эта кодировка позволяет корректно отображать кириллицу. На этом корректность в работе с кириллице в кодировке latin1 заканчивается. Cимволы вы видите правильные, но коды в таблице символов в данной кодировке не соответствуют реальным кириллическим символам. И получается, что поиск и сортировка выдают непредсказуемые результаты, потому что работа происходит не с символами, которые вы видите, а с кодами. Может, запутано объяснил, тогда ставлю перед фактом: кодировка latin1 – это некорректная кодировка для полноценной работы с криллицей. Для корректно работы с кириллицей нужно выбирать кодировки, которые поддерживают кириллицу. Для того, что бы узнать полный список этих кодировок RTFM по MySQL. Я же остановлюсь на двух распространённых из них: utf-8 и cp1251. Начну с cp1251, потому что это родная кодировка для ОС Windows.
Итак, перед нами стоит задача: конвертировать данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. Идем в документацию по языку:
Не спешим выполнять предложенное решение, а читаем дальше:
Простое конвертирование данных (1) нам не подходит, потому что у нас некорректные коды для символов, а так как конвертирование происходит согласно таблице кодов, то мы получим некорректный результат. Выход в данной ситуации – это избавиться от привязки символов к кодировке. Это делается путем преобразования данных и символьного типа в двоичные данные (2). Представьте, что кодировка – это маска, которая накладывается на двоичные данные. Т.е. сам порядок байтов не изменяется, а изменяется только маска, по которой выбираются коды для визуального формирования символов. Итак, запросом (2) вы избавились от маски, а запросом (3) вы применили новую маску. Повторюсь еще раз (грубо говоря): физически мы не изменили ни одного байта данных, мы изменили правило формирования символов! В итоге вы получаете следующее: сами данные как были корректными для кодировки cp1251, так они и остались, но теперь мы правильно указали кодировку cp1251.
После таких манипуляций жизнь вашей БД и выборок должна наладиться, потому что теперь все сходится и все на своих местах. Теперь, если вы захотите изменить кодировку таблицы и полей, то можете смело пользоваться запросом (1). Кстати, я сейчас думаю переходить с cp1251 на utf-8, но пока что не могу чётко озвучить, зачем мне это надо: кое-какие предпосылки есть, но ясного понимания пока что нет.
Замечания:
Статья взята отсюда: Записки самоучки: Изменение кодировки таблиц в MySQL
Итак, все началось с того, что вы установили MySQL, создали таблицы, наполнили их данными, а потом обнаружили, что запросы к таблицам, в которых есть поиск или сортировка по полям типа CHAR, VARCHAR, TEXT выдают непредсказуемые результаты. Если это происходит, то, скорее всего, у вас некорректно указаны кодировки для данных. Попытаюсь объяснить, в чем проблема. Надеюсь, вы понимаете, что такое кодировка. Если нет, то прогугилите вопрос и получите хотя бы первичные знания.
Итак. Проследим, как получилось так, что у вас появились проблемы с кодировкой. MySQL при установке по умолчанию указывает кодировку для БД latin1. Большинство клиентов, при подключении, настроены на кодировку latin1. Вы, пользуясь клиентами, вносите данные, просматриваете результаты и т.д. При этом везде используете кодировку latin1. Эта кодировка позволяет корректно отображать кириллицу. На этом корректность в работе с кириллице в кодировке latin1 заканчивается. Cимволы вы видите правильные, но коды в таблице символов в данной кодировке не соответствуют реальным кириллическим символам. И получается, что поиск и сортировка выдают непредсказуемые результаты, потому что работа происходит не с символами, которые вы видите, а с кодами. Может, запутано объяснил, тогда ставлю перед фактом: кодировка latin1 – это некорректная кодировка для полноценной работы с криллицей. Для корректно работы с кириллицей нужно выбирать кодировки, которые поддерживают кириллицу. Для того, что бы узнать полный список этих кодировок RTFM по MySQL. Я же остановлюсь на двух распространённых из них: utf-8 и cp1251. Начну с cp1251, потому что это родная кодировка для ОС Windows.
Итак, перед нами стоит задача: конвертировать данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. Идем в документацию по языку:
If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; (1)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; (1)
Не спешим выполнять предложенное решение, а читаем дальше:
Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like cp1251). In this case, you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB; (2)
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET cp1251; (3)
The reason this works is that there is no conversion when you convert to or from BLOB columns.
ALTER TABLE t1 CHANGE c1 c1 BLOB; (2)
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET cp1251; (3)
The reason this works is that there is no conversion when you convert to or from BLOB columns.
Простое конвертирование данных (1) нам не подходит, потому что у нас некорректные коды для символов, а так как конвертирование происходит согласно таблице кодов, то мы получим некорректный результат. Выход в данной ситуации – это избавиться от привязки символов к кодировке. Это делается путем преобразования данных и символьного типа в двоичные данные (2). Представьте, что кодировка – это маска, которая накладывается на двоичные данные. Т.е. сам порядок байтов не изменяется, а изменяется только маска, по которой выбираются коды для визуального формирования символов. Итак, запросом (2) вы избавились от маски, а запросом (3) вы применили новую маску. Повторюсь еще раз (грубо говоря): физически мы не изменили ни одного байта данных, мы изменили правило формирования символов! В итоге вы получаете следующее: сами данные как были корректными для кодировки cp1251, так они и остались, но теперь мы правильно указали кодировку cp1251.
После таких манипуляций жизнь вашей БД и выборок должна наладиться, потому что теперь все сходится и все на своих местах. Теперь, если вы захотите изменить кодировку таблицы и полей, то можете смело пользоваться запросом (1). Кстати, я сейчас думаю переходить с cp1251 на utf-8, но пока что не могу чётко озвучить, зачем мне это надо: кое-какие предпосылки есть, но ясного понимания пока что нет.
Замечания:
- Если вы конвертируете данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. Нельзя просто взять и изменить кодировку для столбца, т.е. не выполнять запрос (2), а сразу выполнить запрос (3). Потому что выполнив сразу запрос (3) мы физически не перестроим маску, а просто изменим текущее значение маски. Если у вас нет проблем с несоответствием кодировок, и вам нужно просто поменять кодировку таблицы, то вам нужно всего-навсего выполнить запрос (1).
- Если вы конвертируете данные, из изначально некорректно заявленной кодировки latin1 в корректную кодировку cp1251. При этом вы изменяете кодировку у поля, у которого есть индексы, то перестройте индексы (удалите и создайте заново).
- Если вы решите переходить на кодировку utf-8 – проверьте, что бы визуальные клиенты поддерживали unicode. Например, так горячо мною любимый SQLyog 5.2 курит бамбук при отображении содержимого таблиц, хранящих данные в utf-8.
- MySQL начал поддерживать unicode только с версии 4.1.x, т.е. до этого поддержки юникода не было. Это значит, если вы захотите перенсти таблицы с юникодом на версию младше 4.1.x, то у вас ничего не получится. Выходов два: или конвертировать данные из юникода во что-то менее многобайтовое, например, cp1251; или обновлять СУБД до версии не младше 4.1.x. Обновление СУБД предпочитетльней, потому что у MySQL уже 3ка по-моему не поддерживается и 4ка тоже скоро перестанет поддерживаться, при этом активно 5ка в двух ветках разрабатывается. Да и весь мир в юникоде активно в сети работает.
Статья взята отсюда: Записки самоучки: Изменение кодировки таблиц в MySQL
Похожие материалы:
Комментарии:
1 Апреля 2018 (11:03:33)
Денис
(гость)
• ответить
Статья (кстати спизженная, буква в букву, отсюда https://4matic.wordpress.com/2006/11/11/izmenenie-kodirovki-tablits-v-mysql/) не отвечает на вопрос "как изменить кодировку таблиц или БД"
для тех у кого еще нет, приведу несколько фактов:
utf-8 полностью совместим с ASCII
utf-8 включает миллионы символов почти всех письменностей мира против 256 русских букв в windows-1251 (перечислять зачем это надо нужно только младенцу в программировании, хотя бы сайт на нескольких языках сделать)
utf-8 является самосинхронизирующейся кодировкой
uft-8 является стандартом современных технологий обмена данными (напр., AJAX + JSON, XML)
utf-8 является рекомендацией W3C
utf-8 лучше воспринимается поисковиками, а sitemap и rss вообще обязаны быть в этой кодировке
utf-8 будет нативной кодировкой для строковых функций в PHP 6
utf-8 содержит типографские знаки, так необходимые для красивого оформления текста (кое-кто вставлял даже изображения вместо недостающих символов)
utf-8 стала стандартом для почти всех популярных форумных движков (напр. phpBB начиная с 3, кажется)
если немного подумать, то наверное найдутся еще преимущества, но я напоследок приведу несколько цитат, слова из одной книжки по HTML и отрывок из письма самого создателя cp1251 (одного из):
wiki: "Windows-1251 ... Пользуется довольно большой популярностью. Была создана на базе кодировок, использовавшихся в ранних «самопальных» русификаторах Windows в 1990—1991 гг." (что как бы намекает)
msdn.microsoft.com: "... to help developers move their applications to Unicode. Using Unicode is recommended in preference to any code page because it has better language support and is less ambiguous than any of the code pages."
HTML и CSS (автор Ростислав Чебыкин): "Сейчас все, что относится к расширенным кодировкам ASCII и подстановкам символов (кроме двух обязательных), окончательно устарело"
Игорь Семенюк (человек принимавший cp1251): "Конечно, вполне возможно, что выработанные рекомендации до Микрософта не дошли, или были им проигнорированы. Hо факт - русские буквы в cp1251 расположены именно так. Может быть, дядю Билла стоит винить в том, что он пошел на поводу у несознательных русских, которые сами не знали, что творят? :-) Честно скажу, я тогда про ISO8859-5 не знал напрочь. Знал бы, никогда бы за cp1261 выступать не стал бы." (cp1261 - естественно, авторская опечатка)