Cột sinh tự động trong SQLite: VIRTUAL và STORED qua ví dụ

Cột sinh tự động là cột được tính toán

Cột sinh tự động (generated column) là cột có giá trị đến từ một biểu thức, không phải từ lệnh INSERT. Bạn khai báo công thức một lần trong CREATE TABLE, phần còn lại SQLite lo. Bạn không bao giờ ghi vào cột này; thử làm vậy sẽ gây lỗi.

Ví dụ ngắn nhất có thể:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total chưa bao giờ được thêm vào, nhưng nó xuất hiện ở mọi hàng. SQLite tính lại nó từ price + tax mỗi khi bạn đọc hàng. Cập nhật một trong hai cột và total sẽ thay đổi theo.

Cụm từ khóa GENERATED ALWAYS AS là bắt buộc. ALWAYS chỉ là hình thức theo chuẩn SQL: SQLite không có lựa chọn nào khác.

VIRTUAL và STORED

Mỗi cột sinh tự động thuộc một trong hai loại. Mặc định là VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- recomputed on every SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- computed once and saved to disk
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

Cách hình dung:

• VIRTUAL: không tốn byte nào trên đĩa, tốn CPU mỗi lần đọc. Thêm dễ, sau này đổi cũng dễ.
• STORED: tốn dung lượng đĩa, đọc không tốn thêm gì. Đáng dùng khi biểu thức tốn kém hoặc cột được đọc thường xuyên hơn ghi rất nhiều.

Nếu không ghi từ khóa nào, bạn sẽ nhận VIRTUAL. Đó gần như luôn là lựa chọn mặc định đúng.

Dùng để làm gì? Giá trị suy ra có thể đánh index

Tính năng đắt giá nhất là bạn có thể đặt index trên cột sinh tự động. Nhờ đó bạn tra cứu nhanh trên các giá trị suy ra mà không phải viết lại từng truy vấn.

Giả sử bạn muốn tra cứu email không phân biệt hoa thường:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

Index bao phủ dạng chữ thường. Truy vấn lọc theo email_lower dùng index trực tiếp. SQLite cũng có index trên biểu thức (CREATE INDEX ... ON users(lower(email))), nhưng cột sinh tự động khiến giá trị suy ra hiện ra như một cột thật mà bạn có thể SELECT, tham chiếu trong view và dùng lại từ code ứng dụng.

Lấy giá trị ra từ JSON

Cột sinh tự động phát huy tác dụng rõ nhất khi đi cùng JSON. Hỗ trợ JSON của SQLite cho bạn ->> để trích một giá trị đơn; bọc nó trong một cột sinh tự động là bạn có một trường có kiểu, có thể đánh index, nằm trên một khối dữ liệu linh hoạt.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

Với truy vấn của bạn, user_id và kind trông như các cột bình thường, nhưng dữ liệu nằm trong payload. Thay đổi JSON, các cột sẽ cập nhật theo. Index trên user_id giúp việc tra cứu nhanh.

Quy tắc và ràng buộc

Vài điều SQLite bắt buộc, nên biết trước khi đụng phải:

• Biểu thức phải tất định (deterministic). Không được dùng random(), datetime('now') hay các hàm không tất định khác. Giá trị phải tái tạo được từ chính hàng đó.
• Biểu thức chỉ được tham chiếu các cột trong cùng hàng. Không truy vấn con, không hàm tổng hợp, không bảng khác.
• Bạn không thể INSERT hay UPDATE trực tiếp một cột sinh tự động. INSERT INTO products (total) VALUES (5) sẽ báo lỗi.
• Không thể thêm cột STORED bằng ALTER TABLE ... ADD COLUMN. Chỉ cột VIRTUAL mới thêm được sau khi tạo bảng.
• Cột sinh tự động có thể có ràng buộc NOT NULL, CHECK, UNIQUE và cả FOREIGN KEY. Về những mặt đó, chúng hoạt động như mọi cột khác.

Minh họa nhanh quy tắc về ghi:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

Cách sửa là bỏ cột sinh tự động khỏi danh sách cột của INSERT và để SQLite tự tính.

Chọn VIRTUAL hay STORED

Quyết định thường phụ thuộc vào tỷ lệ đọc/ghi và chi phí của biểu thức:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Cheap arithmetic, fine to recompute every read.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Heavier expression, queried often, written rarely → store it.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

Kinh nghiệm thực tế:

• Mặc định dùng VIRTUAL. Nó không tốn gì lúc ghi và ổn với gần như mọi trường hợp.
• Chuyển sang STORED khi bạn đánh index cho cột trên một bảng ghi nhiều (dù sao index cũng cần giá trị được lưu lại), hoặc khi biểu thức thực sự tốn kém.
• Đừng quá đắn đo. Loại cột là một phần của cấu trúc bảng, nhưng bạn có thể xóa và tạo lại cột nếu đổi ý, ít nhất là với VIRTUAL.

Cột sinh tự động và view

Cột sinh tự động có phần chồng lấn với view: cả hai đều đưa ra giá trị tính toán mà không lưu chúng (ừm, đôi khi thôi). Cách phân chia thường là:

• Cột sinh tự động thuộc về một hàng và một bảng. Dùng nó cho các phép suy ra theo từng hàng: định dạng email, trích một trường JSON, tính tổng.
• View là một truy vấn được lưu lại. Dùng nó khi phép tính cần join, tổng hợp hoặc lọc trên nhiều hàng.

Bạn có thể kết hợp cả hai. Một view có thể SELECT từ bảng có cột sinh tự động và join thêm thông tin khác. Cột sinh tự động nằm ở tầng lưu trữ; view nằm ở tầng truy vấn.

Tiếp theo: ATTACH DATABASE

Cột sinh tự động giúp một bảng tự tính giá trị của chính nó. Trang tiếp theo đi theo hướng ngược lại: kết nối nhiều cơ sở dữ liệu SQLite cùng lúc bằng ATTACH DATABASE, để một truy vấn có thể trải rộng trên nhiều tệp.