mysql ALTER 语句的使用

最新推荐文章于 2025-02-15 22:51:50 发布

HC梁文韬

最新推荐文章于 2025-02-15 22:51:50 发布

阅读量420

点赞数

数据库专栏收录该内容

1 篇文章

订阅专栏

基本语法：
ALTER [ONLINE | OFFLINE] [IGNORE] 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] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | FORCE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | 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}
  | PARTITION BY partitioning_expression
  | REMOVE PARTITIONING

index_col_name:
    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] ...  (see CREATE TABLE options)

partition_options:
    (see CREATE TABLE options)



其中table_options：包括ENGINE，ROW_FORMAT,AUTO_INCREMENT,CHARACTER SET,COMMENT
AUTO_INCREMENT 可以表示当前自动增长的值，COMMENT表示表的注释。

　×　在大多数执行ATLER TABLE语句的时候，数据库都会创建一个临时的表，它是旧表的一个辅助，数据库会等待所有的其他操作完成以后才讲对表的更变加入到临时表中。
　×　在ALTER TABLE 语句执行的时候，表仍然是可读的，但是所有对表的更新与写操作都会被阻塞，知道新表被准备好。
    ×    有些ATLER TABLE的操作不会阻塞一个表，对表中列进行重命名不会阻塞表。
    ×    对表的元数据的修改不会导致表的复制：比如对表的重命名，列默认值的修改，还有就是增加一个枚举成员等等。
     ALTER TABLE with ADD          PARTITION, DROP PARTITION,          COALESCE PARTITION, REBUILD          PARTITION, or REORGANIZE          PARTITION 
     does not create any temporary tables          (except when used with NDB          tables); 
     however, these operations can and do create temporary          partition files.