SQLite - Lệnh PRAGMA: Hướng dẫn chi tiết cho người mới bắt đầu

Xin chào các bạn đam mê cơ sở dữ liệu! Hôm nay, chúng ta sẽ bắt đầu một hành trình thú vị vào thế giới của các lệnh PRAGMA trong SQLite. Đừng lo lắng nếu bạn chưa từng nghe đến PRAGMA trước đây - đến cuối bài hướng dẫn này, bạn sẽ sử dụng chúng như một chuyên gia!

PRAGMA là gì?

Trước khi chúng ta đi sâu vào, hãy bắt đầu từ những điều cơ bản. Trong SQLite, một PRAGMA là một lệnh đặc biệt cho phép chúng ta thay đổi hành vi của thư viện SQLite hoặc truy vấn trạng thái nội bộ của nó. Hãy nghĩ về PRAGMA như một bảng điều khiển cho cơ sở dữ liệu SQLite của bạn - chúng cho phép bạn tinh chỉnh các thiết lập và nhận thông tin về cách cơ sở dữ liệu của bạn đang hoạt động.

Bây giờ, hãy cùng khám phá một số PRAGMA thường được sử dụng trong SQLite.

PRAGMA auto_vacuum

PRAGMA auto_vacuum giống như việc có một người bạn cùng phòng gọn gàng cho cơ sở dữ liệu của bạn. Nó tự động dọn dẹp và sắp xếp tệp cơ sở dữ liệu khi bạn xóa dữ liệu.

PRAGMA auto_vacuum = FULL;

Lệnh này đặt chế độ auto_vacuum thành FULL, có nghĩa là SQLite sẽ làm việc chăm chỉ để giữ cho tệp cơ sở dữ liệu của bạn nhỏ nhất có thể.

PRAGMA cache_size

PRAGMA cache_size giống như việc cho cơ sở dữ liệu của bạn một bộ não lớn hơn. Nó xác định số lượng trang mà SQLite giữ trong bộ nhớ để truy cập nhanh hơn.

PRAGMA cache_size = 10000;

Điều này đặt kích thước bộ nhớ cache thành 10.000 trang. Bộ nhớ cache nhiều hơn có nghĩa là hiệu suất nhanh hơn, nhưng nó cũng sử dụng nhiều bộ nhớ hơn.

PRAGMA case_sensitive_like

PRAGMA này xác định LIKE operator có phân biệt chữ cái hay không. Nó giống như việc dạy cơ sở dữ liệu của bạn đọc có hoặc không có kính.

PRAGMA case_sensitive_like = TRUE;

Với thiết lập này, 'Apple' LIKE 'app%' sẽ là false, trong khi 'Apple' LIKE 'App%' sẽ là true.

PRAGMA count_changes

PRAGMA count_changes giống như một người ghi điểm cho các thao tác cơ sở dữ liệu của bạn. Nó cho bạn biết có bao nhiêu hàng đã bị thay đổi bởi một lệnh INSERT, UPDATE hoặc DELETE.

PRAGMA count_changes = ON;

Sau khi đặt điều này, khi bạn chạy một lệnh UPDATE, ví dụ, SQLite sẽ cho bạn biết có bao nhiêu hàng đã bị ảnh hưởng.

PRAGMA database_list

PRAGMA này giống như một danh bạ cho cơ sở dữ liệu của bạn. Nó liệt kê tất cả các cơ sở dữ liệu đã gắn kết, bao gồm cả cơ sở dữ liệu chính và bất kỳ cơ sở dữ liệu nào khác bạn đã kết nối.

PRAGMA database_list;

Điều này sẽ hiển thị danh sách tất cả các cơ sở dữ liệu đã gắn kết, đường dẫn tệp của chúng và tên nội bộ của chúng.

PRAGMA encoding

PRAGMA encoding đặt hoặc truy vấn mã hóa văn bản được sử dụng bởi cơ sở dữ liệu. Nó giống như việc chọn ngôn ngữ mà cơ sở dữ liệu của bạn sử dụng.

PRAGMA encoding = "UTF-8";

Điều này đặt mã hóa của cơ sở dữ liệu thành UTF-8, đây là một lựa chọn phổ biến để hỗ trợ nhiều ngôn ngữ.

PRAGMA freelist_count

PRAGMA này cho bạn biết có bao nhiêu trang trống trong tệp cơ sở dữ liệu. Nó giống như việc kiểm tra bạn có bao nhiêu không gian trống trong đơn vị lưu trữ của bạn.

PRAGMA freelist_count;

Điều này sẽ trả về số lượng trang trống trong tệp cơ sở dữ liệu.

PRAGMA index_info và PRAGMA index_list

Những PRAGMA này giống như việc có một thư viện cho các chỉ mục cơ sở dữ liệu của bạn. Chúng cung cấp thông tin về cấu trúc và nội dung của các chỉ mục của bạn.

PRAGMA index_list(table_name);
PRAGMA index_info(index_name);

Lệnh đầu tiên liệt kê tất cả các chỉ mục trên một bảng, trong khi lệnh thứ hai cung cấp thông tin chi tiết về một chỉ mục cụ thể.

PRAGMA journal_mode

PRAGMA journal_mode kiểm soát cách SQLite theo dõi các giao dịch. Nó giống như việc chọn giữa các loại hệ thống sao lưu khác nhau.

PRAGMA journal_mode = WAL;

Điều này đặt chế độ journal thành Write-Ahead Logging (WAL), có thể cải thiện hiệu suất trong nhiều trường hợp.

PRAGMA max_page_count và PRAGMA page_count

Những PRAGMA này liên quan đến số lượng trang trong tệp cơ sở dữ liệu của bạn. Chúng giống như việc đặt và kiểm tra số lượng trang tối đa trong một cuốn sách.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

Lệnh đầu tiên đặt số lượng trang tối đa, trong khi lệnh thứ hai cho bạn biết số lượng trang hiện tại đang được sử dụng.

PRAGMA page_size

PRAGMA này đặt hoặc truy vấn kích thước trang của cơ sở dữ liệu. Nó giống như việc quyết định kích thước mỗi trang trong cuốn sách của bạn.

PRAGMA page_size = 4096;

Điều này đặt kích thước trang thành 4096 byte, đây là một lựa chọn phổ biến.

PRAGMA parser_trace và PRAGMA sql_trace

Những PRAGMA này giống như việc bật chế độ gỡ lỗi cho cơ sở dữ liệu của bạn. Chúng cung cấp thông tin chi tiết về cách SQLite xử lý các lệnh của bạn.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

Những lệnh này rất hữu ích khi bạn cố gắng hiểu tại sao một truy vấn cụ thể không hoạt động như mong đợi.

PRAGMA recursive_triggers

PRAGMA này kiểm soát xem các trigger có thể bắn ra các trigger khác một cách đệ quy hay không. Nó giống như việc cho phép hiệu ứng domino trong cơ sở dữ liệu của bạn.

PRAGMA recursive_triggers = ON;

Với thiết lập này, một trigger có thể gây ra một trigger khác bắn ra, có thể gây ra một trigger khác bắn ra, và vân vân.

PRAGMA schema_version và PRAGMA user_version

Những PRAGMA này cho phép bạn đặt và truy vấn số phiên bản cho schema của cơ sở dữ liệu. Chúng giống như các thẻ phiên bản cho cấu trúc cơ sở dữ liệu của bạn.

PRAGMA schema_version;
PRAGMA user_version = 1;

Lệnh đầu tiên truy vấn phiên bản schema hiện tại, trong khi lệnh thứ hai đặt phiên bản người dùng thành 1.

PRAGMA secure_delete

PRAGMA này kiểm soát cách SQLite ghi đè dữ liệu đã xóa. Nó giống như việc chọn giữa việc shredding tài liệu của bạn hoặc chỉ đơn giản là bỏ chúng vào thùng rác.

PRAGMA secure_delete = ON;

Với thiết lập này, SQLite sẽ ghi đè dữ liệu đã xóa bằng zeros, làm cho việc khôi phục dữ liệu khó khăn hơn.

PRAGMA synchronous

PRAGMA này kiểm soát cách SQLite viết dữ liệu vào đĩa. Nó là sự cân bằng giữa an toàn và tốc độ.

PRAGMA synchronous = NORMAL;

Điều này đặt mức độ an toàn ở mức trung bình, cân bằng giữa an toàn tối đa (FULL) và tốc độ tối đa (OFF).

PRAGMA temp_store và PRAGMA temp_store_directory

Những PRAGMA này kiểm soát nơi và cách SQLite lưu trữ dữ liệu tạm thời. Nó giống như việc chọn nơi để đặt giấy nháp khi bạn làm việc.

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/path/to/directory';

Lệnh đầu tiên chỉ định SQLite lưu trữ dữ liệu tạm thời trong bộ nhớ, trong khi lệnh thứ hai chỉ định thư mục cho các tệp tạm thời.

PRAGMA writable_schema

PRAGMA này kiểm soát xem bạn có thể chỉnh sửa bảng sqlite_master trực tiếp hay không. Nó giống như việc có một chìa khóa.master cho cấu trúc cơ sở dữ liệu của bạn.

PRAGMA writable_schema = ON;

Hãy rất cẩn thận với điều này! Nó cho phép bạn thay đổi cấu trúc cơ bản của cơ sở dữ liệu của bạn, điều này có thể nguy hiểm nếu không được thực hiện đúng cách.

Dưới đây là bảng tóm tắt tất cả các PRAGMA mà chúng ta đã thảo luận:

PRAGMA	Mô tả
auto_vacuum	Kiểm soát tự động vacuuming của cơ sở dữ liệu
cache_size	Đặt số lượng trang giữ trong bộ nhớ
case_sensitive_like	Kiểm soát tính phân biệt chữ cái của LIKE operator
count_changes	Báo cáo số lượng hàng thay đổi bởi các thao tác
database_list	Liệt kê các cơ sở dữ liệu đã gắn kết
encoding	Đặt hoặc truy vấn mã hóa văn bản của cơ sở dữ liệu
freelist_count	Báo cáo số lượng trang trống trong tệp cơ sở dữ liệu
index_info	Cung cấp thông tin về một chỉ mục cụ thể
index_list	Liệt kê tất cả các chỉ mục trên một bảng
journal_mode	Kiểm soát cách theo dõi giao dịch
max_page_count	Đặt số lượng trang tối đa trong cơ sở dữ liệu
page_count	Báo cáo số lượng trang hiện tại trong cơ sở dữ liệu
page_size	Đặt hoặc truy vấn kích thước trang của cơ sở dữ liệu
parser_trace	Bật thông tin phân tích chi tiết
recursive_triggers	Cho phép trigger bắn ra các trigger khác một cách đệ quy
schema_version	Đặt hoặc truy vấn số phiên bản schema
secure_delete	Kiểm soát cách ghi đè dữ liệu đã xóa
sql_trace	Bật thông tin thực thi SQL chi tiết
synchronous	Kiểm soát cách dữ liệu được viết vào đĩa
temp_store	Kiểm soát nơi dữ liệu tạm thời được lưu trữ
temp_store_directory	Đặt thư mục cho các tệp tạm thời
user_version	Đặt hoặc truy vấn số phiên bản người dùng
writable_schema	Cho phép chỉnh sửa bảng sqlite_master trực tiếp

Và như vậy, bạn đã có足够的装备来开始尝试SQLite的PRAGMA了。记住，这些强大的工具可以显著影响数据库的行为和性能，所以总是在生产环境中使用之前彻底测试。快乐编码！

Credits: Image by storyset