Корректная настройка mysql для работы с utf8

Вариант 1 — Через names

mysql> set names 'cp1251';
Query OK,  rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | cp1251                           |
| character_set_connection | cp1251                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | cp1251                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

Ну, тут всё ясно, три самые нужные кодировки в одном )))

Вариант 2 — Через непосредственно переменные character_set_*

mysql> set @@character_set_client='cp1251';
Query OK,  rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | cp1251                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

Более детальная настройка, чем names.

Кодирование при помощи функции json_encode

Функция работает только с кодировкой UTF-8.

Рассмотрим простой пример:

$array = ;

$json = json_encode($array);

echo $json;

Результат выполнения кода:

{"one":1,"two":2}

Как видим ассоциативный массив превратился в обычную json строку.

Более сложный пример:

$array = ;

$json = json_encode($array);

echo $json;

Результат выполнения кода:

{"\u041a\u043b\u044e\u0447 1":"\u0417\u043d\u0430\u0447\u0435\u043d\u0438\u0435 1","\u041a\u043b\u044e\u0447 2":"\u0417\u043d\u0430\u0447\u0435\u043d\u0438\u0435 2","\u041a\u043b\u044e\u0447 3":"\u0417\u043d\u0430\u0447\u0435\u043d\u0438\u0435 3"}

Что произошло c кириллицей?

Дело в том, что по умолчанию многобайтовые символы Unicode кодируются как \uXXXX. При раскодировании функцией json_decode они преобразуются в нормальные строки.
В некоторых случаях мы можем захотеть избежать этого экранирования, например, чтобы посмотреть как выглядит наш JSON.

Для этого воспользуемся флагом JSON_UNESCAPED_UNICODE:

$json = json_encode($array, JSON_UNESCAPED_UNICODE);

echo $json;

Получаем такой результат:

{"Ключ 1":"Значение 1","Ключ 2":"Значение 2","Ключ 3":"Значение 3"}

Мы может еще в целях изучения кода преобразовать его в более человеческий вид, при помощи дополнительного флага JSON_PRETTY_PRINT

$json = json_encode($array, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

echo $json;

Получаем такой результат:

{
    "Ключ 1": "Значение 1",
    "Ключ 2": "Значение 2",
    "Ключ 3": "Значение 3"
}

Мы разобрались, как кодировать наши переменные в формат JSON при помощи json_encode.

Более подробная информация об этой функции: https://www.php.net/manual/ru/function.json-encode.php

Другие предопределенные константы с префиксом https://www.php.net/manual/ru/json.constants.php

Правильный вариант работы с MySQL

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

## Кодировки выставлены неверно, нужно их настроить
mysql> set names 'koi8r';
Query OK,  rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | koi8r                            |
| character_set_connection | koi8r                            |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | koi8r                            |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

## Я работаю через koi8r, поэтому и выставляю её, 
## но данные в таблице буду хранить в cp1251
mysql> create table `test2` (`field` varchar(60)) charset cp1251;
Query OK,  rows affected (0.01 sec)

## Проверяем, всё ли в порядке
mysql> show create table `test2`;
+-------+--------------------------------------------+
| Table | Create Table                               |
+-------+--------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `field` varchar(60) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
+-------+--------------------------------------------+
1 row in set (0.01 sec)

## Вносим данные 
mysql> insert into `test2` values ('и раз'), ('Два'),('три'), ('И ять'), ('шесть');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates:   Warnings: 

## Проверяем сортировки
## В обычном сравнении "И" и "и" одинаковы, поэтому 
## сравнение идёт до первого отличного символа
mysql> select * from `test2` order by `field` collate cp1251_general_ci ASC;
+-------+
| field |
+-------+
| Два   |
| и раз |
| И ять |
| три   |
| шесть |
+-------+
5 rows in set (0.01 sec)

## В бинарном сравнении "И" меньше чем "и", поскольку у неё код меньше
mysql> select * from `test2` order by `field` collate cp1251_bin ASC;
+-------+
| field |
+-------+
| Два   |
| И ять |
| и раз |
| три   |
| шесть |
+-------+
5 rows in set (0.00 sec)

Таким образом, клиент работает в KOI8-R, но данные хранятся в cp1251, MySQL знает об этом и делает перекодировку на лету.

Ну и на посошок:

mysql> set character_set_results='cp1251';
Query OK,  rows affected (0.00 sec)

mysql> select * from `test2`;
+-------+
| field |
+-------+
| Х ПЮГ |
| дБЮ   |
| РПХ   |
| х ЪРЭ |
| ЬЕЯРЭ |
+-------+
5 rows in set (0.00 sec)

Выбирать данные можно в любой кодировке, так же, как и вносить, главное — правильно сообщить об этом MySQL.

Экспорт базы данных сайта в режиме совместимости со старым MySQL

Этот способ обхода ошибки Unknown collation: utf8mb4 может быть использован в том случае, если мы имеем возможность повторного экспорта базы данных. Данное условие вытекает из того, что основные действия в этом варианте производятся на этапе создания бэкапа, а не его загрузки на сервер.

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

Рассмотрим, как выгрузить базу сайта в режиме совместимости MySQL на примере web-приложения phpMyAdmin.
Заходим в phpMyAdmin, выбираем нашу базу в списке и переходим на вкладку Экспорт

На открывшейся странице в разделе Метод экспорта ставим флаг-галку у пункта Обычный — отображать все возможные настройки. Теперь прокручиваем страницу вниз до раздела Параметры формата и в пункте Максимальная совместимость с системой базы данных, или устаревшей версией MySQL: из выпадающего списка выбираем вариант MYSQL40.
Остальные параметры можно не менять, скроллим страницу до конца и нажимаем кнопку Вперёд.

Сформированный архив будет импортироваться на сервер с MySQL старой версии без ошибок.

Если после этого при открытии страничек сайта будут проблемы с кодировкой: некорректное отображение текста, «иероглифы» и так далее — то дополнительно будет необходимо в файле конфигурации вашего CMS (к примеру, в WordPress это файл wp-config.php) заменить параметр, отвечающий за кодировку с utf8mb4 на utf8, сохранить внесённые изменения и обновить страницу

Обновление MySQL на Denwer

Оптимальный вариант решения ошибки #1273, по нашему мнению, это обновление версии системы sql до актуального релиза. Главный минус данного способа заключается в том, что для его реализации вам необходимо иметь доступ до СУБД на сервере.
То есть, этот вариант подходит в том случае, если вы работаете с программным продуктом Denwer, расположенным на вашем компьютере (не рассматриваем ситуацию, при которой вы используете VDS — виртуальный выделенный сервер).

Чтобы обновить систему MySQL в установленном пакете Denwer на компьютере, в первую очередь, необходимо скачать нужные файлы новой версии sql. Предлагаем вам сделать это одним из двух способов:

Далее останавливаем Денвер запуском ярлыка Stop Denwer, переходим на компьютере в директорию MySQL установленного пакета Denwer. По умолчанию задан следующий путь:

C:\WebServers\usr\local\mysql-5.5\bin

В этот каталог помещаем скачанные exe-файлы и соглашаемся на замену.
Запускаем Денвер ярлыком Start Denwer и повторно импортируем базу данных. Ошибка устранена.

Обработка ошибок

В случае ошибки, эти функции просто молча возвращают null.

Мы можем проверить, что нам вернулось null и посмотреть какая произошла ошибка следующим образом:

$json = '{{{ некорректный JSON!';

$var = json_decode($json, true);

if($var === null) {
    echo json_last_error_msg(); // эта строка выведет: Syntax error
}

Иногда нам может быть полезно не молчаливо возвращать null, а выкинуть Exception и обработать его.
PHP >7.3 предоставляет нам такую возможность.

Это можно сделать при помощи флага JSON_THROW_ON_ERROR

Рассмотрим пример:

$json = '{{{ некорректный JSON!';

$var = json_decode($json, true, JSON_THROW_ON_ERROR);

Теперь результат этого кода JsonException с сообщением Syntax error

Более подробно про обработку ошибок JSON:

Что делать, если данные внесены в неправильной кодировке

Если база/таблица/данные были созданы/внесены в кодировке отличной от нужной, то необходимо сделать следующее:

Создать бэкап базы данных
Создать текстовый дамп базы в SQL-запросах (mysqldump или PhpMyAdmin)
С помощью текстового редактора исправить вхождения неверной кодировки на нужную (а лучше попросту удалить всю информацию о кодировках и сравнениях)
Удалить базу/таблицу
Выставить нужную кодирвку на клиента/соединение
Импортировать данные исправленного SQL-дампа

Этот вариант подходит почти для всех случаев, за исключением некоторых особых ситуаций, например, когда сравнение, выставленное по-умолчанию, не уместно для некоторых полей. Пример — поле для хранения пароля, необходимо сравнивать его с учётом регистра, тогда как по-умолчанию выставляется сравнение без учёта регистра.

Устанавливаем MySQL 5.1

test# pkg_add -r mysql51-server
Fetching ftp//ftp.freebsd.org/pub/FreeBSD/ports/i386/
packages-7.0-release/Latest/mysql51-server.tbz... Done.
Fetching ftp//ftp.freebsd.org/pub/FreeBSD/ports/i386/
packages-7.0-release/All/mysql-client-5.1.22.tbz... Done.
Added group "mysql".
Added user "mysql".

************************************************************************

Remember to run mysql_upgrade (with the optional --datadir=<dbdir> flag)
the first time you start the MySQL server after an upgrade from an
earlier version.

************************************************************************

test# echo mysql_enable="YES" >> /etc/rc.conf
test# cp /usr/local/share/mysql/my-large.cnf /etc/my.cnf
test# /usr/local/etc/rc.d/mysql-server start
Starting mysql.
test# sockstat | grep mysql
mysql    mysqld     1154  13 tcp4   *3306                **
mysql    mysqld     1154  14 stream /tmp/mysql.sock
test#

Пускай это не самый «правильный» способ установки MySQL-сервера, зато быстрый и рабочий.

Как вывести JSON ответ на ajax запрос

Когда к нашему PHP скрипту обращается например javascript с ajax запросом, для того, чтобы подгрузить на страницу новые данные, то часто возникает необходимость ответить в формате JSON.

Для того, чтобы это сделать, нужно отправить заголовок и просто вывести строку с закодированными данными.

Содержимое файла

$array = ;
$jsonString = json_encode($array); // переводим данные в JSON строку

// отправляем заголовок, который позволит клиенту определить, 
// что возвращается ответ в формате JSON
header('Content-type:application/json;charset=utf-8'); 

echo $jsonString; // выводим JSON строку

exit(); // прекращаем выполнение скрипта

Тем временем в javascript мы можем обратиться к нашему php скрипту таким образом:

$('.js-button').on('click', function(){
    $.ajax({
        url: '/text_json.php',
        type: 'get',
        success: function(response) {
            // выводим alert со строкой "ваши данные", 
            // которую мы только что получили из php
            alert(response.data); 
        }
    });
});

10 ответов

Лучший ответ

Готов поспорить, вы получаете данные в кодировке, отличной от utf8: попробуйте поставить перед запросом .

258

ntd
29 Дек 2009 в 00:36

Если у вас установлен PHP не ниже 5.5, вы можете использовать json_last_error_msg () , который вернет строку с описанием проблемы.

Если у вас нет версии 5.5, но вы используете версию 5.3 или выше, вы можете использовать json_last_error (), чтобы узнать, в чем проблема.

Он вернет целое число, которое вы можете использовать для определения проблемы в функции документация. В настоящее время (2012.01.19) идентификаторы:

Они могут измениться в будущих версиях, поэтому лучше обратиться к руководству.

Если у вас ниже 5,3, вам не повезло, нет возможности спросить, в чем была ошибка.

119

K. Norbert
22 Авг 2013 в 12:10

Anwser ntd не решил мою проблему. Для тех, кто находится в такой же ситуации, вот как я, наконец, справился с этой ошибкой: просто utf8_encode для каждого из ваших результатов.

Надеюсь, это поможет!

Pablo Abdelhay
13 Мар 2013 в 14:59

Несколько дней назад у меня ТАКАЯ проблема с 1 таблицей.

Сначала попробуйте:

Если последняя строка возвращает 5, проблема с вашими данными . Я знаю, что ваши таблицы в UTF-8, но не введены . Например, ввод был в текстовом файле, но был создан на машине Win с дурацкой кодировкой (в моем случае Win-1250 = CP1250), и эти данные были введены в БД.

Решение? Найдите новые данные (Excel, веб-страницу), отредактируйте исходный текстовый файл через PSPad (или что-то еще), измените кодировку на UTF-8 , удалите все строки и теперь поместите данные из оригинала. Сохранить. Вход в БД .

Вы также можете изменить кодировку только на utf-8, а затем вручную изменить все строки (укажите столбцы со специальными символами — desc, …). Хорошо для рабов …

Ivo Urbanek
22 Фев 2013 в 18:22

Вы должны передать строку в кодировке utf8 в json_encode. Вы можете использовать функции и , как показано ниже:

Hussein Nazzal
24 Июн 2013 в 10:36

Для всех, кто использует PDO, решение похоже на ответ ntd.

На странице PHP PDO :: __ construct в качестве комментария пользователя Kiipa в live dot com :

Community
20 Июн 2020 в 09:12

ААА !!! Это выглядит так неправильно, что у меня болит голова. Попробуйте что-нибудь еще подобное …

При итерации по вы должны использовать , а не . Вы также должны кэшировать это значение (сохранить его в переменной) вместо того, чтобы пересчитывать его в каждом цикле. Кто знает, что он делает под капотом … (может быть эффективным, я не совсем уверен)
Вам не нужно явно копировать каждое значение таким образом … вы просто усложняете себе задачу. Если запрос возвращает больше значений, чем вы указали там, укажите только те, которые вы хотите в своем SQL.
возвращает значения как по , так и по . Вы не используете индексы, поэтому не извлекайте их.

Если это действительно проблема с , могу ли я предложить заменить тело цикла чем-то вроде

Возможно, там есть некоторые особые символы, которые все портят …

mpen
28 Дек 2009 в 23:35

Рекомендуемый PHP.net способ установки кодировки теперь следующий:

bejs
19 Сен 2014 в 04:37

Для меня проблема, при которой json_encode возвращала нулевую кодировку сущности, заключалась в том, что моя реализация jsonSerialize извлекала целые объекты для связанных сущностей; Я решил проблему, убедившись, что я получил идентификатор связанной / связанной сущности и вызвал -> toArray (), когда с объектом для сериализации json было связано несколько сущностей

Обратите внимание, я говорю о случаях, когда один на сущностях

Victor S
13 Сен 2018 в 20:08

У меня была та же проблема, и я решил использовать мою собственную функцию вместо

-4

Jérôme Verstrynge
13 Сен 2011 в 19:04

Вопросиков можно было избежать

На самом деле, ситуация, когда изначально выставлена неправильная кодировка, встречается сплошь и рядом. Симптомы можно выявить следующим образом:

mysql> show variables like "char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.02 sec)

Именно эти переменные отвечают за дефолтные значения кодировок.

— кодировка, в которой данные будут поступать от клиента
— кодировка по умолчанию для всего, что в рамках соединения не имеет кодировки
— кодировка по умолчанию для баз
— кодировка для работы с файловой системой (LOAD DATA INFILE, SELECT … INTO OUTFILE, и т.д.)
— кодировка, в которой будет выбран результат
— кодировка, в которой работает сервер
— кодировка, в которой задаются идентификаторы MySQL, всегда UTF8
— папка с кодировками

ВАЖНО: Если установлена неверно, то работа с кодировками будет под угрозой. Не пытайтесь менять её значение, если вы неуверены в своих силах

Если вы системный администратор, то перед установкой лучше ознакомиться с мануалом.

Наиболее значимые для простых пользователей следующие переменные: character_set_client, character_set_results, character_set_connection. Поскольку именно они отвечают за внесение, извлечение информации и создание таблиц/баз соответственно. Какими они могут быть?

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
+----------+-----------------------------+---------------------+--------+
26 rows in set (0.00 sec)

Любую из этих кодировок можно пользовать на свой вкус

Обычно русскоязычные пользователи предпочитают cp1251 или utf8, но по сути, неважно, в какой кодировке хранятся данные, важно, чтобы она была изначально правильно указана и данные были корректно внесены

Ручное изменение кодировки базы данных сайта

И последний пример борьбы с ошибкой импорта базы данных сайта, который мы рассмотрим в рамках текущей публикации — это изменение кодировки SQL базы в таблице.
Проделать это можно как с помощью специальных sql-команд, так и вручную. Рассмотрим второй вариант.

Для того, чтобы отредактировать кодировку в базе, нам необходимо разархивировать бэкап сайта, если он находится в сжатой папке zip или rar. После этого открываем получившийся файл с расширением *.sql в удобном текстовом редакторе, например, в Notepad++
В окне текстового редактора нам необходимо заменить значения utf8mb4_unicode_ci и utf8mb4_unicod_520i на utf8_general_ci. Кроме этого, если в таблицах базы встречается значение utf8mb4, то, как и в предыдущем способе, его также надо изменить на utf8.

После проделанной корректировки сохраняем изменённый файл и повторяем импорт базы данных на сервер.

Мы рассмотрели три способа борьбы с ошибкой #1273 — Unknown collation: ‘utf8mb4_unicode_ci’.
Надеемся, что вам удалось побороть данную проблему одним из вариантов.