OracleとMySQLのalter table column動作の違い(データがある場合)
OracleとMySQLを使用しているとカラム変更の動作がいろいろと違ってくるので、
どっちがどっちかわからなくなることか多いのでまとめ。
OracleDatabaseSE(11.2.0.4)とMySQL5.6.29(INNODB)で調査。
1. 実行可能なものは(可)
2. 実行が即時で終了するものは(即時)
3. 実行不可のものは(不可)
両DB共に以下のようなテーブルを用意した。
>desc test 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) ID2 NUMBER(38) TX VARCHAR2(50) > select count(*) from test; +----------+ | count(*) | +----------+ | 1600000 | +----------+ 1 row in set (0.63 sec)
ADD COLUMNS
制約なしのカラム追加
Oracle:即時 MySQL:可
# Oracle ALTER TABLE test ADD id3 int; 経過: 00:00:00.00 # MySQL ALTER TABLE test ADD id3 int; Query OK, 0 rows affected (12.50 sec)
デフォルト句のカラム追加
Oracle:可 MySQL:可
# Oracle ALTER TABLE test ADD id3 int default '3'; 経過: 00:00:29.65 # MySQL ALTER TABLE test ADD id3 int default '3'; Query OK, 0 rows affected (19.67 sec)
デフォルト句+NOTNULL制約のカラム追加
Oracle:即時 MySQL:可
# Oracle ALTER TABLE test ADD id3 int not null default '3'; 経過: 00:00:00.04 # MySQL ALTER TABLE test ADD id3 int not null default '3'; Query OK, 0 rows affected (16.56 sec)
MODIFY COLUMNS
カラムのデータ型変更
Oracle:不可 MySQL:可
# Oracle ALTER TABLE test MODIFY id2 varchar2(100); 行1でエラーが発生しました。: ORA-01439: データ型を変更するには、修正する列を空にする必要があります # MySQL ALTER TABLE test MODIFY id2 varchar(100); Query OK, 1600000 rows affected (17.75 sec)
カラムデータサイズ拡大
Oracle:即時 MySQL:可
# Oracle ALTER TABLE test MODIFY id2 varchar2(1000); 経過: 00:00:00.03 # MySQL ALTER TABLE test MODIFY id2 varchar(1000); Query OK, 1600000 rows affected (20.48 sec)
カラムデータサイズ縮小
Oracle:即時また不可 MySQL:可
Oracleの場合は数値の縮小は対象の列を空にしなくてはいけない。
# Oracle ALTER TABLE test MODIFY id2 varchar2(50); 経過: 00:00:00.23 ALTER TABLE test MODIFY numbercol number(7); ORA-01440: 精度またはスケールを下げるには、修正する列を空にする必要があります # MySQL ALTER TABLE test MODIFY id2 varchar(50); Query OK, 1600000 rows affected (22.79 sec)
カラムデータサイズ縮小:サイズオーバーする値がある場合
Oracle:不可 MySQL:可
MySQLの場合、varchar(1)
に指定すると先頭の1文字以降切り捨てる。
# Oracle ALTER TABLE test MODIFY id2 varchar2(1); ORA-01441: 大きすぎる値があるため、列の長さを減らせません。 経過: 00:00:00.17 # MySQL ALTER TABLE test MODIFY id2 varchar(1); Query OK, 1600000 rows affected, 65535 warnings (19.60 sec) Warning (Code 1265): Data truncated for column 'id2' at row 1 Warning (Code 1265): Data truncated for column 'id2' at row 2
RENAME COLUMNS
カラム名の変更
Oracle:即時 MySQL:即時
OracleもMySQLも即時で完了する。 ただし、MySQLは指定するカラムのデータ型とデータサイズを同じにした場合。
# Oracle ALTER TABLE test RENAME COLUMN id2 TO xid2; 経過: 00:00:00.02 # MySQL ALTER TABLE test CHANGE COLUMN id2 xid2 varchar(1); Query OK, 0 rows affected (0.06 sec)
DROP COLUMNS
データありのカラム削除
Oracle:可 MySQL:可
# Oracle ALTER TABLE test DROP COLUMN xid2; 経過: 00:00:16.90 # MySQL ALTER TABLE test DROP COLUMN xid2; Query OK, 0 rows affected (19.96 sec)
データなしのカラム削除
Oracle:可(早い) MySQL:可
# Oracle ALTER TABLE test DROP COLUMN nullcol; 経過: 00:00:03.29 # MySQL ALTER TABLE test DROP COLUMN nullcol; Query OK, 0 rows affected (19.51 sec)