DBの文字コードで大いに悩んだ話

こんにちは。船井総合研究所のいっちーです。

前提

私個人の好みというか慣れの問題で、DB設計をする際はMySQLを基準に考えることが多いです。

DB設計

とある案件で、SQLServerを使ってDB設計をすることになりました。バックエンドをNestJSで実装することになったので、ORMはPrismaを使って実装していこう…ということになりました。主キーはUUIDを用いたサロゲートキーなので固定長で、それ以外の文字列は要件から適切と思われる長さのものを可変長で設計します。

論理名	物理名	型・桁	その他色々
ID	id	CHAR(36)	UUID
名前	name	VARCHAR(128)	-
郵便番号	zip	VARCHAR(8)	-
住所	address	VARCHAR(256)	-
メールアドレス	email	VARCHAR(256)	メールアドレスフォーマット
...	...	...	...

こんな感じで項目定義を作成して、Prismaのモデルに落とし込みます。

/// ユーザー
model Users {
  /// ID
  id String @id @db.Char(36) @default(dbgenerated("newid()")) @map("id")
  /// 名前
  name String @db.VarChar(128) @map("name")
  /// 郵便番号
  zip String @unique @db.VarChar(8) @map("zip")
  /// 住所
  address String @db.VarChar(256) @map("address")
  /// メールアドレス
  email String @db.VarChar(256) @map("email")
  @@map("users")
}

実際にはもっと事細かに設計して、Prismaコードも設計書から自動生成できるコンバーターを作成しています。
サンプルに書かれていない部分は適宜脳内補完してください

あとはPrismaのマイグレーションで取り込めば、テーブルが作成されます。データ投入をしてDBを見てみましょう。

あれれ？

nameが文字化けしてる？？？

原因

2バイト文字が入る前提の項目をVARCHARで定義してしまったので、ここに2バイト文字を入れてもSQLServerがうまく処理できずに文字化けが起きていたようです。MySQLではDBの設定で文字コードを設定し、カラム定義ではVARCHARで定義すれば問題なく2バイト文字を扱えるので、これをゴッチャにしてしまったのが文字化けの原因でした。まとめると、つまりこういうことです。

-	SQLServer	MySQL
文字コード決定の粒度	カラム型レベル	DB/テーブル/カラムレベル
Unicodeの定義	型として明示	文字セットとして明示
VARCHARの役割	非Unicodeデータ専用。日本語は格納不可(文字化けの原因)。	設定されたCHARACTER SETに従う(utf8mb4ならUnicode可)。
NVARCHARの役割	Unicodeサポートの必須型。データ長が2倍になる。	内部的にはVARCHARのエイリアスとして処理される。
あるべき設計	カラム型でUnicodeサポート(NVARCHAR)を強制する。	DB/テーブル設定でutf8mb4を強制する。

原因が分かれば対処は簡単。UUIDのような、「非マルチバイトかつ固定長前提」の項目をCHAR(N)で、それ以外をすべてNVARCHAR(N)で定義し直し、Prismaコードも下記のように直しました。

/// ユーザー
model Users {
  /// ID
  id String @id @db.Char(36) @default(dbgenerated("newid()")) @map("id")
  /// 名前
-  name String @db.VarChar(128) @map("name")
+  name String @db.NVarChar(128) @map("name")
  /// 郵便番号
-  zip String @unique @db.VarChar(8) @map("zip")
+  zip String @unique @db.NVarChar(8) @map("zip")
  /// 住所
-  address String @db.VarChar(256) @map("address")
+  address String @db.NVarChar(256) @map("address")
  /// メールアドレス
-  email String @db.VarChar(256) @map("email")
+  email String @db.NVarChar(256) @map("email")
  @@map("users")
}

データを登録し直して…

うまくいきました。

まとめ

文字コードは色々と面倒が多い…というお話でした。

それではまた。