DROP COLUMN

DROP COLUMN 语句用于从指定的表中删除列。在 TiDB 中,COLUMN 为在线操作,不会阻塞表中的数据读写。

语法图

AlterTableStmt
ALTERIgnoreOptionalTABLETableNameAlterTableSpecListOptAlterTablePartitionOptANALYZEPARTITIONPartitionNameListINDEXIndexNameListAnalyzeOptionListOpt
AlterTableSpec
TableOptionListSETTIFLASHREPLICALengthNumLocationLabelListCONVERTTOCharsetKwCharsetNameDEFAULTOptCollateADDColumnKeywordOptIfNotExistsColumnDefColumnPosition(TableElementList)ConstraintPARTITIONIfNotExistsNoWriteToBinLogAliasOptPartitionDefinitionListOptPARTITIONSNUMCHECKTRUNCATEPARTITIONOPTIMIZEREPAIRREBUILDPARTITIONNoWriteToBinLogAliasOptAllOrPartitionNameListCOALESCEPARTITIONNoWriteToBinLogAliasOptNUMDROPColumnKeywordOptIfExistsColumnNameRestrictOrCascadeOptPRIMARYKEYPARTITIONIfExistsPartitionNameListKeyOrIndexIfExistsCHECKIdentifierFOREIGNKEYIfExistsSymbolEXCHANGEPARTITIONIdentifierWITHTABLETableNameWithValidationOptIMPORTDISCARDPARTITIONAllOrPartitionNameListTABLESPACEREORGANIZEPARTITIONNoWriteToBinLogAliasOptReorganizePartitionRuleOptORDERBYAlterOrderItem,DISABLEENABLEKEYSMODIFYColumnKeywordOptIfExistsCHANGEColumnKeywordOptIfExistsColumnNameColumnDefColumnPositionALTERColumnKeywordOptColumnNameSETDEFAULTSignedLiteral(Expression)DROPDEFAULTCHECKIdentifierEnforcedOrNotINDEXIdentifierIndexInvisibleRENAMECOLUMNKeyOrIndexIdentifierTOIdentifierTO=ASTableNameLockClauseAlgorithmClauseFORCEWITHWITHOUTVALIDATIONSECONDARY_LOADSECONDARY_UNLOAD
ColumnName
Identifier.Identifier.Identifier

示例

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
INSERT INTO t1 (col1,col2) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
SELECT * FROM t1;
+----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 5 | 5 | 5 | +----+------+------+ 5 rows in set (0.01 sec)
ALTER TABLE t1 DROP COLUMN col1, DROP COLUMN col2;
ERROR 1105 (HY000): can't run multi schema change
SELECT * FROM t1;
+----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 5 | 5 | 5 | +----+------+------+ 5 rows in set (0.00 sec)
ALTER TABLE t1 DROP COLUMN col1;
Query OK, 0 rows affected (0.27 sec)
SELECT * FROM t1;
+----+------+ | id | col2 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec)

MySQL 兼容性

  • 目前不支持在一条语句中同时删除多个列。
  • 目前不支持删除主键列或组合索引相关列。

另请参阅