SQLite - PRAGMA：初學者的全面指南

您好，有抱負的數據庫愛好者！今天，我們將踏上一段令人興奮的旅程，探索SQLite PRAGMAs的世界。別擔心，如果您以前從未聽說過PRAGMAs——在這個教學結束之時，您將會像專家一樣使用它們！

PRAGMA是什麼？

在我們深入之前，讓我們從基礎開始。在SQLite中，PRAGMA是一種特殊命令，讓我們可以修改SQLite庫的行為或查詢其內部狀態。可以把PRAGMAs看作是您的SQLite數據庫的控制面板——它們讓您可以調整設置並獲取關於數據庫運行的信息。

現在，讓我們探討一些SQLite中最常見的PRAGMAs。

auto_vacuum Pragma

auto_vacuum PRAGMA就像為您的數據庫找了一個愛乾淨的室友。它在您刪除數據時自動清理和組織數據庫文件。

PRAGMA auto_vacuum = FULL;

這個命令將auto_vacuum模式設置為FULL，這意味著SQLite會努力讓您的數據庫文件保持最小。

cache_size Pragma

cache_size PRAGMA就像給您的數據庫一個更大的腦袋。它決定了SQLite在內存中保留多少頁以供快速訪問。

PRAGMA cache_size = 10000;

這將緩存大小設置為10,000頁。更多的緩存意味著更快的性能，但同時也會使用更多的內存。

case_sensitive_like Pragma

這個PRAGMA決定了LIKE運算符是否區分大小寫。這就像教導您的數據庫帶著或沒帶著眼鏡閱讀。

PRAGMA case_sensitive_like = TRUE;

有了這個設置，'Apple' LIKE 'app%'會是false，而'Apple' LIKE 'App%'會是true。

count_changes Pragma

count_changes PRAGMA就像您的數據庫操作的記分員。它告訴您INSERT、UPDATE或DELETE語句修改了多少行。

PRAGMA count_changes = ON;

設置這個之後，當您運行例如UPDATE語句時，SQLite會告訴您影響了多少行。

database_list Pragma

這個PRAGMA就像您的數據庫的名錄。它列出了所有附加的數據庫，包括主數據庫和您已經連接的任何其他數據庫。

PRAGMA database_list;

這將顯示所有附加數據庫的列表、它們的文件路徑和它們的內部名稱。

encoding Pragma

encoding PRAGMA設置或查詢數據庫使用的文本編碼。這就像選擇您的數據庫說哪種語言。

PRAGMA encoding = "UTF-8";

這將數據庫編碼設置為UTF-8，這是支持多種語言的常見選擇。

freelist_count Pragma

這個PRAGMA告訴您數據庫文件中有多少未使用的頁。這就像檢查您在存儲單元中有多少空間。

PRAGMA freelist_count;

這將返回數據庫文件中的未使用頁數。

index_info 和 index_list Pragmas

這些PRAGMAs就像為您的數據庫索引找了一個圖書管理員。它們提供了關於索引結構和內容的信息。

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

第一個命令列出了表上的所有索引，而第二個命令提供了關於特定索引的詳細信息。

journal_mode Pragma

journal_mode PRAGMA控制SQLite如何跟蹤交易。這就像選擇不同類型的備份系統。

PRAGMA journal_mode = WAL;

這將日誌模式設置為寫前日誌（WAL），在許多情況下可以提高性能。

max_page_count 和 page_count Pragmas

這些PRAGMAs處理數據庫文件中的頁數。它們就像設置和檢查書中的最大頁數。

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

第一個命令設置最大頁數，而第二個命令告訴您當前使用了多少頁。

page_size Pragma

這個PRAGMA設置或查詢數據庫的頁大小。這就像決定您的書中的每一頁應該多大。

PRAGMA page_size = 4096;

這將頁大小設置為4096個字节，這是一個常見的選擇。

parser_trace 和 sql_trace Pragmas

這些PRAGMAs就像為您的數據庫開啟了調試模式。它們提供了關於SQLite如何處理您的命令的詳細信息。

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

這些在您試圖理解為什麼特定的查詢不按預期工作時非常有用。

recursive_triggers Pragma

這個PRAGMA控制觸發器是否能夠遞歸地點燃其他觸發器。這就像允許數據庫中的多米諾效應。

PRAGMA recursive_triggers = ON;

有了這個設置，一個觸發器可以引起另一個觸發器被點燃，這可能會引起另一個觸發器被點燃，依此類推。

schema_version 和 user_version Pragmas

這些PRAGMAs讓您可以為數據庫模式設置和查詢版本號。它們就像數據庫結構的版本標籤。

PRAGMA schema_version;
PRAGMA user_version = 1;

第一個命令查詢當前的模式版本，而第二個命令將用戶版本設置為1。

secure_delete Pragma

這個PRAGMA控制SQLite如何徹底地覆蓋刪除的數據。這就像選擇將您的文件切碎還是直接扔進垃圾箱。

PRAGMA secure_delete = ON;

有了這個設置，SQLite會用零來覆蓋刪除的數據，使其更難恢復。

synchronous Pragma

synchronous PRAGMA控制SQLite如何小心地將數據寫入磁盤。這是在安全性和速度之間的平衡。

PRAGMA synchronous = NORMAL;

這將同步級別設置為中等，在完全安全（FULL）和最大速度（OFF）之間取得平衡。

temp_store 和 temp_store_directory Pragmas

這些PRAGMAs控制SQLite存儲臨時數據的位置和方式。這就像選擇您在工作時將草稿放在哪裡。

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

第一個命令告訴SQLite在內存中存儲臨時數據，而第二個命令指定了臨時文件的目錄。

writable_schema Pragma

這個PRAGMA控制您是否能夠直接修改sqlite_master表。這就像擁有數據庫結構的總鍵。

PRAGMA writable_schema = ON;

請非常小心使用這個！它讓您可以改變數據庫的基本結構，如果操作不正確，可能會非常危險。

這裡是一個總結我們討論過的所有PRAGMAs的表格：

PRAGMA	描述
auto_vacuum	控制數據庫的自動清理
cache_size	設置內存中保留的頁數
case_sensitive_like	控制LIKE運算符的大小寫敏感性
count_changes	報告操作改變的行數
database_list	列出附加的數據庫
encoding	設置或查詢數據庫文本編碼
freelist_count	報告數據庫中的未使用頁數
index_info	提供關於特定索引的信息
index_list	列出表上的所有索引
journal_mode	控制如何跟蹤交易
max_page_count	設置數據庫的最大頁數
page_count	報告數據庫中的當前頁數
page_size	設置或查詢數據庫頁大小
parser_trace	啟用詳細的語法分析信息
recursive_triggers	允許觸發器遞歸地點燃其他觸發器
schema_version	設置或查詢模式版本號
secure_delete	控制刪除數據的覆蓋程度
sql_trace	啟用詳細的SQL執行信息
synchronous	控制數據寫入磁盤的細心程度
temp_store	控制臨時數據的存儲位置
temp_store_directory	設置臨時文件的目錄
user_version	設置或查詢用戶定義的版本號
writable_schema	允許直接修改sqlite_master表

以上就是全部內容！您現在已經有足夠的裝備來開始嘗試SQLite PRAGMAs了。記住，這些強大的工具可以顯著影響您的數據庫行為和性能，所以在生產環境中使用之前一定要徹底測試。快樂編程！

Credits: Image by storyset