Bài 24: INSERT và cập nhật khi trùng khóa

Câu lạc bộ vừa nhận một học viên mới nên cô Mai cần bổ sung tên vào danh sách. Cô cũng muốn đổi tên một lớp khi nhập lại danh mục cũ. Hai việc đều nhận dữ liệu nhập vào, nhưng cách xử lý khi gặp mã đã có lại khác nhau.

Ghi tên cột để mỗi giá trị có đúng chỗ

Thêm dòng (insert) là tạo một dòng dữ liệu mới trong bảng. Bạn dùng câu lệnh INSERT INTO, ghi tên bảng, rồi chỉ rõ các cột sẽ nhận giá trị. Phần VALUES chứa các giá trị theo đúng thứ tự ấy.

Giả sử học viên mới tên Ngọc, có mã 9, học khối 7 và sống ở Huế. Bạn ấy chưa có xu và chưa được phân công người hướng dẫn. Hãy xem danh sách trước và sau khi thêm:

INSERT INTO students(id, name, grade, city, coins, mentor)
VALUES (9, 'Ngọc', 7, 'Huế', 0, NULL);

Dòng mới có coins bằng 0 và mentor bằng NULL. Số 0 là một số dư đã biết, còn NULL cho biết chưa có thông tin người hướng dẫn. Nhập chuỗi 'NULL' sẽ lưu bốn chữ cái, không diễn tả giá trị chưa biết.

Hãy đọc các cặp cột và giá trị từ trái sang phải. Giá trị đầu thuộc id, giá trị thứ hai thuộc name, rồi tiếp tục cho tới mentor. Nếu đổi thứ tự cột, bạn cũng phải đổi thứ tự giá trị tương ứng.

• Ghi mã học viên vào cột id.
• Ghi chuỗi có dấu nháy đơn vào các cột chữ.
• Ghi số hoặc NULL theo đúng ý nghĩa thông tin cần lưu.

Tên cột làm câu lệnh dễ kiểm tra hơn khi bảng có thêm cột ở một lần thay đổi cấu trúc sau này. Cách bỏ tên cột buộc người đọc nhớ thứ tự của toàn bộ lược đồ. Câu lệnh vẫn có thể chạy, nhưng bạn khó phát hiện một giá trị đặt nhầm chỗ.

Trong bàn thực hành, mỗi lần chạy dùng một bản dữ liệu mẫu mới. Vì vậy, muốn xem dòng vừa thêm bằng truy vấn riêng, hãy đặt truy vấn sau thao tác thêm trong cùng lượt chạy. Ví dụ trong bài độc lập với nhau, không tích lũy thay đổi từ khối trước.

Một câu lệnh có thể thêm cả một lô

Lô dữ liệu (batch) là một nhóm dòng được gửi cùng nhau. Sau VALUES, bạn viết nhiều bộ giá trị, ngăn cách bằng dấu phẩy. Mỗi bộ vẫn phải có đủ giá trị cho danh sách cột đã ghi.

Danh mục hiện có các lớp mã 1, 2, 3. Câu lạc bộ mở thêm hai lớp kỹ năng web, mỗi lớp có một mã riêng:

INSERT INTO classes(id, name)
VALUES (4, 'Web cơ bản'), (5, 'Web nâng cao');

Kết quả có thêm hai dòng mã 4 và 5. Một dấu phẩy giữa các bộ giá trị không tạo ra hai câu lệnh riêng. Cả hai dòng thuộc cùng một câu INSERT, nên bạn cần xem lỗi của cả câu khi một dòng vi phạm ràng buộc.

Với cách thêm mặc định đang dùng, nếu một dòng trùng khóa thì toàn bộ câu lệnh bị hủy. Điều này khác với việc gửi hai câu thêm riêng, trong đó câu đầu có thể đã được lưu. Bạn sẽ học cách nhóm nhiều câu thành một thao tác ở bài 27.

Hãy thử thêm lớp mới cùng một lớp có mã đã tồn tại:

INSERT INTO classes(id, name)
VALUES (4, 'Web'), (1, 'Python mới');

Bảng sau lỗi không có lớp mã 4. SQLite đã hủy cả câu thêm này, dù dòng đầu riêng lẻ không trùng mã. Các lớp có từ trước vẫn còn nguyên, vì lỗi không yêu cầu xóa chúng.

Hãy chạy lại lô vừa bị từ chối, lần này với INSERT OR IGNORE:

INSERT OR IGNORE INTO classes(id, name)
VALUES (4, 'Web'), (1, 'Python mới');

Danh mục có lớp Web mã 4, còn lớp mã 1 vẫn tên Python. Câu lệnh không báo lỗi nhưng chỉ nhận một trong hai dòng. Nếu chỉ nhìn thông báo thành công, cô Mai có thể tưởng tên mới cũng đã được lưu.

Khi nhập danh sách thật, người dùng cần biết bao nhiêu dòng được nhận và dòng nào bị từ chối. Giấu thông báo lỗi không làm dữ liệu đầy đủ hơn. Trước hết, hãy quyết định lô cần được nhận trọn vẹn hay có thể nhận từng dòng.

Khóa trùng có thể dẫn tới cập nhật đã được cho phép

Đôi khi mã đã có không phải lỗi nhập liệu. Cô Mai gửi lại danh mục vì muốn đổi tên lớp, còn mã lớp phải giữ nguyên. Thêm hoặc cập nhật (upsert) chọn cách cập nhật dòng cũ khi xung đột đúng khóa đã chỉ định.

Trong SQLite, bạn đặt ON CONFLICT(id) sau phần thêm để chỉ ra khóa cần xử lý. Nhánh DO UPDATE mô tả cột được sửa. Tên excluded chỉ dòng đang định thêm nhưng gặp xung đột.

Sau đây là lần nhập lại lớp mã 2 với tên mới:

INSERT INTO classes(id, name)
VALUES (2, 'Scratch sáng tạo')
ON CONFLICT(id) DO UPDATE
SET name = excluded.name;

Lớp mã 2 đổi từ Scratch sang Scratch sáng tạo. excluded.name là tên mới trong bộ giá trị, không phải tên cũ đang lưu. Các lớp khác không đổi vì chỉ dòng xung đột mã 2 đi vào nhánh cập nhật.

Nếu mã chưa tồn tại, câu lệnh sẽ thêm dòng mới và không chạy nhánh cập nhật. Vì vậy, cùng một mẫu câu xử lý được danh mục có cả dòng mới và dòng được gửi lại.

Luồng này chỉ mô tả quyết định đối với mã lớp. Nó không nói rằng mọi dữ liệu gửi vào đều hợp lệ. Dòng mới hoặc tên mới vẫn phải thỏa các ràng buộc khác của bảng.

Chỉ xử lý xung đột mà đề bài đã xác định

Danh mục lớp còn yêu cầu tên không trùng nhờ UNIQUE. Xử lý xung đột id không có nghĩa là bỏ qua xung đột ở name. Nếu đặt tên một lớp bằng tên lớp khác, câu lệnh vẫn phải báo lỗi.

Thử thêm mã mới nhưng dùng lại tên Python:

INSERT INTO classes(id, name)
VALUES (4, 'Python')
ON CONFLICT(id) DO UPDATE
SET name = excluded.name;

Lỗi xảy ra ở tính duy nhất của tên, không phải mã lớp. Vì không có xung đột id, nhánh xử lý dành cho mã không giải quyết được lỗi này. Bạn cần sửa tên nhập vào hoặc chọn chính sách khác phù hợp với yêu cầu.

Nếu yêu cầu là giữ tên cũ khi nhận lại mã, dùng DO NOTHING. Cách này giữ nguyên dòng đã tồn tại, nên không thể thông báo rằng tên mới đã được lưu. Hãy so sánh hai chính sách trước khi viết câu lệnh:

Khóa có thể gồm nhiều cột. Với một đăng ký lớp, phải xét cả student_id và class_id, vì một học viên được đăng ký nhiều lớp. Chính sách cập nhật phải chỉ đúng cặp, không gộp mọi đăng ký của một người.

Dung đang tạm dừng lớp Scratch, còn Lan chưa đăng ký lớp nào. Hãy nhập trạng thái đang học cho Dung ở lớp mã 2 và thêm Lan vào lớp mã 1:

INSERT INTO enrollments(student_id, class_id, status)
VALUES (4, 2, 'active'), (6, 1, 'active')
ON CONFLICT(student_id, class_id) DO UPDATE
SET status = excluded.status;

Ở cặp (4, 2), nhánh cập nhật đổi trạng thái của Dung từ paused sang active. Cặp (6, 1) chưa tồn tại nên nhánh thêm tạo đăng ký cho Lan. Cùng một lô đi qua cả hai nhánh, còn những cặp khác giữ nguyên.

Lỗi thường gặp

Tóm tắt

• Danh sách cột chỉ rõ vị trí nhận từng giá trị trong VALUES.
• Một câu thêm nhiều dòng mặc định bị hủy cả câu nếu một dòng vi phạm ràng buộc.
• excluded cung cấp dữ liệu của dòng định thêm cho nhánh cập nhật.
• Xử lý trùng mã không thay thế việc kiểm các ràng buộc còn lại.

Kiểm tra nhanh

Đọc thêm

• SQLite: thêm dòng bằng INSERT.
• SQLite: phạm vi xử lý xung đột của UPSERT.