SQLite指南(5) – PRAGMA命令用法（完整)

PRAGMA語句是SQLITE資料的SQL擴充，是它專屬的特性，主要用於修改SQLITE庫或者內資料查詢的操作。它採用與SELECT、INSERT等語句一樣的形式來發出請求，但也有幾個重要的不同：
1. 特定的PRAGMA語句可能被移走，新的PRAGMA語句可能在新的版本中添加。因此，與舊版相容無法保證。
2. 未知的PRAGMA命令不會有錯誤訊息出現，它只是簡單的忽略。
3. 有些PRAGMA只在SQL的編譯階段起作用，而不是執行階段。 這意味著如果使用C語言，sqlite3_prepare(), sqlite3_step(), sqlite3_finalize()這幾個API，pragma命令可能只在prepare()的調用裡運行，而不是在後兩個API當中執行。或者，pragma可能在sqlite3_step()執行的時候運行。到底在哪個階段執行，取決於pragma從本身，以及是哪個sqlite的release版本。
4. pragma命令是sqlite特有的，基本上不可能與其它資料庫保持相容。
PRAGMA命令的文法格式如：

它可以不帶參數，或者只帶一個參數。這個參數可以是等號賦值，也可以用括弧括起來。兩者效果一樣。很多情況下，參數值是布爾型，值為(1,yes,true 或on)或者(0, no, false, off)
關鍵字參數，可以使用引號括起來，e.g. 'yes' [FALSE]。有些pragma命令會使用字串作為參數，"0"和"no"表示相同的含義。當查詢某設定的值時，很多情況下返回的是數值，而不是關鍵字。
pragma名之前，可以選帶資料庫的名字。資料庫名是被"attach"（關聯）上的資料庫名字，或者是"main", "temp"來表示主要資料庫和臨時資料庫。如果可選的資料庫名被略去，則預設為"main"資料庫。在有些pragma命令裡，資料庫名沒有意義，則簡單的忽略掉。
下面我們看看sqlite到底有些有用的pragma命令：
auto_vacuum
automatic_index
cache_size
case_sensitive_like
checkpoint_fullfsync
collation_list
compile_options
count_changes¹
database_list
default_cache_size¹
empty_result_callbacks¹
encoding
foreign_key_list
foreign_keys
freelist_count
full_column_names¹
fullfsync
ignore_check_constraints
incremental_vacuum
index_info
index_list
integrity_check
journal_mode
journal_size_limit
legacy_file_format
locking_mode
max_page_count
page_count
page_size
parser_trace²
quick_check
read_uncommitted
recursive_triggers
reverse_unordered_selects
schema_version
secure_delete
short_column_names¹
synchronous
table_info
temp_store
temp_store_directory¹
user_version
vdbe_listing²
vdbe_trace²
wal_autocheckpoint
wal_checkpoint
writable_schema
這裡邊有幾個標了右上標為1的，似乎已經被obsoleted掉了。標為2的，只被用於debug,僅當sqlite在先行編譯宏SQLITE_DEBUG下build出來，才有用。
下面看看這些命令的具體用法：
1. PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 或 NONE | 1 或 FULL | 2 或 INCREMENTAL;
這裡，0和NONE表示的含義相同。
預設值為0, 表示禁用auto vacuum. 除非SQLITE_DEFAULT_AUTOVACUUM宏在編譯的時候定義了。資料刪除的時候，資料庫大小不會改變。沒用的資料庫檔案頁面會被添加到freelist裡頭，用於將來重用。這時，使用VACUUM命令，可以重建整個資料庫，以回收無用的磁碟空間。
值為1時，所有的freelist頁會被移動到檔案末尾，每次事務提交的時候檔案會被截短。注意，自動vacuum只是從檔案是截斷freelist頁，並沒有進行片段重整等操作，也就是說，它沒有VACUUM命令來得徹底。事實上，自動vacuum會讓片段更多。
只有在資料庫儲存某些附加資訊的時候，它允許每個資料庫頁來跟蹤它的引用頁，自動vacuum才用得上。它必須在沒有建立任何錶的情況下啟用。在一個表已經建立了之後，是不能啟用和停用auto-vacuum的。
值為2時，表示增量vacuum，意味著並不是在每次提交事務的時候自動vacuum，需要調用一個獨立的incremental_vacuum語句來觸發auto-vacuum。
資料庫可以在1和2兩種vacuum模式下進行切換。但是不能從none到full或incremental間切換。要想切換，要麼資料庫是全新的資料庫（沒有任何錶）， 或者單獨運行vacuum命令以後。改變自動vacuum模式，首先執行auto_vacuum語句設定新的模式，然後調用VACUUM來重整資料庫。
不帶參數的auto_vacuum語句返回當前的auto_vacuum模式值。
2. PRAGMA automatic_index;
PRAGMA automatic_index = boolean;
查詢，設定或者清除自動索引的功能。預設值為true (1).
3. PRAGMA cache_size;
PRAGMA cache_size = <number of pages>;
查詢或者修改開啟的資料庫記憶體裡頭能容納的最多的資料庫頁數。預設值是2000. 這樣設定只會改變當前會話中的cache size，當資料庫重新開啟，又會恢複預設值。你可以使用default_cache_size來設定所有會話中的cache size
4. PRAGMA case_sensitive_like=boolean;
預設行為是忽略ascii字元的大小寫。'a' LIKE 'A'會是true. 當禁用case_sensitive_like時，會用預設的like行為。當啟用它時，就會區分大小寫。
5. PRAGMA checkpoint_fullfsync
PRAGMA checkpoint_fullfsync=boolean;
查詢或設定fullfsync的標誌值。如果設定了該值，則F_FULLFSYNC同步方法會在checkpoint操作時調用，預設值是off。只有Mac OS-X作業系統支援F_FULLFSYNC。另外，如果設定了fullfsync值，那麼F_FULLFSYNC同步方法會在所有sync操作裡使用，也checkpoint_fullfsync標誌完全無關。
6. PRAGMA collation_list;
返回當前資料庫連接定義的所有排序次序。
7. PRAGMA compile_options;
這個要贊，返回編譯SQLITE時使用的所有先行編譯宏。當然，以"SQLITE_"打頭的首碼會被忽略。實際上它是通過調用sqlite3_compileoption_get()方法返回的。
8. PRAGMA count_changes;
PRAGMA count_changes=boolean;
該命令已經停用. 只是為了保持與舊版相容. 如果不設定此值，INSERT, UPDATE, DELETE語句不會返回多少行改變的資料。
事實上，sqlite3_changes()可以擷取改變的行數。
9. PRAGMA database_list;
返回當前資料庫連接關聯的資料庫列表.
10. PRAGMA default_cache_size;
PRAGMA default_cache_size = Number-of-pages;
設定預設的cache sie, 是以頁為單位。不幸的是，該命令也將被廢棄。
11. PRAGMA empty_result_callbacks;
PRAGMA empty_result_callbacks = boolean;
僅作與舊版相容用。如果將該標誌值清除，sqlite3_exec()提供的回呼函數（返回0或多行資料）將不被觸發。
12. PRAGMA encoding;
PRAGMA encoding = "UTF-8";
PRAGMA encoding = "UTF-16";
PRAGMA encoding = "UTF-16le";
PRAGMA encoding = "UTF-16be";
預設值是utf-8。如果使用attach命令，則會要求使用與main資料庫相同的字元集編碼，如果新的資料庫編碼與main不同，則會失敗。
13. PRAGMA foreign_key_list(table-name);
返回外鍵列表
14. PRAGMA foreign_keys;
PRAGMA foreign_keys = boolean;
查詢設定或者清除關於外鍵的限制, 外鍵限制只有在BEGIN或者SAVEPOINT不在PENDING狀態時設定才有效。
改變該設定會影響所有已經準備好的SQL語句的執行。
從3.6.19開始，預設的FK強制限制是OFF。也就是說，不會強制外鍵依賴。
15. PRAGMA freelist_count;
返回資料庫檔案中未使用頁的數目
16. PRAGMA full_column_names;
PRAGMA full_column_names = boolean;
deprecated.
1. 如果有AS子句，列名就會用AS後的別名
2. 如果結果只是普通的運算式，而不是源表的列名，則採用運算式的文本
3. 如果使用了short_column_names開關為ON，則採用源表列名，並且不帶表名首碼
4. 如果兩個開關都設為OFF，則採用第2個規則。
5. 結果列是學有源表源列的組合：TABLE.COLUMN
17. PRAGMA fullfsync;
PRAGMA fullfsync = boolean;
預設值為OFF，也只有MAC os支援F_FULLFSYNC
18. PRAGMA ignore_check_constraints = boolean;
是否強制check約束，預設值為off
19. PRAGMA incremental_vacuum(N);
N頁從freelist中移除。用於設定此參數。每次截短相同的頁數。該命令必須是在auto_vacuum=incremental模式下才有效。如果freelist中的頁數少於N，或者N小於1，或者N被完全忽略，那麼整個freelist會被清除。
20. PRAGMA index_info(index-name);
擷取具名的index資訊。
21. PRAGMA index_list(table-name);
擷取與目標表關聯的索引的的相關資訊
22. PRAGMA integrity_check;
PRAGMA integrity_check(integer);
執行整個庫的完全性檢查，會查看錯序的記錄、丟失的頁，毀壞的索引等。
23.
PRAGMA journal_mode;
PRAGMA database.journal_mode;
PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
PRAGMA database.journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF
用於設定資料庫的journal_mode. DELETE是預設的行為。在此模式下，每次事務終止的時候，journal檔案會被刪除，它會導致事務提交。
TRUNCATE模式，通過將復原journal截短成0，而不是刪除它。大多數情情況下，它要比DELETE模式速度快(因為不用刪除檔案）
PERSIST模式，每次事務結束時，並不刪除rollback journal，而只是在journal的頭部填充0，這樣會阻止別的資料庫連接來rollback. 該模式在某些平台下，是一種最佳化，特別是刪除或者truncate一個檔案比覆蓋檔案的第一塊代價高的時候。
MEMORY模式，只將rollback日誌儲存到RAM中，節省了磁碟I/O，但帶來的代價是穩定性和完整性上的損失。如果中間crash掉了，資料庫有可能損壞。
WAL模式，也就是write-ahead log取代rollback journal。該模式是持久化的，跨多個資料為串連，在重新開啟資料庫以後，仍然有效。該模式只在3.7.0以後才有效。
(經過實驗，發現，它會產生兩個檔案：.shm和.wal)
OFF模式，這樣就沒有事務支援了。
另外要注意的是，對於memory資料庫，只有兩種模式: MEMORY或者OFF。並且，當前如果有活躍的事務，則不允許改變事務模式。
24. PRAGMA journal_size_limit
PRAGMA journal_size_limit = N ;
如果串連時，用了"exclusive mode(PRAGMA locking_mode=exclusive)或者(PRAGMA journal_mode=persist), 提交事務以後，journal檔案會仍然在檔案系系統當中。這可能會提高了效率，但是也損耗了空間。一個大的事務（如VACUUM)，會耗費大量的磁碟空間。
該設定會限制journal檔案的大小。預設值是－1。
25. PRAGMA legacy_file_format;
PRAGMA legacy_file_format = boolean;
如果該值為ON，則會採用3.0.0檔案格式，如果為off, 則會採用最新的檔案格式，可能導致舊版本的sqlite無法開啟該檔案。
第一次新檔案格式的sqlite3資料庫開啟時，該值為off.但是預設值會是on.
26. PRAGMA locking_mode;
PRAGMA locking_mode = NORMAL | EXCLUSIVE
預設值是NORMAL. 資料庫連接在每一個讀或寫事務終點的時候放掉檔案鎖。如果是EXCLUSIVE模式，串連永遠不會釋放檔案鎖。在此模式下，第一次執行讀操作時，會擷取並持有共用鎖定，第一次寫，會擷取並持有排它鎖。
釋放排它鎖，僅當關閉資料庫連接，或者將鎖模式改回為NORMAL時，再次訪問資料庫檔案（讀或寫）才會放掉。簡單的設定為NORMAL是不夠的，只有當下次再訪問時才會釋放排它鎖。
有下述三個理由，去設定鎖模式為EXCLUSIVE
1. 應用程式需要阻止其它進程訪問資料庫檔案
2. 檔案系統的系統調用數量減少了，導致些許效能下降
3. WAL記錄模式可以在EXCLUSIVE模式下使用，而不需要用到共用記憶體
當指定資料庫名時，只能目標資料庫生效。如：
PRAGMA main.locking_mode=EXCLUSIVE; 不指定資料庫名時，則對所有開啟的資料庫生效。temp或者memory資料庫總是使用exclusive鎖模式。
第一次進入WAL記錄模式時，鎖模式使用的是exclusive，這以後，鎖模式也不能改變，直到退出WAL記錄模式，如果鎖模式開始時使用的是NORMAL，第一次進入WAL，這時鎖模式可以改變，並且不需要退出WAL模式。
27. PRAGMA max_page_count;
PRAGMA max_page_count = N;
查詢或者設定資料庫檔案的最大頁數
28. PRAGMA page_count;
返回資料庫檔案的頁數
29. PRAGMA page_size;
PRAGMA page_size = bytes;
查詢或者設定資料庫檔案的頁大小, 必須是2的乘方，並且介於512和65536之間。
建立資料庫時，會給定一個預設的大小。page_size命令會立即改變頁大小（如果資料庫是空的話，就是說在沒有建立任何錶的情況下）。如果指定了新大小，是在運行VACUUM命令之間，同時資料庫不是在WAL記錄模式下，那麼VACUUM命令會將頁大小調整到新的大小（這時應該沒有是事建立表的限制）
SQLITE_DEFAULT_PAGE_SIZE 預設值是1024，最大的預設頁大小是8192. windows下，有時候可能預設頁大小大於1024，取決於GetDiskFreeSpace()來擷取真實的設定扇區大小。
30. PRAGMA parser_trace = boolean;
用在DEBUG的時候。
31. PRAGMA quick_check;
PRAGMA quick_check(integer)
與integrity_check相像，但是略去了對索引內容與表內容匹配的校正。
32. PRAGMA read_uncommitted;
PRAGMA read_uncommitted = boolean;
讀未提交開關。預設的事務隔離級是：可序列化。任何進程或線程都可以設定讀未提交隔離級，但是，SERIALIZABLE仍被使用，除了共用某頁和表模式的緩衝的那些串連。
33. PRAGMA recursive_triggers;
PRAGMA recursive_triggers = boolean;
會影響所有的語句執行。3.6.18以前，這個開關是不支援的。預設值是off.
34. PRAGMA reverse_unordered_selects;
PRAGMA reverse_unordered_selects = boolean;
當開啟此開關時，不帶order by的select語句，會輸出相反順序的結果。
35. PRAGMA schema_version;
PRAGMA schema_version = integer ;
PRAGMA user_version;
PRAGMA user_version = integer ;
schema和user version是在資料庫檔案頭40，60位元組處的32位整數（大端表示）。
schema版本由sqlite內部維護，當schema改變時，就會增加該值。顯式改變該值非常危險。
user版本可以被應用程式使用。
36. PRAGMA secure_delete;
PRAGMA database.secure_delete;
PRAGMA secure_delete = boolean
PRAGMA database.secure_delete = boolean
設為ON時，刪除的內容會用0來覆蓋。預設值由宏SQLITE_SECURE_DELETE 決定。那就是OFF了。
37. PRAGMA short_column_names;
PRAGMA short_column_names = boolean;
deprecated.
38. PRAGMA synchronous;
PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL;
查詢設定sync標誌值。預設值是FULL.
39. PRAGMA table_info(table-name);
返回表的基本資料
40. PRAGMA temp_store;
PRAGMA temp_store = 0 | DEFAULT | 1 | FILE | 2 | MEMORY;
查詢或設定temp_store參數值。
SQLITE_TEMP_STORE PRAGMA temp_store Storage used forTEMP tables
0 any file
1 0 file
1 1 file
1 2 memory
2 0 memory
2 1 file
2 2 memory
3 any memory
40. PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory-name';
設定或改變temp_store的目錄位置. deprecated.
41. PRAGMA vdbe_listing = boolean;
用於DEBUG
42. PRAGMA vdbe_trace = boolean;
用於DEBUG
43. PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint=N;
設定WAL自動檢查點的間隔（以頁為單位）, 預設值是1000。
44. PRAGMA database.wal_checkpoint;
PRAGMA database.wal_checkpoint(PASSIVE);
PRAGMA database.wal_checkpoint(FULL);
PRAGMA database.wal_checkpoint(RESTART);
45. PRAGMA writable_schema = boolean;
當設為ON時，SQLITE_MASTER表可以執行CUD操作。這樣做很危險!!