ALTER TABLE · k8s原理与实践

[TOC] # ALTER TABLE ## 描述该语句用来修改已存在的表的结构，比如：修改表及表属性、新增列、修改列及属性、删除列等。 ## 格式 ~~~ alter_table_stmt: ALTER TABLE table_name alter_table_action_list; | RENAME TABLE rename_table_action_list; alter_table_action_list: alter_table_action [, alter_table_action ...] alter_table_action: ADD [COLUMN] {column_definition | (column_definition_list)} | CHANGE [COLUMN] column_name column_definition | MODIFY [COLUMN] column_definition | ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT} | DROP [COLUMN] column_name | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc | ADD {INDEX | KEY} [index_name] index_desc | ADD FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc | ALTER INDEX index_name [VISIBLE | INVISIBLE | parallel_clause] | DROP {INDEX | KEY} index_name | ADD PARTITION (range_partition_list) | DROP PARTITION (partition_name_list) | REORGANIZE PARTITION name_list INTO partition_range_or_list | TRUNCATE PARTITION name_list | [SET] table_option_list | RENAME [TO] table_name | DROP TABLEGROUP | DROP FOREIGN KEY fk_name rename_table_action_list: rename_table_action [, rename_table_action ...] rename_table_action: table_name TO table_name column_definition_list: column_definition [, column_definition ...] column_definition: column_name data_type [DEFAULT const_value] [AUTO_INCREMENT] [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment index_desc: (column_desc_list) [index_type] [index_option_list] fulltext_index_desc: (column_desc_list) CTXCAT(column_desc_list) [index_option_list] column_desc_list: column_desc [, column_desc ...] column_desc: column_name [(length)] [ASC | DESC] index_type: USING BTREE index_option_list: index_option [ index_option ...] index_option: [GLOBAL | LOCAL] | block_size | compression | STORING(column_name_list) | comment table_option_list: table_option [ table_option ...] table_option: | primary_zone | replica_num | table_tablegroup | block_size | compression | AUTO_INCREMENT [=] INT_VALUE | comment | DUPLICATE_SCOPE [=] "none|zone|region|cluster" | parallel_clause parallel_clause: {NOPARALLEL | PARALLEL integer} partition_option: PARTITION BY HASH(expression) [subpartition_option] PARTITIONS partition_count | PARTITION BY KEY([column_name_list]) [subpartition_option] PARTITIONS partition_count | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)} [subpartition_option] (range_partition_list) subpartition_option: SUBPARTITION BY HASH(expression) SUBPARTITIONS subpartition_count | SUBPARTITION BY KEY(column_name_list) SUBPARTITIONS subpartition_count | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)} (range_subpartition_list) range_partition_list: range_partition [, range_partition ...] range_partition: PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE} range_subpartition_list: range_subpartition [, range_subpartition ...] range_subpartition: SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE} expression_list: expression [, expression ...] column_name_list: column_name [, column_name ...] partition_name_list: partition_name [, partition_name ...] partition_count | subpartition_count: INT_VALUE ~~~ ## 参数解释 <table data-tag="table" id="table-1sy-p0o-a0w" class="table"><colgroup width="360" span="1" data-tag="col" id="col-941-a4v-3a0" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="360" span="1" data-tag="col" id="col-gtb-mdl-wkt" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-jou-muc-tev" class="thead"><tr id="tr-8lm-u5v-gjd"><th id="td-z6n-o7k-ttk">参数</th><th id="td-7no-rcm-6l3">描述</th></tr></thead><tbody data-tag="tbody" id="tbody-ed5-zma-gwd" class="tbody"><tr data-tag="tr" id="tr-8fk-1jw-doo" class="tr"><td data-tag="td" id="td-3bk-gwm-v6f" class="td">ADD [COLUMN]</td><td data-tag="td" id="td-4sc-rmj-1gg" class="td">增加列，目前不支持增加主键列。</td></tr><tr data-tag="tr" id="tr-1qx-xqo-v2m" class="tr"><td data-tag="td" id="td-f5z-r59-w5s" class="td">CHANGE [COLUMN]</td><td data-tag="td" id="td-hby-nkj-uqw" class="td">修改列名和列属性。</td></tr><tr data-tag="tr" id="tr-9d5-jbp-ojz" class="tr"><td data-tag="td" id="td-ts9-4ao-4b4" class="td">MODIFY [COLUMN]</td><td data-tag="td" id="td-ep8-tth-9wj" class="td">修改列属性。</td></tr><tr data-tag="tr" id="tr-ar4-l14-0g5" class="tr"><td data-tag="td" id="td-io5-r03-ouz" class="td">ALTER [COLUMN]</td><td data-tag="td" id="td-tsb-k3s-lji" class="td">修改列的默认值。</td></tr><tr data-tag="tr" id="tr-5iq-gsu-h4f" class="tr"><td data-tag="td" id="td-h02-f22-8e8" class="td">DROP [COLUMN]</td><td data-tag="td" id="td-fk9-mwm-drv" class="td">删除列，不允许删除主键列或者包含索引的列。</td></tr><tr data-tag="tr" id="tr-bqn-luu-gm9" class="tr"><td data-tag="td" id="td-nnb-ear-qj0" class="td">ADD [UNIQUE INDEX]</td><td data-tag="td" id="td-cxc-88n-4y5" class="td">增加唯一索引。</td></tr><tr data-tag="tr" id="tr-gv4-h3x-uft" class="tr"><td data-tag="td" id="td-43u-2rl-l7h" class="td">ADD [INDEX]</td><td data-tag="td" id="td-g79-2zy-3vs" class="td">增加普通索引</td></tr><tr data-tag="tr" id="tr-axm-ibd-xaz" class="tr"><td data-tag="td" id="td-dlh-b9e-qv4" class="td">ALTER [INDEX]</td><td data-tag="td" id="td-4l6-qxx-xfm" class="td">修改索引属性。</td></tr><tr data-tag="tr" id="tr-gfo-izt-nfb" class="tr"><td data-tag="td" id="td-gyb-cx7-6mp" class="td">ADD [PARTITION]</td><td data-tag="td" id="td-pcw-fz8-9bt" class="td">增加分区。</td></tr><tr data-tag="tr" id="tr-8uz-0zy-5pc" class="tr"><td data-tag="td" id="td-yqs-04l-u82" class="td">DROP [PARTITION]</td><td data-tag="td" id="td-hqx-lq3-bgp" class="td">删除分区。</td></tr><tr data-tag="tr" id="tr-9ow-434-e8p" class="tr"><td data-tag="td" id="td-mnp-5d7-y7u" class="td">REORGANIZE [PARTITION]</td><td data-tag="td" id="td-utt-pu8-fs4" class="td">分区重组。</td></tr><tr data-tag="tr" id="tr-aeq-nfy-19d" class="tr"><td data-tag="td" id="td-cc9-uff-l42" class="td">TRUNCATE [PARTITION]</td><td data-tag="td" id="td-r1x-dsi-464" class="td">删除分区数据。</td></tr><tr data-tag="tr" id="tr-acz-nqz-epz" class="tr"><td data-tag="td" id="td-on7-i0b-d27" class="td">RENAME [TO] table_name</td><td data-tag="td" id="td-553-kkb-d4f" class="td">表重命名。</td></tr><tr data-tag="tr" id="tr-01q-jur-vb1" class="tr"><td data-tag="td" id="td-bcm-wmd-sgf" class="td">DROP [TABLEGROUP]</td><td data-tag="td" id="td-ox7-2rq-rgl" class="td">删除表组。</td></tr><tr data-tag="tr" id="tr-01r-6uj-zmu" class="tr"><td data-tag="td" id="td-wia-3qg-628" class="td">DROP [FOREIGN KEY]</td><td data-tag="td" id="td-v5b-btt-ulg" class="td">删除外键。</td></tr><tr data-tag="tr" id="tr-zat-xd9-puc" class="tr"><td data-tag="td" id="td-9jf-cmn-x22" class="td">SET BLOCK_SIZE</td><td data-tag="td" id="td-nll-nli-tzj" class="td">设置Partition表BLOCK大小。</td></tr><tr data-tag="tr" id="tr-b7n-lm5-tqb" class="tr"><td data-tag="td" id="td-13q-pzx-pfe" class="td">SET REPLICA_NUM</td><td data-tag="td" id="td-o2j-ds0-dia" class="td">设置表的副本数（指表的副本总数）。</td></tr><tr data-tag="tr" id="tr-ozd-tyg-s25" class="tr"><td data-tag="td" id="td-bx0-kr0-kfj" class="td">SET COMPRESSION</td><td data-tag="td" id="td-po3-ces-tu6" class="td">设置表的压缩方式。</td></tr><tr data-tag="tr" id="tr-2dm-eab-4mk" class="tr"><td data-tag="td" id="td-z5v-fsv-5kp" class="td">SET USE_BLOOM_FILTER</td><td data-tag="td" id="td-u5s-2jf-rto" class="td">设置是否使用BloomFilter。</td></tr><tr data-tag="tr" id="tr-0os-9qr-ulx" class="tr"><td data-tag="td" id="td-6pg-2th-ou6" class="td">SET COMMENT</td><td data-tag="td" id="td-9ro-7vy-9id" class="td">设置注释信息。</td></tr><tr data-tag="tr" id="tr-qme-gf2-5s2" class="tr"><td data-tag="td" id="td-y9g-96x-o57" class="td">SET PROGRESSIVE_MERGE_NUM</td><td data-tag="td" id="td-aws-xg0-316" class="td">设置渐进合并步数，取值范围是1~64。</td></tr><tr data-tag="tr" id="tr-cea-p8p-rfy" class="tr"><td id="td-a5k-475-9ru">parallel_clause</td><td id="td-qol-xb7-30d">指定表级别的并行度：<ul id="ul-q9p-hqk-ycc"><li id="li-bzl-9w0-jb1">NOPARALLEL：并行度为1，默认配置</li><li id="li-ieu-dv6-2gy">PARALLEL integer：指定并行度，integer 取值大于等于 1。</li></ul></td></tr></tbody></table> ## 示例 * 把表 t2 的字段 d 改名为 c，并同时修改字段类型 ~~~ ALTER TABLE t2 CHANGE COLUMN d c CHAR(10); ~~~ * 增加、删除列 * 增加列前，执行`DESCRIBE test;`命令查看表信息，如下图所示： ![](https://img.kancloud.cn/24/11/24110c08dbcf605c6ae8197052201491_527x144.png) * 执行以下命令增加 c3 列 ~~~ ALTER TABLE test ADD c3 int; ~~~ * 增加列后，执行`DESCRIBE test;`命令查看表信息，如下图所示： ![](https://img.kancloud.cn/e3/0b/e30b90a4ad3dd9f1276a0d989ad67d48_545x158.png) * 执行以下命令删除 c3 列 ~~~ ALTER TABLE test DROP c3; ~~~ * 删除列后，执行`DESCRIBE test;`命令查看表信息，如下图所示： ![](https://img.kancloud.cn/6f/ea/6fea64d2e25735b449098ef9d42d18db_521x138.png) * 设置表格 test 的副本数，并且增加列 c5 ~~~ ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT; ~~~ * 修改表 t1 的并行度为 2 ~~~ ALTER TABLE t1 PARALLEL 2; ~~~