Important

You are viewing the documentation of TiDB v6.0 (DMR). PingCAP does not provide bug fixes for v6.0. Any bugs will be fixed in future releases.

For general purposes, please 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
ALTERIgnoreOptionalTABLETableNameAlterTableSpecListOptAlterTablePartitionOptANALYZEPARTITIONPartitionNameListINDEXIndexNameListAnalyzeOptionListOpt
AlterTableSpec
TableOptionListSETTIFLASHREPLICALengthNumLocationLabelListCONVERTTOCharsetKwCharsetNameDEFAULTOptCollateADDColumnKeywordOptIfNotExistsColumnDefColumnPosition(TableElementList)ConstraintPARTITIONIfNotExistsNoWriteToBinLogAliasOptPartitionDefinitionListOptPARTITIONSNUMCHECKTRUNCATEPARTITIONOPTIMIZEREPAIRREBUILDPARTITIONNoWriteToBinLogAliasOptAllOrPartitionNameListCOALESCEPARTITIONNoWriteToBinLogAliasOptNUMDROPColumnKeywordOptIfExistsColumnNameRestrictOrCascadeOptPRIMARYKEYPARTITIONIfExistsPartitionNameListKeyOrIndexIfExistsCHECKIdentifierFOREIGNKEYIfExistsSymbolEXCHANGEPARTITIONIdentifierWITHTABLETableNameWithValidationOptIMPORTDISCARDPARTITIONAllOrPartitionNameListTABLESPACEREORGANIZEPARTITIONNoWriteToBinLogAliasOptReorganizePartitionRuleOptORDERBYAlterOrderItem,DISABLEENABLEKEYSMODIFYColumnKeywordOptIfExistsCHANGEColumnKeywordOptIfExistsColumnNameColumnDefColumnPositionALTERColumnKeywordOptColumnNameSETDEFAULTSignedLiteral(Expression)DROPDEFAULTCHECKIdentifierEnforcedOrNotINDEXIdentifierIndexInvisibleRENAMECOLUMNKeyOrIndexIdentifierTOIdentifierTO=ASTableNameLockClauseAlgorithmClauseFORCEWITHWITHOUTVALIDATIONSECONDARY_LOADSECONDARY_UNLOAD
ColumnDef
ColumnNameTypeSERIALColumnOptionListOpt
ColumnPosition
FIRSTAFTERColumnName

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?