【MySQL】ALTER TABLEでカラムの設定を変更する際の注意

データ移行の手順書を作成している時、移行データが入ったcsvファイルをデータベースにインポートしていました。その際にALTER TABLEを使用した為今回はALTER TABLEを使う際の注意点を紹介します。

使用データベース:MySQL

1 作成したばかりのデータベースの設定がしたい
2 ALTER TABLEとは何か
3 設定したはずの内容がなくなっている
4 検証1 どういう時に起こるのか
5 検証2 実行する順番を入れ替えたらどうなるのか
6 原因
7 まとめ
8 参考サイト

作成したばかりのデータベースの設定がしたい

今回は移行前のデータベースから抽出したcsvファイルを移行先のデータベースに入るように直した上で移行しようとしていました。

しかし問題が1つあり、移行前のデータベースと新しいデータベースではカラムの数や順番が異なっています。そこで一旦、移行前のデータベースに則った仮のデータベースを作成し、そこにインポートする事にしました。

その後、仮のデータベースを新しいデータベースに則った形に作り変えた上でcsvファイルを出力し、新しいデータベースにインポートします。

その仮のデータベースの設定をする際にALTER TABLEを使い、データベースの設定を行うことにしました。

ALTER TABLEとは何か

テーブルの構造を変更する時に使われます。今回はカラムの内容の変更だけ行いますが、カラムやインデックスの追加、削除にも使うことができます。

設定したはずの内容がなくなっている

長いSQL文を書くのに自信がなかった為、1つのカラムに対しALTER TABLEを使った短いSQL文を複数回行うことで設定を行おうとしました。

しかし、SQL文の入力を終えて確認してみるとSQL文を入力する過程で既にカラムに設定されているはずの内容が、別のALTER TABLEを使ったSQL文をそのカラムに対して入力した後になくなっていたのです。

検証1 どういう時に起こるのか

ここでは、ALTER TABLEを使用したSQL文を内容を変えた上で同じカラムに対して2度行った場合を検証しています。

1.create database information default character set utf8mb4;
を実行し、データベースを作成する。

2.use information;
を実行し、先ほど製作したデータベースに接続する。

3.CREATE TABLE test(test_flg int);
を実行しテーブルを作成する。

4.test_flgがint型でデフォルト値がNULLであることを確認する。


mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `test_flg` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

5.ALTER TABLE test ALTER test_flg SET DEFAULT 3;
を実行しtest_flgのデフォルト値を3にする。

6.test_flgのデフォルト値が3になっていることを確認。

mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`test_flg` int(11) DEFAULT '3'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

7.ALTER TABLE test MODIFY test_flg int COMMENT ‘テスト’;
を実行。

8.test_flgにコメントが追加されていてデフォルト値が3ではないことを確認。

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `test_flg` int(11) DEFAULT NULL COMMENT 'テスト'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

一度行ったデフォルト値の設定がなくなっています。

ただ、SQL文を実行する順番を変えればこの症状が出ない事があった為もう1つ検証してみることにしました。

検証2 実行する順番を入れ替えたらどうなるのか

先程の条件にカラムへのコメントの追加とデフォルト値の設定の順番を入れ換えて行っています。

1.create database information2 default character set utf8mb4;
を実行し、データベースを作成する。

2.use information2;
を実行し、先ほど製作したデータベースに接続する。

3.CREATE TABLE test2(test2_flg int);
を実行しテーブルを作成する。

4.test2_flgがint型でデフォルト値がNULLであることを確認する。

mysql> show create table test2\G;
*************************** 1. row ****************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `test2_flg` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

5.ALTER TABLE test2 MODIFY test2_flg int COMMENT ‘テスト2’;
を実行し、コメントを追加する。

6.test2_flgにコメントが追加されていることを確認する。

mysql> show create table test2\G;
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `test2_flg` int(11) DEFAULT NULL COMMENT 'テスト2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

7.ALTER TABLE test2 ALTER test2_flg SET DEFAULT 4;
を実行し、デフォルト値を4にする。

8.これまでの設定した内容が消えていないことを確認。

mysql> show create table test2\G;
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `test2_flg` int(11) DEFAULT '4' COMMENT 'テスト2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

こちらではこれまでの設定を引き継いだまま設定を変更することができました。