我的书签
添加书签
移除书签ADD COLUMN
ALTER TABLE.. ADD COLUMN 语句用于在已有表中添加列。在 TiDB 中，ADD COLUMN 为在线操作，不会阻塞表中的数据读写。
语法图
1. AlterTableStmt
2. ::= 'ALTER' 'IGNORE'? 'TABLE' TableName AddColumnSpec ( ',' AddColumnSpec )*
4. TableName ::=
5. Identifier ('.' Identifier)?
7. AddColumnSpec
8. ::= 'ADD' 'COLUMN' 'IF NOT EXISTS'? ColumnName ColumnType ColumnOption+ ( 'FIRST' | 'AFTER' ColumnName )?
10. ColumnType
11. ::= NumericType
12. | StringType
13. | DateAndTimeType
14. | 'SERIAL'
16. ColumnOption
17. ::= 'NOT'? 'NULL'
18. | 'AUTO_INCREMENT'
19. | 'PRIMARY'? 'KEY' ( 'CLUSTERED' | 'NONCLUSTERED' )?
20. | 'UNIQUE' 'KEY'?
21. | 'DEFAULT' ( NowSymOptionFraction | SignedLiteral | NextValueForSequence )
22. | 'SERIAL' 'DEFAULT' 'VALUE'
23. | 'ON' 'UPDATE' NowSymOptionFraction
24. | 'COMMENT' stringLit
25. | ( 'CONSTRAINT' Identifier? )? 'CHECK' '(' Expression ')' ( 'NOT'? ( 'ENFORCED' | 'NULL' ) )?
26. | 'GENERATED' 'ALWAYS' 'AS' '(' Expression ')' ( 'VIRTUAL' | 'STORED' )?
27. | 'REFERENCES' TableName ( '(' IndexPartSpecificationList ')' )? Match? OnDeleteUpdateOpt
28. | 'COLLATE' CollationName
29. | 'COLUMN_FORMAT' ColumnFormat
30. | 'STORAGE' StorageMedia
31. | 'AUTO_RANDOM' ( '(' LengthNum ')' )?
示例
1. CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
1. Query OK, 0 rows affected (0.11 sec)
1. INSERT INTO t1 VALUES (NULL);
1. Query OK, 1 row affected (0.02 sec)
1. SELECT * FROM t1;
1. +----+
2. | id |
3. +----+
4. | 1 |
5. +----+
6. 1 row in set (0.00 sec)
1. ALTER TABLE t1 ADD COLUMN c1 INT NOT NULL;
1. Query OK, 0 rows affected (0.28 sec)
1. SELECT * FROM t1;
1. +----+----+
2. | id | c1 |
3. +----+----+
4. | 1 | 0 |
5. +----+----+
6. 1 row in set (0.00 sec)
1. ALTER TABLE t1 ADD c2 INT NOT NULL AFTER c1;
1. Query OK, 0 rows affected (0.28 sec)
1. SELECT * FROM t1;
1. +----+----+----+
2. | id | c1 | c2 |
3. +----+----+----+
4. | 1 | 0 | 0 |
5. +----+----+----+
6. 1 row in set (0.00 sec)
MySQL 兼容性
- 不支持在一条语句中同时添加多列。
- 不支持将新添加的列设为
PRIMARY KEY。 - 不支持将新添加的列设为
AUTO_INCREMENT。 - 对添加生成列有局限性，具体可参考：生成列局限性。
