mysql alter table_MySQL ALTER TABLE语法

ALTER TABLEtbl_name[alter_specification [, alter_specification]...][partition_options]alter_specification:

table_options| ADD [COLUMN] col_namecolumn_definition[FIRST | AFTER col_name]

| ADD [COLUMN] (col_namecolumn_definition,...)| ADD {INDEX|KEY} [index_name]

[index_type] (key_part,...) [index_option]...| ADD {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name](key_part,...)[index_option]...| ADD [CONSTRAINT [symbol]] PRIMARY KEY

[index_type](key_part,...)[index_option]...| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

[index_name] [index_type](key_part,...)[index_option]...| ADD [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (col_name,...)

reference_definition| ADDcheck_constraint_definition| DROP CHECKsymbol| ALTER CHECK symbol [NOT]ENFORCED| ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}| ALTER INDEX index_name {VISIBLE |INVISIBLE}| CHANGE [COLUMN]old_col_name new_col_name column_definition[FIRST|AFTER col_name]

| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=]collation_name]| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| {DISABLE|ENABLE} KEYS| {DISCARD|IMPORT} TABLESPACE| DROP [COLUMN] col_name

| DROP {INDEX|KEY} index_name| DROP PRIMARY KEY

| DROP FOREIGN KEYfk_symbol|FORCE| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}| MODIFY [COLUMN] col_namecolumn_definition[FIRST | AFTER col_name]

| ORDER BY col_name [, col_name]...| RENAME COLUMN old_col_name TOnew_col_name| RENAME {INDEX|KEY} old_index_name TOnew_index_name| RENAME [TO|AS]new_tbl_name| {WITHOUT|WITH} VALIDATION

partition_options:

partition_option[partition_option]...

partition_option:ADDPARTITION (partition_definition)| DROPPARTITION partition_names| DISCARD PARTITION {partition_names | ALL} TABLESPACE| IMPORT PARTITION {partition_names | ALL} TABLESPACE| TRUNCATE PARTITION {partition_names | ALL}| COALESCE PARTITION number

| REORGANIZE PARTITION partition_names INTO(partition_definitions)| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]

| ANALYZE PARTITION {partition_names | ALL}| CHECK PARTITION {partition_names | ALL}| OPTIMIZE PARTITION {partition_names | ALL}| REBUILD PARTITION {partition_names | ALL}| REPAIR PARTITION {partition_names | ALL}|REMOVE PARTITIONING

key_part: {col_name [(length)] | (expr)} [ASC | DESC]index_type:

USING {BTREE|HASH}

index_option:

KEY_BLOCK_SIZE[=]value|index_type| WITHPARSER parser_name| COMMENT 'string'

| {VISIBLE |INVISIBLE}

check_constraint_definition:[CONSTRAINT [symbol]] CHECK (expr) [[NOT]ENFORCED]

table_options:

table_option[[,]table_option] ...

table_option:

AUTO_INCREMENT[=]value| AVG_ROW_LENGTH [=]value| [DEFAULT] CHARACTER SET [=]charset_name| CHECKSUM [=] {0 | 1}| [DEFAULT] COLLATE [=]collation_name| COMMENT [=] 'string'

| COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}| CONNECTION [=] 'connect_string'

| {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'

| DELAY_KEY_WRITE [=] {0 | 1}| ENCRYPTION [=] {'Y' | 'N'}| ENGINE [=]engine_name| INSERT_METHOD [=] { NO | FIRST |LAST }| KEY_BLOCK_SIZE [=]value| MAX_ROWS [=]value| MIN_ROWS [=]value| PACK_KEYS [=] {0 | 1 | DEFAULT}| PASSWORD [=] 'string'

| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}| STATS_AUTO_RECALC [=] {DEFAULT|0|1}| STATS_PERSISTENT [=] {DEFAULT|0|1}| STATS_SAMPLE_PAGES [=]value| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]

| UNION [=] (tbl_name[,tbl_name]...)

partition_options:

(seeCREATE TABLE options)