Комп’ютерна Академія “ШАГ”
Львівська Філія
вул. Замарстинівська 83А
тел.: 240-38-51, 067-480-77-24, 095-518-93-21
lviv@itstep.org
Кирилиця в MySQL
Юрій Максименко
або про налаштування кодувань
По читанню форумів з MySQL у мене склалося враження, що все прогресивне людство вирішує питання налаштувань не-англійських абеток в MySQL. Певно, тому, що це той рідкісний випадок, коли не дуже й допоможе документація з MySQL. Потрібну інформацію, звісно, вона містить (інакше звідки б я дізнався про те, що тут розповім?), але розпорошену по різних параграфах. Сподіваюсь, ця стаття заповнить таку прикру прогалину — сказав би, єдину перепону в справі використовування цього чудового SQL-сервера.
Якщо ми з вами, панове, розберемо деякі теоретичні основи зберігання символів та роботи з ними, то багато що, оповите сутінками, одразу видасться вам простим та очевидним. Так що треба лише трохи терпіння.
Кодування, воно ж charset, воно ж character set
Це таблиця, яка кожному символу ставить у відповідність число (одно- чи двобайтне).
Саме ці числа зберігаються в текстових полях MySQL. До речі, ці поля бувають з можливістю сортування(char й varchar) і без можливості сортування (text), до того ж для тих, що мають можливість сортування, слід вказувати максимальну довжину. Щоб краще це зрозуміти, звернімось до наступного питання.
Collations, або як нам відсортувати рядки
Для простоти розуміння уявімо, що в нас лише дві літери: а і б. Яким чином ми будемо сортувати рядки, складені з них?
Перше, що ми маємо зробити — завдати порядок символів, тобто «хто за ким стоїть». Зробити це просто: літері а поставимо у відповідність 1, літері б — 2
Зверніть увагу: ці числа не є кодуванням (charset).
Як ми відсортуємо рядки, рівні за довжиною, очевидно:
| ааа | 111 |
| ааб | 112 |
| абб | 122 |
Якщо рядки за довжиною не рівні, ми вирішимо проблему, якщо доставимо нулі справа:
| ааа | 111 |
| аб | 120 |
| б | 200 |
Алгоритм є очевидним: нулі додаються справа доти, поки довжина рядка не досягне максимальної. Тепер вам зрозуміло, навіщо для полів char и varchar необхідно вкзувати максимальний розмір рядка.
І ще стає зрозумілим наступне: щобільшим є максимальний розмір рядка, тим страшнішим є число, в котре перетворюється рядок для сортування. Тим більше, що в реальності число, яке співставляється з символом згідно collation, зовсім не є однозначним, як в нашому прикладі. Наблизимо приклад до реальності, й побачимо, якими числами доведеться оперувати за максимального числа символів 3 (три):
| ааа | A0A0A016 | 10 526 880 |
| аб | A0A10016 | 10 526 976 |
| б | A1000016 | 10 551 296 |
Висновок очевидний: треба економно завдавати максимальний розмір рядка для поля, щоб не гальмувати без потреби сортування та пошук за ним.
Вам тепер також зрозуміло, чому довгі тексти не сортуються.
Трохи складніше зрозуміти, чому для одного кодування (charset) можливим є існування кількох collation. За двома причинами:
- Одне кодування може «обслуговувати» кілька мов.
- Крім символів національних абеток, в кодуваннях присутні цифри та інші символи.
Які кодування підтримує Ваша версія MySQL?
Завдайте це питання своему серверу:
SHOW CHARACTER SET;
Буде виведено таблицю кодувань з поясненнями (англійською) і вказуванням collation за змовчанням.
Кирилических кодувань версія 5.0.67 пропонує чотире:
+---------+--------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+--------------------------+---------------------+--------+ | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | utf8 | Windows Cyrillic | utf8_general_ci | 1 | +---------+--------------------------+---------------------+--------+
На мою думку, найкраще використовати utf8.
* * *
Отже, ми вже знаємо, що таке charset и collation. Лишилося ще розглянути, як і для яких об'єктів вони встановлюються в MySQL.
Найперше нам необхідно згадати, що додаток, який використовує MySQL, складається з двох частин: сервер MySQL і програма-клієнт, як скеровує серверу запити й отримує відповіді.
Встановлення кодування за змовчанням на сервері
Що значить кодування за змовчанням?
Річ у тому, що ми можемо явно встановити кодування для наступних об'єктів:
- таблиць (знову-таки за змовчанням – про це далі);
- полів таблиць текстового типу ;
- Перемінних текстового типу в функціях та збережених процедурах;
А якщо кодування для якогось із цих об'єктів явно не вказане (так роблять майже всі автори сайтів, із розрахунку на адмінів хостингу)? В цьому випадку діє система змовчань, для якої кореневою буде ось це саме кодування серверу за змовчанням. Тобто, якщо не вказано взагалі жодних кодувань для об'єктів -- буде встановлено кодування за змовчанням на сервері.
Кодування за змовчанням на сервері MySQL завдається трьома параметрами:
- default-character-set – кодування (charset) за змовчанням.
Воно буде встановлено для всіх об'єктів (таблиць, полів і т.д.), де кодування явно не завдано - default-collation
- character-sets-dir – папка с файлами кодувань. За змовчанням це підпапка /share/charsets/
Є три способи встановити ці параметри для серверу:
- Завдати їх у командному рядку mysqld (або mysqld-nt)
- Створення файлу параметрів (option file) my.cnf чи my.ini
- (Unix / Linux) перекомпіляція
Розглянемо перші два способи більш детально.
Командний рядок mysqld (або mysqld-nt)
Командний рядок може мати наступний вигляд:
mysqld --default-character-set=кодування --default-collation=порядок сортування --character-sets-dir=папка з кодуваннями
Ідентичний формат рядка й для mysqld-nt:
mysqld-nt --default-character-set=кодування --default-collation=порядок сортування --character-sets-dir=папка з кодуваннями
Приклад (для Windows):
mysqld --default-character-set=utf8 --default-collation= utf8_general_ci --character-sets-dir=c:\mysql\share\charsets
Файл параметрів (option file) my.cnf або my.ini
Файл my.cnf згідно документації має працювати і в Linux, і в Windows, але, як я не намагався пристосувати його до Windows, він у мене так і не спрацював (не знаю, чия це помилка. Можливо, я щось не так робив). Так що я раджу в Windows використовувати my.ini. Він повинен бути в кореневій папці MySQL (якщо Ви при інсталяції не міняли папку для встановлення, то це щось на зразок C:\program files\MySQL\MySQL 5.0 Server ).
Приведу приклад конфігураційного файлу мy.ini, який дозволяє підтримувати кирилицю. В цьому файлі дозволяються коментарі, що починаються з # — чим я і скористаюсь для відмічання рядків, що відповідають за кирилицю.
Вам доведеться змінити в цьому файлі шляхи для свого серверу, про це дивіться коментарі.
Отже, конфігураційний файл мy.ini.
[client] port = 3306 #Конфігураційні параметри для серверу MySQL [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K # Встановлення кирилиці на сервері default-character-set=utf8 #Вказування кодування #Вказування шляху до папки кодувань (скорегуйте для свого серверу!) character-sets-dir=g:/mysql/share/charsets server-id = 1 # Конфігураційні параметри для програми резервного копіювання [mysqldump] quick max_allowed_packet = 16M # Встановлення кирилиці на сервері default-character-set=utf8 #Вказування кодування #Вказування шляху до папки кодувань (скорегуйте для свого серверу!) character-sets-dir=g:/mysql/share/charsets # Конфігураційні параметри для програми-клієнта mysql.exe [mysql] no-auto-rehash # Встановлення кирилиці на сервері default-character-set=utf8 #Вказування кодування #Вказування шляху до папки кодувань (скорегуйте для свого серверу!) character-sets-dir=g:/mysql/share/charsets [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout
(Unix / Linux) перекомпіляція
Я цей спосіб не практикував, тому відсилаю читача до документації:
9.1.3.1. Server Character Set and Collation
Встановлення кодування за змовчанням на клієнті
Під клієнтом в даному випадку розуміємо програму, яка скеровує MySQL запити й отримує відповіді.
Для випадку текстових полів і величин кодування клієнта й серверу мають бути узгоджені. Ми ж пам'ятаємо: символи зберігаються у вигляді чисел, а ці числа в залежності від кодування можуть відповідати різним символам.
Приклад 1. php-скрипт
Якщо в таблицях є текстові поля в кодуванні utf8, то для коректної обробки даних в них потрібно для MySQL-сесії встановити кодування:
mysql_query("set names utf8",$conn);
де $conn вказує на сесію MySQL (MySQL link identifier).
Приклад 2. Програма mysql
В склад MySQL входить програма mysql для роботи з сервером через командний рядок. Хоч вона й входить в поставку серверу MySQL — це теж клієнт: вона скеровує серверу запити й отримує від нього відповіді.
Тому й для неї потрібно встановлювати кодування при роботі з текстовими полями:
mysql> set names utf8; Query OK, 0 rows affected (0.05 sec)
Встановлювання кодування для таблиць і полів таблиць
При створенні таблиці можна встановити для її текстових полів кодування за змовчанням. В цьому випадку всі її текстові поля, для котрих явно не вказано кодування, будуть мати кодування таблиці за змовчанням. Розглянемо на прикладі:
mysql> create table test
-> (
-> id int primary key,
-> txt1 varchar(10), /* кодування цього поля буде utf8 */
-> txt2 varchar(10) character set utf8 /* utf8 */
-> ) default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql>
В цьому прикладі ми встановили для таблиці test кодування за змовчанням utf8, і поскільки для поля txt1 кодування явно не була встановлено, для нього буде встановлено кодування utf8. Зауважте — зовсім не встановлене на сервері за змовчанням: діє більш «близьке» змовчання — для таблиці.
Цей приклад також ілюструє, як можна явно прописати кодування для поля таблиці (в нашому прикладі для txt2).
Якщо для таблиці не встановити default charset — його буде встановлено сервером з використанням кодування за змовчанням на сервері.
А чому не встановити всюди utf-8?
Хороше питання. Я його для себе ще не вирішив.
З одного боку, для того и придумали Unicode — щоб було одне кодування на всі випадки.
З іншого боку, не все зрозуміло у питанні сортування. Те, що воно буде повільнішим для utf-8, зрозуміло. Якщо символи кодуються двома байтами, а не одним — з матеріалу параграфа Collations, або як нам відсортувати рядки вам стане зрозуміло, що розрядність числа, в яке перетворюється рядок, зростає колосально.
Та й чи є сортування, скажімо, кирилиці? Відповідь MySQL 5.0.67 щось дає мало надій:
mysql> SHOW COLLATION like 'utf%'; +--------------------+---------+-----+---------+----------+ | Collation | Charset | Id | Default | Compiled | +--------------------+---------+-----+---------+----------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | | utf8_bin | utf8 | 83 | | Yes | | utf8_unicode_ci | utf8 | 192 | | Yes | | utf8_icelandic_ci | utf8 | 193 | | Yes | | utf8_latvian_ci | utf8 | 194 | | Yes | | utf8_romanian_ci | utf8 | 195 | | Yes | | utf8_slovenian_ci | utf8 | 196 | | Yes | | utf8_polish_ci | utf8 | 197 | | Yes | | utf8_estonian_ci | utf8 | 198 | | Yes | | utf8_spanish_ci | utf8 | 199 | | Yes | | utf8_swedish_ci | utf8 | 200 | | Yes | | utf8_turkish_ci | utf8 | 201 | | Yes | | utf8_czech_ci | utf8 | 202 | | Yes | | utf8_danish_ci | utf8 | 203 | | Yes | | utf8_lithuanian_ci | utf8 | 204 | | Yes | | utf8_slovak_ci | utf8 | 205 | | Yes | | utf8_spanish2_ci | utf8 | 206 | | Yes | | utf8_roman_ci | utf8 | 207 | | Yes | | utf8_persian_ci | utf8 | 208 | | Yes | | utf8_esperanto_ci | utf8 | 209 | | Yes | | utf8_hungarian_ci | utf8 | 210 | | Yes | +--------------------+---------+-----+---------+----------+
Якось не кидається в очі російське чи українське сортування. Так що я поки почекаю з переходом на Юнікод...
* * *
DIXI
Бібрка, 16 грудня 2008 г.