描述

该语句用来修改已存在的表的结构,比如:修改表及表属性、新增列、修改列及属性、删除列等。

格式

  1. alter_table_stmt:
  2. ALTER TABLE table_name
  3. alter_table_action_list;
  4. | RENAME TABLE rename_table_action_list;
  5. alter_table_action_list:
  6. alter_table_action [, alter_table_action ...]
  7. alter_table_action:
  8. ADD [COLUMN] {column_definition | (column_definition_list)}
  9. | CHANGE [COLUMN] column_name column_definition
  10. | MODIFY [COLUMN] column_definition
  11. | ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
  12. | DROP [COLUMN] column_name
  13. | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
  14. | ADD {INDEX | KEY} [index_name] index_desc
  15. | ADD FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc
  16. | ALTER INDEX index_name [VISIBLE | INVISIBLE]
  17. | DROP {INDEX | KEY} index_name
  18. | ADD PARTITION (range_partition_list)
  19. | DROP PARTITION (partition_name_list)
  20. | REORGANIZE PARTITION name_list INTO partition_range_or_list
  21. | TRUNCATE PARTITION name_list
  22. | [SET] table_option_list
  23. | RENAME [TO] table_name
  24. | DROP TABLEGROUP
  25. | DROP FOREIGN KEY fk_name
  26. rename_table_action_list:
  27. rename_table_action [, rename_table_action ...]
  28. rename_table_action:
  29. table_name TO table_name
  30. column_definition_list:
  31. column_definition [, column_definition ...]
  32. column_definition:
  33. column_name data_type
  34. [DEFAULT const_value] [AUTO_INCREMENT]
  35. [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
  36. index_desc:
  37. (column_desc_list) [index_type] [index_option_list]
  38. fulltext_index_desc:
  39. (column_desc_list) CTXCAT(column_desc_list) [index_option_list]
  40. column_desc_list:
  41. column_desc [, column_desc ...]
  42. column_desc:
  43. column_name [(length)] [ASC | DESC]
  44. index_type:
  45. USING BTREE
  46. index_option_list:
  47. index_option [ index_option ...]
  48. index_option:
  49. [GLOBAL | LOCAL]
  50. | block_size
  51. | compression
  52. | STORING(column_name_list)
  53. | comment
  54. table_option_list:
  55. table_option [ table_option ...]
  56. table_option:
  57. | primary_zone
  58. | replica_num
  59. | table_tablegroup
  60. | block_size
  61. | compression
  62. | AUTO_INCREMENT [=] INT_VALUE
  63. | comment
  64. | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
  65. partition_option:
  66. PARTITION BY HASH(expression)
  67. [subpartition_option] PARTITIONS partition_count
  68. | PARTITION BY KEY([column_name_list])
  69. [subpartition_option] PARTITIONS partition_count
  70. | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
  71. [subpartition_option] (range_partition_list)
  72. subpartition_option:
  73. SUBPARTITION BY HASH(expression)
  74. SUBPARTITIONS subpartition_count
  75. | SUBPARTITION BY KEY(column_name_list)
  76. SUBPARTITIONS subpartition_count
  77. | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
  78. (range_subpartition_list)
  79. range_partition_list:
  80. range_partition [, range_partition ...]
  81. range_partition:
  82. PARTITION partition_name
  83. VALUES LESS THAN {(expression_list) | MAXVALUE}
  84. range_subpartition_list:
  85. range_subpartition [, range_subpartition ...]
  86. range_subpartition:
  87. SUBPARTITION subpartition_name
  88. VALUES LESS THAN {(expression_list) | MAXVALUE}
  89. expression_list:
  90. expression [, expression ...]
  91. column_name_list:
  92. column_name [, column_name ...]
  93. partition_name_list:
  94. partition_name [, partition_name ...]
  95. partition_count | subpartition_count:
  96. INT_VALUE

参数解释

参数

描述

ADD [COLUMN]增加列,目前不支持增加主键列。
CHANGE [COLUMN]修改列名和列属性。
MODIFY [COLUMN]修改列属性。
ALTER [COLUMN]修改列的默认值。
DROP [COLUMN]删除列,不允许删除主键列或者包含索引的列。
ADD [UNIQUE INDEX]增加唯一索引。
ADD [INDEX]增加普通索引
ALTER [INDEX]修改索引属性。
ADD [PARTITION]增加分区。
DROP [PARTITION]删除分区。
REORGANIZE [PARTITION]分区重组。
TRUNCATE [PARTITION]删除分区数据。
RENAME [TO] table_name表重命名。
DROP [TABLEGROUP]删除表组。
DROP [FOREIGN KEY]删除外键。
SET BLOCK_SIZE设置Partition表BLOCK大小。
SET REPLICA_NUM设置表的副本数(指表的副本总数)。
SET COMPRESSION设置表的压缩方式。
SET USE_BLOOM_FILTER设置是否使用BloomFilter。
SET COMMENT设置注释信息。
SET PROGRESSIVE_MERGE_NUM设置渐进合并步数,取值范围是1~64。

示例

  • 把表t2的字段d改名为c,并同时修改字段类型
  1. ALTER TABLE t2 CHANGE COLUMN d c CHAR(10);
  • 增加、删除列

  • 增加列前,执行DESCRIBE test; 命令查看表信息,如下图所示:

image.png

  • 执行以下命令增加c3列
  1. ALTER TABLE test ADD c3 int;
  • 增加列后,执行DESCRIBE test; 命令查看表信息,如下图所示:

image.png

  • 执行以下命令删除c3列
  1. ALTER TABLE test DROP c3;
  • 删除列后,执行DESCRIBE test; 命令查看表信息,如下图所示:

image.png

  • 设置表格test的副本数,并且增加列c5
  1. ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;