ALTER TABLE更改表的结构。比如,您可以添加或删除列,创建或销毁索引,更改现有列的类型,或重命名列或表本身。还可以更改特性,例如用于表或表注释的存储引擎。
ALTER TABLE tbl_name[alter_specification [, alter_specification] ...][partition_options]alter_specification:table_options| ADD [COLUMN] col_name column_definition[FIRST | AFTER col_name]| ADD [COLUMN] (col_name column_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| ADD CHECK (expr)| ALGORITHM [=] {DEFAULT|INPLACE|COPY}| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}| 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 KEY fk_symbol| FORCE| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}| MODIFY [COLUMN] col_name column_definition[FIRST | AFTER col_name]| ORDER BY col_name [, col_name] ...| RENAME {INDEX|KEY} old_index_name TO new_index_name| RENAME [TO|AS] new_tbl_name| {WITHOUT|WITH} VALIDATIONpartition_options:partition_option [partition_option] ...partition_option:ADD PARTITION (partition_definition)| DROP PARTITION 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| UPGRADE PARTITIONINGkey_part:col_name [(length)] [ASC | DESC]index_type:USING {BTREE | HASH}index_option:KEY_BLOCK_SIZE [=] value| index_type| WITH PARSER parser_name| COMMENT 'string'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:(see CREATE TABLE options)
允许生成列的ALTER TABLE
操作是ADD
,MODIFY
和CHANGE
。
可以添加生成的列。
CREATE TABLE t1 (c1 INT);ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
可以修改生成列的数据类型和表达式。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;--修改c2的类型
如果没有其他列引用它们,则可以重命名或删除生成的列。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;--把c2改为c3ALTER TABLE t1 DROP COLUMN c3;--删除c3
虚拟生成的列不能更改为存储的生成列,反之亦然。要解决此问题,请删除该列,然后使用新定义添加该列。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);ALTER TABLE t1 DROP COLUMN c2;ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
非生成列可以更改为已存储但不是虚拟生成列。
CREATE TABLE t1 (c1 INT, c2 INT);ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
存储但不是虚拟生成的列可以更改为非生成列。存储的生成值将成为非生成列的值。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);ALTER TABLE t1 MODIFY COLUMN c2 INT;
总结,虚不可转实,实不可转虚,非可以转实,实可以转非。
虚为自动关联生成的虚拟列,实为自动生成的存储字段,非为普通字段。
未完待续。。。