先看一下定义（密密麻麻）
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_constraint_definition
| DROP CHECK symbol
| ALTER CHECK symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| 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 COLUMN old_col_name TO new_col_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| RENAME [TO|AS] new_tbl_name
| {WITHOUT|WITH} VALIDATION partition_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 key_part: {col_name [(length)] | (expr)} [ASC | DESC] index_type:
USING {BTREE | HASH} index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE} check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED] 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)
实际上也不复杂，一条一条看，就看懂了。我介绍一些常用的：
测试表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1. 重置自增值
ALTER TABLE `user` AUTO_INCREMENT = 15
> OK
> 时间: 0.013s
2. 更改字符集
ALTER TABLE `user` CHARACTER SET = utf8;
3. 更改表注释
ALTER TABLE `user` COMMENT = 'New table comment';
4. 添加列
ALTER TABLE `user` ADD COLUMN create_time datetime NOT NULL
> OK
> 时间: 0.057s
5. 删除列
ALTER TABLE `user` DROP COLUMN create_time
> OK
> 时间: 0.046s
如果要单个语句删除多个列
ALTER TABLE `user` DROP COLUMN col_1, DROP COLUMN col_2;
6. 重新定义列
CHANGE
-- 可以重命名列并更改其定义，或两者。
-- 具有比MODIFY或 更多的能力RENAME COLUMN，但是以某些操作的便利性为代价。CHANGE 如果不重命名，则需要将列命名两次，如果仅重命名，则需要重新指定列定义。
-- 使用FIRST或AFTER可以重新排序列。
ALTER TABLE `user` CHANGE create_time create_time int NOT NULL AFTER id;
MODIFY
-- 可以更改列定义但不能更改其名称。
-- 比CHANGE不更改列定义的情况下，更改列定义更方便，因为不用命名两次。
-- 使用FIRST或AFTER可以重新排序列。
ALTER TABLE `user` MODIFY create_time char(32) NOT NULL FIRST;
RENAME
-- 可以更改列名但不能更改其定义。
-- 比CHANGE重命名列而不更改其定义的情况下更方便。
ALTER TABLE `user` RENAME COLUMN create_time TO order_id;
这个在MySQL5.X是不行的，如下：
ALTER TABLE `user` RENAME COLUMN create_time TO order_id
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN create_time TO order_id' at line 11
> 时间: 0s
但是在MySQL8.X是可以的（我在Docker上创建一个8.X的容器）
查看表结构
