"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу
Использование русского языка и символов кириллицы в базах данных Oracle
На сегодняшний день использование набора символов Unicode стало стандартом в почти всём программном обеспечении. Вот уже несколько лет Oracle предлагает элегантное решение в виде кодировки AL32UTF8. Тем не менее, во многих случаях использование Unicode нежелательно, по причинам несовместимости со старыми версиями программных продуктов либо из соображений экономии дискового пространства, как в случае с бесплатным Oracle Express Edition.
Данная заметка знакомит читателя с двумя подходами к использованию русского языка и сохранению кириллических символов в базах данных Oracle. Первый способ основан на "рекомендованном подходе" использования Юникод, что неизбежно влечёт за собой двойной перерасход места на диске. Второй способ считается устаревшим, но уменьшает обьём данных, используя строго один байт для каждого кириллического символа. Это особенно важно при использовании Oracle Express, где обьём данных физически ограничен всего одиннадцатью гигабайтами.
Прежде чем мы продолжим, я хотел бы привести строки из Евангелия:
......................... == Третья книга Ездры == .............................
=== Глава 2, Стих 22 ===
20 Оправдай вдову, дай суд бедному, помоги нищему, защити сироту, одень
нагого,
21 о расслабленном и немощном попекись, над хромым не смейся, безрукого
защити, и слепого приведи к видению света Моего,
22 старца и юношу в стенах твоих сохрани,
23 мертвых, где найдешь, запечатлев, предай гробу, и Я дам тебе первое место
в Моем воскресении.
Лично для вас благая весть - Единородный Сын Божий Иисус Христос любит вас, Он взошёл на крест за ваши грехи, был распят и на третий день воскрес, сел одесную Бога и открыл нам дорогу в Царствие Небесное.
Как же нам воспользоваться этой дорогой, как достичь Царствия Небесного? Путь этот труден и не даётся легко. Каждый сам настойчиво прилагает усилия, усмиряет страсти, борется с искушениями, познаёт Слово Господне. Только вот очень часто забываем мы, в процессе нашей собственной борьбы, о других - близких нам братьях и сёстрах. Вот и напоминает нам Бог о помощи нищим и защите сирот.
"и слепого приведи к видению света Моего" - говорит нам Господь. Как же так, ведь не можем мы с вами открыть глаза слепца? Можем, если речь идёт о слепце духовном, о таком, каким был каждый из нас до Покаяния. Расскажи ближнему своему о Боге, открой для него Евангелие, помоги ему дойти до Слова Божия - вот и откроешь глаза ему. А дальше - в молении и посте проси Бога о дарах Его - возможно, получишь дар исцеления болезней, и будешь открывать глаза и мирским слепцам. Велик Господь Бог и милосерд, и щедро благословляет верующих!
Покайтесь, примите Иисуса как вашего Спасителя, ибо наступают последние времена и время близко - стоит Судья у ворот.
Пожалуйста, в своих каждодневных трудах, какими бы занятыми вы себе ни казались - находите время для Бога, Его заповедей и Библии.
На главной странице этого сайта вы найдете программу для чтения Библии в командной строке - буду очень рад если программа окажется полезной. Пожалуйста, читайте Библию, на экране или в печатном виде - вы будете искренне удивлены как много там сказано лично про вас и ваши обстоятельства.
Вернёмся к нашим техническим деталям.
Я предполагаю, что уважаемый читатель уже устанавил Oracle Database Express Edition 11.2 на своём Linux сервере и создал базу данных. Если это не так - воспользуйтесь одной из моих заметок на сайте, описывающей процесс установки программного обеспечения Oracle.
Мы рассмотрим два варианта работы с русскими символами:
Но сначала остановимся на минутку и подумаем, какие программные компоненты участвуют в выводе русского символа из поля записи базы данных.
"Внутри" базы, Оракл хранит символы в двоичном формате, в кодировке заданной при создании базы данных командой "CREATE DATABASE". Оракл должен знать что же за символ представлен тем или иным набором битов - иначе встроенные функции типа "upper", "lower" и "like" не смогут работать правильно.
В нашем случае использования Linux, все начинается со взаимодействия со стандартной библиотекой C или C++. Оракл, как и другие программы, читает с диска несколько байт данных, преобразует их и отправляет в стандартный поток вывода один или несколько байт в некоей кодировке, как букву или цифру из определённого набора символов. Поскольку мы взаимодействуем с базой данных через клиента (предположим SQL*Plus), то приложение "sqlplus" отвечает за посылку данных в стандартный поток вывода и использует переменную окружения NLS_LANG для представления записей в правильном "выходном" формате.
Linux, в свою очередь, принимает эти один или несколько байт, и выводит их на экран, по правилам текущей локали для оболочки пользователя, зависящих от переменной окружения LANG.
Как видно, имеются три "параметра", определяющих что мы увидим на экране - правильную запись на русском языке или "крокозябли". А именно мы должны "объяснить" Ораклу и Линуксу на каком языке (в какой кодировке и какого набора символов) разговаривать с нами.
Таким образом, для правильного вывода кириллицы из базы данных на экран мы должны установить эти переменные так, чтобы они совпали:
Установив все их, например, в кодировку UTF8 набора символов Юникод, мы сможем сохранять в базе, обрабатывать и выводить кириллические (и любые другие) символы на экран без потери их начального значения. Именно это и происходит, когда мы устанавливаем Линукс и Оракл "из коробки", используя параметры по-умолчанию.
Естественно, каждый кириллический символ в кодировке UTF8 занимает ровно 2 байта - как и на этой странице, например, каждая буква нашего алфавита представлена в виде последовательности двух байт. То есть, любой текст будет иметь в два раза больше байт, чем символов. В случае однобайтной кодировки КОИ-8, каждый символ представлен только одним байтом, и тот же самый текст занимает в два раза меньший объём. Зная всё это, перейдём к примерам.
Multibyte - использование кодировки AL32UTF8
Этот случай предсталяет собой "рекомендованный подход" и применяется практически везде. Установив rpm-пакет с Ораклом Экспресс 11.2, вы получите именно эту конфигурацию. Посмотрим как происходит работа с русским текстом в кодировке UTF8 набора символов Unicode.
Прежде всего, проверим, что было использовано в качестве CHARACTERSET при создании базы данных (создаваемой в процессе установки со стандартным именем "XE").
1 select * from nls_database_parameters 2* where parameter like '%CHARACTERSET' 15:14:49 SQL> / PARAMETER VALUE ------------------------------ ------------------------- NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16
Обратим внимание на значение параметра "NLS_CHARACTERSET" равное "AL32UTF8" (и проигнорируем "NLS_NCHAR_CHARACTERSET" - изучение этого параметра выходит за рамки нашей заметки. Также существует инициализационный параметр "NLS_LENGTH_SEMANTICS", который нужно устанавливать только на уровне сессии пользователя, но не для всего экземпляра оракла, обязательно прочтите про "NLS_LENGTH_SEMANTICS" в документации). Кодировка AL32UTF8 - это "ораклячая" версия UTF8 кодировки для набора символов Unicode. AL32UTF8 использует один байт для ASCII символов и до четырёх байт для всех остальных. Не используйте "ораклячую" UTF8 никогда - несмотря на совпадение имени, эта кодировка может занимать до шести байт для одного символа. Ещё раз - название кодировки "utf8" в Oracle - AL32UTF8.
Проверим, какую кодировку будут использовать наш клиент (sqlplus) и оболочка Линукс (bash).
[ora11@attack ~]$ env | grep LANG NLS_LANG=English_America.AL32UTF8 LANG=en_US.utf8 [ora11@attack ~]$ [ora11@attack ~]$ locale -a | grep ru ru_RU ru_RU.iso88595 ru_RU.koi8r ru_RU.utf8 russian ru_UA ru_UA.koi8u ru_UA.utf8 [ora11@attack ~]$
Заметьте, что переменная NLS_LANG используется Ораклом и поэтому "играет по его правилам" - название кодировки Unicode в этой переменной обычно должно совпадать со значением NLS_CHARACTERSET в базе данных. Оболочка линукса bash использует значения, продиктованные стандартной библиотекой C (их можно вывести командой "locale -a"). В нашем примере мы не используем значение "ru_RU.utf8" чтобы не пугаться русских фраз в линуксе. Посмотрите ниже как это выглядит.
[ora11@attack ~]$ echo $LANG en_US.utf8 [ora11@attack ~]$ ls $ ls: cannot access $: No such file or directory [ora11@attack ~]$ [ora11@attack ~]$ export LANG=ru_RU.utf8 [ora11@attack ~]$ echo $LANG ru_RU.utf8 [ora11@attack ~]$ ls $ ls: невозможно получить доступ к $: Нет такого файла или каталога [ora11@attack ~]$
Таким образом, первая часть значения переменной LANG определяет на каком языке (и для какой страны) с вами будет разговаривать Линукс, а вторая часть после точки задаёт кодировку набора символов.
Как мы убедились, и Оракл и Линукс будут разговаривать с нами используя одну и ту же кодировку UTF8 и набор символов Unicode. Таков же и формат текстовых записей внутри базы данных (NLS_CHARACTERSET). Установку кодовой страницы терминала Гном проверим зайдя в меню "Terminal"-"Set Character Encoding". Должно быть отмечено значение "Unicode (UTF-8)"
Проверим работу всей цепочки.
-- Можно использовать "character semantics": -- create table t (id number, msg varchar2(100 CHAR)); -- В наших примерах мы используем "byte semantics". 15:45:10 SQL> create table t (id number, msg varchar2(100)); Table created. Elapsed: 00:00:00.37 15:45:42 SQL> insert into t values (1, 'Тест 1'); 1 row created. Elapsed: 00:00:00.01 15:46:02 SQL> insert into t values (2, 'Проверка 2'); 1 row created. Elapsed: 00:00:00.00 15:46:23 SQL> commit; Commit complete. Elapsed: 00:00:00.02 15:46:25 SQL> col msg for a25 15:46:39 SQL> select * from t; ID MSG ---------- ------------------------- 1 Тест 1 2 Проверка 2 2 rows selected. Elapsed: 00:00:00.01 15:46:44 SQL> 15:48:01 SQL> select * from t where msg like 'Про%'; ID MSG ---------- ------------------------- 2 Проверка 2 1 row selected. Elapsed: 00:00:00.01 15:48:17 SQL> select * from t where upper (msg) = 'ТЕСТ 1'; ID MSG ---------- ------------------------- 1 Тест 1 1 row selected. Elapsed: 00:00:00.01 15:48:53 SQL> select lower(msg) from t; LOWER(MSG) ------------------------------------ тест 1 проверка 2 2 rows selected. Elapsed: 00:00:00.01 15:49:05 SQL>
Очевидно, что мы не только можем сохранить и вновь прочесть введённые записи, но и в состоянии правильно обработать их, используя встроенные функции Оракла. Что произойдёт, если мы изменим одно из значений в нашей "цепи"?
[ora11@attack ~]$ env | grep LANG NLS_LANG=English_America.AL32UTF8 LANG=ru_RU.koi8r [ora11@attack ~]$ [ora11@attack ~]$ sqlplus -s / as sysdba select * from t; ID ---------- MSG -------------------------- 1 п╒п╣я│я┌ 1 2 п÷я─п╬п╡п╣я─п╨п╟ 2 2 rows selected. [ora11@attack ~]$ [ora11@attack ~]$ env | grep LANG NLS_LANG=English_America.CL8ISO8859P5 LANG=en_US.utf8 [ora11@attack ~]$sqlplus -s / as sysdba select * from t; ID ---------- MSG --------------------------- 1 ���� 1 2 �������� 2 2 rows selected.
Если вы установите значение NLS_LANG в что-то несуществующее в представлении "v$NLS_VALID_VALUES", на экране возникнет ошибка "ORA-12705: Cannot access NLS data files or invalid environment specified". Но даже если одна из переменных окружения LANG или NLS_LANG будет установлена в правильное, но не совпадающее с базой данных (NLS_CHARACTERSET) значение - данные будет невозможно прочесть.
Single byte - Данные сохраняются в КОИ-8
Как сказано выше, сохранение русского текста в формате Юникод имеет нежелательный побочный эффект - объём данных удваивается. В большинстве случаев это не является проблемой и использование набора символов AL32UTF8 рекомендовано к использованию во всех базах данных Oracle.
В то же время, я чётко вижу два сценария когда я бы предпочёл Юникоду какую-либо однобайтную кодировку, например КОИ-8. Во-первых, для очень большой базы с большим содержанием текстовой информации размером, скажем в 300Тб, разница в цене дисковой памяти между 600Тб и 300Тб слишком велика, чтобы пренебречь ею ради стандартизации. Во-вторых, при использовании Oracle XE, где максимальный размер хранимых данных ограничен производителем.
В начале проверим, соответствует ли практика нашей теории.
-- Вставляем русский текст в кодировке AL32UTF8, 2 байта на символ
declare i number;
begin
for i in 1..400 loop
insert into t values (i, 'тестируем');
end loop;
commit;
end;
/
16:29:48 SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
16:29:49 SQL> select count(*) from t;
COUNT(*)
----------
400
1 row selected.
Elapsed: 00:00:00.00
16:29:56 SQL>
16:31:43 SQL> exec dbms_stats.gather_table_stats('SYS', 'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.97
16:31:53 SQL>
16:32:14 SQL> select NUM_ROWS, blocks, AVG_SPACE, AVG_ROW_LEN
16:32:55 2 from dba_tables
16:33:03 3 where table_name = 'T';
NUM_ROWS BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- -----------
400 3 0 41
1 row selected.
Elapsed: 00:00:00.03
16:33:10 SQL>
16:33:10 SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.12
16:34:51 SQL>
-- Теперь тот же текст, но в ASCII одно-байтных символах
declare i number;
begin
for i in 1..400 loop
insert into t values (i, 'testiruem');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
16:36:02 SQL> exec dbms_stats.gather_table_stats('SYS', 'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
16:36:20 SQL> select NUM_ROWS, blocks, AVG_SPACE, AVG_ROW_LEN
16:36:27 2 from dba_tables
16:36:30 3 where table_name = 'T';
NUM_ROWS BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- -----------
400 2 0 14
1 row selected.
Elapsed: 00:00:00.00
16:36:33 SQL>
Мы видим почти 3-х кратную разницу в размере одной записи. Дополнительное место занимают внутренние служебные структуры базы данных. Таким образом, наша терория подтверждена практикой. И, поскольку мы используем Oracle XE, посмотрим как можно использовать кодировку КОИ-8 чтобы снизить объём данных и в то же время не потерять функциональность работы с русскими символами.
............ == Послание к Ефесянам святого апостола Павла == ................ === Глава 3, Стих 17 === 14 Для сего преклоняю колени мои пред Отцем Господа нашего Иисуса Христа, 15 от Которого именуется всякое отечество на небесах и на земле, 16 да даст вам, по богатству славы Своей, крепко утвердиться Духом Его во внутреннем человеке, 17 верою вселиться Христу в сердца ваши, 18 чтобы вы, укорененные и утвержденные в любви, могли постигнуть со всеми святыми, что широта и долгота, и глубина и высота, 19 и уразуметь превосходящую разумение любовь Христову, дабы вам исполниться всею полнотою Божиею. (b+/b-, c+/c-, +/-, *) > [oracle@attack ~]$ [oracle@attack ~]$ sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 12 16:56:09 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 16:56:18 SQL> 1 select * from nls_database_parameters 2* where parameter = 'NLS_CHARACTERSET' 16:57:26 SQL> / PARAMETER VALUE ------------------------- ------------------------- NLS_CHARACTERSET CL8KOI8R 1 row selected. Elapsed: 00:00:00.00 16:57:27 SQL> 16:57:27 SQL> !env | grep LANG NLS_LANG=American_America.CL8KOI8R LANG=en_US.koi8r 16:57:51 SQL> 16:57:51 SQL> create table t (id number, msg varchar2(100)); Table created. Elapsed: 00:00:00.39 16:58:47 SQL> insert into t values (1, 'Тестируем русский в КОИ-8'); 1 row created. Elapsed: 00:00:00.01 16:59:16 SQL> commit; Commit complete. Elapsed: 00:00:00.02 16:59:18 SQL> select * from t; ID ---------- MSG --------------------------- 1 Тестируем русский в КОИ-8 1 row selected. Elapsed: 00:00:00.01 16:59:22 SQL>
Те же самые правила работают - кодировки всех "звеньев" в нашей цепи программных средств должны совпадать. База данных и клиент Оракла работают в кодировке "CL8KOI8R", локаль системы установлена в "koi8r" и эмулятор терминала использует кодовую страницу "Cyrillic (KOI8-R)". Удостоверимся, что использование одно-байтной кодировки позволяет нам существенно уменьшить размер записи.
declare i number;
begin
for i in 1..400 loop
insert into t values (i, 'тестируем');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
17:10:54 SQL> exec dbms_stats.gather_table_stats('SYS', 'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
17:11:01 SQL> select NUM_ROWS, blocks, AVG_SPACE, AVG_ROW_LEN
17:11:06 2 from dba_tables
17:11:11 3 where table_name = 'T';
NUM_ROWS BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- -----------
400 2 0 14
1 row selected.
Elapsed: 00:00:00.01
17:11:15 SQL> select * from t where rownum <5;
ID
----------
MSG
----------------------------------------
1
Тестируем
2
Тестируем
3
Тестируем
4
Тестируем
4 rows selected.
Elapsed: 00:00:00.01
17:11:27 SQL>
В заключение надо отметить, что Оракл может конвертировать данные из одного набора симболов в другой "налету", такой эффект достигается установкой переменных окружения LANG и NLS_LANG в одно значение, соответствующее текущей локали операционной системы. В это же время NLS_CHARACTERSET "внутри" базы данных может отличаться от среды окружения пользователя - и в этом случае Оракл произведёт конвертирование данных, как показано ниже.
[ora11@NAU ~]$ sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Чт Окт 13 12:03:37 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Введите имя пользователя: / as sysdba Присоединен к: Oracle Database 11g Release 11.2.0.2.0 - Production 12:03:40 SQL> select * from nls_database_parameters; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET CL8ISO8859P5 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.2.0 20 строк выбрано. Затрач.время: 00:00:00.01 12:03:52 SQL> !env | grep LANG NLS_LANG=Russian_Russia.CL8KOI8R LANG=ru_RU.koi8r 12:04:00 SQL> select * from t where rownum <3; ID ---------- MSG -------------------------------- 1 Тестируем 2 Тестируем 2 строк выбрано. Затрач.время: 00:00:00.01 12:04:09 SQL> Отсоединено от Oracle Database 11g Release 11.2.0.2.0 - Production [ora11@NAU ~]$
Как видно, работать с русскими символами в Оракле просто. Всё что необходимо - помнить о том, какую кодировку использует база данных и настраивать клиента, локаль и эмулятор терминала соответственно.
Другие средства разработки также успешно будут работать с вашими данными - и в AL32UTF8, и в CL8KOI8R. В качестве упражнения я предлагаю читателю установить Oracle SQL Developer и проверить его работу с русским текстом в таблице "Т". Не забудьте проверить какую кодировку (и как) устанавливает для вас эта java-программа.
Спасибо что зашли,
Будьте благословенны!
Денис
