[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"><p id="p-y1z-ntl-67e"><b>參數</b></p></th><th id="td-7no-rcm-6l3"><p id="p-ddk-us9-ugi"><b>描述</b></p></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"><p id="p-e6u-w1e-81p">ADD [COLUMN]</p></td><td data-tag="td" id="td-4sc-rmj-1gg" class="td"><p id="p-6d3-5u0-iwv">增加列，目前不支持增加主鍵列。</p></td></tr><tr data-tag="tr" id="tr-1qx-xqo-v2m" class="tr"><td data-tag="td" id="td-f5z-r59-w5s" class="td"><p id="p-7ak-v8a-ije">CHANGE [COLUMN]</p></td><td data-tag="td" id="td-hby-nkj-uqw" class="td"><p id="p-zsh-vmz-941">修改列名和列屬性。</p></td></tr><tr data-tag="tr" id="tr-9d5-jbp-ojz" class="tr"><td data-tag="td" id="td-ts9-4ao-4b4" class="td"><p id="p-dcd-kh5-7gz">MODIFY [COLUMN]</p></td><td data-tag="td" id="td-ep8-tth-9wj" class="td"><p id="p-95t-4kg-l8q">修改列屬性。</p></td></tr><tr data-tag="tr" id="tr-ar4-l14-0g5" class="tr"><td data-tag="td" id="td-io5-r03-ouz" class="td"><p id="p-wve-mqb-3x5">ALTER [COLUMN]</p></td><td data-tag="td" id="td-tsb-k3s-lji" class="td"><p id="p-x27-ii1-7ah">修改列的默認值。</p></td></tr><tr data-tag="tr" id="tr-5iq-gsu-h4f" class="tr"><td data-tag="td" id="td-h02-f22-8e8" class="td"><p id="p-1h5-lt8-2z3">DROP [COLUMN]</p></td><td data-tag="td" id="td-fk9-mwm-drv" class="td"><p id="p-bhw-kw4-np3">刪除列，不允許刪除主鍵列或者包含索引的列。</p></td></tr><tr data-tag="tr" id="tr-bqn-luu-gm9" class="tr"><td data-tag="td" id="td-nnb-ear-qj0" class="td"><p id="p-fgg-hwm-5l4">ADD [UNIQUE INDEX]</p></td><td data-tag="td" id="td-cxc-88n-4y5" class="td"><p id="p-f9g-45w-fpo">增加唯一索引。</p></td></tr><tr data-tag="tr" id="tr-gv4-h3x-uft" class="tr"><td data-tag="td" id="td-43u-2rl-l7h" class="td"><p id="p-igu-uel-xfa">ADD [INDEX]</p></td><td data-tag="td" id="td-g79-2zy-3vs" class="td"><p id="p-pta-xja-ohc">增加普通索引</p></td></tr><tr data-tag="tr" id="tr-axm-ibd-xaz" class="tr"><td data-tag="td" id="td-dlh-b9e-qv4" class="td"><p id="p-wqd-qbh-pod">ALTER [INDEX]</p></td><td data-tag="td" id="td-4l6-qxx-xfm" class="td"><p id="p-4l0-pxx-m26">修改索引屬性。</p></td></tr><tr data-tag="tr" id="tr-gfo-izt-nfb" class="tr"><td data-tag="td" id="td-gyb-cx7-6mp" class="td"><p id="p-5t6-g7c-da0">ADD [PARTITION]</p></td><td data-tag="td" id="td-pcw-fz8-9bt" class="td"><p id="p-dlu-e84-7wi">增加分區。</p></td></tr><tr data-tag="tr" id="tr-8uz-0zy-5pc" class="tr"><td data-tag="td" id="td-yqs-04l-u82" class="td"><p id="p-vxj-wy5-bcz">DROP [PARTITION]</p></td><td data-tag="td" id="td-hqx-lq3-bgp" class="td"><p id="p-r5k-tis-gxw">刪除分區。</p></td></tr><tr data-tag="tr" id="tr-9ow-434-e8p" class="tr"><td data-tag="td" id="td-mnp-5d7-y7u" class="td"><p id="p-53b-4i1-hwi">REORGANIZE [PARTITION]</p></td><td data-tag="td" id="td-utt-pu8-fs4" class="td"><p id="p-qte-oeh-b3x">分區重組。</p></td></tr><tr data-tag="tr" id="tr-aeq-nfy-19d" class="tr"><td data-tag="td" id="td-cc9-uff-l42" class="td"><p id="p-61v-c0u-1j0">TRUNCATE [PARTITION]</p></td><td data-tag="td" id="td-r1x-dsi-464" class="td"><p id="p-qks-q16-1y4">刪除分區數據。</p></td></tr><tr data-tag="tr" id="tr-acz-nqz-epz" class="tr"><td data-tag="td" id="td-on7-i0b-d27" class="td"><p id="p-ij8-ycg-yj7">RENAME [TO] table_name</p></td><td data-tag="td" id="td-553-kkb-d4f" class="td"><p id="p-njt-6io-q49">表重命名。</p></td></tr><tr data-tag="tr" id="tr-01q-jur-vb1" class="tr"><td data-tag="td" id="td-bcm-wmd-sgf" class="td"><p id="p-0u4-k5h-950">DROP [TABLEGROUP]</p></td><td data-tag="td" id="td-ox7-2rq-rgl" class="td"><p id="p-8f7-9z3-2o1">刪除表組。</p></td></tr><tr data-tag="tr" id="tr-01r-6uj-zmu" class="tr"><td data-tag="td" id="td-wia-3qg-628" class="td"><p id="p-wim-d9k-99j">DROP [FOREIGN KEY]</p></td><td data-tag="td" id="td-v5b-btt-ulg" class="td"><p id="p-m9m-qxn-0ff">刪除外鍵。</p></td></tr><tr data-tag="tr" id="tr-zat-xd9-puc" class="tr"><td data-tag="td" id="td-9jf-cmn-x22" class="td"><p id="p-j7e-098-4ig">SET BLOCK_SIZE</p></td><td data-tag="td" id="td-nll-nli-tzj" class="td"><p id="p-7wp-nuu-wpx">設置Partition表BLOCK大小。</p></td></tr><tr data-tag="tr" id="tr-b7n-lm5-tqb" class="tr"><td data-tag="td" id="td-13q-pzx-pfe" class="td"><p id="p-af2-0n5-us8">SET REPLICA_NUM</p></td><td data-tag="td" id="td-o2j-ds0-dia" class="td"><p id="p-ami-760-dgj">設置表的副本數（指表的副本總數）。</p></td></tr><tr data-tag="tr" id="tr-ozd-tyg-s25" class="tr"><td data-tag="td" id="td-bx0-kr0-kfj" class="td"><p id="p-1y3-t92-h1h">SET COMPRESSION</p></td><td data-tag="td" id="td-po3-ces-tu6" class="td"><p id="p-zxb-22a-h0x">設置表的壓縮方式。</p></td></tr><tr data-tag="tr" id="tr-2dm-eab-4mk" class="tr"><td data-tag="td" id="td-z5v-fsv-5kp" class="td"><p id="p-bym-ihj-q5y">SET USE_BLOOM_FILTER</p></td><td data-tag="td" id="td-u5s-2jf-rto" class="td"><p id="p-kdm-964-wgp">設置是否使用BloomFilter。</p></td></tr><tr data-tag="tr" id="tr-0os-9qr-ulx" class="tr"><td data-tag="td" id="td-6pg-2th-ou6" class="td"><p id="p-i0a-thm-u27">SET COMMENT</p></td><td data-tag="td" id="td-9ro-7vy-9id" class="td"><p id="p-a7n-1rv-27z">設置注釋信息。</p></td></tr><tr data-tag="tr" id="tr-qme-gf2-5s2" class="tr"><td data-tag="td" id="td-y9g-96x-o57" class="td"><p id="p-bk9-c9r-pt7">SET PROGRESSIVE_MERGE_NUM</p></td><td data-tag="td" id="td-aws-xg0-316" class="td"><p id="p-132-k8o-g5d">設置漸進合并步數，取值范圍是1~64。</p></td></tr><tr data-tag="tr" id="tr-cea-p8p-rfy" class="tr"><td id="td-a5k-475-9ru"><p id="p-4nb-io0-rnc">parallel_clause</p></td><td id="td-qol-xb7-30d"><p id="p-f0q-46s-2hf">指定表級別的并行度：</p><ul id="ul-q9p-hqk-ycc"><li id="li-bzl-9w0-jb1"><p id="p-pqf-gh2-yxs">NOPARALLEL：并行度為1，默認配置</p></li><li id="li-ieu-dv6-2gy"><p id="p-vc5-wq5-ox9">PARALLEL integer：指定并行度，integer 取值大于等于 1。</p></li></ul></td></tr></tbody></table> ## 示例 * 把表 t2 的字段 d 改名為 c，并同時修改字段類型 ~~~ ALTER TABLE t2 CHANGE COLUMN d c CHAR(10); ~~~ * 增加、刪除列 * 增加列前，執行`DESCRIBE test;`命令查看表信息，如下圖所示：  * 執行以下命令增加 c3 列 ~~~ ALTER TABLE test ADD c3 int; ~~~ * 增加列后，執行`DESCRIBE test;`命令查看表信息，如下圖所示：  * 執行以下命令刪除 c3 列 ~~~ ALTER TABLE test DROP c3; ~~~ * 刪除列后，執行`DESCRIBE test;`命令查看表信息，如下圖所示：  * 設置表格 test 的副本數，并且增加列 c5 ~~~ ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT; ~~~ * 修改表 t1 的并行度為 2 ~~~ ALTER TABLE t1 PARALLEL 2; ~~~