Important

You are viewing the documentation of an older version of the TiDB database (TiDB v5.4).

It is recommended that you use the latest LTS version of the TiDB database.

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_INCREMENTPRIMARYKEYCLUSTEREDNONCLUSTEREDUNIQUEKEYDEFAULTNowSymOptionFractionSignedLiteralNextValueForSequenceSERIALDEFAULTVALUEONUPDATENowSymOptionFractionCOMMENTstringLitCONSTRAINTIdentifierCHECK(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 multiple columns at the same time in a statement is currently not supported.
  • 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

Was this page helpful?