kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

OracleとMySQLのalter table column動作の違い(データがある場合)

OracleMySQLを使用しているとカラム変更の動作がいろいろと違ってくるので、
どっちがどっちかわからなくなることか多いのでまとめ。
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:即時

OracleMySQLも即時で完了する。 ただし、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)
  • 結論
    Oracleは最適化されているものが多く、制限もある。
    MySQLはとりあえずなんでもできる!!