Type affinity trong SQLite: kiểu cột hoạt động ra sao

Affinity là ưu tiên, không phải quy tắc

SQLite dùng kiểu động. Mỗi giá trị tự mang lớp lưu trữ (storage class) của nó (NULL, INTEGER, REAL, TEXT, BLOB), và kiểu khai báo của cột không giới hạn chặt chẽ những gì bạn có thể đưa vào. Điều kiểu khai báo thực sự làm là gán cho cột một affinity: lớp lưu trữ ưu tiên mà SQLite cố chuyển các giá trị đi vào sang.

Hãy xem chuyện gì xảy ra khi affinity không đủ để chặn một giá trị lệch kiểu:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'apple');
INSERT INTO items VALUES ('two', 'banana');

SELECT id, typeof(id), name FROM items;

Hàng thứ hai lưu chuỗi 'two' vào một cột INTEGER. SQLite đã thử chuyển 'two' thành số, không được (vì nó không phải số), và vẫn lưu nó dưới dạng TEXT. typeof() cho thấy lớp lưu trữ thật của từng giá trị, và nó không phải lúc nào cũng khớp với khai báo của cột.

Điều này khiến người quen Postgres hay MySQL bất ngờ. Nhưng đó là thiết kế có chủ đích.

Năm loại affinity

Mỗi cột trong bảng không phải STRICT có đúng một trong các loại sau:

• TEXT: ưu tiên chuỗi.
• NUMERIC: ưu tiên số, nhưng chấp nhận văn bản nếu không chuyển đổi được.
• INTEGER: giống NUMERIC, nhưng lưu các giá trị không có phần thập phân dưới dạng số nguyên.
• REAL: ưu tiên số thực dấu phẩy động.
• BLOB: không ưu tiên gì, lưu đúng thứ bạn đưa vào.

Affinity BLOB còn được gọi là "không có affinity": đó là thứ bạn nhận được khi không khai báo kiểu nào cả.

CREATE TABLE samples (
    a,                 -- BLOB affinity (no type declared)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

Cùng một đầu vào là chuỗi '42', nhưng ra năm kiểu lưu trữ khác nhau. Mỗi cột chuyển đổi (hoặc không) theo affinity của nó.

SQLite chọn affinity từ khai báo của bạn như thế nào

Đây là phần khiến nhiều người vấp: SQLite không có danh sách cố định các kiểu "hợp lệ". Bạn có thể viết gần như bất cứ thứ gì sau tên cột, và SQLite xác định affinity bằng cách dò tìm chuỗi con trong văn bản đó, theo thứ tự sau:

1. Chứa INT → INTEGER
2. Chứa CHAR, CLOB hoặc TEXT → TEXT
3. Chứa BLOB, hoặc không có kiểu → BLOB
4. Chứa REAL, FLOA hoặc DOUB → REAL
5. Mọi trường hợp khác → NUMERIC

Toàn bộ thuật toán chỉ có vậy. Nó giải thích được rất nhiều điều kỳ quặc:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contains CHAR)
    b BIGINT,             -- INTEGER (contains INT)
    c DOUBLE PRECISION,   -- REAL (contains DOUB)
    d DECIMAL(10,2),      -- NUMERIC (matches none of the above)
    e BOOLEAN,            -- NUMERIC (matches none of the above)
    f DATETIME,           -- NUMERIC (matches none of the above)
    g FLOATING_POINTS,    -- INTEGER (contains INT - surprise!)
    h GIBBERISH           -- NUMERIC (matches none of the above)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS thành INTEGER vì chuỗi con INT xuất hiện trong POINTS. Quy tắc khớp đầu tiên thắng, xét từ trên xuống dưới. Đó là lý do việc sao chép nguyên kiểu từ một cơ sở dữ liệu khác có thể cho ra kết quả khác với điều bạn mong đợi.

Affinity trong thực tế: chuyển đổi khi insert

Affinity quan trọng nhất ở thời điểm SQLite quyết định chuyển đổi giá trị của bạn hay lưu nguyên như cũ. Quy tắc như sau:

• Affinity TEXT: số và BLOB được chuyển thành văn bản.
• Affinity NUMERIC, INTEGER, REAL: văn bản trông giống số được chuyển đổi; văn bản không giống số thì vẫn là văn bản.
• Affinity BLOB: không chuyển đổi gì cả.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

Xét từng hàng:

• '123' trong cột NUMERIC trở thành số nguyên 123. Việc chuyển từ văn bản sang số thành công và không mất dữ liệu.
• '12.5' trở thành số thực 12.5.
• 'hello' trong cột NUMERIC vẫn là văn bản, vì không có số nào để chuyển sang.
• Cột TEXT chuyển các số thành dạng chuỗi của chúng.
• Cột BLOB lưu mọi thứ đúng như được đưa vào, giữ nguyên cả kiểu.

Điểm khác biệt giữa INTEGER và REAL

Affinity INTEGER hoạt động gần như y hệt NUMERIC, chỉ khác một điểm: một giá trị như 3.0, không có phần thập phân thực sự, sẽ được lưu thành số nguyên 3 để tiết kiệm chỗ.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 được lưu thành INTEGER ở cả hai cột, vì tối ưu này cũng áp dụng cho NUMERIC. 3.5 giữ phần thập phân và vẫn là REAL. Điều cần nhớ: đừng dựa vào typeof() để biết một cột được khai báo là INTEGER hay REAL. Nó cho bạn biết thứ thực sự được lưu, và điều đó có thể khác nhau ở từng hàng.

Khi affinity gây rắc rối

Sự linh hoạt này tiện lợi cho đến khi nó không còn tiện nữa. Có hai kiểu lỗi hay gặp trong code thực tế:

1. Dữ liệu xấu lọt vào. Nếu ứng dụng có bug gửi 'N/A' vào một cột INTEGER, SQLite vẫn lưu. Các truy vấn sau đó làm phép tính trên cột này sẽ trả về kết quả lạ hoặc NULL. Không lỗi, không cảnh báo, chỉ là dữ liệu hỏng một cách âm thầm.

2. Phép so sánh trở nên kỳ lạ. Việc sắp xếp và so sánh bằng xử lý các giá trị thuộc lớp lưu trữ khác nhau theo những cách khác nhau:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

Số nguyên được sắp xếp theo giá trị số, sau đó các giá trị văn bản được sắp xếp theo thứ tự từ điển, và chúng đứng sau tất cả các số. Vì vậy bạn nhận được 2, 3, 10 (các số nguyên theo thứ tự số), rồi '20', '100' (các chuỗi theo thứ tự chữ cái). Không phải điều phần lớn mọi người muốn.

Nếu bạn kiểm soát các lệnh insert và kiểm tra dữ liệu cẩn thận, bảng thông thường vẫn ổn. Nếu không, hoặc nếu bạn chỉ muốn cơ sở dữ liệu tự ép kiểu giúp mình, có một lựa chọn tốt hơn.

Tiếp theo: bảng STRICT

SQLite 3.37 thêm bảng STRICT, loại bảng tắt affinity và từ chối các giá trị không khớp với kiểu đã khai báo. Bạn có kiểu động mặc định khi muốn, và ép kiểu chặt chẽ như Postgres khi không muốn. Đó là nội dung của trang tiếp theo.