ADD COLUMN

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

语法图

AlterTableStmt
ALTERIgnoreOptionalTABLETableNameAlterTableSpecListOptAlterTablePartitionOptANALYZEPARTITIONPartitionNameListINDEXIndexNameListAnalyzeOptionListOpt
AlterTableSpec
TableOptionListSETTIFLASHREPLICALengthNumLocationLabelListCONVERTTOCharsetKwCharsetNameDEFAULTOptCollateADDColumnKeywordOptIfNotExistsColumnDefColumnPosition(TableElementList)ConstraintPARTITIONIfNotExistsNoWriteToBinLogAliasOptPartitionDefinitionListOptPARTITIONSNUMCHECKTRUNCATEPARTITIONOPTIMIZEREPAIRREBUILDPARTITIONNoWriteToBinLogAliasOptAllOrPartitionNameListCOALESCEPARTITIONNoWriteToBinLogAliasOptNUMDROPColumnKeywordOptIfExistsColumnNameRestrictOrCascadeOptPRIMARYKEYPARTITIONIfExistsPartitionNameListKeyOrIndexIfExistsCHECKIdentifierFOREIGNKEYIfExistsSymbolEXCHANGEPARTITIONIdentifierWITHTABLETableNameWithValidationOptIMPORTDISCARDPARTITIONAllOrPartitionNameListTABLESPACEREORGANIZEPARTITIONNoWriteToBinLogAliasOptReorganizePartitionRuleOptORDERBYAlterOrderItem,DISABLEENABLEKEYSMODIFYColumnKeywordOptIfExistsCHANGEColumnKeywordOptIfExistsColumnNameColumnDefColumnPositionALTERColumnKeywordOptColumnNameSETDEFAULTSignedLiteral(Expression)DROPDEFAULTCHECKIdentifierEnforcedOrNotINDEXIdentifierIndexInvisibleRENAMECOLUMNKeyOrIndexIdentifierTOIdentifierTO=ASTableNameLockClauseAlgorithmClauseFORCEWITHWITHOUTVALIDATIONSECONDARY_LOADSECONDARY_UNLOAD
ColumnKeywordOpt
COLUMN
ColumnDef
ColumnNameTypeSERIALColumnOptionListOpt
ColumnPosition
FIRSTAFTERColumnName

示例

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 兼容性

  • 不支持同时添加多列。
  • 不支持将新添加的列设为 PRIMARY KEY
  • 不支持将新添加的列设为 AUTO_INCREMENT

另请参阅

下载 PDF
产品
TiDB
TiDB Cloud
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.