$shibayu36->blog;

クラスター株式会社のソフトウェアエンジニアです。エンジニアリングや読書などについて書いています。

MySQLのALTER TABLEでTEXT NOT NULLなカラムをエラー無しで追加する

tech

課題

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.6 データ型デフォルト値によると、

  • MySQLのTEXTカラムにはデフォルト値を設定できない
  • 厳密な SQL モードを有効にした場合、NOT NULLなカラムがINSERTに含まれていないとエラーが発生する

そのため、以下のようなスキーマが存在していて

CREATE TABLE `article` (
  `id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(100) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

アプリケーションから次のようにINSERT文を発行しているとして

INSERT INTO `article`
  SET id = :id,
      title = :title
  • 先にALTER TABLE article ADD COLUMN body TEXT NOT NULLすると、INSERT文がエラーになってしまう
  • 先にアプリケーションをデプロイして、INSERTのSETにbodyを追加しても、存在しないカラムへのセットになってしまい、INSERT文がエラーになってしまう

という風に、ALTER TABLEが先でもアプリケーションデプロイが先でもエラーが発生してしまう。

解決策

先にDEFAULT句付きのVARCHARなカラムを追加し、デプロイしてINSERT句を直した後、TEXTカラムに変更すると良い。

  • ALTER TABLE article ADD body VARCHAR(100) NOT NULL DEFAULT '';
  • アプリケーションをデプロイして、SETにbodyを追加する
  • ALTER TABLE article MODIFY body TEXT NOT NULL;

こうするとエラーを起こさずにALTER TABLEが成功した。

補足: 全部VARCHARでいいんじゃないか問題

varcharとtextの違い(mysql innodb) - lxyuma BLOGのように、全部VARCHARで良いんじゃないかという話題がある。しかし、https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html によると、

  • すべてのテーブル (ストレージエンジンには無関係) の最大行サイズは 65,535 バイトです
  • すべてのカラムの合計長は最大行サイズを超えられないので、このサイズはカラム数 (およびサイズの可能性もあり) を制約します
  • BLOB カラムと TEXT カラムは、その内容が行の残りとは別に格納されるので、行サイズに対してそれぞれ 9 から 12 (1から4+8) バイトになります。

ということで、VARCHARで長い文字列長を使うと一気に行サイズを使い果たして死にそう。