ALTER TABLE
,, and
Syntax
ALTER TABLE table_name ADD COLUMN col_name data_type [constraints] | MODIFY COLUMN col_name new_data_type | CHANGE COLUMN old_name new_name data_type | DROP COLUMN col_name | RENAME TO new_table_name;
Parameters
| Parameters | Description | Examples | Level |
|---|---|---|---|
ADD COLUMN |
New | ALTER TABLE users ADD COLUMN phone VARCHAR(20); |
Common |
MODIFY COLUMN |
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) NOT NULL; |
Common | |
CHANGE COLUMN |
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20); |
Common | |
DROP COLUMN |
ALTER TABLE users DROP COLUMN temp_field; |
||
ADD INDEX |
ALTER TABLE users ADD INDEX idx_phone (phone); |
Common | |
RENAME TO |
ALTER TABLE users RENAME TO members; |
Advanced |
Examples
New
-- ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '' AFTER email; -- ALTER TABLE orders ADD COLUMN shipping_fee DECIMAL(8,2) DEFAULT 0.00 AFTER total_amount, ADD COLUMN discount_amount DECIMAL(8,2) DEFAULT 0.00 AFTER shipping_fee;
AFTER New,
-- ALTER TABLE products MODIFY COLUMN description TEXT COMMENT ''; -- ALTER TABLE orders ALTER COLUMN status SET DEFAULT 0;
,
-- MySQL 8.0+ Recommended RENAME COLUMN ALTER TABLE users RENAME COLUMN phone TO mobile; -- MySQL 5.7 CHANGE ALTER TABLE users CHANGE phone mobile VARCHAR(20);
CHANGE New
DDL()
-- MySQL 8.0 INSTANT () ALTER TABLE orders ADD COLUMN remark VARCHAR(500), ALGORITHM=INSTANT; -- INSTANT INPLACE ALTER TABLE orders ADD INDEX idx_remark (remark(100)), ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM=INSTANT,
Common Errors
ERROR 1060 (42S21): Duplicate column name 'xxx',
ERROR 1091 (42000): Can't DROP 'xxx'; check that column/key exists, DESC table
Tips
- ALTER, pt-online-schema-change gh-ost
- MySQL 8.0 INSTANT DDL,
- New