Utf general ci что это

Корректная настройка MySQL для работы с UTF8

Сегодня речь пойдет о MySQL и о настройке UTF8 кодировки по-умолчанию. Тема заезжена, но как я убедился за прошедшую неделю, мало кто в состоянии нормально пояснить какие параметры и куда надо прописать для полноценной работы с UTF8 в MySQL. К сожалению, ситуация на тематических блогах оставляет желать лучшего. Основной тип ответа — приведение соедржимого конфигурационного файла с комментарием типа “попробуй, у меня это работает”.

Основная цель данного поста — выяснить, какие параметры и с какими значениями следует прописать в конфигурационный файл my.cnf (my.ini) для дальнейшей беспроблемной работы с Юникодом.

Рабочее окружение

UTF8 на данный момент у меня успешно работает в Мастер-Слейв конфигурации:

• MySQL версии 5.1.66
• Два сервера CentOS версии 6.3
• Репликация между серверами Master-Slave на базе SSL

Любой внешний клиент в состоянии корректно работать с 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 на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.

Главный раздел по описанию кодировок (character sets) и их представлений (collations — используется например при сортировке) в контексте сервера, базы, таблиц — это секция 10.1.3. Specifying Character Sets and Collations.

Символьная кодировка может быть задана для:

1. сервера,
2. базы данных,
3. таблицы и
4. колонок в таблице.

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

Все параметры могут быть переданы серверу тремя разными способами:

1. через командную строку mysqld
2. через конфигурационный файл my.cnf (my.ini)
3. через опции компиляции.

Второй и третий варианты рассматриваться не будут. Тут уместно будет просто прочитать официальные доки — в каждом разделе приведены примеры конфигурации с использованием всех трех способов. Я же буду использовать первый вариант.

Кодировка (character set) и представление (collation) сервера

Кодировка (characher set) — набор используемых символов.
Представление (collation) — набор правил для сравнения символов в наборе.

Тут есть несколько фундаментальных вещей которые надо понимать.

Основные параметры используемые в контексте сервера — это character_set_server и collation_server . Оба параметра влияют на определение кодировки и отображения сервера MySQL.

Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:

Не заданы — используются значения по умолчанию (дефолтные),

Заданы оба — используются указанные кодировка и ее представление,

Задана только кодировка — ее представление выставляется по умолчанию для данного типа кодировки. Что это значит? Для каждого типа кодировки есть ее дефолтное представление, например, дефолтная кодировка сервера — latin1 , а дефолтное отображение для нее — latin1_swedish_ci . Посмотреть соответствие кодировки и ее дефолтного представления можно используя команду:

Поле Default дает ответ о представлении выбранной кодировки.

В нашем случае, при настройке дефолтной кодировки в UTF8, параметры должны быть определены, так как могут быть использованы при определении кодировки или представления базы данных:

Наши команды:
my.cnf (my.ini)

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

Дефолтное представление для utf8 — utf8_general_ci , так что если бы мы его использовали вместо utf8_unicode_ci , то параметр collation_server можно было бы вообще опустить.

Кодировка (character set) и представление (collation) базы данных

Тут есть два варианта определения кодировки и представления:

явно — при выполнении запроса на создание базы данных:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

неявно через переменные character_set_database и collation_database . Однако, эти переменные нельзя задать явно ни в командной строке ни в конфигурационном файле. Как они инициализируются — чуть ниже.

Вообще при работе с базой данных огромную роль помимо серверных настроек играют настройки клиент-серверного соединения (connection). На этом этапе вступают в игру следующие специфичные для соединения параметры:

• character_set_client — кодировка в которой посылается запрос от клиента
• character_set_connection — кодировка используемая для конвертации пришедшего запроса (statement’а)
• character_set_results — кодировку, в которую сервер должен перевести результат перед его отправкой клиенту

Есть еще представление кодировки соединения ( 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

А так как у нас utf8 и ее дефолтное представление utf8_general_ci , то нам нужно выпонить полный запрос:

SET NAMES utf8 COLLATE utf8_unicode_ci

Таким образом, используя только этот запрос, можно добиться корректной UTF8 инициализации соединения.

Однако, тут есть один нюанс:

SET NAMES x , как понятно из определения, определяет настройку клиента при коннекте к серверу. Но что делать, если клиент — сам mysql.exe и нам хочется установить collation_connection по-умолчанию, не выполняя каждый раз SET NAMES x при коннекте?
Для этих целей, существует еще один параметр — default_character_set . Он эквивалентен запросу SET NAMES utf8 . В случае его использования задать collation_connection отличный от дефолтного уже не получится, поэтому придется заюзать еще одну команду init_connect (так как напрямую collation_connection нельзя прописать в конфигурационном файле):

init_connect=‘SET collation_connection = utf8_unicode_ci’

Но и тут есть еще одно но: init_connect команда не выполняется для SUPER пользователей — пользователей, обладающих привилегией SUPER. root входит в этот перечень, поэтому при коннекте root’ом команду 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 соответственно (выше я про них упоминал).

За более детальной информацией отсылаю по двум источникам — собственно к официальной документации и прекрасно оформленному ответу на stackoverflow.com. Для нашей задачи вполне хватает первого параметра вместе с дополнительной командой.

Подытожим: различные сценарии и что юзается на каждом из них — относительно к настройкам соединения:

• Если к базе коннектится mysql.exe клиент с пользователем с привилегией SUPER:
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • надо выполнить вручную команду init_connect=’SET collation_connection = utf8_unicode_ci’
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • срабатывает команда в конфигурационном файле init_connect=’SET collation_connection = utf8_unicode_ci’
  • надо выполнить вручную команду SET NAMES utf8 COLLATE utf8_unicode_ci

  Наши команды:
  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

  Помимо освещенных параметров, есть еще один довольно часто фигурирующий в разного рода источниках — 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.

  utf8_general_ci VS utf8_unicode_ci what should we use?

  These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared.

  Note: in new versions of MySQL use utf8mb4 , rather than utf8 , which is the same UTF-8 data format with same performance but previously only accepted the first 65,536 Unicode characters.

  • Accuracy
    utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide range of languages.
    utf8mb4_general_ci fails to implement all of the Unicode sorting rules, which will result in undesirable sorting in some situations, such as when using particular languages or characters.
  • Performance
    utf8mb4_general_ci is faster at comparisons and sorting, because it takes a bunch of performance-related shortcuts.
    On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.
    utf8mb4_unicode_ci , which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.

  As far as Latin (ie “European”) languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:

  • For examples, the Unicode collation sorts “ß” like “ss”, and “Œ” like “OE” as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).
  • Some Unicode characters are defined as ignorable, which means they shouldn’t count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.

  In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.

  What should you use?
  There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.
  The difference in performance is only going to be measurable in extremely specialised situations, and if that's you, you probably already know about it. If you're experiencing slow sorting, in almost all cases it'll be an issue with your indexes/query plan. Changing your collation function should not be high on the list of things to troubleshoot.
  In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.
  One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

  What's the difference between utf8_general_ci and utf8_unicode_ci?

  Between utf8_general_ci and utf8_unicode_ci , are there any differences in terms of performance?

  9 Answers 9

  For those people still arriving at this question in 2020 or later, there are newer options that may be better than both of these. For example, utf8_unicode_520_ci .

  All these collations are for the UTF-8 character encoding. The differences are in how text is sorted and compared.

  _unicode_ci and _general_ci are two different sets of rules for sorting and comparing text according to the way we expect. Newer versions of MySQL introduce new sets of rules, too, such as _unicode_520_ci for equivalent rules based on Unicode 5.2, or the MySQL 8.x specific _0900_ai_ci for equivalent rules based on Unicode 9.0 (and with no equivalent _general_ci variant). People reading this now should probably use one of these newer collations instead of either _unicode_ci or _general_ci . The description of those older collations below is provided for interest only.

  MySQL is currently transitioning away from an older, flawed UTF-8 implementation. For now, you need to use utf8mb4 instead of utf8 for the character encoding part, to ensure you are getting the fixed version. The flawed version remains for backward compatibility, though it is being deprecated.

  Key differences

  utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

  utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

  On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today’s computers.

  Benefits of utf8mb4_unicode_ci over utf8mb4_general_ci

  utf8mb4_unicode_ci , which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call ‘alphabetical order’.

  As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:

  For examples, the Unicode collation sorts "ß" like "ss", and "Œ" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).

  Some Unicode characters are defined as ignorable, which means they shouldn’t count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.

  In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it’ll be quite inadequate.

  What should you use?

  There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.

  In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.

  There’s an argument to be made that if speed is more important to you than accuracy, you may as well not do any sorting at all. It’s trivial to make an algorithm faster if you do not need it to be accurate. So, utf8mb4_general_ci is a compromise that’s probably not needed for speed reasons and probably also not suitable for accuracy reasons.

  One other thing I’ll add is that even if you know your application only supports the English language, it may still need to deal with people’s names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

  What the parts mean

  Firstly, ci is for case-insensitive sorting and comparison. This means it’s suitable for textual data, and case is not important. The other types of collation are cs (case-sensitive) for textual data where case is important, and bin , for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data — if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.

  Next, unicode or general refers to the specific sorting and comparison rules — in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with unicode and general being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that unicode uses rules from Unicode 4.0. Recent versions of MySQL and MariaDB add the rulesets unicode_520 using rules from Unicode 5.2, and MySQL 8.x adds 0900 (dropping the "unicode_" part) using rules from Unicode 9.0.

  And lastly, utf8mb4 is of course the character encoding used internally. In this answer I’m talking only about Unicode based encodings.

  MySQL UTF-8 charsets and collations explained

  Which MySQL UTF-8 character set and collation should you choose for your database or table? When you run SHOW COLLATION in MySQL or MariaDB, you will see a large amount of available character sets and collations such as:

  • utf8_general_ci
  • utf8_general_mysql500_ci
  • utf8_unicode_ci
  • utf8_unicode_520_ci
  • utf8_bin
  • utf8mb4_general_ci
  • utf8mb4_unicode_ci
  • utf8mb4_unicode_520_ci

  That is confusing. This article will attempt to provide the most important answers so that you will be able to make an informed and rational decision on which MySQL charset and collation you should use for your particular use cases.

  What are the differences between the utf8 and utf8mb4 character sets?

  utf8 uses a maximum of three bytes per character while utf8mb4 uses four bytes per character. While the utf8 charset is able to store Chinese, Japanese, and Korean characters (which are in the Basic Multilingual Plane), it may still not be able to store all the characters that you want. For example, with a utf8 charset, it is not possible to insert the Unicode character ‘SNOWMAN’ (U+2603) ☃, but this is possible with utf8mb4 charsets. More importantly, new emojis (such as ‘ROBOT FACE’ ��) are outside the Basic Multilingual Plane, so to store them, you will need to use a utf8mb4 charset.

  Choosing utf8 vs. utf8mb4 will depend on what characters you want to store, but if in doubt, use a utf8mb4 charset because it is able to store all the characters in the utf8 charset and more.

  What are the differences between the utf8_general_ci, utf8_unicode_ci, and utf8_bin collations?

  The difference between them is how they compare characters. The binary (bin) collation blindly compares the binary value of characters (so it is case sensitive). The unicode collations follows the Unicode standard for comparisons. The general collation is faster but less correct than the unicode collation. If you need more correct comparisons (for example, between ‘a’ and ‘á’, or ‘C’ and ‘Ç’), use the unicode collation, otherwise, you may be satisfied with the general collation.

  What are the differences between the unicode and unicode_520 collations?

  The unicode collation follows an older standard for character comparison, while the unicode_520 collation follows a newer (but arguably still outdated) Unicode standard for character comparison.

  What is the utf8_general_mysql500_ci collation?

  The utf8_general_mysql500_ci collation was introduced for backward compatibility of old tables that used the old utf8_general_ci from before MySQL 5.1.24. There is no good reason to use this in new tables.

  Conclusion

  After knowing all this, it may still be difficult to choose a charset and a collation. My suggestion is that you should always use utf8mb4 charsets over utf8 charsets, and when in doubt, use utf8mb4_unicode_520_ci as it offers the greatest number of characters that you can store, and it sorts characters in the most correct way possible.

  If you feel that some of the points were unclear, please share what you think can be improved. If you feel that you need a more in depth explanation of the differences between the charsets and the collations, do consult the MySQL or MariaDB documentation.

  Читать:

  Как скачать драйвер для веб камеры на компьютер бесплатно