Sửa AUTO_INCREMENT bị đứt (1,3,5 → 1,2,3) trong MySQL

Vấn đề: Khắc phục lỗ hổng AUTO_INCREMENT sau khi xóa hàng

Sau khi xóa ID 2, 4, bảng còn ID 1, 3, 5. Cần đánh số lại liên tục 1, 2, 3.

Giải pháp: UPDATE với biến phiên bản (User-defined Variable)

Dùng biến @increment_value tăng dần theo thứ tự ID cũ.

1. Tạo bảng và dữ liệu

CREATE TABLE DemoTable (
    UserId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO DemoTable VALUES (), (), (), (), (); -- ID 1,2,3,4,5

2. Xóa ID 2 và 4

DELETE FROM DemoTable WHERE UserId IN (2, 4);

Còn lại: 1, 3, 5.

3. Cập nhật lại ID liên tục

SET @increment_value := 0;
UPDATE DemoTable 
SET UserId = (@increment_value := @increment_value + 1)
ORDER BY UserId;

4. Kết quả: 1, 2, 3

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

Giải thích

• SET @increment_value := 0 – Khởi tạo biến = 0.
• @increment_value := @increment_value + 1 – Tăng và gán giá trị mới cho UserId.
• ORDER BY UserId – Đảm bảo xử lý theo thứ tự ID cũ tăng dần (1,3,5 → thành 1,2,3).

CẢNH BÁO NGHIÊM TRỌNG

• Không bao giờ làm trên Production! Thay đổi khóa chính (Primary Key) sẽ phá vỡ tham chiếu khóa ngoại (Foreign Key) từ bảng khác.
• Chỉ áp dụng cho bảng độc lập, không có FK, hoặc môi trường test/dev.
• Nếu bảng có FK, phải DISABLE FOREIGN_KEY_CHECKS, cập nhật cả bảng con, rồi bật lại – cực kỳ rủi ro.
• AUTO_INCREMENT gap (lỗ hổng) là bình thường, không ảnh hưởng hiệu suất. Không cần "sửa".

Cách an toàn hơn: Tạo bảng mới

CREATE TABLE NewTable LIKE DemoTable;
INSERT INTO NewTable (UserName, ...) SELECT UserName, ... FROM DemoTable ORDER BY UserId;
RENAME TABLE DemoTable TO OldTable, NewTable TO DemoTable;

ID sẽ tự động sinh liên tục 1,2,3... Giữ nguyên dữ liệu khác.