ROWID trong SQLite: khóa chính ẩn, bí danh và WITHOUT ROWID

Mọi bảng đều có một cột bí mật

Tạo một bảng SQLite bình thường và bạn đã có sẵn một cột mà mình không khai báo:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('first', 'hello');
INSERT INTO notes (title, body) VALUES ('second', 'world');

SELECT rowid, title, body FROM notes;

Cột rowid đó là thật. SQLite gán một rowid cho mọi hàng của mọi bảng thông thường, dù bạn có yêu cầu hay không. Đó là một số nguyên có dấu 64 bit, duy nhất trong bảng, và là khóa thật sự mà SQLite dùng để tìm hàng trong cấu trúc lưu trữ B-tree. Hãy xem nó như xương sống của bảng, chỉ mục giữ mọi thứ khác ngăn nắp.

Bạn thường không thấy nó vì SELECT * không bao gồm nó. Bạn phải gọi đích danh mới lấy được.

ROWID có ba bí danh

Vì rowid xuất hiện rất thường xuyên trong SQL viết cho các cơ sở dữ liệu khác, SQLite chấp nhận ba cái tên cho cùng một cột:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid và _rowid_ đều chỉ cùng một cột ẩn. Nếu bạn đã khai báo một cột thật trùng một trong các tên đó, cột của bạn được ưu tiên và bí danh đó không dùng được nữa, nhưng đó là điểm vướng duy nhất. Trong code hằng ngày, cứ viết rowid.

INTEGER PRIMARY KEY là cụm từ thần kỳ

Đây là phần làm mọi người đến từ cơ sở dữ liệu khác vấp. Nếu bạn khai báo một cột đúng là INTEGER PRIMARY KEY, cột đó không được lưu riêng mà trở thành rowid:

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

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid và id là cùng một cột với hai cái tên. Lệnh insert bỏ qua id sẽ nhận một số nguyên được chọn tự động (thường là rowid lớn nhất cộng 1). Đó là lý do INTEGER PRIMARY KEY là cách hiệu quả nhất để cho một bảng khóa tự tăng trong SQLite: không thêm cột, không thêm chỉ mục, chỉ có chính rowid.

Cách viết chính xác rất quan trọng. INT PRIMARY KEY không giống vậy: INT và INTEGER có tác dụng khác nhau ở đây:

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

Ở bảng a, id và rowid khớp nhau. Ở bảng b, id là một cột bình thường và rowid là số nguyên ẩn riêng biệt. Tệ hơn, b.id không tự điền khi insert; nó là NULL cho đến khi bạn gán giá trị. Hãy dùng INTEGER PRIMARY KEY (viết đủ chữ) khi bạn muốn hành vi bí danh.

Lấy ROWID sau khi insert

Sau một lệnh INSERT, bạn thường muốn biết rowid vừa được gán, thường là để liên kết một hàng con với nó. SQLite cung cấp last_insert_rowid():

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('first');
INSERT INTO posts (title) VALUES ('second');

SELECT last_insert_rowid();

Hàm này trả về rowid của lần insert thành công gần nhất trên kết nối hiện tại. Hầu hết driver cơ sở dữ liệu cung cấp cùng giá trị đó dưới dạng cursor.lastrowid hoặc tương tự. Mệnh đề RETURNING (sẽ nói ở phần sau) là một cách khác để lấy lại nó ngay trong chính lệnh insert.

ROWID không vĩnh viễn

Rowid của một hàng ổn định khi hàng còn tồn tại, nhưng nó không phải định danh trọn đời. VACUUM có thể đánh số lại rowid, và nếu bạn xóa một hàng, số đó có thể được dùng lại cho lần insert sau:

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

Hãy để ý rằng hàng mới có thể dùng lại rowid cũ hoặc không, tùy phiên bản và hoàn cảnh. Điểm mấu chốt là bạn không thể dựa vào việc nó duy nhất mãi mãi. Nếu cần một định danh tồn tại qua các lần xóa, vacuum và xuất dữ liệu, hãy khai báo cột INTEGER PRIMARY KEY của riêng bạn (cố định giá trị cho hàng đó) và cân nhắc từ khóa AUTOINCREMENT nếu bạn thật sự cần giá trị tăng đơn điệu và không bao giờ bị dùng lại.

Bảng WITHOUT ROWID

Đôi khi rowid là chi phí bạn không muốn, thường nhất là khi khóa thật của bạn không phải số nguyên. Ví dụ, một bảng thành phố có khóa là tên sẽ có hai cấu trúc: B-tree của rowid, và một chỉ mục riêng trên name để đảm bảo khóa chính. WITHOUT ROWID gộp chúng làm một:

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- error: no such column: rowid

Giờ name là khóa lưu trữ thật sự. Tra cứu theo name bỏ qua một tầng gián tiếp, và bảng nhỏ hơn. Những đánh đổi:

• Không có rowid, oid hay _rowid_: các cột đó đơn giản là không tồn tại.
• last_insert_rowid() không cập nhật khi insert vào bảng này.
• I/O BLOB tăng dần và một vài tính năng sao chép dữ liệu không dùng được.
• Bảng bắt buộc phải khai báo PRIMARY KEY.

WITHOUT ROWID là một tối ưu có cân nhắc, không phải mặc định. Hãy dùng nó khi khóa chính không phải số nguyên và bảng lớn hoặc ghi nhiều. Với các bảng có khóa số nguyên thông thường, cách bố trí rowid thông thường đã là tối ưu.

Cách hình dung

Rút gọn lại những điều cốt lõi:

• Mọi bảng SQLite thông thường đều có một khóa số nguyên 64 bit ẩn tên là rowid.
• INTEGER PRIMARY KEY (viết chính xác) biến cột của bạn thành bí danh của nó.
• Dùng last_insert_rowid() để đọc giá trị vừa được gán.
• Rowid có thể được dùng lại sau khi xóa và được đánh số lại bởi VACUUM.
• Bảng WITHOUT ROWID bỏ khóa ẩn và dùng trực tiếp khóa chính bạn khai báo: hữu ích với khóa không phải số nguyên, nhưng phải từ bỏ một số tính năng.

Phần lớn thời gian bạn chẳng cần nghĩ tới rowid. Bạn khai báo id INTEGER PRIMARY KEY, để SQLite lo việc đánh số, rồi làm tiếp. Cơ chế này chỉ quan trọng khi bạn tinh chỉnh lưu trữ, đọc các schema có sẵn, hoặc thắc mắc vì sao INT PRIMARY KEY hoạt động khác INTEGER PRIMARY KEY.

Tiếp theo: NOT NULL và DEFAULT

Giờ danh tính của hàng đã rõ, lớp tiếp theo là đảm bảo các cột còn lại chứa giá trị hợp lý. NOT NULL và DEFAULT là hai mệnh đề làm phần lớn việc đó, và chúng là nội dung tiếp theo.