DROP COLUMN

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

语法图

AlterTableStmt

DROP COLUMN - 图1

DropColumnSpec

DROP COLUMN - 图2

ColumnName

DROP COLUMN - 图3

  1. AlterTableStmt
  2. ::= 'ALTER' 'IGNORE'? 'TABLE' TableName DropColumnSpec ( ',' DropColumnSpec )*
  3. DropColumnSpec
  4. ::= 'DROP' 'COLUMN'? 'IF EXISTS'? ColumnName ( 'RESTRICT' | 'CASCADE' )?
  5. ColumnName
  6. ::= Identifier ( '.' Identifier ( '.' Identifier )? )?

示例

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

MySQL 兼容性

  • 目前不支持删除主键列或组合索引相关列。

另请参阅