Bảng STRICT trong SQLite: kiểm tra kiểu cột chặt chẽ

Vì sao có bảng STRICT

Cách SQLite xử lý kiểu dữ liệu theo mặc định nổi tiếng là dễ dãi. Khai báo một cột INTEGER, thêm chuỗi "hello" vào, SQLite cũng chẳng phàn nàn và cứ thế lưu chuỗi đó. Sự linh hoạt này là một lựa chọn thiết kế có chủ đích từ những năm 90, nhưng nó khiến người quen dùng Postgres hay MySQL bất ngờ, và nó che giấu bug.

Bảng STRICT, được thêm vào từ SQLite 3.37, khắc phục điều đó. Bạn bật chế độ này cho từng bảng, và từ đó trở đi, kiểu của cột mang đúng ý nghĩa mà nó ghi.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

Từ khóa STRICT đặt sau dấu ngoặc đóng. Mọi thứ khác trông như một câu CREATE TABLE bình thường. Khác biệt lộ ra ngay khi bạn cố đặt sai loại giá trị vào một cột.

STRICT thực sự kiểm tra những gì

Trong bảng thường, type affinity cố chuyển giá trị sang kiểu đã khai báo, và nếu không được thì lưu nguyên trạng. Trong bảng STRICT, sai kiểu là lỗi.

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- ok
INSERT INTO accounts (balance) VALUES ('100');   -- ok, '100' converts cleanly
INSERT INTO accounts (balance) VALUES ('oops');  -- error

Thử cùng các câu lệnh đó với một bảng không STRICT, câu thêm thứ ba vẫn thành công: SQLite vui vẻ lưu chuỗi 'oops' vào một cột bạn đã khai báo là INTEGER. Vài tháng sau, một truy vấn tổng hợp trả về kết quả vô nghĩa và bạn mất cả buổi chiều để truy tìm. STRICT khiến lỗi xảy ra ngay lúc thêm dữ liệu, nơi bạn có thể sửa nó.

Lỗi bạn sẽ thấy:

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

Rõ ràng, tức thì, khó mà bỏ qua.

Năm kiểu được phép

Bảng STRICT chỉ chấp nhận năm tên kiểu:

• INTEGER: số nguyên.
• REAL: số thực dấu phẩy động.
• TEXT: chuỗi.
• BLOB: byte thô.
• ANY: mọi kiểu, không chuyển đổi.

Chỉ vậy thôi. Các bí danh tùy tiện mà SQLite thường chấp nhận như VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT đều gây lỗi bên trong bảng STRICT:

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

Lỗi:

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

Cách sửa là dùng một trong năm tên kiểu chuẩn. VARCHAR(255) thành TEXT, DATETIME thành TEXT (dù sao SQLite cũng lưu ngày tháng dưới dạng chuỗi ISO), BOOLEAN thành INTEGER (với 0 và 1).

Lối thoát ANY

ANY là kiểu duy nhất cho phép một cột STRICT chứa các giá trị khác kiểu nhau, hữu ích cho những trường hợp như cột value chung chung trong một bảng khóa/giá trị:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY đặc biệt bên trong bảng STRICT: nó lưu giá trị mà không ép kiểu như cùng từ đó sẽ ngụ ý ở chỗ khác. Chuỗi '100' vẫn là chuỗi; số nguyên 100 vẫn là số nguyên. Các lời gọi typeof() trong truy vấn trên chứng minh điều đó.

Trong bảng không STRICT, một cột có affinity ANY sẽ ép các chuỗi trông giống số thành số. STRICT giữ nguyên chính xác kiểu ban đầu.

STRICT và PRIMARY KEY

Một khác biệt tinh tế: trong bảng thường, INTEGER PRIMARY KEY là trường hợp đặc biệt: nó trở thành bí danh của rowid và chỉ chấp nhận số nguyên. Các khai báo khóa chính khác thì lỏng lẻo hơn.

Trong bảng STRICT, kiểu của cột được kiểm tra bất kể nó có phải khóa chính hay không:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- 42 converts to '42'? No - error.

SELECT * FROM products;

Câu thêm thứ hai thất bại. Trong bảng không STRICT, 42 sẽ được lặng lẽ lưu vào cột khóa chính TEXT. Ở đây, bạn sẽ được báo lỗi.

Kết hợp bảng STRICT và không STRICT

STRICT áp dụng cho từng bảng, không phải cho cả cơ sở dữ liệu. Bạn có thể có một bảng users chặt chẽ và một bảng events dễ dãi trong cùng một tệp. Khóa ngoại giữa chúng vẫn hoạt động như bình thường.

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- no type declared; affinity rules apply
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

Bảng events không có STRICT và cột payload không khai báo kiểu, nên nó chấp nhận bất cứ thứ gì bạn đưa vào. Thỉnh thoảng hữu ích, nhưng rủi ro nếu dùng làm mặc định. Hãy chỉ dùng cột không kiểu khi bạn thực sự cần một cột chứa đủ thứ.

Khi nào dùng STRICT

Với schema mới, câu trả lời là "gần như luôn luôn". Cái giá rất nhỏ: một từ khóa cho mỗi bảng và nhớ năm tên kiểu chuẩn. Lợi ích là những bug vốn thường ẩn nấp trong dữ liệu sẽ lộ ra ngay tại câu lệnh thêm đã gây ra chúng.

Bỏ qua STRICT khi:

• Bạn đang bảo trì một cơ sở dữ liệu SQLite cũ mà schema hiện có dựa vào kiểu dễ dãi.
• Bạn nhắm đến SQLite cũ hơn 3.37 (tháng 10 năm 2021): từ khóa này chưa tồn tại ở đó.
• Bạn thực sự muốn một cột chứa nhiều kiểu; kể cả khi đó, hãy ưu tiên STRICT kèm một cột ANY thay vì bảng không STRICT, vì mọi thứ khác vẫn được kiểm tra.

Danh sách kiểm tra ngắn khi chuyển một bảng thường sang STRICT:

• Thay VARCHAR, CHAR, NVARCHAR bằng TEXT.
• Thay DOUBLE, FLOAT, NUMERIC bằng REAL.
• Thay BOOLEAN, BIT, TINYINT bằng INTEGER.
• Thay DATETIME, TIMESTAMP, DATE bằng TEXT (hoặc INTEGER nếu bạn lưu unix timestamp).
• Thêm STRICT sau dấu ngoặc đóng.

Tiếp theo: khóa chính

Bảng STRICT siết chặt cách các cột lưu dữ liệu. Điều tiếp theo đáng siết chặt là cột nào định danh mỗi hàng, và khóa chính trong SQLite có vài điểm lạ (nhất là quanh INTEGER PRIMARY KEY và rowid) đáng biết trước khi bạn thiết kế một schema thật.