Oracle DatabaseでのUnicode設計

OracleでUTF-8を使うときの豆知識です。

これは、Oracle XE 11g Release2での検証結果です。

Oracleの文字列型

通常はVARCHAR2またはNVARCHAR2を使うことになると思います。違いは以下のとおりです。

Charset	説明
VARCHAR2	DatabaseのCharset依存
NVARCHAR2	Unicodeのみ(Oracleを含む大抵のDBではUTF-16でエンコードする)

Oracleの場合、それぞれの型に別ののCharsetを指定することが可能です。ふつうにOracleをインストールすると、

NLS_CHARSET=AL32UTF8
NLS_NCHAR_CHARSET=AL16UTF16

になるかと思います。当然ながらNLS_NCHAR_CHARSETには、Unicode系のCharsetしか設定できません。(実際にはNLS_NCHAR_CHARSETに、AL16UTF16以外をセットしたことがないので、それ以外のときにどういう挙動になるか分かってません。)

実際にどの型でどのCharsetを使うかは、以下のSQLで見ることができます。(要sysオブジェクトへの参照権限)

SELECT distinct(nls_charset_name(charsetid)) CHARACTERSET,
  decode(type#,1,decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2', 'UNKNOWN'),
  9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
  96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
  112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
FROM sys.col$
WHERE charsetform in (1,2) and type# in (1,9,96,112)
ORDER BY characterset;

CHARACTERSET                             TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16                                NCHAR
AL16UTF16                                NCLOB
AL16UTF16                                NVARCHAR2
AL32UTF8                                 CHAR
AL32UTF8                                 CLOB
AL32UTF8                                 VARCHAR2

VARCHAR2とNVARCHAR2の相違点

VARCHAR2とNVARCHAR2の違いはなんでしょうか。VARCHAR2はカラム長がバイト指定で、NVARCHAR2は文字数指定という認識をしている人が、オールドタイプには多いのですが、正確ではありません。
VARCHAR2には長さセマンティクスという概念があり、カラムサイズがバイト数か文字数かを選択できます(9i以降)。デフォルトはバイト数です。

つまり、

HOGE   VARCHAR2(10 CHAR)

とキャラクタ・セマンティクスを使うと、この列には10文字格納できるようになります。では

HOGE   NVARCHAR2(10)

とは何が違うのでしょうか。

ここでの違いが前述のCharsetの違いになります。
NLS_CHARSETをAL32UTF8とした場合、VARCHAR2の文字列はUTF-8でエンコードされて格納されます。一方、NVARCHAR2は、UTF-8で1～3バイトで扱っていた文字は全て2バイトで格納されます。

この違いはディスク使用量によって違いが出てきます。つまりSJISでいうところの半角文字(UTF-8 1バイト文字)が多めのデータは、VARCHAR2の方が容量が少なく、ひらがな・漢字が多めのデータはNVARCHAR2の方が容量が少なくなります。

SQL> CREATE TABLE varchar_col (a varchar2(10 CHAR));

表が作成されました。

SQL> insert into varchar_col values('abc');

1行が作成されました。

SQL> insert into varchar_col values('あいう');

1行が作成されました。

SQL> CREATE TABLE nvarchar_col (a nvarchar2(10));

表が作成されました。

SQL> insert into nvarchar_col values('abc');

1行が作成されました。

SQL> insert into nvarchar_col values('あいう');

1行が作成されました。

SQL> select a, length(a), lengthb(a) from varchar_col;

A                                         LENGTH(A) LENGTHB(A)
---------------------------------------- ---------- ----------
abc                                               3          3
あいう                                             3          9

SQL> select a, length(a), lengthb(a) from nvarchar_col;

A                                         LENGTH(A) LENGTHB(A)
---------------------------------------- ---------- ----------
abc                                               3          6
あいう                                             3          6

※ LENGTHが文字数、LENGTHBがバイト数です。

が現代において、この差にそこまで神経質になることは無いかと思います。ので、この結果をもって、どっちを使うか決めるのは早計気味です。

サロゲートペア

さて、サロゲートペアの扱いはどうなのでしょうか? 主だったRDBMSはサロゲートペアを1文字として扱えるようになっているようです。

• Oracle (9i以降)
• MySQL (5.5.4以降) utf8mb4は問題多し
• PostgreSQL (8.2.2以降)

ということで、OracleもVARCHAR2にもNVARCHAR2にもサロゲートペア文字は入ります。

が、ここで違いがあります。

SQL> insert into varchar_col values('123456789𠮷');

1行が作成されました。

SQL> insert into nvarchar_col values('123456789𠮷');
insert into nvarchar_col values('123456789𠮷')
*
行1でエラーが発生しました。:
ORA-12899: 列"SCOTT"."NVARCHAR_COL"."A"の値が大きすぎます(実

NVARCHAR2はサロゲートペアを2文字とカウントしてしまうので、10文字のサイズに入りきりません。一方でVARCHAR2はちゃんと1文字として扱ってくれるので、INSERT成功します。

実際、文字数を見てみると以下のとおりです。

SQL> select a, length(a), lengthb(a) from varchar_col;

A                                         LENGTH(A) LENGTHB(A)
---------------------------------------- ---------- ----------
𠮷野家                                            3         10

SQL> select a, length(a), lengthb(a) from nvarchar_col;

A                                         LENGTH(A) LENGTHB(A)
---------------------------------------- ---------- ----------
𠮷野家                                            4          8

では、サロゲートペアを含む文字が、それぞれの型で最大どれだけのサイズが指定可能なのでしょうか?

型	MAX
VARCHAR2	4000
NVARCHAR2	2000

それぞれの最大長でカラムを作ります。

SQL> CREATE TABLE nvarchar_col (a nvarchar2(2000));

表が作成されました。


SQL> CREATE TABLE varchar_col (a varchar2(4000 CHAR));

表が作成されました。

SQL*Plusからは行長制限に引っかかって、INSERT難しいので、こういうときはClojureからデータ投入すると便利です。

user=> (j/insert! oracle-db :varchar_col {:a (apply str (take 1000 (repeat "𠮷")))})
;; 成功
user=> (j/insert! oracle-db :varchar_col {:a (apply str (take 1001 (repeat "𠮷")))})

SQLException ORA-01461: LONG値はLONG列にのみバインドできます。

user=> (j/insert! oracle-db :nvarchar_col {:a (apply str (take 1000 (repeat "𠮷")))})
;; 成功
user=> (j/insert! oracle-db :nvarchar_col {:a (apply str (take 1001 (repeat "𠮷")))})

SQLException ORA-01461: LONG値はLONG列にのみバインドできます。

どちらも1000文字が境界点です。
つまりOracleでは文字列型はCharsetにかかわらず、4000バイトが格納上限だということのようです。

したがって、サロゲートペアを含む文字を格納する可能性があるときは、カラムサイズは1000を上限とする点に注意してください。

まとめ

結局のところOracleにおいては、NVARCHAR2とVARCHAR2を比較すると、NLS_CHARSET=AL32UTF8の世界においては、NVARCHAR2を積極採用する理由に乏しく、サロゲートペアの扱いの差からキャラクタ・セマンティクスのVARCHAR2にしておくのがベターと言えそうです。

そしてどちらにせよ、1000文字を越える場合は、CLOBまたはNCLOBを使うようにしましょう。