Help us understand the problem. What is going on with this article?

Oracle DatabaseでのUnicode設計

More than 3 years have passed since last update.

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

kawasima
Clojure関連のことをブログがわりに書き綴ります。 ※ここでの発言はシステムエンジニアを代表するものであって、所属する組織は二の次です。
https://github.com/kawasima/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした