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
ALTERIGNORETABLETableNameAddColumnSpec,
TableName
Identifier.Identifier
AddColumnSpec
ADDCOLUMNIF NOT EXISTSColumnNameColumnTypeColumnOptionFIRSTAFTERColumnName
ColumnType
NumericTypeStringTypeDateAndTimeTypeSERIAL
ColumnOption
NOTNULLAUTO_INCREMENTPRIMARYKEYCLUSTEREDNONCLUSTEREDGLOBALLOCALUNIQUEKEYGLOBALLOCALDEFAULTNowSymOptionFractionSignedLiteralNextValueForSequenceSERIALDEFAULTVALUEONUPDATENowSymOptionFractionCOMMENTstringLitCONSTRAINTIdentifierCHECK(Expression)NOTENFORCEDNULLGENERATEDALWAYSAS(Expression)VIRTUALSTOREDREFERENCESTableName(IndexPartSpecificationList)MatchOnDeleteUpdateOptCOLLATECollationNameCOLUMN_FORMATColumnFormatSTORAGEStorageMediaAUTO_RANDOM(LengthNum)

Examples

mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO t1 VALUES (NULL); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM t1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 ADD COLUMN c1 INT NOT NULL; Query OK, 0 rows affected (0.28 sec) mysql> SELECT * FROM t1; +----+----+ | id | c1 | +----+----+ | 1 | 0 | +----+----+ 1 row in set (0.00 sec) mysql> ALTER TABLE t1 ADD c2 INT NOT NULL AFTER c1; Query OK, 0 rows affected (0.28 sec) mysql> SELECT * FROM t1; +----+----+----+ | id | c1 | c2 | +----+----+----+ | 1 | 0 | 0 | +----+----+----+ 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.
  • Setting a global index by specifying PRIMARY KEY or UNIQUE INDEX as GLOBAL when you add a new column is a TiDB extension for partitioned tables and is not compatible with MySQL.

See also

Was this page helpful?