• ALTER TABLE
    • 语法图
    • 示例
    • MySQL 兼容性
    • 另请参阅

    ALTER TABLE

    ALTER TABLE 语句用于对已有表进行修改,以符合新表结构。ALTER TABLE 语句可用于:

    • ADDDROP,或 RENAME 索引
    • ADDDROPMODIFYCHANGE

    语法图

    AlterTableStmt:

    AlterTableStmt

    TableName:

    TableName

    AlterTableSpec:

    AlterTableSpec

    示例

    1. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, c1 INT NOT NULL);
    2. Query OK, 0 rows affected (0.11 sec)
    3. mysql> INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
    4. Query OK, 5 rows affected (0.03 sec)
    5. Records: 5 Duplicates: 0 Warnings: 0
    6. mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
    7. +---------------------+----------+------+-------------------------------------------------------------+
    8. | id | count | task | operator info |
    9. +---------------------+----------+------+-------------------------------------------------------------+
    10. | TableReader_7 | 10.00 | root | data:Selection_6 |
    11. | └─Selection_6 | 10.00 | cop | eq(test.t1.c1, 3) |
    12. | └─TableScan_5 | 10000.00 | cop | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
    13. +---------------------+----------+------+-------------------------------------------------------------+
    14. 3 rows in set (0.00 sec)
    15. mysql> ALTER TABLE t1 ADD INDEX (c1);
    16. Query OK, 0 rows affected (0.30 sec)
    17. mysql> EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
    18. +-------------------+-------+------+-----------------------------------------------------------------+
    19. | id | count | task | operator info |
    20. +-------------------+-------+------+-----------------------------------------------------------------+
    21. | IndexReader_6 | 10.00 | root | index:IndexScan_5 |
    22. | └─IndexScan_5 | 10.00 | cop | table:t1, index:c1, range:[3,3], keep order:false, stats:pseudo |
    23. +-------------------+-------+------+-----------------------------------------------------------------+
    24. 2 rows in set (0.00 sec)

    MySQL 兼容性

    • 支持除空间类型外的所有数据类型。
    • 不支持 FULLTEXTHASHSPATIAL 索引。

    另请参阅

    • ADD COLUMN
    • DROP COLUMN
    • RENAME COLUMN
    • ADD INDEX
    • DROP INDEX
    • RENAME INDEX
    • CREATE TABLE
    • DROP TABLE
    • SHOW CREATE TABLE