Trong SQLite, hãy ưu tiên dùng bảng STRICT

 (evanhahn.com)
1 điểm bởi GN⁺ 3 giờ trước | 1 bình luận | Chia sẻ qua WhatsApp
  • Thêm STRICT vào cuối định nghĩa bảng để chặn sớm lỗi kiểu dữ liệu như văn bản tùy ý bị đưa vào cột số nguyên, từ đó tăng tính toàn vẹn dữ liệu
  • Khi chèn hoặc cập nhật, SQLite sẽ kiểm tra kiểu dữ liệu, nhưng vẫn cho phép các giá trị có thể chuyển đổi không mất mát như '123'; kiểu cột bị giới hạn ở INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Với các cột cần chứa nhiều kiểu dữ liệu, có thể dùng ANY, nhờ đó vừa áp dụng kiểm tra nghiêm ngặt vừa giữ được sự linh hoạt lưu trữ trong cùng một bảng
  • Không thể chuyển ngay bảng hiện có sang STRICT, nên cần tạo bảng mới và sao chép dữ liệu; dữ liệu cũ không đúng kiểu phải được dọn dẹp hoặc ép kiểu
  • Bảng STRICT chỉ được hỗ trợ từ SQLite 3.37.0 trở lên; về lý thuyết có thêm chi phí kiểm tra, nhưng trong các thử nghiệm không chính thức không thấy khác biệt rõ rệt về hiệu năng hay kích thước tệp

Tạo bảng STRICT

  • Bảng STRICT của SQLite áp dụng kiểm tra kiểu dữ liệu nghiêm ngặt hơn, tương tự các SQL engine khác
  • Chỉ cần thêm STRICT vào cuối câu lệnh CREATE TABLE
CREATE TABLE people (name TEXT) STRICT;

Kiểm tra kiểu khi chèn và cập nhật

  • Bảng SQLite thông thường có thể lưu văn bản như 'garbage' ngay cả trong cột INTEGER, nhưng bảng STRICT sẽ coi không khớp kiểu là lỗi
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- xử lý bình thường

CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- lỗi: cannot store TEXT value in INTEGER column
  • Cùng một cơ chế kiểm tra cũng áp dụng cho UPDATE, nên khi thay đổi giá trị sau khi lưu vẫn có thể chặn kiểu không hợp lệ
  • Tuy vậy, nếu giá trị có thể được chuyển đổi mà không mất mát thì vẫn được phép ngay cả trong bảng STRICT
    • Chuỗi '123' có thể được chuyển hoàn toàn thành số nguyên 123, nên hai câu lệnh chèn dưới đây được xử lý như nhau
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);

Giới hạn kiểu ở bước định nghĩa bảng

  • Với bảng thông thường, SQLite vẫn cho phép dùng cả những tên kiểu mà nó không hỗ trợ trong khai báo cột
  • Với bảng STRICT, nếu dùng tên kiểu không được hỗ trợ thì sẽ phát sinh lỗi ngay ở bước tạo bảng
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
  • Các kiểu cột được phép chỉ gồm INT, INTEGER, REAL, TEXT, BLOB, ANY
  • Mọi cột đều phải có kiểu, nên không thể bỏ qua kiểu như CREATE TABLE tbl (name)

Giữ tính linh hoạt với ANY

  • Nếu cần lưu dữ liệu có kiểu không cố định, bạn có thể dùng cột ANY
  • Ngay cả trong bảng STRICT, cột ANY vẫn có thể lưu mọi kiểu như số nguyên, văn bản, số thực, BLOB...
CREATE TABLE tbl (value ANY) STRICT;

INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');

Di chuyển bảng hiện có

  • Không có cách nào dùng ALTER để chuyển trực tiếp bảng không nghiêm ngặt hiện có sang bảng STRICT, nên cách đơn giản hơn là tạo bảng nghiêm ngặt ngay từ đầu
  • Để chuyển đổi bảng hiện có, cần đi qua quy trình tạo bảng STRICT mới → sao chép dữ liệu → thay thế bảng cũ
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
  • Nếu dữ liệu hiện có bị sai kiểu, chẳng hạn cột số nguyên lại chứa văn bản, quá trình sao chép có thể báo lỗi
    • Trước khi migrate, có thể cần dọn dữ liệu hoặc dùng CAST để ép kiểu
  • Cũng có thể áp dụng STRICT chỉ cho bảng mới, nhưng khi đó mức độ kiểm tra sẽ khác nhau giữa các bảng, khiến hành vi khó đoán hơn so với trường hợp tất cả đều lỏng

Khi kiểu linh hoạt phù hợp hơn

  • Nhóm phát triển SQLite cũng có một bài riêng về ưu điểm của kiểu linh hoạt, trong đó nêu các trường hợp mà hành vi mặc định là hữu ích
  • Các trường hợp dùng bảng không nghiêm ngặt có thể là hợp lý gồm:
    • Kho lưu trữ key-value thuần túy chứa nguyên trạng nhiều kiểu dữ liệu
    • Nơi lưu các thuộc tính phụ trợ khác có kiểu dữ liệu khác nhau
    • Trường hợp nhập trực tiếp CSV lộn xộn và cần giữ nguyên cả các giá trị không hợp lệ thay vì làm mất chúng
  • Tuy nhiên, các kiểu dữ liệu ngoài dự kiến có thể gây ra những lỗi tinh vi, nên với bảng thông thường, việc báo lỗi ngay thường có lợi hơn là âm thầm chấp nhận
  • Trong mã nguồn SQLite cũng có một chú thích gọi bảng không nghiêm ngặt là legacy, nhưng đây khó có thể xem là căn cứ đáng tin cậy hơn tài liệu chính thức

Tương thích phiên bản

  • Bảng STRICT được giới thiệu trong SQLite 3.37.0 phát hành vào tháng 11 năm 2021
  • Các phiên bản SQLite cũ hơn không thể dùng bảng STRICT
  • Phiên bản cũ cũng không đọc được cơ sở dữ liệu có chứa bảng STRICT
    • Nếu tạo bảng STRICT bằng SQLite mới rồi mở cùng cơ sở dữ liệu đó bằng SQLite 3.36.0, sẽ phát sinh lỗi

Hiệu năng và dung lượng lưu trữ

  • Bảng STRICT kiểm tra thêm kiểu dữ liệu khi chèn hoặc cập nhật, nên về lý thuyết có thể chậm hơn
  • Trong các thử nghiệm không chính thức với việc chèn hàng triệu dòng vào một bảng có 100 cột, trên nhiều máy khác nhau đều không thấy khác biệt hiệu năng rõ rệt
  • Kích thước tệp cơ sở dữ liệu trên đĩa cũng giống nhau, dù đây không phải benchmark chặt chẽ nên vẫn có thể có khác biệt chưa được phát hiện
  • Việc ngăn các giá trị không phù hợp với column affinity bị lưu nhầm thậm chí có thể cải thiện hiệu năng, nhưng chưa được kiểm chứng riêng

Những điểm cần cân nhắc khi áp dụng

  • Bảng STRICT không giải quyết mọi vấn đề dữ liệu, nhưng giúp giảm lỗi liên quan đến kiểu và tăng tính toàn vẹn dữ liệu
  • Trong đa số trường hợp, chỉ cần thêm STRICT vào định nghĩa bảng nên việc áp dụng khá đơn giản
  • Với các bảng thông thường, lợi ích từ kiểm tra kiểu nghiêm ngặt thường lớn hơn gánh nặng migrate, việc không tương thích với phiên bản cũ và sự linh hoạt bị hạn chế

Bài viết liên quan

1 bình luận

 
GN⁺ 3 giờ trước
Ý kiến trên Hacker News

  • Từ thực tế là SQLite không có chức năng ALTER bảng để chuyển sang chế độ nghiêm ngặt, nên phải sao chép dữ liệu từ bảng không nghiêm ngặt sang bảng nghiêm ngặt, sqlite-utils 4.1 đã được bổ sung tính năng chuyển đổi hai chiều
    Trên CLI có thể dùng uvx sqlite-utils transform data.db mytable --strict, còn trong Python có thể dùng db.table("mytable").transform(strict=True)
    Có thể xem ghi chú phát hành tại https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., tài liệu Python API tại https://sqlite-utils.datasette.io/en/stable/python-api.html#..., và tài liệu CLI tại https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  • https://sqlite.org/flextypegood.html giải thích vì sao kiểu dữ liệu linh hoạt là mặc định và có lẽ sẽ không thay đổi trong tương lai, nhưng điều này không khớp với trải nghiệm thực tế
    Trái với lập luận rằng ngay cả khi đưa tên khách hàng vào Customer.creditScore kiểu số nguyên thì cũng có thể dễ dàng phát hiện và sửa, trên thực tế các hàng dữ liệu bị hỏng rất khó khôi phục và dữ liệu có thể đã biến mất hoàn toàn
    Cũng rất khó đồng ý với logic cho rằng kiểm tra kiểu nghiêm ngặt chỉ ngăn được những lỗi dễ tìm, nên lại khiến việc phát hiện và sửa lỗi khó hơn

    • Thật khó hiểu quan điểm cho rằng sự an toàn và độ tin cậy từ một cấu hình cơ sở dữ liệu đáng tin cậy lại khiến việc sửa lỗi dữ liệu trở nên khó hơn
      Với Postgres, người ta thường thêm càng nhiều kiểm tra và cơ chế an toàn càng tốt, để không phải đi tìm những lỗi vốn dĩ không nên xảy ra về sau
    • MongoDB cũng từng có lập luận tương tự rằng có thể lưu bất cứ thứ gì, nhưng phần lớn mọi người sau khi dùng thực tế đều nhận ra đó thường là một đặc tính tệ
      Điều này trông giống hệ quả của bối cảnh thời điểm SQLite ra đời và niềm tin rất mạnh của tác giả hơn là một nguyên tắc kỹ thuật nghiêm ngặt; sau nhiều chỉ trích, vẫn tạo cảm giác cố giữ quan điểm bằng mọi lý lẽ có thể
      Có thể cung cấp mức linh hoạt mong muốn bằng các kiểu như JSON hoặc HSTORE của Postgres, nhưng gần như lúc nào cũng tốt hơn nếu cho phép chọn khi cần, thay vì mặc định ép buộc kiểu dữ liệu không giới hạn

  • Sẽ tốt hơn nếu STRICT là mặc định
    Ngoài điểm này ra, các nhà phát triển SQLite là những người xuất sắc đã tạo ra một công cụ tuyệt vời

    • Ngay cả khóa ngoại cũng không được bật mặc định mà phải dùng PRAGMA foreign_keys = ON;
      Vấn đề lớn hơn là với bảng nghiêm ngặt không có pragma tương đương, nên phải thêm STRICT không chuẩn vào mọi câu lệnh CREATE TABLE
      Một pragma STRICT toàn cục cũng từng được xem xét nhưng chưa được triển khai; có thể xem thêm tại https://sqlite.org/foreignkeys.html và https://sqlite.org/forum/forumpost/1b9d073a37ca5998
    • SQLite coi trọng khả năng tương thích ngược nên hầu như không thay đổi các giá trị mặc định
      Mục đích là tránh trường hợp phần mềm cho SQLite 3.53 sau khi nâng cấp lên 3.54 thì CREATE TABLE đột nhiên tạo bảng nghiêm ngặt, phát sinh lỗi và làm hỏng toàn bộ hệ thống
    • Ràng buộc khóa ngoại cũng có vấn đề tương tự khi bị tắt mặc định vì lý do tương thích
      Điều đó khiến người ta tự hỏi liệu đã từng có thời SQLite chỉ hỗ trợ cú pháp khóa ngoại mà chưa thực sự triển khai chức năng này hay không
    • Cũng cần có kiểu ngày tháng·dấu thời gian đúng nghĩa, thay vì chỉ là chuỗi đơn thuần
    • Không có lý do gì để cơ sở dữ liệu cho phép vô tình chèn sai kiểu dữ liệu, và triết lý an toàn kiểu dữ liệu của SQLite thật đáng tiếc
      Đã từng phải dọn dẹp dữ liệu khi mã được triển khai trên hàng nghìn thiết bị lưu chuỗi '1' và '0' vào cột boolean, và đó hoàn toàn không phải trải nghiệm dễ chịu
      Cũng vì không có kiểu timestamp nên phải lưu vào cột văn bản, và ngay cả các hàm ngày giờ tiêu chuẩn cũng tạo ra yyyy-mm-dd HH:MM:SS với giả định ngầm là UTC, thay vì định dạng ISO yyyy-mm-ddTHH:MM:SSZ
      SQLite thực sự là một dự án tuyệt vời, nhưng một số quyết định thiết kế vẫn gây khó hiểu

  • Từ góc nhìn của môi trường SQL cho doanh nghiệp, tôi chưa từng xem SQLite một cách nghiêm túc vì kiểu của trường không bị ép buộc theo mặc định, và cũng từng thấy ngạc nhiên khi nó trở thành nền tảng cho metadata của ứng dụng điện thoại thông minh
    Điều này gợi nhớ đến câu chuyện cũ trong mạng máy tính: chọn UDP vì độ trễ thấp và sự đơn giản, rồi tự xây lại phần lớn các tính năng đảm bảo độ tin cậy của TCP ở tầng ứng dụng

    • Tự triển khai thì có thể làm khác TCP và đôi khi đạt hiệu quả lớn, QUIC và HTTP/3 là ví dụ điển hình
      Nhưng việc tự triển khai kiểm tra kiểu trên SQLite không nghiêm ngặt dường như không mang lại ưu thế tương tự
    • Khóa ngoại thậm chí còn chưa được hỗ trợ trước năm 2009 và đến giờ vẫn không được bật mặc định: https://sqlite.org/foreignkeys.html
    • Cách làm này đôi khi vẫn có thể thành công trong kinh doanh thực tế: https://aeron.io/
    • Có thể hiểu thái độ không xem trọng SQLite nếu việc kiểm tra kiểu không được hỗ trợ đầy đủ, nhưng hầu hết cơ sở dữ liệu khác cũng không cung cấp cấu hình mặc định hoàn hảo
      PostgreSQL cũng có giá trị mặc định hướng đến hệ thống cấu hình thấp nên muốn đạt hiệu năng tối ưu vẫn phải tinh chỉnh
      Ngoài ra, TCP dựa trên luồng nên nhiều ứng dụng gặp vấn đề HOL blocking, và nếu tự xây tầng tin cậy trên UDP thì sau rất nhiều công sức vẫn có thể cho kết quả tốt hơn TCP
    • Nếu quan tâm đến Jeep hay Bronco thì bạn sẽ không tìm đến một nhà phê bình ô tô phổ thông
      Họ sẽ chê ồn và khó điều khiển, nhưng đó là vì họ tiếp cận một sản phẩm vốn nhắm đến mục đích khác như thể chỉ trường hợp sử dụng của họ mới quan trọng

  • Bảng nghiêm ngặt có nhược điểm là không dùng được một số kiểu như Date, nhưng vẫn nên là mặc định
    Nếu nhiều ứng dụng cùng chia sẻ một cơ sở dữ liệu thì phải có thể tin vào kiểu đã khai báo; chỉ cần một ứng dụng lưu chuỗi vào cột số là các ứng dụng còn lại đều có thể hỏng
    Ngược lại, với cơ sở dữ liệu nhúng là trường hợp sử dụng chính của SQLite, thường chỉ có một ứng dụng dùng nó, nên việc có thể tiến hóa schema thay vì phải tạo cơ sở dữ liệu mới rồi sao chép dữ liệu cũng là một lợi thế
    Khi đó mã ứng dụng biết phải kỳ vọng điều gì ở từng cột, kể cả khi có kiểu dữ liệu lẫn lộn

    • SQLite chỉ có 5 kiểu dữ liệu là INTEGER, TEXT, BLOB, REAL, NUMERIC: https://sqlite.org/datatype3.html
    • SQLite không có kiểu dữ liệu ngày tháng, và cũng không có cách gọi EXPLAIN trên một truy vấn SELECT bất kỳ để lấy ra tên kiểu đã khai báo về mặt hình thức
      Vì vậy dù dùng các tên như DATE hay DATETIME thì cũng không thể suy ra được
    • Date không phải kiểu thật, mà chỉ tạo ra một cột có numeric affinity

  • Chế độ nghiêm ngặt lại cản trở hệ thống kiểu chặt chẽ hơn ở tầng ứng dụng, vì nó giới hạn cách ghi kiểu cột khiến không thể dùng những tên có ý nghĩa hơn, và khiến mã ánh xạ giữa kiểu của cơ sở dữ liệu với kiểu của ứng dụng không thể tận dụng những tên đó
    Xem thêm tại https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type=comment, và nếu bạn dùng các crate như sqlx của Rust để làm việc với cơ sở dữ liệu thì có lẽ nên tránh chế độ nghiêm ngặt

  • Tôi muốn kiểu dữ liệu nghiêm ngặt cho cơ sở dữ liệu và RPC, nhưng SQLite có mục đích hơi khác, và nếu dùng thử trực tiếp thì có thể bạn sẽ hiểu hơn tài liệu https://sqlite.org/flextypegood.html
    Ví dụ, mục tiêu để một script bất kỳ vốn không viết riêng cho SQLite vẫn tình cờ chạy được là một đặc điểm mà các DBMS khác thường không tính đến

    • Mục ưu và nhược điểm trong tài liệu đó bỏ sót luận điểm cốt lõi
      Ưu điểm quan trọng nhất của việc định kiểu linh hoạt là có thể tiến hóa schema dễ dàng
      Khi yêu cầu của một cơ sở dữ liệu nhúng thay đổi, bạn có thể chỉnh schema ngay tại chỗ thay vì phải tạo cơ sở dữ liệu mới rồi di chuyển dữ liệu, và nếu chỉ có một ứng dụng đọc ghi thì việc một chuỗi được thêm vào cột số nguyên cũng không phải điều bất ngờ
      Ngược lại, nếu nhiều ứng dụng cập nhật theo lịch riêng và cùng chia sẻ một cơ sở dữ liệu thì schema là hợp đồng, nên chỉ một kiểu sai cũng có thể phá vỡ ứng dụng khác
      Nếu một ứng dụng dùng bảng do ứng dụng khác tạo ra thì các kiểu dữ liệu đã thống nhất phải được tuân thủ nghiêm ngặt
    • Phần lớn trường hợp, mã nguồn đã được kiểm tra ở thời điểm biên dịch để xem có tuân theo cấu trúc kiểu hay không, và schema SQLite được phát triển cùng nó cung cấp thông tin kiểu cần cho phân tích tĩnh
      Kiểm tra lại ở thời gian chạy không mang lại lợi ích thực tế đáng kể, và đoạn mã mà phân tích tĩnh xác nhận là chèn chuỗi sẽ không đột nhiên biến đổi để chèn số nguyên
      SQLite khác với Postgres, nơi nhiều ứng dụng cùng chia sẻ dữ liệu và phải tin cả bên thứ ba
      Trong môi trường như Postgres, xác thực thời gian chạy là bắt buộc, nhưng SQLite về cơ bản được thiết kế cho một ứng dụng và một cơ sở dữ liệu, nên chỉ cần tin vào chính mã của mình, thứ có thể được đánh giá ở thời điểm biên dịch
      Trong tình huống không điển hình khi nhiều ứng dụng cùng chia sẻ một tệp, bạn có thể bật bảng nghiêm ngặt

  • Kiểu dữ liệu động có thể phù hợp với các trường hợp như kho lưu trữ khóa-giá trị đơn giản, nhưng cần tự hỏi đâu mới đúng với nguyên tắc ít gây ngạc nhiên nhất: INTEGER chấp nhận 'hello world' mà không lỗi, hay chỉ có thể chèn giá trị như vậy khi phải ghi rõ từ khóa như NONSTRICT hoặc kiểu ANY
    Có lẽ đa số người dùng SQLite sẽ không mong đợi trường hợp đầu tiên là có thể xảy ra

    • Có thể là vì SQLite hướng đến một cơ sở dữ liệu không kiểu nhưng lại muốn cú pháp trông giống SQL chuẩn
      Điều này cũng phù hợp với lời giải thích rằng họ muốn mã viết cho DBMS khác có thể tình cờ chạy được trên SQLite
      Dù vậy, việc người dùng không hề bỏ qua kiểu mà vẫn có thể ghi rõ INTEGER rồi chèn văn bản vào thì thực sự rất khó ngờ tới

  • Đã từng có trường hợp một UUID bắt đầu bằng giá trị như 08123… bị phân tích như số bát phân, khiến một phần bị chuyển sai thành số
    Rất khó hiểu và phiền toái, nhưng đã giải quyết bằng cách áp dụng STRICT rồi tạo lại toàn bộ bảng

    • Đó không phải do SQLite mà là do driver của ngôn ngữ cố giúp quá mức
      SQLite không có kiểu bát phân

  • Mặc định dùng CREATE TABLE ... STRICT WITHOUT ROWID, và tôi không hiểu có lý do gì để phải tạo khác đi