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;

这将缓存大小设置为10000页。更多的缓存意味着更快的性能，但也使用更多的内存。

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;

这将日志模式设置为Write-Ahead Logging（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