MySQL5.6 ALTER TABLE의 예

13.1.7.3 ALTER TABLE의 예

다음과 같이 작성되는 테이블 t1 에서 시작합니다.

 CREATE TABLE t1 (a INTEGER, b CHAR (10));

테이블의 이름을 t1 에서 t2 로 변경하려면 다음과 같이합니다.

 ALTER TABLE t1 RENAME t2;

컬럼 a 를 INTEGER 에서 TINYINT NOT NULL 로 변경 (이름은 동일하게 유지합니다) 또한 열 b 를 CHAR(10) 에서 CHAR(20) 으로 변경하고, 그 이름을 b 에서 c 로 변경하려면 다음과 같이합니다.

 ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE bc CHAR (20);

d 라는 새로운 TIMESTAMP 컬럼을 추가하려면 다음과 같이합니다.

 ALTER TABLE t2 ADD d TIMESTAMP;

컬럼 d 에 인덱스를 또한 컬럼 a 에 UNIQUE 인덱스를 추가하려면 다음과 같이합니다.

 ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);

컬럼 c 를 삭제하려면 다음과 같이합니다.

 ALTER TABLE t2 DROP COLUMN c;

c 라는 새로운 AUTO_INCREMENT 정수 컬럼을 추가하려면 다음과 같이합니다.

 ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
   ADD PRIMARY KEY (c);

AUTO_INCREMENT 컬럼에 인덱스를 설정해야하기 때문에 c 에 ( PRIMARY KEY 로) 인덱스를 설정하고 또한 프라이 머리 키 컬럼은 NULL 로 할 수 없기 때문에 c 를 NOT NULL 로 선언합니다.

NDB 테이블의 경우 테이블 또는 컬럼에 사용되는 저장 형식을 변경할 수 있습니다. 예를 들어, 다음과 같이 생성 된 NDB 테이블을 생각해 보겠습니다.

 mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
 Query OK, 0 rows affected (1.27 sec)

이 테이블을 디스크 기반 스토리지로 변환하려면 다음 ALTER TABLE 문을 사용할 수 있습니다.

 mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;
 Query OK, 0 rows affected (2.99 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 mysql> SHOW CREATE TABLE t1\G
 *************************** 1. row ******************** *******
        Table : t1
 Create Table : CREATE TABLE`t1` (
   `c1` int (11) DEFAULT NULL
 ) / *! 50100 TABLESPACE ts_1 STORAGE DISK * /
 ENGINE = ndbcluster DEFAULT CHARSET = latin1
 1 row in set (0.01 sec)

테이블을 처음 만들 때 테이블 스페이스가 참조되는 필요는 없지만, 테이블 스페이스는 ALTER TABLE 의해 참조 될 필요가 있습니다.

 mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;
 Query OK, 0 rows affected (1.00 sec)

 mysql> ALTER TABLE t2 STORAGE DISK;
 ERROR 1005 (HY000) : Can not create table 'c # sql-1750_3'(errno : 140)
 mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;
 Query OK, 0 rows affected (3.42 sec)
 Records : 0 Duplicates : 0 Warnings : 0
 mysql> SHOW CREATE TABLE t2\G
 *************************** 1. row ******************** *******
        Table : t1
 Create Table : CREATE TABLE`t2` (
   `c1` int (11) DEFAULT NULL
 ) / *! 50100 TABLESPACE ts_1 STORAGE DISK * /
 ENGINE = ndbcluster DEFAULT CHARSET = latin1
 1 row in set (0.01 sec)

각 컬럼의 저장 유형을 변경하려면 ALTER TABLE ... MODIFY [COLUMN] 를 사용할 수 있습니다. 예를 들어, 다음의 CREATE TABLE 문을 사용하여 2 개의 컬럼을 포함 MySQL Cluster 디스크 데이터 테이블을 만들려고합니다.

 mysql> CREATE TABLE t3 (c1 INT, c2 INT)
     -> TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
 Query OK, 0 rows affected (1.34 sec)

컬럼 c2 를 디스크 기반의 스토리지에서 인 메모리 스토리지로 변경하려면 다음과 같이 ALTER TABLE 문에서 사용되는 열 정의에 STORAGE MEMORY 절을 포함합니다.

 mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
 Query OK, 0 rows affected (3.14 sec)
 Records : 0 Duplicates : 0 Warnings : 0

같은 방법으로 STORAGE DISK 를 사용하여 인 메모리 컬럼을 디스크 기반의 컬럼 할 수 있습니다.

컬럼 c1 은 디스크 기반 스토리지를 사용합니다. 이것이 ( CREATE TABLE 문에서 테이블 수준 STORAGE DISK 절에 의해 결정되는) 테이블의 기본이기 때문입니다. 그러나 다음 SHOW CREATE TABLE 의 출력에 같이 컬럼 c2 는 인 메모리 스토리지를 사용합니다.

 mysql> SHOW CREATE TABLE t3\G
 *************************** 1. row ******************** *******
        Table : t3
 Create Table : CREATE TABLE`t3` (
   `c1` int (11) DEFAULT NULL,
   `c2` int (11) / *! 50120 STORAGE MEMORY * / DEFAULT NULL
 ) / *! 50100 TABLESPACE ts_1 STORAGE DISK * / ENGINE = ndbcluster DEFAULT CHARSET = latin1
 1 row in set (0.02 sec)

AUTO_INCREMENT 컬럼을 추가하면 컬럼 값에 자동으로 일련 번호가 입력됩니다. MyISAM 테이블의 경우 ALTER TABLE 의 전에 SET INSERT_ID= value 를 실행하거나 AUTO_INCREMENT= value 테이블 옵션을 사용하여 첫 번째 시퀀스 번호를 설정할 수 있습니다. 섹션 5.1 "서버 시스템 변수" 를 참조하십시오.

MyISAM 테이블에서 AUTO_INCREMENT 컬럼을 변경하지 않는 경우, 시퀀스 번호는 영향을받지 않습니다. AUTO_INCREMENT 컬럼을 제거하고 다른 AUTO_INCREMENT 컬럼을 추가하면 시퀀스 번호는 1부터 다시 지정됩니다.

복제가 사용되는 경우, 테이블에 AUTO_INCREMENT 컬럼을 추가하면 슬레이브와 마스터 행의 순서가 동일하지 않을 수 있습니다. 이것이 발생하는 것은 행이 번호 매기기 순서가 테이블에 사용되는 고유의 스토리지 엔진 및 행이 삽입 된 순서에 의존하기 때문입니다. 마스터와 슬레이브로 같은 순서를 가지는 것이 중요하다 경우 행을 정렬 한 AUTO_INCREMENT 번호를 할당해야합니다. 테이블 t1 에 AUTO_INCREMENT 컬럼을 추가한다고 가정하면 다음 명령문은 t1 과 동일하지만, AUTO_INCREMENT 컬럼을 포함하는 새 테이블 t2 을 생성합니다.

 CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
 SELECT * FROM t1 ORDER BY col1, col2;

여기에서는 테이블 t1 에 열 col1 과 col2 가 존재하는 것을 전제로하고 있습니다.

이 일련의 명령문은 또한 t1 과 동일하지만, AUTO_INCREMENT 컬럼이 추가 된 새 테이블 t2 도 생성됩니다.

 CREATE TABLE t2 LIKE t1;
 ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
 INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

중요

마스터와 슬레이브로 순서가 동일 함을 보증하려면 ORDER BY 절에서 t1 의 모든 컬럼을 참조해야합니다.

AUTO_INCREMENT 컬럼을 가진 복사본을 만들고 채우는 데 사용하는 방법에 관계없이 마지막 단계는 원래 테이블을 삭제하고 복사본의 이름을 변경하는 것입니다.

 DROP TABLE t1;
 ALTER TABLE t2 RENAME t1;