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オブジェクトへの参照権限)

```sql
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には[長さセマンティクスという概念](http://otndnld.oracle.co.jp/document/products/timesten/html/E05167-01/Operations%20Guide-07-05.htm)があり、カラムサイズがバイト数か文字数かを選択できます(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
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
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
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
SQL> CREATE TABLE nvarchar_col (a nvarchar2(2000));

表が作成されました。


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

表が作成されました。
```

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

```clj
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を使うようにしましょう。