Utf8 general ci что это
Sergey Danielyan
Корректная настройка MySQL для работы с UTF8
Основная цель данного поста — выяснить, какие параметры и с какими значениями следует прописать в конфигурационный файл my.cnf (my.ini) для дальнейшей беспроблемной работы с Юникодом.
Рабочее окружение
UTF8 на данный момент у меня успешно работает в Мастер-Слейв конфигурации:
Любой внешний клиент в состоянии корректно работать с UTF8 базой (проверено на EMS Manager for MySQL c Windows 8 x64).
Все опции и настройки я привожу для версии сервера 5.1.x, однако с минимальными (а то и вовсе без оных) изменениями все это будет работать и на версиях 5.5.x и 5.6.x.
Параметры кодировок MySQL
Довольно часто приходится видеть в ответах на вопросы о настройке UTF8 следующее:
Предполагается, что после вставки всего этого добра (тут кстати есть противоречащие друг другу опции) в конфигурационный файл my.cnf (my.ini) магический Юникод начнет работать.
Но давайте забудем о списке и попытаемся разбираться со всеми опциями сами и начнем с самого начала. То есть с документации. Потому как все это прекрасно описано в документации MySQL на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.
Символьная кодировка может быть задана для:
Сделано это для гибкой настройки баз данных и доступа клиентов с разными кодировками. Однако, последнее не входит в область рассмотрения данного поста, поэтому будем рассматривать вариант с кодировкой UTF8 настроенной для всего по-умолчанию.
Все параметры могут быть переданы серверу тремя разными способами:
Второй и третий варианты рассматриваться не будут. Тут уместно будет просто прочитать официальные доки — в каждом разделе приведены примеры конфигурации с использованием всех трех способов. Я же буду использовать первый вариант.
Кодировка (character set) и представление (collation) сервера
Тут есть несколько фундаментальных вещей которые надо понимать.
Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:
SHOW COLLATION LIKE ‘your_character_set_name’;
Поле Default дает ответ о представлении выбранной кодировки.
В нашем случае, при настройке дефолтной кодировки в UTF8, параметры должны быть определены, так как могут быть использованы при определении кодировки или представления базы данных:
Наши команды:
my.cnf (my.ini)
[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
Кодировка (character set) и представление (collation) базы данных
Тут есть два варианта определения кодировки и представления:
явно — при выполнении запроса на создание базы данных:
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Вообще при работе с базой данных огромную роль помимо серверных настроек играют настройки клиент-серверного соединения (connection). На этом этапе вступают в игру следующие специфичные для соединения параметры:
Есть еще представление кодировки соединения ( colation_connection ). Для чего нужен этот параметр думаю пояснять не надо.
Озадачиваться проблемой инициализации всех этих переменных не стоит (хотя в нашем случае присвоить им значения необходимо). Есть способ проще: существует два типа запросов (statements) которые задают настройки соединения клиента с сервером группой:
Запрос SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]
Параметр определяет в какой кодировке теперь будут приходить сообщения для сервера от клиента. Прелесть в том, что запрос SET NAMES x эквивалентен следующей группе:
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
Для определении представления кодировки соединения ( colation_connection ) отличного от дефолтного, следует дополнить запрос:
SET NAMES x COLLATE y
SET NAMES utf8 COLLATE utf8_unicode_ci
Таким образом, используя только этот запрос, можно добиться корректной UTF8 инициализации соединения.
Однако, тут есть один нюанс:
init_connect=‘SET collation_connection = utf8_unicode_ci’
Запрос SET CHARACTER SET charset_name
Запрос групповой и он также эквивалентен следующей группе:
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;
Согласно документации, разница между двумя запросами в том, что параметры character_set_connection и collation_connection будут установлены на @@character_set_database и @@collation_database соответственно (выше я про них упоминал).
Наши команды:
my.cnf (my.ini)
[client]
default_character_set = utf8
[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
Кодировка (character set) и представление (collation) таблиц
Тут все довольно просто. Задать кодировку и ее представление можно через команды:
CREATE TABLE t1 ( … )
CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Тут главное иметь в виду, что если эти настройки не заданы, то берутся настройки базы данных (см. пред. раздел). Нам эти настройки не интересны.
Кодировка (character set) и представление (collation) колонок в таблице
Тут по аналогии с пред. секцией. Если параметры кодировок не указаны, берутся те, что указывались для таблицы.
Прежде чем перейти к след. разделу, должен сказать, что все команды и запросы относятся к указанной версии MySQL и в случае возникновения каких-либо проблем советую обратиться к соответствующей версии документации.
skip-character-set-client-handshake
Верификация настроек
Итак, вот финальный snapshot наших изменений в файле my.cnf (my.ini):
[mysqld]
init_connect=‘SET collation_connection = utf8_unicode_ci’
character-set-server = utf8
collation-server = utf8_unicode_ci
[client]
default-character-set = utf8
После применения всех опций и рестарта сервера mysql для проверки настроек можно воспользоваться командами SHOW VARIABLES LIKE ‘char%’ и SHOW VARIABLES LIKE ‘collation%’ ;
Состояние среды до изменений:
Состояние среды после изменений (в случае, если вы приконнектились не SUPER пользователем):
Для примера, вот отличие при соединении через mysql.exe пользователем с и без привилегии SUPER:
с привилегией и выполненной вручную командой ‘SET collation_connection = utf8_unicode_ci’:
Поздравляю, теперь ваши база, таблицы и все в таблицах по-умолчанию в кодировке UTF8.
Drupal Русскоязычное сообщество
Подскажите, в какой кодировке в utf8_general_ci или в utf8_unicode_ci будет меньше проблем и в чем различия между этими двумя кодировками?
Комментарии
Это НЕ КОДИРОВКА, это способ СРАВНЕНИЯ слов и букв.
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html
MySQL implements the utf8_unicode_ci collation according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. The following discussion uses utf8_unicode_ci, but it is also true for ucs2_unicode_ci.
Currently, the utf8_unicode_ci collation has only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese, Yoruba, and some smaller languages such as Navajo.
The most significant feature in utf8_unicode_ci is that it supports expansions; that is, when one character compares as equal to combinations of other characters. For example, in German and some other languages ‘ß’ is equal to ‘ss’.
utf8_general_ci is a legacy collation that does not support expansions. It can make only one-to-one comparisons between characters. This means that comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci.
For example, the following equalities hold in both utf8_general_ci and utf8_unicode_ci:
A difference between the collations is that this is true for utf8_general_ci:
Whereas this is true for utf8_unicode_ci:
MySQL implements language-specific collations for the utf8 character set only if the ordering with utf8_unicode_ci does not work well for a language. For example, utf8_unicode_ci works fine for German and French, so there is no need to create special utf8 collations for these two languages.
utf8_general_ci also is satisfactory for both German and French, except that ‘ß’ is equal to ‘s’, and not to ‘ss’. If this is acceptable for your application, then you should use utf8_general_ci because it is faster. Otherwise, use utf8_unicode_ci because it is more accurate.
utf8_swedish_ci, like other utf8 language-specific collations, is derived from utf8_unicode_ci with additional language rules. For example, in Swedish, the following relationship holds, which is not something expected by a German or French speaker:
Да, конечно, если в языке нет таких хитрых букв как в немецком.
В чем разница между utf8 general ci и utf8 unicode ci
5 ответов
эти две сортировки предназначены для кодировки символов UTF-8. Различия заключаются в том, как сортируется и сравнивается текст.
точность
utf8mb4_unicode_ci основан на стандарте Unicode для сортировки и сравнения, который сортирует точно в очень широком диапазоне языков.
utf8mb4_general_ci не удается реализовать все правила сортировки Unicode, что приведет к нежелательной Сортировке в некоторых ситуациях, например, при использовании определенных языков или символов.
производительность
utf8mb4_general_ci быстрее при сравнении и сортировке, потому что требуется куча производительности, связанных ярлыки.
на современных серверах это повышение производительности будет почти незначительным. Он был разработан в то время, когда серверы имели крошечную долю производительности процессора современных компьютеров.
что касается латинских (т. е. «европейских») языков, нет большой разницы между сортировкой Unicode и упрощенным utf8mb4_general_ci сортировка в MySQL, но есть еще несколько различий:
например, параметры сортировки Юникода сортируют » ß «как» ss «и» Œ «как» OE», как люди, использующие эти символы, обычно хотят, тогда как utf8mb4_general_ci сортирует их как одиночные символы (предположительно, как «s» и «e» соответственно).
некоторые символы Юникода определяются как игнорируемые, что означает, что они не должны рассчитывать на порядок сортировки, и сравнение должно перейти к следующему символу. utf8mb4_unicode_ci обрабатывает их должным образом.
в нелатинских языках, таких как азиатские языки или языки с разными алфавитами, может быть много больше различия между Unicode сортировка и упрощенная utf8mb4_general_ci сортировка. Пригодность utf8mb4_general_ci будет сильно зависеть от используемого языка. Для некоторых языков этого будет недостаточно.
что вы должны использовать?
почти наверняка нет причин использовать utf8mb4_general_ci больше, так как мы оставили позади точку, где скорость процессора достаточно низкая, что разница в производительности будет важна. Ваша база данных почти наверняка будет ограничена другими узкими местами, чем этот.
разница в производительности будет измеряться только в чрезвычайно специализированных ситуациях, и если это вы, вы, вероятно, уже знаете об этом. Если вы испытываете медленную сортировку, почти во всех случаях это будет проблема с вашим планом индексов/запросов. Изменение функции сортировки не должно быть высоким в списке вещей для устранения неполадок.
в прошлом, некоторые люди рекомендуют использовать utf8mb4_general_ci за исключением тех случаев, когда точная сортировка будет достаточно важно, чтобы оправдать затраты на производительность. Сегодня эта стоимость производительности практически исчезла, и разработчики относятся к интернационализации более серьезно.
еще одна вещь, которую я добавлю, что даже если вы знаете, что ваше приложение поддерживает только английский язык, ему все равно может потребоваться иметь дело с именами людей, которые часто могут содержать символы, используемые на других языках, в которых так же важно правильно сортировать. Использование правил Unicode для всего помогает добавьте душевное спокойствие, что очень умные люди Unicode очень много работали, чтобы сортировка работала правильно.
Я хотел знать, какова разница в производительности между использованием utf8_general_ci и utf8_unicode_ci, но я не нашел никаких тестов, перечисленных в интернете, поэтому я решил создать тесты сам.
Я создал очень простую таблицу с 500000 строк:
затем я заполнил его случайными данными, выполнив эту хранимую процедуру:
затем я создал следующие хранимые процедуры для проверки простого выбора, выбора с помощью LIKE и сортировка (выбрать по порядку):
в хранимых процедурах выше utf8_general_ci используется сортировка, но, конечно, во время тестов я использовал utf8_general_ci и utf8_unicode_ci.
я вызывал каждую хранимую процедуру 5 раз для каждой сортировки (5 раз для utf8_general_ci и 5 раз для utf8_unicode_ci), а затем вычислял средние значения.
мои результаты:
benchmark_simple_select() с utf8_general_ci: 9957 МС
benchmark_simple_select () с utf8_unicode_ci: 10271 ms
В этом тесте использовать utf8_unicode_ci медленнее, чем utf8_general_ci на 3,2%.
benchmark_select_like () с utf8_general_ci: 11441 ms
benchmark_select_like () с utf8_unicode_ci: 12811 ms
В этом тесте использование utf8_unicode_ci медленнее, чем utf8_general_ci на 12%.
benchmark_order_by () с utf8_general_ci: 11944 ms
benchmark_order_by() с utf8_unicode_ci: 12887 ms
В этом тесте использовать utf8_unicode_ci медленнее, чем utf8_general_ci на 7,9%.
этот пост описывает это очень красиво.
короче говоря: utf8_unicode_ci использует алгоритм сортировки Юникода, как определено в стандартах Юникода, тогда как utf8_general_ci-более простой порядок сортировки, который приводит к «менее точным» результатам сортировки.
для любого набора символов Юникода, операции, выполняемые с использованием _general_ci параметры сортировки быстрее, чем для _unicode_ci параметров сортировки. Например, сравнения для utf8_general_ci сортировки быстрее, но чуть менее правильно, чем сравнение utf8_unicode_ci. Этот причина этого в том, что utf8_unicode_ci поддерживает такие сопоставления как расширения; то есть, когда один персонаж сравнивает равным комбинации других персонажей. Для например, на немецком и некоторых других языках языки «ß» равны «ss». utf8_unicode_ci также поддерживает схватки и игнорируемые персонажи. utf8_general_ci-это устаревшие параметры сортировки это не поддерживает расширения, схватки, или игнорируемые персонажи. Это может сделать только один-к-одному сравнения между персонажами.
Итак, utf_general_ci использует меньший и менее правильные (согласно стандарту) набор сравнений, чем utf_unicode_ci, который должны выполнить весь стандарт. Набор general_ci будет быстрее, потому что меньше вычислений.
вкратце:
Если вам нужен лучший порядок сортировки-используйте utf8_unicode_ci (это предпочтительный способ),
различия с точки зрения производительности очень незначительны.
Как перейти с utf8 на utf8mb4 в MySQL
Содержание:
Переходим с utf8 на utf8mb4 в MySQL.
utf8 или utf8mb4
Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.
Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.
utf8mb4_general_ci или utf8mb4_unicode_ci
В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.
Настройка кодировки utf8mb4 для СУБД MySQL
Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.
Если у вас уже есть базы данных, то обязательно создайте резервные копии всех баз данных.
В конфигурационном файле MySQL ( my.ini (windows)/ my.cnf (Linux)) необходимо изменить кодировку на utf8mb4:
Проверяем корректность работы применимых настроек:
Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL
Для базы данных:
Для таблицы:
Для столбцов:
Восстановление и оптимизация всех таблиц
После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:
Или с использованием команды mysqlcheck :
Пример миграции для Yii2
В этом примере мы изменим кодировку для столбца content в таблице post :
В чём разница между кодировками utf8_general_ci, utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci. Какую кодировку выбрать для базы данных MySQL
Начиная с MySQL 5.5.3 вы должны использовать utf8mb4, а не utf8. Обе эти группы относятся к кодировке UTF-8, но более старая utf8 имеет специфичные для MySQL ограничения, не дающие использовать символы, пронумерованные выше 0xFFFD.
Таким образом, больше не нужно использовать ни utf8_general_ci, ни utf8_unicode_ci.
Что касается новых версий кодировки utf8mb4_general_ci и utf8mb4_unicode_ci. То предпочтительной является unicode, а не general. Вариант utf8mb4_general_ci будет чуть более быстрым при сортировке (в настоящее время это уже неактуально), но имеет проблемы с сортировкой в определённых языках. Кодировка utf8mb4_unicode_ci лишена этих недостатков.
Итак, в настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.
Совет: для сохранения места с utf8mb4, используйте VARCHAR вместо CHAR. В противном случае MySQL будет резервировать четыре байта для каждого символа в стобце CHAR CHARACTER SET utf8mb4, поскольку это максимально возможная длина. Например, MySQL должна зарезервировать 40 байт для столбца CHAR(10) CHARACTER SET utf8mb4.