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