Câu lệnh ALTER TABLE trong MySQL

Câu lệnh ALTER TABLE trong MySQL được sử dụng để thay đổi cấu trúc của một bảng hiện có mà không cần xóa và tạo lại bảng. Các thay đổi có thể bao gồm thêm cột mới, thay đổi kiểu dữ liệu, thêm hoặc xóa các ràng buộc, và nhiều hơn nữa. Hướng dẫn này sẽ bao gồm từ cơ bản đến nâng cao với các minh họa chi tiết về câu lệnh ALTER TABLE.

1. Thêm cột mới vào bảng

Cú pháp cơ bản:

ALTER TABLE table_name
ADD COLUMN column_name data_type;

Ví dụ:

Giả sử có bảng employees:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

Bây giờ bạn muốn thêm cột email vào bảng employees:

ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

2. Thêm nhiều cột mới cùng lúc

Cú pháp:

ALTER TABLE table_name
ADD COLUMN column1 data_type,
ADD COLUMN column2 data_type;

Ví dụ:

Thêm cột address và phone_number vào bảng employees:

ALTER TABLE employees
ADD COLUMN address VARCHAR(255),
ADD COLUMN phone_number VARCHAR(15);

3. Xóa cột khỏi bảng

Cú pháp:

ALTER TABLE table_name
DROP COLUMN column_name;

Ví dụ:

Xóa cột address khỏi bảng employees:

ALTER TABLE employees
DROP COLUMN address;

4. Đổi tên cột

Cú pháp:

ALTER TABLE table_name
CHANGE COLUMN old_name new_name data_type;

Ví dụ:

Đổi tên cột phone_number thành contact_number:

ALTER TABLE employees
CHANGE COLUMN phone_number contact_number VARCHAR(20);

5. Thay đổi kiểu dữ liệu của cột

Cú pháp:

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

Ví dụ:

Thay đổi kiểu dữ liệu của cột contact_number từ VARCHAR(20) thành INT:

ALTER TABLE employees
MODIFY COLUMN contact_number INT;

6. Đổi tên bảng

Cú pháp:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Ví dụ:

Đổi tên bảng employees thành staff:

ALTER TABLE employees
RENAME TO staff;

7. Thêm ràng buộc FOREIGN KEY

Cú pháp:

ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES other_table (other_column);

Ví dụ:

Giả sử có bảng departments:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

Thêm khóa ngoại department_id vào bảng employees, tham chiếu đến bảng departments:

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

8. Xóa ràng buộc FOREIGN KEY

Cú pháp:

ALTER TABLE table_name
DROP FOREIGN KEY fk_name;

Ví dụ:

Xóa ràng buộc khóa ngoại fk_department khỏi bảng employees:

ALTER TABLE employees
DROP FOREIGN KEY fk_department;

9. Thêm INDEX cho một cột

Cú pháp:

ALTER TABLE table_name
ADD INDEX index_name (column_name);

Ví dụ:

Thêm chỉ mục (INDEX) cho cột email để cải thiện tốc độ truy vấn:

ALTER TABLE employees
ADD INDEX idx_email (email);

10. Xóa INDEX

Cú pháp:

ALTER TABLE table_name
DROP INDEX index_name;

Ví dụ:

Xóa chỉ mục idx_email khỏi bảng employees:

ALTER TABLE employees
DROP INDEX idx_email;

11. Thêm UNIQUE INDEX

Cú pháp:

ALTER TABLE table_name
ADD UNIQUE (column_name);

Ví dụ:

Đảm bảo rằng giá trị của cột email là duy nhất:

ALTER TABLE employees
ADD UNIQUE (email);

12. Thêm hoặc xóa ràng buộc PRIMARY KEY

• Thêm PRIMARY KEY:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

• Xóa PRIMARY KEY:

ALTER TABLE table_name
DROP PRIMARY KEY;

Ví dụ:

• Thêm khóa chính PRIMARY KEY cho cột id:

ALTER TABLE employees
ADD PRIMARY KEY (id);

• Xóa khóa chính PRIMARY KEY:

ALTER TABLE employees
DROP PRIMARY KEY;

13. Thêm cột với giá trị mặc định (DEFAULT)

Cú pháp:

ALTER TABLE table_name
ADD COLUMN column_name data_type DEFAULT default_value;

Ví dụ:

Thêm cột status với giá trị mặc định là 'active':

ALTER TABLE employees
ADD COLUMN status VARCHAR(20) DEFAULT 'active';

14. Thay đổi giá trị mặc định của cột

Cú pháp:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Ví dụ:

Thay đổi giá trị mặc định của cột status thành 'inactive':

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'inactive';

15. Xóa giá trị mặc định của cột

Cú pháp:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Ví dụ:

Xóa giá trị mặc định của cột status:

ALTER TABLE employees
ALTER COLUMN status DROP DEFAULT;

16. Tạo và xóa cột ảo (VIRTUAL)

Cột ảo trong MySQL là cột mà giá trị của nó được tính toán từ các cột khác trong bảng.

Cú pháp tạo cột ảo:

ALTER TABLE table_name
ADD COLUMN virtual_column AS (expression) VIRTUAL;

Ví dụ:

Thêm cột full_name là kết quả ghép giữa cột first_name và last_name:

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

Cú pháp xóa cột ảo:

ALTER TABLE table_name
DROP COLUMN virtual_column;

17. Thêm cột có kiểu dữ liệu JSON

Cú pháp:

ALTER TABLE table_name
ADD COLUMN column_name JSON;

Ví dụ:

Thêm cột extra_data kiểu dữ liệu JSON để lưu trữ các thông tin bổ sung:

ALTER TABLE employees
ADD COLUMN extra_data JSON;

18. Thay đổi loại ENGINE của bảng

Cú pháp:

ALTER TABLE table_name ENGINE = engine_type;

Ví dụ:

Thay đổi loại ENGINE của bảng từ InnoDB sang MyISAM:

ALTER TABLE employees ENGINE = MyISAM;

19. Phân vùng lại bảng (PARTITIONING)

Phân vùng bảng có thể giúp quản lý dữ liệu lớn hiệu quả hơn. Bạn có thể thêm, xóa, hoặc thay đổi phân vùng bảng với câu lệnh ALTER TABLE.

Thêm phân vùng:

ALTER TABLE employees
PARTITION BY RANGE (YEAR(hire_date)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Xóa phân vùng:

ALTER TABLE employees
REMOVE PARTITIONING;

20. Xóa bảng nếu tồn tại

Đây là câu lệnh nâng cao để xóa bảng chỉ khi bảng đó tồn tại, giúp tránh lỗi nếu bảng đã bị xóa trước đó:

DROP TABLE IF EXISTS table_name;

Ví dụ:

DROP TABLE IF EXISTS employees;

Những ví dụ trên bao gồm hầu hết các thao tác với ALTER TABLE từ cơ bản đến nâng cao, giúp bạn quản lý cấu trúc bảng MySQL một cách hiệu quả trong các tình huống khác nhau.

Các ví dụ nâng cao khác

Dưới đây là các ví dụ nâng cao khác về câu lệnh ALTER TABLE trong MySQL, giúp bạn hiểu rõ hơn về những tính năng mạnh mẽ và phức tạp của nó.

21. Chuyển đổi cột thành cột tạo tự động (AUTO_INCREMENT)

Trong một số tình huống, bạn có thể muốn chuyển một cột hiện tại thành cột tự động tăng giá trị (AUTO_INCREMENT). Điều này rất hữu ích khi bạn muốn cột ID tự động tăng mà không cần thay đổi cấu trúc bảng hiện tại.

Cú pháp:

ALTER TABLE table_name
MODIFY COLUMN column_name data_type AUTO_INCREMENT;

Ví dụ:

Giả sử cột id trong bảng employees hiện không phải là AUTO_INCREMENT. Bây giờ, bạn muốn thay đổi nó thành tự động tăng:

ALTER TABLE employees
MODIFY COLUMN id INT AUTO_INCREMENT;

22. Thêm và xóa ràng buộc CHECK

CHECK là một loại ràng buộc cho phép bạn đặt điều kiện cho dữ liệu được phép nhập vào một cột cụ thể.

Cú pháp thêm CHECK:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Ví dụ:

Thêm một ràng buộc CHECK để đảm bảo rằng tuổi của nhân viên phải lớn hơn 18:

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age > 18);

Xóa ràng buộc CHECK:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Ví dụ:

ALTER TABLE employees
DROP CONSTRAINT chk_age;

23. Chuyển đổi bảng sang dạng không có khóa ngoại (FOREIGN KEY)

Trong một số trường hợp, bạn có thể muốn xóa tất cả các khóa ngoại khỏi bảng, ví dụ như khi bạn cần xóa bảng hoặc thực hiện một quá trình migration mà không bị khóa ngoại ngăn chặn.

Xóa tất cả các khóa ngoại:

Để xóa tất cả các ràng buộc khóa ngoại, bạn cần phải xác định các khóa ngoại hiện có trong bảng và sau đó xóa chúng:

ALTER TABLE employees
DROP FOREIGN KEY fk_department;

24. Thay đổi vị trí cột trong bảng

MySQL cho phép bạn thay đổi vị trí của các cột trong bảng, điều này có thể hữu ích trong việc sắp xếp lại cấu trúc bảng cho dễ đọc hơn.

Cú pháp:

ALTER TABLE table_name
MODIFY COLUMN column_name data_type AFTER another_column;

Ví dụ:

Giả sử bạn muốn di chuyển cột email sau cột name trong bảng employees:

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(255) AFTER name;

Nếu bạn muốn di chuyển cột email lên đầu bảng, bạn có thể sử dụng từ khóa FIRST:

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(255) FIRST;

25. Thêm phân vùng HASH cho bảng

Phân vùng theo HASH thường được sử dụng để phân tán dữ liệu đều qua các phân vùng. Điều này rất hữu ích khi làm việc với các bảng có lượng dữ liệu lớn, giúp tối ưu hiệu suất truy vấn.

Cú pháp:

ALTER TABLE table_name
PARTITION BY HASH (expression) PARTITIONS number;

Ví dụ:

Phân vùng bảng employees theo id thành 4 phân vùng:

ALTER TABLE employees
PARTITION BY HASH(id) PARTITIONS 4;

26. Thêm phân vùng LIST cho bảng

Phân vùng theo LIST giúp phân chia bảng dựa trên danh sách các giá trị cụ thể. Ví dụ, bạn có thể phân vùng theo quốc gia hoặc khu vực địa lý.

Cú pháp:

ALTER TABLE table_name
PARTITION BY LIST (column_name) (
    PARTITION p0 VALUES IN (value1, value2),
    PARTITION p1 VALUES IN (value3, value4)
);

Ví dụ:

Phân vùng bảng employees dựa trên giá trị của cột country:

ALTER TABLE employees
PARTITION BY LIST (country) (
    PARTITION p1 VALUES IN ('USA', 'Canada'),
    PARTITION p2 VALUES IN ('UK', 'Australia'),
    PARTITION p3 VALUES IN ('India', 'China')
);

27. Chuyển đổi kiểu nén (ROW_FORMAT) cho bảng

MySQL cung cấp các kiểu nén khác nhau cho các bảng, giúp tối ưu hóa việc sử dụng dung lượng lưu trữ và hiệu suất truy vấn. Bạn có thể sử dụng ALTER TABLE để thay đổi định dạng lưu trữ của bảng.

Cú pháp:

ALTER TABLE table_name
ROW_FORMAT = row_format_type;

Ví dụ:

Chuyển bảng employees sang sử dụng định dạng lưu trữ COMPRESSED để tiết kiệm dung lượng:

ALTER TABLE employees
ROW_FORMAT = COMPRESSED;

28. Thêm hoặc xóa ràng buộc UNIQUE cho nhiều cột

Bạn có thể tạo một ràng buộc UNIQUE trên nhiều cột để đảm bảo rằng sự kết hợp của các giá trị trong các cột này là duy nhất.

Cú pháp:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

Ví dụ:

Thêm ràng buộc UNIQUE cho cột email và department_id trong bảng employees:

ALTER TABLE employees
ADD CONSTRAINT unique_email_department UNIQUE (email, department_id);

29. Thêm khóa ngoại với hành vi ON DELETE CASCADE

Khi bạn thêm một khóa ngoại, bạn có thể chỉ định hành vi khi dữ liệu liên quan bị xóa. Tùy chọn ON DELETE CASCADE sẽ tự động xóa các bản ghi liên quan khi một bản ghi trong bảng chính bị xóa.

Cú pháp:

ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES other_table (other_column)
ON DELETE CASCADE;

Ví dụ:

Thêm khóa ngoại department_id vào bảng employees, với hành vi ON DELETE CASCADE:

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;

30. Thay đổi định dạng lưu trữ của bảng (TABLESPACE)

Bạn có thể thay đổi TABLESPACE để lưu trữ dữ liệu bảng trên các ổ đĩa hoặc hệ thống khác nhau, tùy thuộc vào nhu cầu của hệ thống.

Cú pháp:

ALTER TABLE table_name
TABLESPACE tablespace_name STORAGE DISK;

Ví dụ:

Chuyển bảng employees sang sử dụng TABLESPACE khác:

ALTER TABLE employees
TABLESPACE my_tablespace STORAGE DISK;

Những ví dụ nâng cao này giúp bạn nắm vững các tính năng mạnh mẽ của câu lệnh ALTER TABLE trong MySQL. Khi quản lý các bảng lớn và phức tạp, bạn có thể tận dụng các tính năng này để tối ưu hóa hiệu suất và quản lý dữ liệu một cách hiệu quả hơn.

• Câu Lệnh Trong MySQL
• MySQL