RETURNING trong SQLite: lấy lại hàng từ INSERT, UPDATE, DELETE

Một cách để xem điều gì vừa xảy ra

Khi bạn chạy INSERT, UPDATE hoặc DELETE, SQLite cho biết có bao nhiêu hàng bị tác động, nhưng không cho biết những hàng nào, hay giá trị cuối cùng của chúng trông ra sao. Cách xoay xở kinh điển là chạy thêm một câu SELECT. Như vậy là hai lượt đi về, hai câu lệnh, và một khe hở nhỏ mà ai đó có thể thay đổi hàng giữa hai lần.

RETURNING giải quyết chuyện đó. Bạn thêm nó vào cuối một câu lệnh ghi, liệt kê các cột muốn lấy lại, và SQLite trả về các hàng bị tác động như thể bạn vừa chạy SELECT trên chúng:

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title) VALUES ('First note')
RETURNING id, title, created_at;

Một câu lệnh, một lượt, và bạn nhận lại id vừa được sinh cùng giá trị mặc định created_at mà cơ sở dữ liệu đã điền cho bạn.

RETURNING được thêm vào SQLite 3.35.0 (tháng 3 năm 2021). Nếu câu lệnh của bạn bị từ chối vì lỗi cú pháp, hãy kiểm tra SELECT sqlite_version();: các bản build cũ không biết từ khóa này.

Lấy lại id vừa được sinh

Lý do phổ biến nhất để dùng RETURNING là lấy khóa chính tự sinh ngay sau khi insert:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('rosa@example.com')
RETURNING id;

Trước khi có RETURNING, bạn phải insert rồi gọi last_insert_rowid() (hoặc hàm tương đương của driver) trên cùng kết nối. Cách đó vẫn chạy được, nhưng nó dựa vào trạng thái của kết nối, dễ sai khi dùng connection pool hoặc nhiều luồng. RETURNING id rõ ràng, gắn liền với câu lệnh, và hoạt động như nhau bất kể kết nối được quản lý ở đâu.

Nếu bảng của bạn không khai báo rõ INTEGER PRIMARY KEY, bạn vẫn lấy được định danh hàng ngầm định:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
RETURNING rowid, event;

Mọi bảng SQLite thông thường đều có rowid, và RETURNING sẽ trả nó về.

Nhiều cột và biểu thức

RETURNING nhận cùng dạng với danh sách cột của SELECT. Liệt kê cột, dùng *, dựng biểu thức, đặt bí danh:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO line_items (product, price, quantity)
VALUES ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * rất tiện khi bạn muốn lấy tất cả, kể cả các giá trị mặc định mà cơ sở dữ liệu đã điền, mà không cần ghi tên từng cột:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login')
RETURNING *;

Bạn thấy id mới, name bạn đã truyền vào, và mốc thời gian mà SQLite đã tính.

RETURNING với UPDATE

Với UPDATE, RETURNING trả về giá trị sau khi cập nhật, tức hàng trông như thế nào sau khi thay đổi của bạn đã được áp dụng:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Ada', 100), ('Boris', 50);

UPDATE accounts
SET balance = balance + 25
WHERE name = 'Ada'
RETURNING id, name, balance;

Bạn nhận lại số dư mới của Ada là 125, không phải 100 cũ. Điều đó khiến RETURNING rất hợp cho bộ đếm nguyên tử và các thao tác cộng trừ tiền: bạn không cần đọc, tính, ghi rồi đọc lại.

Nếu WHERE khớp nhiều hàng, bạn nhận lại một hàng cho mỗi hàng bị tác động:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30);

UPDATE products
SET price = price * 1.1
RETURNING id, name, price;

Ba hàng vào, ba hàng ra. Thứ tự không được đảm bảo; nếu cần một thứ tự cụ thể, hãy sắp xếp kết quả ở phía máy khách.

RETURNING với DELETE

Với DELETE, RETURNING trả về các hàng như chúng vốn có ngay trước khi bị xóa. Hữu ích để lưu trữ, ghi vết kiểm toán, hoặc đơn giản là xác nhận những gì đã bị xóa:

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES
    (1, '2024-01-01'), (2, '2024-01-02'), (3, '2026-01-01');

DELETE FROM sessions
WHERE expires_at < '2025-01-01'
RETURNING id, user_id, expires_at;

Bạn nhận lại hai phiên đã hết hạn với đầy đủ các trường, dù chúng không còn tồn tại trong bảng. Nếu muốn chuyển chúng sang nơi khác, đây là bước chuẩn bị hoàn hảo cho một bảng lưu trữ: đọc kết quả và insert nó vào chỗ khác trong cùng giao dịch.

RETURNING với UPSERT

RETURNING cũng dùng được với INSERT ... ON CONFLICT ... DO UPDATE. Hàng được trả về phản ánh nhánh nào đã chạy: insert mới hay cập nhật do xung đột:

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET value = value + 1
RETURNING name, value;

Chạy câu lệnh đó hai lần. Lần đầu nó insert và trả về ('visits', 1). Lần thứ hai xung đột xảy ra, giá trị được tăng lên, và bạn nhận lại ('visits', 2). Dù thế nào, chỉ một câu lệnh và một hàng trả về, không cần hỏi "nó đã insert hay update?" trước khi làm tiếp.

Đây là mẫu gọn gàng nhất trong SQLite cho yêu cầu "cho tôi giá trị hiện tại, tạo mới nếu cần" mà không phải đi về nhiều lượt.

Vài điều cần biết

Một số chi tiết hay làm người ta bất ngờ:

• RETURNING luôn thấy hàng sau khi thay đổi với INSERT và UPDATE, và trước khi thay đổi với DELETE. Không có cú pháp nào để lấy phía còn lại.
• Thứ tự các hàng trả về không được đảm bảo. Hãy sắp xếp ở phía máy khách nếu thứ tự quan trọng.
• Bạn không thể đặt RETURNING bên trong một truy vấn con. Nó là mệnh đề cấp cao nhất của câu lệnh ghi, không phải một biểu thức.
• RETURNING không trả về dữ liệu đã bị trigger BEFORE sửa đổi; nó trả về các giá trị thật sự được ghi. Trigger AFTER chạy giữa lúc ghi và lúc hàng được trả về.
• Cột được sinh (generated column) và giá trị DEFAULT đều hiện trong kết quả. Đó chính là lý do RETURNING * là cách nhanh để xem cơ sở dữ liệu đã điền gì cho bạn.

Tiếp theo: nhập dữ liệu CSV

RETURNING rất tuyệt khi bạn ghi một hàng hoặc vài hàng mỗi lần và muốn thấy kết quả ngay. Khi nạp hàng nghìn hàng từ một tệp, bạn sẽ dùng các công cụ nhập CSV của SQLite. Đó là trang tiếp theo.