MySQL Alter와 관련된 좋은 글이 있어서 퍼옵니다.
출처 : http://radiocom.kunsan.ac.kr/lecture/mysql/alter_table.html
ALTER TABLE 문
ALTER TABLE 문의 PROTOTYPE
ALTER TABLE 문은 이미 존재하는 테이블의 구조를 변경하는데 사용한다.
| 테이블 형식 변경 | Engine 사용 | ALTER TABLE 테이블이름 ENGINE=형식 |
| type 사용 | ALTER TABLE 테이블이름 TYPE=형식 | |
| 테이블 이름 변경 | alter에 의한 | ALTER TABLE 테이블이름 RENAME 새테이블이름 |
| rename에 의한 | RENAME TABLE 테이블이름 TO 새테이블이름 | |
| 컬럼 추가 | 마지막 컬럼에 | ALTER TABLE 테이블이름 ADD COLUMN 컬럼이름 컬럼타입 |
| 지정한 컬럼 뒤에 | ALTER TABLE 테이블이름 ADD COLUMN 새컬럼이름 컬럼타입 AFTER 컬럼이름 | |
| 첫 컬럼에 | ALTER TABLE 테이블이름 ADD COLUMN 새컬럼이름 컬럼타입 FIRST | |
| 컬럼 삭제 | ALTER TABLE 테이블이름 DROP COLUMN 컬럼이름 | |
| 컬럼 변경 | modify에 의한 | ALTER TABLE 테이블이름 MODIFY 컬럼이름 새컬럼타입 |
| change에 의한 | ALTER TABLE 테이블이름 CHANGE 컬럼이름 (새)컬럼이름 새컬럼타입 | |
| 인덱스에 새항목 추가 | ALTER TABLE 테이블이름 ADD INDEX(컬럼이름) | |
| 인덱스 삭제 | alter에 의한 | ALTER TABLE 테이블이름 DROP INDEX 컬럼이름 |
| drop에 의한 | DROP INDEX 인덱스이름 ON 테이블이름 | |
ALTER TABLE 문의 동작은 원래의 테이블을 복사해서 임시테이블을 만들어 테이블 내용을 수정하게 되며, 수정이 모두 끝난 다음에 원래의 테이블은 삭제하고 임시테이블의 이름을 원래의 테이블 이름으로 변경하게 된다.
그런데 ALTER TABLE 문이 진행하고 있을 동안에 원래 테이블이 다른 클라이언트에 의해 읽혀 질 수 있을 때, 새 테이블이 준비될 때까지 원래 테이블에 update나 write가 안 된다.
• ALTER TABLE을 사용하려면, 해당 테이블에 대한 ALTER, INSERT, CREATE 권한이 있어야 한다.
• IGNORE 옵션이 없을 경우,
새 테이블 복제에서 unique key에 에러가 발생하면 alter table 문 수행이 취소되지만, IGNORE 옵션을 사용한 경우에는 unique key의 복제에서 첫 번째 row만 사용하고 나머지 rows는 삭제된다.
• 하나의 ALTER TABLE 문에서 ADD, ALTER, DROP, CHANGE 절을 다중으로 사용할 수 있다.
• 옵션의 COLUMN은 의미 없는 문자로 빼도 좋다.
• 아무런 옵션 없이 ALTER TABLE tbl_name RENAME AS new_name처럼 사용하면 temporary 테이블을 만들지 않고 단순히 테이블 이름만 바꾼다. 이는 RENAME TABLE 문과 같다.
• create_definition 절은 CREATE TABLE 문에서와 그 쓰임새가 같지만, 여기서는 컬럼 이름은 포함하지만, 컬럼 타입은 포함하지 않는다.
• CHANGE old_name create_definition 절을 사용하여 컬럼을 rename할 수 있다. 컬럼 이름을 변경하지 않고 타입만 변경한다면, 같은 이름을 두 번 사용하면 되며, "ALTER TABLE ... MODIFY..." 형식을 사용하여 컬럼 이름과 타입을 변경할 수 있다.
컬럼 이름과 타입을 변경하는 경우:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
컬럼 이름은 변경하지 않고 타입만 변경하는 경우:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
MODIFY로 컬럼 이름과 타입을 변경하는 경우:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
• CHANGE나 MODIFY를 사용하여 컬럼의 일부분을 인덱스로 사용하고 있는 해당 컬럼의 긴 문자를 짧게 단축할 수 없다. 가령 varchar(20)을 varchar(10)으로 변경 할 수 없다.
• CHANGE나 MODIFY로 컬럼 타입을 변경할 때, 가능하다면 새 타입으로 변환한다.
• FIRST나 ADD ... AFTER col_name을 사용하여 지정한 위치에 컬럼을 추가할 수 있다. 위치를 지정하지 않고 컬럼을 추가하면 마지막 필드에 컬럼이 첨부된다. 또 다른 방법으로 CHANGE나 MODIFY 절에서 FIRST나 AFTER 키워드를 사용하여 컬럼을 추가할 수도 있다.
• ALTER COLUMN으로 컬럼에 새로운 디폴트값을 지정하거나 또는 구 디폴트값을 제거한다. 구 디폴트값을 제거만 하면 그 컬럼의 디폴트값은 NULL이 된다.
• DROP INDEX는 인덱스를 제거한다. 이는 drop index문과 같다.
• 테이블에서 컬럼을 없애면, 해당 테이블과 관계있는 인덱스에서도 컬럼이 제거된다.
• 하나의 컬럼만으로 구성된 테이블에서 해당 컬럼을 삭제할 수 없다. 컬럼을 지우면 테이블이 삭제되므로 오히려 DROP TABLE을 사용하면 된다.
• DROP PRIMARY KEY는 primary 인덱스를 제거한다. 만약 해당 인덱스가 존재하지 않으면, 테이블에 있는 첫 번째 UNIQUE 인덱스가 제거된다. MySQL에서는 PRIMARY KEY를 지정하지 않으면 첫 번째 UNIQUE를 PRIMARY KEY로 간주하기 때문이다.
• ORDER BY는 주어진 순서에 따른 ROWS를 가진 새 테이블을 만든다. insert 나 delete 후 이 순서가 테이블에 남아 있지 않게 된다.
• MyISAM 테이블에서 ALTER TABLE을 사용하면, 모든 non-unique 인덱스는 각각의 batch에(예:REPAIR) 만들어진다. 이는 많은 인덱스를 가지고 있을 경우 ALTER TABLE을 빠르게 한다.
• MyISAM 테이블에서 ALTER TABLE ... DISABLE KEYS는 non-unique 인덱스의 update를 못하게 한다. 반면에, ALTER TABLE ... ENABLE KEYS는 분실된 인덱스를 다시 만든다.
• mysql_info() 함수를 사용하여 몇 개의 레코드가 복사되었는지 알 수 있고, IGNORE를 지정한 경우에는 unique key 때문에 복제에서 몇 개의 레코드가 삭제(겹쳐 씀)되었는지 알 수 있다.
• InnoDB형 테이블을 제외하고 FOREIGN KEY, CHECK, REFERENCES절은 실제로 아무 일도 하지 않는다. InnoDB형 테이블은 ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...)을 지원한다.
InnoDB에서는 index_name을 지정하지 못한다. ALTER TABLE 문의 사용 예를 살펴보기로 하자, 여기서 테이블 t1은 다음과 같이 만들어져 있다고 한다.
mysql> CREATE TABLE t1 (a INTEGER, b CHAR(10));
1) 테이블 이름 t1을 t2로 변경하는 경우:
mysql> ALTER TABLE t1 RENAME t2;
2) 컬럼 a는 INTEGER에서 TINYINT NOT NULL로, 컬럼 b는 CHAR(10)에서
CHAR(20)으로, 컬럼이름 b를 c로 변경하는 경우:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
3) TIMESTAMP를 갖는 새 컬럼 d를 추가하는 경우:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
4) 컬럼 d를 인덱스에 추가하고, 컬럼 a를 primary 키로 지정하는 경우:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
5) 컬럼 c를 제거하는 경우:
mysql> ALTER TABLE t2 DROP COLUMN c;
6) AUTO_INCREMENT 정수를 갖는 새 컬럼 c를 추가하는 경우:
mysql> ALTER TABLE t2 ADD C INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD INDEX (c);
여기서 AUTO_INCREMENT 컬럼인 c가 인덱스 되었고, NOT NULL이므로 인덱스된 컬럼 c은 NULL이 될 수 없다.
AUTO_INCREMENT로 선언된 컬럼이 추가되었기 때문에 해당 컬럼값은 연속된 숫자로 자동으로 채워져야 된다. 그러기 위해서는 ALTER TABLE문을 실행하기 전에 SET INSERT_ID=# 또는 AUTO_INCREMENT=# 테이블 옵션을 사용하여야 연속 숫자의 시작 숫자를 지정하게 된다.
MyISAM 테이블에서, AUTO_INCREMENT 컬럼을 변경하지 않으면, 연속된 숫자도 영향을 받지 않는다. AUTO_INCREMENT 컬럼이 삭제되고 나서 다른 AUTO_INCREMENT 컬럼이 추가되면, 숫자는 1부터 시작한다.
【예제】 mysql> show table status; ☜ 테이블의 종류를 확인 +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | bb | MyISAM | 9 | Dynamic | 4 | 24 | 96 | 4294967295 | 2048 | 0 | NULL | 2005-05-30 13:54:53 | 2005-05-30 13:54:53 | NULL | euckr_korean_ci | NULL | | | | test | MyISAM | 9 | Dynamic | 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2005-05-31 15:28:27 | 2005-05-31 15:28:27 | NULL | euckr_korean_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 2 rows in set (0.00 sec) mysql> alter table bb type=innodb; ☜ 테이블 bb의 타입을 innidb로 변경 Query OK, 4 rows affected, 1 warning (0.12 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show table status; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+----------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+----------------------+ | bb | InnoDB | 9 | Dynamic | 4 | 4096 | 16384 | NULL | 0 | 0 | NULL | 2005-05-31 15:30:35 | NULL | NULL | euckr_korean_ci | NULL | | InnoDB free: 4096 kB | | test | MyISAM | 9 | Dynamic | 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2005-05-31 15:28:27 | 2005-05-31 15:28:27 | NULL | euckr_korean_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+----------------------+ 2 rows in set (0.00 sec) mysql> alter table bb type=myisam; Query OK, 4 rows affected, 1 warning (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show table status; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | bb | MyISAM | 9 | Dynamic | 4 | 24 | 96 | 4294967295 | 2048 | 0 | NULL | 2005-05-31 15:31:03 | 2005-05-31 15:31:03 | NULL | euckr_korean_ci | NULL | | | | test | MyISAM | 9 | Dynamic | 0 | 0 | 0 | 4294967295 | 1024 | 0 | NULL | 2005-05-31 15:28:27 | 2005-05-31 15:28:27 | NULL | euckr_korean_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 2 rows in set (0.00 sec) mysql> alter table bb rename as aa; ☜ 테이블 bb를 aa로 이름을 변경 Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_jijoeDB | +-------------------+ | aa | | test | +-------------------+ 2 rows in set (0.00 sec) mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table aa add column addr varchar(200); ☜ addr 필드를 끝에 추가 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table aa add column phone varchar(20) after name; ☜ phone 필드를 name 필드 다음에 추가 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table aa add column no int(5) first; ☜ no 필드를 첫 필드로 삽입 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | no | int(5) | YES | | NULL | | | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table aa drop column no; ☜ no 필드를 삭제 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table aa modify id int(5); ☜ id 필드를 int(5)로 변경 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(5) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table aa change id id int(8); ☜ id 필드를 int(8)로 변경 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int(8) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table aa change id no int(5); ☜ id 필드를 no 필드로 이름을 변경하면서 int(5)로 바꿈 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc aa; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | no | int(5) | | PRI | 0 | | | e_mail | varchar(200) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | phone | varchar(20) | YES | | NULL | | | addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> show index from aa; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | aa | 0 | PRIMARY | 1 | no | A | 4 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) mysql> alter table aa add index(name); ☜ 테이블 aa의 인덱스로 name을 추가 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show index from aa; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | aa | 0 | PRIMARY | 1 | no | A | 4 | NULL | NULL | | BTREE | | | aa | 1 | name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) mysql> alter table aa drop index name; ☜ 테이블 aa의 인덱스에서 name을 삭제 Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show index from aa; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | aa | 0 | PRIMARY | 1 | no | A | 4 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) mysql>