tidb ADD COLUMN

谁借莪１个温暖的怀抱￠ 2023-01-01 14:53 184阅读 0赞

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

## 语法图 ##

**AlterTableStmt:**

![AlterTableStmt][]

**AlterTableSpec:**

![AlterTableSpec][]

**ColumnDef:**

![ColumnDef][]

**ColumnPosition:**

![ColumnPosition][]

## 示例 ##

Copy

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

Query OK, 0 rows affected (0.11 sec)

Copy

INSERT INTO t1 VALUES (NULL);

Query OK, 1 row affected (0.02 sec)

Copy

SELECT * FROM t1;

+----+
    | id |
    +----+
    |  1 |
    +----+
    1 row in set (0.00 sec)

Copy

ALTER TABLE t1 ADD COLUMN c1 INT NOT NULL;

Query OK, 0 rows affected (0.28 sec)

Copy

SELECT * FROM t1;

+----+----+
    | id | c1 |
    +----+----+
    |  1 |  0 |
    +----+----+
    1 row in set (0.00 sec)

Copy

ALTER TABLE t1 ADD c2 INT NOT NULL AFTER c1;

Query OK, 0 rows affected (0.28 sec)

Copy

SELECT * FROM t1;

+----+----+----+
    | id | c1 | c2 |
    +----+----+----+
    |  1 |  0 |  0 |
    +----+----+----+
    1 row in set (0.00 sec)

## MySQL 兼容性 ##

*  不支持在一条语句中同时添加多列。
 *  不支持将新添加的列设为 `PRIMARY KEY`。
 *  不支持将新添加的列设为 `AUTO_INCREMENT`。
 *  对添加生成列有局限性，具体可参考：[生成列局限性][Link 1]。

## 另请参阅 ##

*  [ADD INDEX][]
 *  [CREATE TABLE][]

[AlterTableStmt]: /images/20221120/0df24182d6384222a820ed42a03f51d6.png
[AlterTableSpec]: /images/20221120/5618eb9a22dd4904ba6eb664bb9def82.png
[ColumnDef]: /images/20221120/26e25ec600f94bc788e2b377b1a87206.png
[ColumnPosition]: /images/20221120/24620d761d9f4f2d8110a9c6deb95da2.png
[Link 1]: https://docs.pingcap.com/zh/tidb/stable/generated-columns#%E7%94%9F%E6%88%90%E5%88%97%E7%9A%84%E5%B1%80%E9%99%90%E6%80%A7
[ADD INDEX]: https://docs.pingcap.com/zh/tidb/stable/sql-statement-add-index
[CREATE TABLE]: https://docs.pingcap.com/zh/tidb/stable/sql-statement-create-table