📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

ALTER TABLE

此语句用于修改现有表以符合新的表结构。ALTER TABLE 可以用来:

概述

AlterTableStmt
ALTERIgnoreOptionalTABLETableNameAlterTableSpecListOptAlterTablePartitionOptANALYZEPARTITIONPartitionNameListINDEXIndexNameListAnalyzeOptionListOptCOMPACTPARTITIONPartitionNameListTIFLASHREPLICA
TableName
Identifier.Identifier
AlterTableSpec
TableOptionListSETTIFLASHREPLICALengthNumLocationLabelListCONVERTTOCharsetKwCharsetNameDEFAULTOptCollateADDColumnKeywordOptIfNotExistsColumnDefColumnPosition(TableElementList)ConstraintPARTITIONIfNotExistsNoWriteToBinLogAliasOptPartitionDefinitionListOptPARTITIONSNUMCHECKTRUNCATEPARTITIONOPTIMIZEREPAIRREBUILDPARTITIONNoWriteToBinLogAliasOptAllOrPartitionNameListCOALESCEPARTITIONNoWriteToBinLogAliasOptNUMDROPColumnKeywordOptIfExistsColumnNameRestrictOrCascadeOptPRIMARYKEYPARTITIONIfExistsPartitionNameListKeyOrIndexIfExistsCHECKIdentifierFOREIGNKEYSymbolEXCHANGEPARTITIONIdentifierWITHTABLETableNameWithValidationOptIMPORTDISCARDPARTITIONAllOrPartitionNameListTABLESPACEREORGANIZEPARTITIONNoWriteToBinLogAliasOptReorganizePartitionRuleOptORDERBYAlterOrderItem,DISABLEENABLEKEYSMODIFYColumnKeywordOptIfExistsCHANGEColumnKeywordOptIfExistsColumnNameColumnDefColumnPositionALTERColumnKeywordOptColumnNameSETDEFAULTSignedLiteral(Expression)DROPDEFAULTCHECKIdentifierEnforcedOrNotINDEXIdentifierVISIBLEINVISIBLERENAMECOLUMNKeyOrIndexIdentifierTOIdentifierTO=ASTableNameLockClauseAlgorithmClauseFORCEWITHWITHOUTVALIDATIONSECONDARY_LOADSECONDARY_UNLOADAUTO_INCREMENTAUTO_ID_CACHEAUTO_RANDOM_BASESHARD_ROW_ID_BITSEqOptLengthNumCACHENOCACHETTLEqOptTimeColumnName+INTERVALExpressionTimeUnitTTLEnableEqOptONOFFREMOVETTLTTLEnableEqOptONOFFTTLJobIntervalEqOptstringLitPlacementPolicyOption
PlacementPolicyOption
PLACEMENTPOLICYEqOptPolicyNamePLACEMENTPOLICYEqOptSETDEFAULT

示例

创建一个带有初始数据的表:

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL); INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 0 rows affected (0.11 sec) Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0

以下查询需要进行全表扫描,因为列 c1 没有建立索引:

EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+----------+-----------+---------------+--------------------------------+ | TableReader_7 | 10.00 | root | | data:Selection_6 | | └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t1.c1, 3) | | └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +-------------------------+----------+-----------+---------------+--------------------------------+ 3 rows in set (0.00 sec)

可以使用 ALTER TABLE .. ADD INDEX 来为 t1 表添加索引。EXPLAIN 确认,原始查询现在使用索引范围扫描,效率更高:

ALTER TABLE t1 ADD INDEX (c1); EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
Query OK, 0 rows affected (0.30 sec) +------------------------+---------+-----------+------------------------+---------------------------------------------+ | id | estRows | task | access object | operator info | +------------------------+---------+-----------+------------------------+---------------------------------------------+ | IndexReader_6 | 10.00 | root | | index:IndexRangeScan_5 | | └─IndexRangeScan_5 | 10.00 | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo | +------------------------+---------+-----------+------------------------+---------------------------------------------+ 2 rows in set (0.00 sec)

TiDB 支持断言 DDL 更改使用特定的 ALTER 算法。注意,这只是一个断言,并不会改变实际用于修改表的算法:

ALTER TABLE t1 DROP INDEX c1, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.24 sec)

在需要 INPLACE 算法的操作上使用 ALGORITHM=INSTANT 断言会导致语句错误:

ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot alter table by INSTANT. Try ALGORITHM=INPLACE.

然而,在 INPLACE 操作中使用 ALGORITHM=COPY 断言会产生警告而非错误。这是因为 TiDB 将断言理解为 this algorithm or better。这种行为差异对于 MySQL 兼容性很有用,因为 TiDB 使用的算法可能与 MySQL 不同:

ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=COPY; SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.25 sec) +-------+------+---------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------+ | Error | 1846 | ALGORITHM=COPY is not supported. Reason: Cannot alter table by COPY. Try ALGORITHM=INPLACE. | +-------+------+---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

MySQL 兼容性

在 TiDB 中,ALTER TABLE 主要限制如下:

  • 当在单个 ALTER TABLE 语句中修改多个 schema 对象时:

    • 不支持对同一对象进行多次修改。
    • TiDB 在执行前会验证表的 schema 在执行之前。例如,执行 ALTER TABLE t ADD COLUMN c1 INT, ADD COLUMN c2 INT AFTER c1; 时会报错,因为表中不存在列 c1
    • 对于 ALTER TABLE 语句,TiDB 的执行顺序是从左到右逐个执行每个变更,这在某些情况下与 MySQL 不兼容。
  • 不支持对主键列进行 Reorg-Data 类型的变更。

  • 不支持对分区表的列类型变更。

  • 不支持对生成列的列类型变更。

  • 由于 TiDB 和 MySQL 在 CAST 函数行为上的兼容性问题,不支持某些数据类型(例如部分 TIME、Bit、Set、Enum 和 JSON 类型)的变更。

  • 不支持空间数据类型。

  • ALTER TABLE t CACHE | NOCACHE 是 TiDB 对 MySQL 语法的扩展。详情请参见 Cached Tables

关于更多限制,请参见 MySQL Compatibility

相关链接

文档内容是否有帮助?