Вы здесь

Использование русского языка и символов кириллицы в базах данных Oracle

На сегодняшний день использование набора символов Unicode стало стандартом в почти всём программном обеспечении. Вот уже несколько лет Oracle предлагает элегантное решение в виде кодировки AL32UTF8. Тем не менее, во многих случаях использование Unicode нежелательно, по причинам несовместимости со старыми версиями программных продуктов либо из соображений экономии дискового пространства, как в случае с бесплатным Oracle Express Edition.Данная заметка знакомит читателя с двумя подходами к использованию русского языка и сохранению кириллических символов в базах данных Oracle. Первый способ основан на "рекомендованном подходе" использования Юникод, что неизбежно влечёт за собой двойной перерасход места на диске. Второй способ считается устаревшим, но уменьшает обьём данных, используя строго один байт для каждого кириллического символа. Это особенно важно при использовании Oracle Express, где обьём данных физически ограничен всего одиннадцатью гигабайтами. Мы рассмотрим два варианта работы с русскими символами:

  • Multibyte - использование кодировки AL32UTF8.
  • Single byte - Данные сохраняются в КОИ-8 или ISO 8859-5.

 Но сначала остановимся на минутку и подумаем, какие программные компоненты участвуют в выводе русского символа из поля записи базы данных."Внутри" базы, Оракл хранит символы в двоичном формате, в кодировке заданной при создании базы данных командой "CREATE DATABASE". Оракл должен знать что же за символ представлен тем или иным набором битов - иначе встроенные функции типа "upper", "lower" и "like" не смогут работать правильно.В нашем случае использования Linux, все начинается со взаимодействия со стандартной библиотекой C или C++. Оракл, как и другие программы, читает с диска несколько байт данных, преобразует их и отправляет в стандартный поток вывода один или несколько байт в некоей кодировке, как букву или цифру из определённого набора символов. Поскольку мы взаимодействуем с базой данных через клиента (предположим SQL*Plus), то приложение "sqlplus" отвечает за посылку данных в стандартный поток вывода и использует переменную окружения NLS_LANG для представления записей в правильном "выходном" формате.Linux, в свою очередь, принимает эти один или несколько байт, и выводит их на экран, по правилам текущей локали для оболочки пользователя, зависящих от переменной окружения LANG.Как видно, имеются три "параметра", определяющих что мы увидим на экране - правильную запись на русском языке или "крокозябли". А именно мы должны "объяснить" Ораклу и Линуксу на каком языке (в какой кодировке и какого набора символов) разговаривать с нами.Таким образом, для правильного вывода кириллицы из базы данных на экран мы должны установить эти переменные так, чтобы они совпали:

  • CHARACTERSET при создании базы данных
  • переменную окружения NLS_LANG для клиента (sqlplus)
  • переменную окружения LANG для командной оболочки (bash)
  • в дополнение, кодовую страницу нашей программы эмулятора терминала (gnome-terminal)
  • Установив все их, например, в кодировку 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-программа.

Post new comment

Filtered HTML

  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Разрешённые HTML-теги: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Строки и параграфы переносятся автоматически.

Plain text

  • HTML-теги не обрабатываются и показываются как обычный текст
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Строки и параграфы переносятся автоматически.
CAPTCHA
Этот вопрос задается для того, чтобы выяснить, являетесь ли Вы человеком или представляете из себя автоматическую спам-рассылку.
6 + 0 =
Решите эту простую математическую задачу и введите результат. Например, для 1+3, введите 4.