ADD COLUMN

ALTER TABLE.. ADD COLUMN 语句用于在已有表中添加列。在 TiDB 中，ADD COLUMN 为在线操作，不会阻塞表中的数据读写。

语法图

AlterTableStmt

ADD COLUMN - 图1

TableName

ADD COLUMN - 图2

AddColumnSpec

ADD COLUMN - 图3

ColumnType

ADD COLUMN - 图4

ColumnOption

ADD COLUMN - 图5

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。
  • 对添加生成列有局限性，具体可参考：生成列局限性。

另请参阅