くりーむわーかー

プログラムとか。作ってて ・試しててハマった事など。誰かのお役に立てば幸いかと。 その他、いろいろエトセトラ。。。

サロゲ

MySQL(MariaDB)とPostgreSQLで文字コード指定で文字を作る(外字とか)

なんか久しぶりに書く気がする。

タイトル通り。サロゲとか外字の文字を文字コード指定でDBに入れたい時用。

MySQL(MariaDB)

insert testchar values ('外字',CHAR(0xEE8080 USING utf8mb4));
insert testchar values ('サロゲ',CHAR(0xF0A08083 USING utf8mb4));

サロゲを表現する場合、普通にIMEとかで見ると、「0xD840 , 0xDC03」って見えるけどこれを合わせた形式にする。

SELECT HEX(CONVERT(CHAR(0xD840DC03 USING utf16) USING utf8mb4));

上で出てきたコードをchar()に指定すればサロゲ文字が出る。あと、エンコード指定でutf8mb4を指定しないとダメ。サロゲ対応用の文字コードらし。

PostgreSQL

insert into testchar values('外字',chr('xE000' :: bit(16) :: int));
insert into testchar values('サロゲ','𠀃');--文字コード指定がわからん。

サロゲの文字コード指定が分かりませんでした。ごめんなさい。今度調べよ。

SQLServer 照合順序の変更

最近、DBの照合順序を変更する必要があった。SQLServer2012。一部のテーブルのみとか、部分的に照合順序を変える事は正直あまり考えられないので、インスタンス丸ごと変える。

で、色々見てるとだいたい↓の感じで書いてある。

ユーザー データベースに作成する新しいオブジェクトの照合順序は、 ALTER DATABASE ステートメントの COLLATE 句を使用して変更できます。 このステートメントを実行しても、既存のユーザー定義テーブルの列の照合順序は変わりません。 ALTER TABLEの COLLATE 句で変更することができます。

とか。⇒microsoftのdoc

master、model、msdb、および tempdb の各システム データベースを再構築する場合は、元の場所からデータベースを削除して再作成する必要があります。 再構築ステートメントに新しい照合順序を指定する場合は、その照合順序の設定でシステム データベースが作成されます。 これらのデータベースに対するユーザーの変更はすべて失われます。 たとえば、master データベースのユーザー定義オブジェクト、msdb にスケジュールされたジョブ、または model データベースの既定のデータベース設定に対する変更が対象になります。

とかとか。⇒TechNet

ユーザーDBは変えれなくはないけど、DBの照合順序変えても既存は変更されない。あと、システムデータベース(tempとか)はユーザDBとか存在してると変えられない。作り直しの必要あり。。。

それ何て再インストール?

正直、ユーザDBのインスタンス変えても、一時テーブルとか使うとtempテーブルにデータが出来るから、そっちと照合順序違くなって、Joinとか落ちるので、全DB変更する以外の事はあまし考えられない。。。

ググると色々出てくるんだけど、ぶっちゃけインスタンス再インストール(or別で作る)以外に無難な方法がありませんね。

あと、見てるとDBバックアップして変更後に復元するとか書いてあるサイトもあるけど、それやると、照合順序復元されてまるで意味なし。

ストアドとかの戻りとかはDBの照合順序変えれば適用されるらしいけど、テーブルの定義は変わらないようす。ついでにDBの照合順序は新しく作成されるテーブルに適用されるものみたいなので、既存は何も変わらない。

DBの照合順序変えてもsp_helpでテーブル見れば定義変わってないのが確認できる。

とゆーことで、以下の感じで全部作り直した。

  1. 既存をバックアップするか、リンクサーバ出来るようにしておく
  2. インスタンスを新しく作る(照合順序はインストール時に指定する)
  3. リンクサーバかバックアップを別名で復元
  4. 元のDBと同じものをCreateする
  5. 元のDBの全オブジェクトをスクリプト化しておいて、新しい方に全部作成
  6. 元DB復元したものかリンクサーバで元データを新しい方に入れ込む
めんどくさし。でもこれしか綺麗にいく方法が思いつかん。

ただ、↓のコマンドは通ったから、全列に対し実行するスクリプト作ればいけるかな?

alter table [テーブル名] alter column [列名] nvarchar(200) collate Japanese_XJIS_100_CI_AS_SC

見かけ上は上でも上手くいってるんだけど、本当にうまくいってるのか自信がありません。どうやって確認すればいいんだろうか。。。

ちなみに変更するのはサロゲートペア文字への対応用です。プログラム的な話はあれですが、SQLでいくと普通の照合順序使ってると、len()とかおかしくなる。サロゲの文字の場合はlen()が2でかえってくるので。なのでrightとかsubstringとか全部おかしくなる。ゆえに照合順序を変えて、サロゲでもlen()で1って帰ってきてくれるようにしないと色々あれなのです。

照合順序的には「japanese*****SC」みたいに「SC」つく照合順序が補助文字があーだこーだでサロゲ文字入ってきても上手くいく照合順序の様子。

あー、いちを個別のクエリ上でcollate句で照合順序指定すればテーブルとかDBの照合順序が違くても上手くいく。でも、んな事をやり続ける自信ありませんヨネ。。。

問合せ