ADD COLUMN

The ALTER TABLE.. ADD COLUMN statement adds a column to an existing table. This operation is online in TiDB, which means that neither reads or writes to the table are blocked by adding a column.

Synopsis

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 ')' )?

Examples

  1. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
  2. Query OK, 0 rows affected (0.11 sec)
  4. mysql> INSERT INTO t1 VALUES (NULL);
  5. Query OK, 1 row affected (0.02 sec)
  7. mysql> SELECT * FROM t1;
  8. +----+
  9. | id |
  10. +----+
  11. |  1 |
  12. +----+
  13. 1 row in set (0.00 sec)
  15. mysql> ALTER TABLE t1 ADD COLUMN c1 INT NOT NULL;
  16. Query OK, 0 rows affected (0.28 sec)
  18. mysql> SELECT * FROM t1;
  19. +----+----+
  20. | id | c1 |
  21. +----+----+
  22. |  1 |  0 |
  23. +----+----+
  24. 1 row in set (0.00 sec)
  26. mysql> ALTER TABLE t1 ADD c2 INT NOT NULL AFTER c1;
  27. Query OK, 0 rows affected (0.28 sec)
  29. mysql> SELECT * FROM t1;
  30. +----+----+----+
  31. | id | c1 | c2 |
  32. +----+----+----+
  33. |  1 |  0 |  0 |
  34. +----+----+----+
  35. 1 row in set (0.00 sec)

MySQL compatibility

  • Adding a new column and setting it to the PRIMARY KEY is not supported.
  • Adding a new column and setting it to AUTO_INCREMENT is not supported.
  • There are limitations on adding generated columns, refer to: generated column limitations.

See also