THE HOLY BIBLE - King James Version - БИБЛИЯ в Синодальном переводе
"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу

Использование русского языка и символов кириллицы в базах данных 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.

Мы рассмотрим два варианта работы с русскими символами:

  • 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-программа.

    Спасибо что зашли,

    Будьте благословенны!
    Денис


    Сайт не содержит активных элементов - скриптов и cookies