15.1.11 ALTER TABLE Statement

15.1.11 ALTER TABLE Statement

15.1.11.1 ALTER TABLE Partition Operations 15.1.11.2 ALTER TABLE and Generated Columns 15.1.11.3 ALTER TABLE Examples

1ALTER TABLE tbl_name
2    [alter_option [, alter_option] ...]
3    [partition_options]
4
5alter_option: {
6    table_options
7  | ADD [COLUMN] col_name column_definition
8        [FIRST | AFTER col_name]
9  | ADD [COLUMN] (col_name column_definition,...)
10  | ADD {INDEX | KEY} [index_name]
11        [index_type] (key_part,...) [index_option] ...
12  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
13        (key_part,...) [index_option] ...
14  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
15        [index_type] (key_part,...)
16        [index_option] ...
17  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
18        [index_name] [index_type] (key_part,...)
19        [index_option] ...
20  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
21        [index_name] (col_name,...)
22        reference_definition
23  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
24  | DROP {CHECK | CONSTRAINT} symbol
25  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
26  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
27  | ALTER [COLUMN] col_name {
28        SET DEFAULT {literal | (expr)}
29      | SET {VISIBLE | INVISIBLE}
30      | DROP DEFAULT
31    }
32  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
33  | CHANGE [COLUMN] old_col_name new_col_name column_definition
34        [FIRST | AFTER col_name]
35  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
36  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
37  | {DISABLE | ENABLE} KEYS
38  | {DISCARD | IMPORT} TABLESPACE
39  | DROP [COLUMN] col_name
40  | DROP {INDEX | KEY} index_name
41  | DROP PRIMARY KEY
42  | DROP FOREIGN KEY fk_symbol
43  | FORCE
44  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
45  | MODIFY [COLUMN] col_name column_definition
46        [FIRST | AFTER col_name]
47  | ORDER BY col_name [, col_name] ...
48  | RENAME COLUMN old_col_name TO new_col_name
49  | RENAME {INDEX | KEY} old_index_name TO new_index_name
50  | RENAME [TO | AS] new_tbl_name
51  | {WITHOUT | WITH} VALIDATION
52}
53
54partition_options:
55    partition_option [partition_option] ...
56
57partition_option: {
58    ADD PARTITION (partition_definition)
59  | DROP PARTITION partition_names
60  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
61  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
62  | TRUNCATE PARTITION {partition_names | ALL}
63  | COALESCE PARTITION number
64  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
65  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
66  | ANALYZE PARTITION {partition_names | ALL}
67  | CHECK PARTITION {partition_names | ALL}
68  | OPTIMIZE PARTITION {partition_names | ALL}
69  | REBUILD PARTITION {partition_names | ALL}
70  | REPAIR PARTITION {partition_names | ALL}
71  | REMOVE PARTITIONING
72}
73
74key_part: {col_name [(length)] | (expr)} [ASC | DESC]
75
76index_type:
77    USING {BTREE | HASH}
78
79index_option: {
80    KEY_BLOCK_SIZE [=] value
81  | index_type
82  | WITH PARSER parser_name
83  | COMMENT 'string'
84  | {VISIBLE | INVISIBLE}
85}
86
87table_options:
88    table_option [[,] table_option] ...
89
90table_option: {
91    AUTOEXTEND_SIZE [=] value
92  | AUTO_INCREMENT [=] value
93  | AVG_ROW_LENGTH [=] value
94  | [DEFAULT] CHARACTER SET [=] charset_name
95  | CHECKSUM [=] {0 | 1}
96  | [DEFAULT] COLLATE [=] collation_name
97  | COMMENT [=] 'string'
98  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
99  | CONNECTION [=] 'connect_string'
100  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
101  | DELAY_KEY_WRITE [=] {0 | 1}
102  | ENCRYPTION [=] {'Y' | 'N'}
103  | ENGINE [=] engine_name
104  | ENGINE_ATTRIBUTE [=] 'string'
105  | INSERT_METHOD [=] { NO | FIRST | LAST }
106  | KEY_BLOCK_SIZE [=] value
107  | MAX_ROWS [=] value
108  | MIN_ROWS [=] value
109  | PACK_KEYS [=] {0 | 1 | DEFAULT}
110  | PASSWORD [=] 'string'
111  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
112  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
113  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
114  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
115  | STATS_SAMPLE_PAGES [=] value
116  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
117  | UNION [=] (tbl_name[,tbl_name]...)
118}
119
120partition_options:
121    (see CREATE TABLE options)

ALTER TABLE 은 테이블의 구조를 변경합니다. 예를 들어, 컬럼을 추가하거나 삭제하고, 인덱스를 생성하거나 제거하고, 기존 컬럼의 타입을 변경하거나, 컬럼 또는 테이블 자체의 이름을 바꿀 수 있습니다. 또한 테이블에 사용되는 스토리지 엔진이나 테이블 코멘트와 같은 특성을 변경할 수도 있습니다.

• ALTER TABLE 을 사용하려면 해당 테이블에 대해 ALTER, CREATE, INSERT 권한이 필요합니다. 테이블 이름을 변경하려면, 기존 테이블에 대해 ALTER 과 DROP, 새 테이블에 대해 ALTER, CREATE, INSERT 권한이 필요합니다.

• 테이블 이름 뒤에, 수행할 변경 사항을 지정합니다. 아무 것도 지정하지 않으면 ALTER TABLE 은 아무 작업도 수행하지 않습니다.

• 허용되는 많은 변경에 대한 구문은 CREATE TABLE 문장의 절과 유사합니다. column_definition 절은 ADD 와 CHANGE 에 대해 CREATE TABLE 에서와 동일한 구문을 사용합니다. 자세한 내용은 Section 15.1.24, “CREATE TABLE Statement” 을 참조하십시오.

• COLUMN 이라는 단어는 선택 사항이며 RENAME COLUMN 을 제외하고 생략할 수 있습니다(RENAME 테이블 이름 변경 연산과 컬럼 이름 변경 연산을 구분하기 위해 필요).

• 하나의 ALTER TABLE 문 안에서 여러 개의 ADD, ALTER, DROP, CHANGE 절을 쉼표로 구분하여 사용할 수 있습니다. 이는 표준 SQL에 대한 MySQL 확장으로, 표준 SQL에서는 하나의 ALTER TABLE 문당 각 절을 한 번만 허용합니다. 예를 들어 하나의 문장에서 여러 컬럼을 드롭하려면 다음과 같이 합니다:

1ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

• 특정 스토리지 엔진이 시도한 ALTER TABLE 연산을 지원하지 않으면 경고가 발생할 수 있습니다. 이러한 경고는 SHOW WARNINGS 로 확인할 수 있습니다. Section 15.7.7.43, “SHOW WARNINGS Statement” 을 참조하십시오. ALTER TABLE 관련 문제 해결에 대한 정보는 Section B.3.6.1, “Problems with ALTER TABLE” 을 참조하십시오.

• 생성된 컬럼에 대한 정보는 Section 15.1.11.2, “ALTER TABLE and Generated Columns” 을 참조하십시오.

• 사용 예는 Section 15.1.11.3, “ALTER TABLE Examples” 을 참조하십시오.

• InnoDB 는 JSON 컬럼에 대한 멀티값 인덱스 추가를 지원하며, 이를 위해 key_part 지정은 (CAST json_path AS type ARRAY) 형식을 사용할 수 있습니다. 멀티값 인덱스 생성 및 사용과 관련한 상세 정보와 제한 사항은 Multi-Valued Indexes 를 참조하십시오.

• C API 함수 mysql_info() 를 사용하면 ALTER TABLE 에 의해 복사된 행 수를 알 수 있습니다. mysql_info() 를 참조하십시오.

이 섹션에서는 ALTER TABLE 문과 관련된 몇 가지 추가 측면을 다음 토픽에서 설명합니다:

• Table Options

• Performance and Space Requirements

• Concurrency Control

• Adding and Dropping Columns

• Renaming, Redefining, and Reordering Columns

• Primary Keys and Indexes

• Foreign Keys and Other Constraints

• Changing the Character Set

• Importing InnoDB Tables

• Row Order for MyISAM Tables

• Partitioning Options

Table Options

table_options 는 CREATE TABLE 문에서 사용할 수 있는 ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, MAX_ROWS, ROW_FORMAT, TABLESPACE 와 같은 테이블 옵션을 의미합니다.

모든 테이블 옵션에 대한 설명은 Section 15.1.24, “CREATE TABLE Statement” 을 참조하십시오. 단, ALTER TABLE 은 테이블 옵션으로 제공된 DATA DIRECTORY 와 INDEX DIRECTORY 를 무시합니다. ALTER TABLE 은 이러한 옵션을 파티셔닝 옵션으로만 허용하며, FILE 권한이 필요합니다.

ALTER TABLE 과 함께 테이블 옵션을 사용하면 개별 테이블 특성을 편리하게 변경할 수 있습니다. 예:

• t1 이 현재 InnoDB 테이블이 아니라면, 다음 문장은 스토리지 엔진을 InnoDB 로 변경합니다:

1ALTER TABLE t1 ENGINE = InnoDB;

• 테이블을 InnoDB 스토리지 엔진으로 전환할 때 고려 사항은 Section 17.6.1.5, “Converting Tables from MyISAM to InnoDB” 를 참조하십시오.

• ENGINE 절을 지정하면, ALTER TABLE 은 테이블을 리빌드합니다. 이는 테이블이 이미 지정한 스토리지 엔진을 사용하는 경우에도 마찬가지입니다.

• 기존 InnoDB 테이블에 대해 ALTER TABLE tbl_name ENGINE=INNODB 를 실행하면 “null” ALTER TABLE 연산이 수행되며, 이는 Section 17.11.4, “Defragmenting a Table” 에 설명된 대로 InnoDB 테이블을 디프래그먼트하는 데 사용할 수 있습니다. ALTER TABLE tbl_name FORCE 를 InnoDB 테이블에 대해 실행하면 동일한 기능을 수행합니다.

• ALTER TABLE tbl_name ENGINE=INNODB 와 ALTER TABLE tbl_name FORCE 는 online DDL 을 사용합니다. 자세한 내용은 Section 17.12, “InnoDB and Online DDL” 을 참조하십시오.

• 테이블 스토리지 엔진을 변경하려는 시도의 결과는 대상 스토리지 엔진의 사용 가능 여부와 NO_ENGINE_SUBSTITUTION SQL 모드 설정에 따라 달라지며, 이에 대해서는 Section 7.1.11, “Server SQL Modes” 에 설명되어 있습니다.

• 데이터의 우발적인 손실을 방지하기 위해, ALTER TABLE 은 테이블의 스토리지 엔진을 MERGE 또는 BLACKHOLE 로 변경하는 데 사용할 수 없습니다.

• InnoDB 테이블이 압축된 행 저장 형식을 사용하도록 변경하려면:

1ALTER TABLE t1 ROW_FORMAT = COMPRESSED;

• ENCRYPTION 절은 InnoDB 테이블에 대해 페이지 레벨 데이터 암호화를 활성화하거나 비활성화합니다. 암호화를 활성화하려면 키링 플러그인이 설치 및 설정되어 있어야 합니다.

table_encryption_privilege_check](https://dev.mysql.com/doc/refman/9.5/en/server-system-variables.html#sysvar_table_encryption_privilege_check) 변수가 활성화된 경우, 스키마 기본 암호화 설정과 다른 값을 갖는 ENCRYPTION 절을 사용하려면 TABLE_ENCRYPTION_ADMIN 권한이 필요합니다.

ENCRYPTION 은 제네럴 테이블스페이스에 있는 테이블에도 지원됩니다.

제네럴 테이블스페이스에 있는 테이블의 경우, 테이블과 테이블스페이스 암호화 설정은 일치해야 합니다.

ENCRYPTION 옵션은 InnoDB 스토리지 엔진에서만 지원됩니다. 따라서 테이블이 이미 InnoDB 를 사용하고 있고(그리고 테이블의 스토리지 엔진을 변경하지 않거나), ALTER TABLE 문이 ENGINE=InnoDB 도 함께 지정한 경우에만 작동합니다. 그렇지 않으면 문은 ER_CHECK_NOT_IMPLEMENTED 에러와 함께 거부됩니다.

테이블을 다른 테이블스페이스로 이동하거나 스토리지 엔진을 변경하여 테이블 암호화를 변경하는 것은, ENCRYPTION 절을 명시적으로 지정하지 않으면 허용되지 않습니다.

테이블이 암호화를 지원하지 않는 스토리지 엔진을 사용하는 경우에는 'N' 또는 '' 이 아닌 값을 갖는 ENCRYPTION 절을 지정할 수 없습니다. 또한 암호화가 활성화된 스키마에서 암호화를 지원하지 않는 스토리지 엔진을 사용하면서 ENCRYPTION 절 없이 테이블을 생성하는 것 역시 허용되지 않습니다.

자세한 내용은 Section 17.13, “InnoDB Data-at-Rest Encryption” 을 참조하십시오.

• 현재 auto-increment 값을 리셋하려면:

1ALTER TABLE t1 AUTO_INCREMENT = 13;

현재 사용 중인 값 이하로 카운터를 리셋할 수는 없습니다. InnoDB 와 MyISAM 모두에 대해, 지정한 값이 AUTO_INCREMENT 컬럼에 현재 저장된 최대 값보다 작거나 같으면, 해당 값은 현재 최대 AUTO_INCREMENT 컬럼 값에 1을 더한 값으로 리셋됩니다.

• 기본 테이블 캐릭터 세트를 변경하려면:

1ALTER TABLE t1 CHARACTER SET = utf8mb4;

Changing the Character Set 도 참조하십시오.

• 테이블 코멘트를 추가(또는 변경)하려면:

1ALTER TABLE t1 COMMENT = 'New table comment';

• TABLESPACE 옵션과 함께 ALTER TABLE 을 사용하여 InnoDB 테이블을 기존 general tablespace, file-per-table 테이블스페이스 및 system tablespace 사이에서 이동할 수 있습니다. Moving Tables Between Tablespaces Using ALTER TABLE 을 참조하십시오.

  • ALTER TABLE ... TABLESPACE 연산은, TABLESPACE 속성이 이전 값과 동일하더라도 항상 전체 테이블 리빌드를 수행합니다.

  • ALTER TABLE ... TABLESPACE 구문은 테이블을 임시 테이블스페이스에서 영구 테이블스페이스로 이동하는 것을 지원하지 않습니다.

  • CREATE TABLE ... TABLESPACE 에서 지원되는 DATA DIRECTORY 절은 ALTER TABLE ... TABLESPACE 에서는 지원되지 않으며, 지정하더라도 무시됩니다.

  • TABLESPACE 옵션의 기능과 제한 사항에 대한 자세한 내용은 CREATE TABLE 을 참조하십시오.

• MySQL NDB Cluster 9.5 는 테이블 코멘트의 일부로 NDB_TABLE 옵션을 설정하여 테이블의 파티션 밸런스(fragment count type), read-from-any-replica 기능, 풀 복제 또는 이들의 조합을 제어하는 것을 지원하며, 이는 CREATE TABLE 의 경우와 동일한 방식으로 ALTER TABLE 문에서도 사용할 수 있습니다. 예:

1ALTER TABLE t1 COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";

또한 ALTER TABLE 문의 일부로 NDB 테이블의 컬럼에 대한 NDB_COMMENT 옵션을 설정할 수도 있습니다. 예:

1ALTER TABLE t1
2    CHANGE COLUMN c1 c1 BLOB
3      COMMENT = 'NDB_COLUMN=BLOB_INLINE_SIZE=4096,MAX_BLOB_PART_SIZE';

ALTER TABLE ... COMMENT ... 는 테이블에 대한 기존 코멘트를 모두 버린다는 점을 유념하십시오. 추가 정보와 예제는 Setting NDB_TABLE options 을 참조하십시오.

• ENGINE_ATTRIBUTE 와 SECONDARY_ENGINE_ATTRIBUTE 옵션은 primary 및 secondary 스토리지 엔진에 대한 테이블, 컬럼, 인덱스 속성을 지정하는 데 사용됩니다. 이러한 옵션은 향후 사용을 위해 예약되어 있습니다. 인덱스 속성은 변경할 수 없습니다. 인덱스 속성을 변경하려면 인덱스를 드롭한 후 원하는 변경 사항을 반영하여 다시 추가해야 하며, 이는 하나의 ALTER TABLE 문에서 수행할 수 있습니다.

테이블 옵션이 의도한 대로 변경되었는지 확인하려면 SHOW CREATE TABLE 을 사용하거나 Information Schema TABLES 테이블을 조회하십시오.

Performance and Space Requirements

ALTER TABLE 연산은 다음 알고리즘 중 하나를 사용하여 처리됩니다:

• COPY: 연산은 원본 테이블의 복사본에서 수행되며, 테이블 데이터는 원본 테이블에서 새 테이블로 행 단위로 복사됩니다. 동시 DML은 허용되지 않습니다.

• INPLACE: 연산은 테이블 데이터 복사를 피하지만 테이블을 제자리에서 리빌드할 수 있습니다. 연산의 준비 및 실행 단계 동안 테이블에 대한 배타 메타데이터 락이 잠시 획득될 수 있습니다. 일반적으로 동시 DML이 지원됩니다.

• INSTANT: 연산은 데이터 딕셔너리의 메타데이터만 수정합니다. 연산의 실행 단계 동안 테이블에 대한 배타 메타데이터 락이 잠시 획득될 수 있습니다. 테이블 데이터는 영향을 받지 않으므로 연산은 즉각적입니다. 동시 DML이 허용됩니다.

NDB 스토리지 엔진을 사용하는 테이블의 경우, 이러한 알고리즘은 다음과 같이 동작합니다:

• COPY: NDB 는 테이블의 복사본을 생성하고 이를 alter합니다. 이후 NDB Cluster 핸들러는 기존 테이블과 새 테이블 사이의 데이터를 복사합니다. 이후 NDB 는 기존 테이블을 삭제하고 새 테이블의 이름을 변경합니다.

이 방식은 “copying” 또는 “offline” ALTER TABLE 이라고도 합니다.

• INPLACE: 데이터 노드가 필요한 변경을 수행하며, NDB Cluster 핸들러는 데이터를 복사하거나 다른 방식으로 관여하지 않습니다.

이 방식은 “non-copying” 또는 “online” ALTER TABLE 이라고도 합니다.

• INSTANT: NDB 에서는 지원되지 않습니다.

자세한 내용은 Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster” 을 참조하십시오.

ALGORITHM 절은 선택 사항입니다. ALGORITHM 절이 생략되면, MySQL은 스토리지 엔진과 ALTER TABLE 절이 이를 지원하는 경우 ALGORITHM=INSTANT 를 사용합니다. 그렇지 않으면 ALGORITHM=INPLACE 를 사용합니다. ALGORITHM=INPLACE 가 지원되지 않으면 ALGORITHM=COPY 를 사용합니다.

참고

ALGORITHM=INSTANT 를 사용하여 컬럼을 파티션 테이블에 추가한 후에는, 해당 테이블에 대해 ALTER TABLE ... EXCHANGE PARTITION 을 수행할 수 없습니다.

ALGORITHM 절을 지정하면, 해당 절과 스토리지 엔진이 지원할 경우 지정한 알고리즘을 사용해야 하며 그렇지 않으면 에러가 발생합니다. ALGORITHM=DEFAULT 를 지정하는 것은 ALGORITHM 절을 생략하는 것과 동일합니다.

COPY 알고리즘을 사용하는 ALTER TABLE 연산은 테이블을 수정 중인 다른 연산이 완료될 때까지 기다립니다. 변경 사항이 테이블 복사본에 적용된 후 데이터가 복사되고, 기존 테이블이 삭제되며 테이블 복사본의 이름이 기존 테이블의 이름으로 변경됩니다. ALTER TABLE 연산이 실행되는 동안, (곧 설명할 예외를 제외하고) 기존 테이블은 다른 세션에서 읽을 수 있습니다. ALTER TABLE 연산이 시작된 이후의 업데이트 및 쓰기는 새 테이블이 준비될 때까지 지연되었다가, 준비가 완료되면 자동으로 새 테이블로 리다이렉트됩니다. 테이블의 임시 복사본은 기존 테이블의 데이터베이스 디렉터리에 생성되며, RENAME TO 연산으로 테이블을 다른 디렉터리에 있는 데이터베이스로 이동하는 경우는 예외입니다.

앞서 언급한 예외는, ALTER TABLE 이 테이블 및 테이블 정의 캐시에서 오래된 테이블 구조를 제거할 준비가 되었을 때 읽기(뿐만 아니라 쓰기)도 블록한다는 점입니다. 이 시점에서 배타 락을 획득해야 하며, 이를 위해 현재 리더가 완료되기를 기다리고 새로운 읽기 및 쓰기를 블록합니다.

COPY 알고리즘을 사용하는 ALTER TABLE 연산은 동시 DML 연산을 방지합니다. 동시 쿼리는 여전히 허용됩니다. 즉, 테이블 복사 연산은 항상 최소한 LOCK=SHARED 의 동시성 제한(쿼리는 허용하지만 DML은 허용하지 않음)을 포함합니다. LOCK 절을 지원하는 연산의 경우, LOCK=EXCLUSIVE 를 지정하여 동시성을 추가로 제한할 수 있으며, 이는 DML과 쿼리 모두를 방지합니다. 자세한 내용은 Concurrency Control 을 참조하십시오.

ALTER TABLE 연산이 원래는 COPY 알고리즘을 사용하지 않을 경우에도, ALGORITHM=COPY 를 지정하거나 old_alter_table 시스템 변수를 활성화하여 강제로 COPY 알고리즘을 사용하게 할 수 있습니다. old_alter_table 설정과 DEFAULT 가 아닌 값을 가진 ALGORITHM 절이 충돌하는 경우에는 ALGORITHM 절이 우선합니다.

InnoDB 테이블의 경우, shared tablespace 에 있는 테이블에 대해 COPY 알고리즘을 사용하는 ALTER TABLE 연산은 테이블스페이스에서 사용되는 공간을 증가시킬 수 있습니다. 이러한 연산은 테이블 데이터 및 인덱스만큼의 추가 공간을 필요로 합니다. shared tablespace에 있는 테이블의 경우, 연산 동안 사용된 추가 공간은 file-per-table 테이블스페이스에 있는 테이블과 달리 운영 체제에 반환되지 않습니다.

online DDL 연산에 필요한 공간에 대한 정보는 Section 17.12.3, “Online DDL Space Requirements” 을 참조하십시오.

INPLACE 알고리즘을 지원하는 ALTER TABLE 연산은 다음과 같습니다:

• InnoDB online DDL 기능에서 지원하는 ALTER TABLE 연산. 자세한 내용은 Section 17.12.1, “Online DDL Operations” 을 참조하십시오.

• 테이블 이름 변경. MySQL은 복사를 만들지 않고 테이블 tbl_name 에 해당하는 파일의 이름을 변경합니다. (테이블 이름을 변경하려면 RENAME TABLE 문도 사용할 수 있습니다. Section 15.1.41, “RENAME TABLE Statement” 을 참조하십시오.) 이름이 변경된 테이블에 대해 명시적으로 부여된 권한은 새 이름으로 자동 이전되지 않으며, 수동으로 변경해야 합니다.

• 테이블 메타데이터만 수정하는 연산. 이러한 연산은 서버가 테이블 내용을 건드리지 않기 때문에 즉시 완료됩니다. 메타데이터만 변경하는 연산에는 다음이 포함됩니다:

  • 컬럼 이름 변경. NDB Cluster에서 이 연산은 online으로 수행할 수도 있습니다.

  • 컬럼의 기본값 변경(단, NDB 테이블은 예외).

  • ENUM 또는 SET 컬럼 정의를 수정하여, 저장 공간 크기가 변하지 않는 한 유효 멤버 값 목록의 끝 에 새 enum 또는 set 멤버를 추가하는 경우. 예를 들어, 8개 멤버를 가진 SET 컬럼에 멤버를 추가하면 값당 필요한 저장공간이 1바이트에서 2바이트로 변경되므로 테이블 복사가 필요합니다. 멤버를 목록 중간에 추가하면 기존 멤버의 번호가 재지정되며, 이는 테이블 복사를 필요로 합니다.

  • spatial 컬럼의 정의를 변경하여 SRID 속성을 제거하는 경우. (SRID 속성을 추가하거나 변경하는 경우에는 리빌드가 필요하며, 서버가 모든 값이 지정된 SRID 값을 갖는지 확인해야 하므로 in-place로 수행할 수 없습니다.)

  • 다음 조건이 적용되는 경우의 컬럼 캐릭터 세트 변경:

    • 컬럼 데이터 타입이 CHAR, VARCHAR, TEXT 타입, 또는 ENUM 인 경우.

    • 캐릭터 세트 변경이 utf8mb3 에서 utf8mb4 로, 또는 임의의 캐릭터 세트에서 binary 로 변경되는 경우.

    • 해당 컬럼에 인덱스가 없는 경우.

  • 다음 조건이 적용되는 경우의 생성된 컬럼 변경:

    • InnoDB 테이블의 경우, 타입, 표현식, null 허용 여부를 변경하지 않는 저장형 생성 컬럼을 수정하는 문장.

    • non-InnoDB 테이블의 경우, 타입, 표현식, null 허용 여부를 변경하지 않는 저장형 또는 가상 생성 컬럼을 수정하는 문장.

이러한 변경의 예로 컬럼 코멘트 변경이 있습니다.

• 인덱스 이름 변경.

• InnoDB 및 NDB 테이블에 세컨더리 인덱스를 추가하거나 제거하는 연산. Section 17.12.1, “Online DDL Operations” 을 참조하십시오.

• NDB 테이블의 경우, 가변 길이 컬럼에 대한 인덱스 추가 및 제거 연산. 이러한 연산은 테이블 복사 없이 online으로 수행되며, 대부분의 시간 동안 동시 DML 작업을 블록하지 않습니다. Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster” 을 참조하십시오.

• ALTER INDEX 연산으로 인덱스 visibility를 수정하는 경우.

• 생성된 컬럼이 있는 테이블에서, DEFAULT 값을 가진 컬럼에 의존하는 생성된 컬럼이 있을 때, 수정되는 컬럼이 생성된 컬럼 표현식에 포함되지 않은 경우의 컬럼 수정. 예를 들어, 다른 컬럼의 NULL 속성을 변경하는 것은 테이블 리빌드 없이 in-place로 수행할 수 있습니다.

INSTANT 알고리즘을 지원하는 ALTER TABLE 연산은 다음과 같습니다:

• 컬럼 추가. 이 기능은 “Instant ADD COLUMN” 이라고 합니다. 제한 사항이 있습니다. Section 17.12.1, “Online DDL Operations” 을 참조하십시오.

• 컬럼 삭제. 이 기능은 “Instant DROP COLUMN” 이라고 합니다. 제한 사항이 있습니다. Section 17.12.1, “Online DDL Operations” 을 참조하십시오.

• 가상 컬럼 추가 또는 삭제.

• 컬럼 기본값 추가 또는 삭제.

• ENUM 또는 SET 컬럼 정의 수정. 위에서 ALGORITHM=INSTANT 에 대해 설명한 동일한 제한 사항이 적용됩니다.

• 인덱스 타입 변경.

• 테이블 이름 변경. 위에서 ALGORITHM=INSTANT 에 대해 설명한 동일한 제한 사항이 적용됩니다.

ALGORITHM=INSTANT 를 지원하는 연산에 대한 자세한 내용은 Section 17.12.1, “Online DDL Operations” 을 참조하십시오.

ALTER TABLE 은 ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, FORCE 연산의 경우 MySQL 5.5 temporal 컬럼을 5.6 포맷으로 업그레이드합니다. 이 변환은 테이블을 리빌드해야 하므로 INPLACE 알고리즘으로 수행할 수 없습니다. 따라서 이러한 경우에 ALGORITHM=INPLACE 를 지정하면 에러가 발생합니다. 필요하다면 ALGORITHM=COPY 를 지정하십시오.

KEY 기준으로 파티션된 테이블에서 사용되는 멀티컬럼 인덱스에서 컬럼 순서를 변경하는 ALTER TABLE 연산은 ALGORITHM=COPY 를 사용해야만 수행할 수 있습니다.

WITHOUT VALIDATION 및 WITH VALIDATION 절은 가상 생성 컬럼 수정 시 ALTER TABLE 이 in-place 연산을 수행하는지 여부에 영향을 줍니다. Section 15.1.11.2, “ALTER TABLE and Generated Columns” 을 참조하십시오.

NDB Cluster 9.5 는 표준 MySQL Server에서 사용하는 것과 동일한 ALGORITHM=INPLACE 구문을 사용한 online 연산을 지원합니다. NDB 는 테이블스페이스를 online으로 변경하는 것을 허용하지 않습니다. Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster” 을 참조하십시오.

copying ALTER TABLE 을 수행할 때, NDB 는 영향을 받는 테이블에 동시 쓰기가 수행되지 않았는지 확인합니다. 동시 쓰기가 발견되면, NDB 는 ALTER TABLE 문을 거부하고 ER_TABLE_DEF_CHANGED 에러를 발생시킵니다.

DISCARD ... PARTITION ... TABLESPACE 또는 IMPORT ... PARTITION ... TABLESPACE 를 사용하는 ALTER TABLE 은 어떠한 임시 테이블이나 임시 파티션 파일도 생성하지 않습니다.

ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, REORGANIZE PARTITION 을 사용하는 ALTER TABLE 은(단, NDB 테이블에 사용되는 경우를 제외하고) 임시 테이블을 생성하지 않습니다. 그러나 이러한 연산은 임시 파티션 파일을 생성할 수 있으며 실제로 생성합니다.

RANGE 또는 LIST 파티션에 대한 ADD 또는 DROP 연산은 즉시 또는 거의 즉시 수행됩니다. HASH 또는 KEY 파티션에 대한 ADD 또는 COALESCE 연산은, LINEAR HASH 또는 LINEAR KEY 를 사용하지 않은 경우 모든 파티션 사이에 데이터를 복사합니다. 이는 새로운 테이블을 생성하는 것과 사실상 동일하지만, ADD 또는 COALESCE 연산은 파티션 단위로 수행됩니다. REORGANIZE 연산은 변경된 파티션만 복사하며 변경되지 않은 파티션은 건드리지 않습니다.

MyISAM 테이블의 경우, alteration 과정에서 가장 느린 부분인 인덱스 재생성을 빠르게 하려면 myisam_sort_buffer_size 시스템 변수를 높은 값으로 설정하십시오.

Concurrency Control

해당 기능을 지원하는 ALTER TABLE 연산에 대해, LOCK 절을 사용하여 테이블이 alter되는 동안 동시 읽기 및 쓰기 수준을 제어할 수 있습니다. 이 절에 기본값이 아닌 값을 지정하면, alter 연산 동안 필요한 수준의 동시 접근 또는 배타성을 요구할 수 있으며, 요청한 locking 수준을 사용할 수 없으면 연산이 중단됩니다.

ALGORITHM=INSTANT 를 사용하는 연산에 대해서는 LOCK = DEFAULT 만 허용됩니다. 다른 LOCK 절 파라미터는 적용되지 않습니다.

LOCK 절에 대한 파라미터는 다음과 같습니다:

• LOCK = DEFAULT

지정된 ALGORITHM 절(있는 경우)과 ALTER TABLE 연산에 대한 최대 동시성 수준: 지원되는 경우 동시 읽기와 쓰기를 허용합니다. 그렇지 않으면 지원되는 경우 동시 읽기를 허용합니다. 둘 다 지원되지 않으면 배타 접근을 강제합니다.

• LOCK = NONE

지원되는 경우 동시 읽기와 쓰기를 허용합니다. 그렇지 않으면 에러가 발생합니다.

• LOCK = SHARED

지원되는 경우 동시 읽기를 허용하지만 쓰기를 블록합니다. 쓰기는 스토리지 엔진이 지정된 ALGORITHM 절(있는 경우) 및 ALTER TABLE 연산에 대해 동시 쓰기를 지원하더라도 블록됩니다. 동시 읽기가 지원되지 않으면 에러가 발생합니다.

• LOCK = EXCLUSIVE

배타 접근을 강제합니다. 이는 스토리지 엔진이 지정된 ALGORITHM 절(있는 경우) 및 ALTER TABLE 연산에 대해 동시 읽기/쓰기를 지원하더라도 동일하게 적용됩니다.

Adding and Dropping Columns

ADD 를 사용하여 테이블에 새로운 컬럼을 추가하고, DROP 을 사용하여 기존 컬럼을 제거합니다. DROP col_name 은 표준 SQL에 대한 MySQL 확장입니다.

테이블 행 내에서 특정 위치에 컬럼을 추가하려면 FIRST 또는 AFTER col_name 을 사용합니다. 기본값은 컬럼을 마지막에 추가하는 것입니다.

테이블에 컬럼이 하나만 있는 경우 해당 컬럼은 드롭할 수 없습니다. 테이블 자체를 제거하려는 경우에는 대신 DROP TABLE 문을 사용하십시오.

테이블에서 컬럼을 드롭하면, 해당 컬럼이 일부로 포함된 인덱스에서도 컬럼이 제거됩니다. 하나의 인덱스를 구성하는 모든 컬럼이 드롭되면 해당 인덱스도 함께 드롭됩니다. 컬럼에 대한 인덱스가 존재하는 상태에서 CHANGE 또는 MODIFY 를 사용하여 컬럼을 줄이고, 결과 컬럼 길이가 인덱스 길이보다 짧아지면 MySQL은 인덱스를 자동으로 줄입니다.

ALTER TABLE ... ADD 에서 컬럼에 비결정적 함수(nondeterministic function)를 사용하는 표현식 기본값이 있는 경우, 문이 경고 또는 에러를 발생시킬 수 있습니다. 자세한 내용은 Section 13.6, “Data Type Default Values” 및 Section 19.1.3.7, “Restrictions on Replication with GTIDs” 을 참조하십시오.

Renaming, Redefining, and Reordering Columns

CHANGE, MODIFY, RENAME COLUMN, ALTER 절을 사용하면 기존 컬럼의 이름과 정의를 변경할 수 있습니다. 각 절의 특성은 다음과 같습니다:

• CHANGE:

  • 컬럼 이름과 정의를 모두 변경하거나, 그 중 하나만 변경할 수 있습니다.

  • MODIFY 나 RENAME COLUMN 보다 기능이 더 많지만, 일부 연산에서는 편의성이 떨어집니다. CHANGE 는 컬럼 이름을 변경하지 않을 때도 컬럼 이름을 두 번 지정해야 하고, 이름만 변경할 때도 컬럼 정의를 다시 지정해야 합니다.

  • FIRST 또는 AFTER 와 함께 사용하여 컬럼 순서를 재배치할 수 있습니다.

• MODIFY:

  • 컬럼 정의만 변경할 수 있고 이름은 변경할 수 없습니다.

  • 컬럼 이름을 변경하지 않고 정의만 변경하려는 경우, CHANGE 보다 사용이 더 편리합니다.

  • FIRST 또는 AFTER 와 함께 사용하여 컬럼 순서를 재배치할 수 있습니다.

• RENAME COLUMN:

  • 컬럼 이름만 변경할 수 있고 정의는 변경할 수 없습니다.

  • 정의를 변경하지 않고 컬럼 이름만 변경하려는 경우, CHANGE 보다 사용이 더 편리합니다.

• ALTER: 컬럼 기본값을 변경하는 데만 사용됩니다.

CHANGE 는 표준 SQL에 대한 MySQL 확장입니다. MODIFY 와 RENAME COLUMN 은 Oracle 호환성을 위한 MySQL 확장입니다.

컬럼의 이름과 정의를 모두 변경하려면, 기존 이름과 새 이름, 새 정의를 지정한 CHANGE 를 사용하십시오. 예를 들어, INT NOT NULL 컬럼의 이름을 a 에서 b 로 변경하고 정의를 BIGINT 데이터 타입을 사용하도록 변경하면서 NOT NULL 속성을 유지하려면 다음과 같이 합니다:

1ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

컬럼 이름은 변경하지 않고 정의만 변경하려면 CHANGE 나 MODIFY 를 사용하십시오. CHANGE 를 사용하는 경우, 구문은 두 개의 컬럼 이름을 요구하므로 이름을 그대로 유지하려면 동일한 이름을 두 번 지정해야 합니다. 예를 들어 컬럼 b 의 정의를 변경하려면 다음과 같이 합니다:

1ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY 는 이름을 변경하지 않고 정의만 변경할 때 더 편리한데, 컬럼 이름을 한 번만 지정하면 되기 때문입니다:

1ALTER TABLE t1 MODIFY b INT NOT NULL;

컬럼 정의는 변경하지 않고 컬럼 이름만 변경하려면 CHANGE 나 RENAME COLUMN 을 사용하십시오. CHANGE 를 사용하는 경우, 구문은 컬럼 정의를 요구하므로 정의를 유지하려면 현재 컬럼 정의를 다시 지정해야 합니다. 예를 들어 INT NOT NULL 컬럼의 이름을 b 에서 a 로 변경하려면 다음과 같이 합니다:

1ALTER TABLE t1 CHANGE b a INT NOT NULL;

RENAME COLUMN 은 정의를 변경하지 않고 이름만 변경할 때 더 편리한데, 기존 이름과 새 이름만 필요하기 때문입니다:

1ALTER TABLE t1 RENAME COLUMN b TO a;

일반적으로, 이미 테이블에 존재하는 이름으로 컬럼 이름을 변경할 수는 없습니다. 그러나 이름을 교환하거나 사이클을 통해 이동하는 경우에는 예외가 있을 수 있습니다. 예를 들어, 테이블에 a, b, c 라는 컬럼이 있는 경우 다음 연산은 유효합니다:

1-- swap a and b
2ALTER TABLE t1 RENAME COLUMN a TO b,
3               RENAME COLUMN b TO a;
4-- "rotate" a, b, c through a cycle
5ALTER TABLE t1 RENAME COLUMN a TO b,
6               RENAME COLUMN b TO c,
7               RENAME COLUMN c TO a;

CHANGE 또는 MODIFY 를 사용한 컬럼 정의 변경의 경우, 새 컬럼에 적용할 데이터 타입과 모든 속성(단, PRIMARY KEY 또는 UNIQUE 와 같은 인덱스 속성은 제외)을 정의에 포함해야 합니다. 기존 정의에 존재하지만 새 정의에서 지정되지 않은 속성은 그대로 유지되지 않습니다. 예를 들어 컬럼 col1 이 INT UNSIGNED DEFAULT 1 COMMENT 'my column' 으로 정의되어 있고, 다음과 같이 INT 를 BIGINT 로만 변경하려 한다고 가정해 봅니다:

1ALTER TABLE t1 MODIFY col1 BIGINT;

이 문장은 데이터 타입을 INT 에서 BIGINT 로 변경하지만, 동시에 UNSIGNED, DEFAULT, COMMENT 속성을 모두 제거합니다. 이러한 속성을 유지하려면 문장에 명시적으로 포함해야 합니다:

1ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

CHANGE 또는 MODIFY 를 사용하여 데이터 타입을 변경하는 경우, MySQL은 가능한 한 기존 컬럼 값을 새 타입으로 변환하려고 합니다.

주의

이 변환은 데이터 변경을 초래할 수 있습니다. 예를 들어 문자열 컬럼을 줄이는 경우 값이 잘릴 수 있습니다. 새 데이터 타입으로의 변환에서 데이터 손실이 발생하는 경우 연산이 성공하는 것을 방지하려면, ALTER TABLE 을 사용하기 전에 strict SQL 모드를 활성화하십시오( Section 7.1.11, “Server SQL Modes” 참조).

인덱스가 존재하는 컬럼에서 CHANGE 또는 MODIFY 를 사용하여 컬럼을 줄이고, 결과 컬럼 길이가 인덱스 길이보다 짧아지는 경우 MySQL은 인덱스를 자동으로 줄입니다.

CHANGE 또는 RENAME COLUMN 으로 이름이 변경된 컬럼에 대해, MySQL은 다음에 대한 참조를 자동으로 rename합니다:

• 해당 컬럼을 참조하는 인덱스 (invisible 인덱스 및 비활성화된 MyISAM 인덱스 포함).

• 해당 컬럼을 참조하는 foreign key.

반면, CHANGE 또는 RENAME COLUMN 으로 컬럼 이름이 변경되어도 MySQL이 자동으로 rename하지 않는 참조는 다음과 같습니다:

• rename된 컬럼을 참조하는 생성된 컬럼과 파티션 표현식. 이러한 표현식은 컬럼을 rename하는 것과 동일한 ALTER TABLE 문에서 CHANGE 를 사용하여 재정의해야 합니다.

• rename된 컬럼을 참조하는 뷰와 저장 프로그램. 이들 객체 정의는 수동으로 변경하여 새 컬럼 이름을 참조하도록 해야 합니다.

테이블 내에서 컬럼 순서를 재배치하려면, CHANGE 또는 MODIFY 연산에서 FIRST 와 AFTER 를 사용하십시오.

ALTER ... SET DEFAULT 또는 ALTER ... DROP DEFAULT 는 각각 컬럼의 새 기본값을 지정하거나 기존 기본값을 제거합니다. 기존 기본값이 제거되고 컬럼이 NULL 가능하다면 새 기본값은 NULL 입니다. 컬럼이 NULL 가능하지 않으면 MySQL은 Section 13.6, “Data Type Default Values” 에 설명된 대로 기본값을 할당합니다.

ALTER ... SET VISIBLE 과 ALTER ... SET INVISIBLE 은 컬럼 visibility를 변경할 수 있게 해줍니다. Section 15.1.24.10, “Invisible Columns” 을 참조하십시오.

Primary Keys and Indexes

DROP PRIMARY KEY 는 primary key 를 드롭합니다. primary key가 없으면 에러가 발생합니다. primary key의 성능 특성, 특히 InnoDB 테이블의 경우에 대해서는 Section 10.3.2, “Primary Key Optimization” 을 참조하십시오.

sql_require_primary_key 시스템 변수가 활성화된 경우, primary key를 드롭하려는 시도는 에러를 발생시킵니다.

테이블에 UNIQUE INDEX 또는 PRIMARY KEY 를 추가하면 MySQL은 중복 키를 가능한 한 빨리 감지할 수 있도록 이를 nonunique 인덱스보다 먼저 저장합니다.

DROP INDEX 는 인덱스를 제거합니다. 이는 표준 SQL에 대한 MySQL 확장입니다. Section 15.1.31, “DROP INDEX Statement” 을 참조하십시오. 인덱스 이름을 알아내려면 SHOW INDEX FROM tbl_name 을 사용하십시오.

일부 스토리지 엔진에서는 인덱스를 생성할 때 인덱스 타입을 지정할 수 있습니다. index_type 지정의 구문은 USING type_name 입니다. USING 에 관한 자세한 내용은 Section 15.1.18, “CREATE INDEX Statement” 을 참조하십시오. 권장 위치는 컬럼 목록 뒤입니다. 컬럼 목록 앞에서 옵션을 사용하는 지원은 향후 MySQL 릴리스에서 제거될 예정이므로, 해당 방식의 사용은 피해야 합니다.

index_option 값은 인덱스에 대한 추가 옵션을 지정합니다. USING 도 이러한 옵션 중 하나입니다. 허용되는 index_option 값에 대한 자세한 내용은 Section 15.1.18, “CREATE INDEX Statement” 을 참조하십시오.

RENAME INDEX old_index_name TO new_index_name 은 인덱스 이름을 변경합니다. 이는 표준 SQL에 대한 MySQL 확장입니다. 테이블의 내용은 변경되지 않습니다. old_index_name 은 테이블에 존재하는 인덱스 이름이어야 하며, 동일한 ALTER TABLE 문에서 드롭되지 않아야 합니다. new_index_name 은 새 인덱스 이름이며, 변경 적용 후 결과 테이블에서 다른 인덱스 이름과 중복될 수 없습니다. 두 인덱스 이름 모두 PRIMARY 가 될 수 없습니다.

MyISAM 테이블에 대해 ALTER TABLE 을 사용하면, 모든 nonunique 인덱스는 REPAIR TABLE 의 경우와 마찬가지로 별도의 배치에서 생성됩니다. 많은 인덱스가 있을 때 이 방식은 ALTER TABLE 을 훨씬 빠르게 해 줍니다.

MyISAM 테이블의 경우, 키 업데이트를 명시적으로 제어할 수 있습니다. ALTER TABLE ... DISABLE KEYS 를 사용하여 MySQL에 nonunique 인덱스 업데이트를 중지하도록 지시합니다. 그 다음 ALTER TABLE ... ENABLE KEYS 를 사용하여 누락된 인덱스를 다시 생성합니다. MyISAM 은 개별 키를 하나씩 insert하는 것보다 훨씬 빠른 특수 알고리즘을 사용하여 이를 처리하므로, 대량 insert 연산을 수행하기 전에 키를 disable하면 상당한 속도 향상을 기대할 수 있습니다. ALTER TABLE ... DISABLE KEYS 를 사용하려면, 앞에서 언급한 권한 외에 INDEX 권한이 필요합니다.

nonunique 인덱스가 disable되어 있는 동안에는, 일반적으로 해당 인덱스를 사용할 SELECT 및 EXPLAIN 등의 문장에서 해당 인덱스가 무시됩니다.

ALTER TABLE 문 이후에는 인덱스 cardinality 정보를 업데이트하기 위해 ANALYZE TABLE 을 실행해야 할 수도 있습니다. Section 15.7.7.24, “SHOW INDEX Statement” 을 참조하십시오.

ALTER INDEX 연산은 인덱스를 visible 또는 invisible로 만들 수 있습니다. invisible 인덱스는 옵티마이저에 의해 사용되지 않습니다. 인덱스 visibility 수정은 primary key(명시적 또는 암시적)가 아닌 인덱스에 적용되며, ALGORITHM=INSTANT 를 사용하여 수행할 수 없습니다. 이 기능은 스토리지 엔진에 독립적이며(어떤 엔진에서도 지원), 자세한 내용은 Section 10.3.12, “Invisible Indexes” 를 참조하십시오.

Foreign Keys and Other Constraints

FOREIGN KEY 및 REFERENCES 절은 ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...) 를 구현하는 InnoDB 및 NDB 스토리지 엔진에서 지원됩니다. Section 15.1.24.5, “FOREIGN KEY Constraints” 를 참조하십시오. 다른 스토리지 엔진의 경우, 이들 절은 파스만 되고 무시됩니다.

CREATE TABLE 과는 달리, ALTER TABLE 에서는 ADD FOREIGN KEY 가 지정된 index_name 을 무시하고 자동 생성된 foreign key 이름을 사용합니다. 이를 우회하려면 foreign key 이름을 지정하는 CONSTRAINT 절을 포함시키십시오:

1ADD CONSTRAINT name FOREIGN KEY (....) ...

주의

MySQL은 컬럼 정의의 일부로 정의된 inline REFERENCES 지정, 즉 컬럼 정의의 일환으로 정의된 참조를 조용히 무시합니다. MySQL은 별도의 FOREIGN KEY 지정의 일부로 정의된 REFERENCES 절만 허용합니다.

참고

파티션된 InnoDB 테이블은 foreign key를 지원하지 않습니다. 이 제한은 [LINEAR] KEY 로 명시적으로 파티션된 경우를 포함하여 NDB 테이블에는 적용되지 않습니다. 자세한 내용은 Section 26.6.2, “Partitioning Limitations Relating to Storage Engines” 을 참조하십시오.

MySQL Server와 NDB Cluster 모두 ALTER TABLE 를 사용하여 foreign key를 드롭하는 것을 지원합니다:

1ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

ALTER TABLE ... ALGORITHM=INPLACE 의 경우에는, 하나의 ALTER TABLE 문에서 foreign key를 추가하고 드롭하는 것이 지원되지만, ALTER TABLE ... ALGORITHM=COPY 의 경우에는 지원되지 않습니다.

서버는 참조 무결성 손실을 초래할 수 있는 foreign key 컬럼 변경을 금지합니다. 우회 방법으로는 컬럼 정의를 변경하기 전에 ALTER TABLE ... DROP FOREIGN KEY 를 사용하고, 이후 ALTER TABLE ... ADD FOREIGN KEY 를 사용하는 방법이 있습니다. 금지되는 변경의 예는 다음과 같습니다:

• 안전하지 않을 수 있는 foreign key 컬럼 데이터 타입 변경. 예를 들어 VARCHAR(20) 를 VARCHAR(30) 로 변경하는 것은 허용되지만, VARCHAR(1024) 로 변경하는 것은 허용되지 않습니다. 이는 값당 필요한 length 바이트 수가 변경되기 때문입니다.

• non-strict 모드에서 NULL 컬럼을 NOT NULL 로 변경하는 것은, 존재하는 NULL 값이 referenced 테이블에 대응 값이 없는 기본 non-NULL 값으로 변환되는 것을 방지하기 위해 금지됩니다. strict 모드에서는 이 연산이 허용되지만, 이러한 변환이 필요하면 에러가 반환됩니다.

ALTER TABLE tbl_name RENAME new_tbl_name 은 내부적으로 생성된 foreign key 제약 이름과 “tbl_name_ibfk_” 문자열로 시작하는 사용자 정의 foreign key 제약 이름을 새 테이블 이름을 반영하도록 변경합니다. InnoDB 는 “tbl_name_ibfk_” 로 시작하는 foreign key 제약 이름을 내부적으로 생성된 이름으로 해석합니다.

ALTER TABLE 을 사용하면 기존 테이블에 대해 CHECK 제약을 추가, 드롭 또는 변경할 수 있습니다:

• 새로운 CHECK 제약 추가:

1ALTER TABLE tbl_name
2      ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED];

제약 구문 요소의 의미는 CREATE TABLE 의 경우와 동일합니다. Section 15.1.24.6, “CHECK Constraints” 을 참조하십시오.

• symbol 이라는 이름의 기존 CHECK 제약 드롭:

1ALTER TABLE tbl_name
2      DROP CHECK symbol;

• symbol 이라는 이름의 기존 CHECK 제약의 enforcement 여부 변경:

1ALTER TABLE tbl_name
2      ALTER CHECK symbol [NOT] ENFORCED;

DROP CHECK 및 ALTER CHECK 절은 표준 SQL에 대한 MySQL 확장입니다.

ALTER TABLE 은 제약 이름에서 제약 타입을 결정하는 보다 일반적(이면서 SQL 표준)인 구문을 사용하여, 기존 제약을 드롭하거나 변경할 수 있도록 합니다:

• symbol 이라는 이름의 기존 제약 드롭:

1ALTER TABLE tbl_name
2      DROP CONSTRAINT symbol;

sql_require_primary_key 시스템 변수가 활성화된 경우, primary key를 드롭하려는 시도는 에러를 발생시킵니다.

• symbol 이라는 이름의 기존 제약의 enforcement 여부 변경:

1ALTER TABLE tbl_name
2      ALTER CONSTRAINT symbol [NOT] ENFORCED;

CHECK 제약만 enforcement 해제 대상으로 변경할 수 있습니다. 다른 모든 제약 타입은 항상 enforced됩니다.

SQL 표준에 따르면, 모든 제약 타입(primary key, unique 인덱스, foreign key, check)은 동일한 네임스페이스에 속합니다. MySQL에서는 각 제약 타입이 스키마당 자체 네임스페이스를 가집니다. 그 결과, 각 제약 타입의 이름은 스키마당 고유해야 하지만, 서로 다른 타입의 제약은 동일한 이름을 가질 수 있습니다. 여러 제약이 동일한 이름을 갖는 경우, DROP CONSTRAINT 와 ADD CONSTRAINT 는 모호해져 에러가 발생합니다. 이러한 경우 제약 수정에는 제약 타입별 구문을 사용해야 합니다. 예를 들어 primary key나 foreign key를 드롭하려면 DROP PRIMARY KEY 또는 DROP FOREIGN KEY 를 사용하십시오.

테이블 alteration이 enforced되는 CHECK 제약을 위반하는 경우 에러가 발생하며 테이블은 변경되지 않습니다. 다음과 같은 연산에서 에러가 발생합니다:

• CHECK 제약에 사용되는 컬럼에 AUTO_INCREMENT 속성을 추가하려는 시도.

• 기존 행이 제약 조건을 위반하는 enforced CHECK 제약을 추가하려는 시도 또는 nonenforced CHECK 제약을 enforced 상태로 변경하려는 시도.

• CHECK 제약에 사용되는 컬럼을 수정, rename, 드롭하려는 시도(단, 해당 제약을 동일한 문장에서 함께 드롭하는 경우는 예외). 예외: CHECK 제약이 단일 컬럼만 참조하는 경우, 해당 컬럼을 드롭하면 제약이 자동으로 드롭됩니다.

ALTER TABLE tbl_name RENAME new_tbl_name 은 내부적으로 생성된 CHECK 제약 이름과 “tbl_name_chk_” 문자열로 시작하는 사용자 정의 CHECK 제약 이름을 새 테이블 이름을 반영하도록 변경합니다. MySQL은 “tbl_name_chk_” 로 시작하는 CHECK 제약 이름을 내부적으로 생성된 이름으로 해석합니다.

Changing the Character Set

테이블 기본 캐릭터 세트와 모든 캐릭터 컬럼( CHAR, VARCHAR, TEXT)을 새 캐릭터 세트로 변경하려면 다음과 같은 문장을 사용하십시오:

1ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

이 문장은 모든 캐릭터 컬럼의 콜레이션도 변경합니다. COLLATE 절을 지정하여 사용할 콜레이션을 명시하지 않으면, 해당 캐릭터 세트의 기본 콜레이션을 사용합니다. 이 콜레이션이 의도된 테이블 용도에 부적합한 경우(예: 대소문자를 구분하는 콜레이션에서 대소문자를 구분하지 않는 콜레이션으로 변경되는 경우), 콜레이션을 명시적으로 지정하십시오.

데이터 타입이 VARCHAR 또는 TEXT 타입 중 하나인 컬럼의 경우, CONVERT TO CHARACTER SET 은 새 컬럼이 기존 컬럼과 동일한 문자 수를 저장할 수 있도록 필요하다면 데이터 타입을 변경합니다. 예를 들어, TEXT 컬럼은 값의 바이트 길이를 저장하는 2개의 length 바이트를 가지며, 최대 65,535 까지 저장할 수 있습니다. latin1 TEXT 컬럼의 경우, 각 문자는 1바이트를 필요로 하므로 최대 65,535 문자를 저장할 수 있습니다. 이 컬럼이 utf8mb4 로 변환되면, 각 문자는 최대 4바이트를 필요로 하므로 가능한 최대 길이는 4 × 65,535 = 262,140 바이트입니다. 이 길이는 TEXT 컬럼의 length 바이트에 맞지 않으므로, MySQL은 데이터 타입을 MEDIUMTEXT 로 변경합니다. 이는 length 바이트가 262,140 값을 기록할 수 있는 가장 작은 문자열 타입입니다. 마찬가지로 VARCHAR 컬럼도 MEDIUMTEXT 로 변환될 수 있습니다.

위에서 설명한 데이터 타입 변경을 피하려면 CONVERT TO CHARACTER SET 을 사용하지 마십시오. 대신, MODIFY 를 사용하여 개별 컬럼을 변경하십시오. 예:

1ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8mb4;
2ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8mb4;

CONVERT TO CHARACTER SET binary 를 지정하면, CHAR, VARCHAR, TEXT 컬럼이 해당하는 바이너리 문자열 타입( BINARY, VARBINARY, BLOB)으로 변환됩니다. 이는 해당 컬럼이 더 이상 캐릭터 세트를 가지지 않으며, 이후 CONVERT TO 연산이 이들 컬럼에 적용되지 않는다는 것을 의미합니다.

charset_name 이 CONVERT TO CHARACTER SET 연산에서 DEFAULT 인 경우, character_set_database 시스템 변수에 의해 지정된 캐릭터 세트가 사용됩니다.

주의

CONVERT TO 연산은 컬럼 값을 기존 캐릭터 세트와 지정된 캐릭터 세트 사이에서 변환합니다. 이는 컬럼이 특정 캐릭터 세트(예: latin1) 에 속해 있지만 실제로 저장된 값이 다른 비호환 캐릭터 세트(예: utf8mb4)를 사용하는 경우에는 원하는 결과가 아닙니다. 이러한 경우 각 컬럼에 대해 다음과 같이 해야 합니다:

1ALTER TABLE t1 CHANGE c1 c1 BLOB;
2ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8mb4;

이 방식이 작동하는 이유는 BLOB 컬럼으로 변환하거나 그 반대로 변환하는 경우에는 어떠한 변환도 수행되지 않기 때문입니다.

테이블의 기본 캐릭터 세트만 변경하려면 다음 문장을 사용하십시오:

1ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

DEFAULT 라는 단어는 선택 사항입니다. 기본 캐릭터 세트는 이후에 ALTER TABLE ... ADD column 과 같이 컬럼을 추가할 때 컬럼에 대해 캐릭터 세트를 별도로 지정하지 않으면 사용되는 캐릭터 세트입니다.

foreign_key_checks 시스템 변수가 활성화된 경우(기본 설정), foreign key 제약에 사용되는 캐릭터 문자열 컬럼을 포함하는 테이블에 대해 캐릭터 세트 변환이 허용되지 않습니다. 해결 방법은 캐릭터 세트 변환을 수행하기 전에 foreign_key_checks 를 비활성화하는 것입니다. 해당 foreign key 제약에 관련된 두 테이블 모두에 대해 변환을 수행한 후에 foreign_key_checks 를 다시 활성화해야 합니다. 하나의 테이블만 변환한 상태에서 foreign_key_checks 를 다시 활성화하면, ON DELETE CASCADE 또는 ON UPDATE CASCADE 연산 중에 발생하는 암묵적 변환으로 인해 참조하는 테이블의 데이터가 손상될 수 있습니다(Bug #45290, Bug #74816).

Importing InnoDB Tables

자체 file-per-table 테이블스페이스에 생성된 InnoDB 테이블은 DISCARD TABLEPACE 및 IMPORT TABLESPACE 절을 사용하여 백업 또는 다른 MySQL 서버 인스턴스에서 import할 수 있습니다. Section 17.6.1.3, “Importing InnoDB Tables” 을 참조하십시오.

Row Order for MyISAM Tables

ORDER BY 를 사용하면 행이 특정 순서로 정렬된 새 테이블을 생성할 수 있습니다. 이 옵션은 대부분의 경우 행을 특정 순서로 쿼리한다는 것을 알고 있을 때 유용합니다. 테이블에 큰 변경을 수행한 이후에 이 옵션을 사용하면 더 나은 성능을 얻을 수 있습니다. 일부 경우에는, 테이블이 나중에 정렬하려는 컬럼 순서로 정렬되어 있는 경우 MySQL이 정렬을 더 쉽게 수행할 수 있습니다.

참고

insert 및 delete가 발생한 후에도 테이블이 지정된 순서를 유지하는 것은 아닙니다.

ORDER BY 구문에서는 하나 이상의 컬럼 이름을 정렬 기준으로 지정할 수 있으며, 각 컬럼 이름 뒤에는 선택적으로 ASC 또는 DESC 를 지정하여 각각 오름차순 또는 내림차순 정렬 순서를 나타낼 수 있습니다. 기본값은 오름차순입니다. 정렬 기준으로는 컬럼 이름만 허용되며 임의 표현식은 허용되지 않습니다. 이 절은 다른 절 뒤에 마지막으로 지정해야 합니다.

ORDER BY 는 InnoDB 테이블의 경우 적합하지 않은데, 그 이유는 InnoDB 가 항상 테이블 행을 clustered index 에 따라 정렬하기 때문입니다.

파티션된 테이블에서 사용될 경우, ALTER TABLE ... ORDER BY 는 각 파티션 내에서만 행을 정렬합니다.

Partitioning Options

partition_options 는 repartitioning, 파티션 추가, 삭제, discard, import, merge, split 및 파티션 유지 관리를 위해 파티션 테이블에 사용할 수 있는 옵션을 의미합니다.

ALTER TABLE 문에는 다른 alter 지정과 함께 PARTITION BY 또는 REMOVE PARTITIONING 절이 포함될 수 있지만, PARTITION BY 또는 REMOVE PARTITIONING 절은 다른 지정 뒤에 마지막으로 지정되어야 합니다. ADD PARTITION, DROP PARTITION, DISCARD PARTITION, IMPORT PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE PARTITION, CHECK PARTITION, REPAIR PARTITION 옵션은 개별 파티션에 대해 동작하므로, 하나의 ALTER TABLE 에서 다른 alter 지정과 함께 사용할 수 없습니다.

파티션 옵션에 대한 자세한 내용은 Section 15.1.24, “CREATE TABLE Statement” 및 Section 15.1.11.1, “ALTER TABLE Partition Operations” 을 참조하십시오. ALTER TABLE ... EXCHANGE PARTITION 문에 대한 정보 및 예제는 Section 26.3.3, “Exchanging Partitions and Subpartitions with Tables” 을 참조하십시오.