Thêm cột mới và tạo index cùng lúc với ALTER TABLE trong MySQL

Sử dụng ALTER TABLE với mệnh đề ADD COLUMN và ADD INDEX trong một lệnh để tối ưu hiệu năng (chỉ quét bảng một lần).

Cú pháp

ALTER TABLE tên_bảng 
ADD COLUMN tên_cột kiểu_dữ_liệu, 
ADD INDEX tên_index (tên_cột);

Ví dụ minh họa

Tạo bảng ban đầu:

mysql> CREATE TABLE AddColumnAndIndexDemo (
    -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(100),
    -> Address VARCHAR(200)
    -> );
Query OK, 0 rows affected (0.81 sec)

Kiểm tra cấu trúc:

mysql> DESC AddColumnAndIndexDemo;

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| Id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name    | varchar(100) | YES  |     | NULL    |                |
| Address | varchar(200) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

Thêm cột Age và index cùng lúc:

mysql> ALTER TABLE AddColumnAndIndexDemo 
    -> ADD COLUMN Age INT, 
    -> ADD INDEX (Age);
Query OK, 0 rows affected (1.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

Kiểm tra cấu trúc mới:

mysql> DESC AddColumnAndIndexDemo;

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| Id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name    | varchar(100) | YES  |     | NULL    |                |
| Address | varchar(200) | YES  |     | NULL    |                |
| Age     | int(11)      | YES  | MUL | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

Cột Key = MUL nghĩa là có index (non-unique)

Kiểm tra index chi tiết:

mysql> SHOW INDEX FROM AddColumnAndIndexDemo;

+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table                 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| addcolumnandindexdemo | 0          | PRIMARY  | 1            | Id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               | YES     |
| addcolumnandindexdemo | 1          | Age      | 1            | Age         | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               | YES     |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Các biến thể

-- Thêm cột NOT NULL với DEFAULT
ALTER TABLE t ADD COLUMN status ENUM('active','inactive') NOT NULL DEFAULT 'active', ADD INDEX(status);

-- Thêm UNIQUE INDEX
ALTER TABLE t ADD COLUMN email VARCHAR(100), ADD UNIQUE INDEX idx_email (email);

-- Thêm FOREIGN KEY (cần index trên cột tham chiếu)
ALTER TABLE orders ADD COLUMN user_id INT, ADD INDEX(user_id), 
    ADD FOREIGN KEY (user_id) REFERENCES users(id);

Lợi ích gộp 2 lệnh

• Chỉ khóa bảng (lock) 1 lần
• Chỉ rebuild bảng 1 lần
• Tiết kiệm thời gian trên bảng lớn