SQLite 命令

点命令（CLI）

点命令是 SQLite Shell 指令，不是 SQL 语句。

.help                    -- list all dot commands
.open mydb.sqlite        -- open or create database
.databases               -- list attached databases
.tables                  -- list tables
.schema users            -- show CREATE statement for table
.schema                  -- show all schemas
.headers on              -- show column names in output
.mode column             -- column-aligned output
.mode csv                -- CSV output
.mode json               -- JSON output
.output results.csv      -- redirect output to file
.output stdout           -- restore output to terminal
.import data.csv users   -- import CSV into table
.dump                    -- dump entire database as SQL
.dump users              -- dump single table
.read script.sql         -- execute SQL file
.quit                    -- exit SQLite shell

PRAGMA 设置

-- Performance and safety PRAGMAs
PRAGMA journal_mode = WAL;         -- Write-Ahead Logging (recommended)
PRAGMA synchronous = NORMAL;       -- FULL (safest) / NORMAL / OFF
PRAGMA cache_size = -64000;        -- 64 MB cache (-kibibytes or pages)
PRAGMA temp_store = MEMORY;        -- temp tables in RAM
PRAGMA mmap_size = 268435456;      -- 256 MB memory-mapped I/O
PRAGMA foreign_keys = ON;          -- enforce FK constraints
PRAGMA auto_vacuum = INCREMENTAL;  -- NONE / FULL / INCREMENTAL

-- Inspection PRAGMAs
PRAGMA table_info(users);          -- column info
PRAGMA index_list(users);          -- indexes on table
PRAGMA index_info(idx_users_email);-- columns in index
PRAGMA integrity_check;            -- check database integrity
PRAGMA optimize;                   -- update query planner statistics
PRAGMA wal_checkpoint(TRUNCATE);   -- checkpoint WAL file

WAL 模式

预写日志允许写入期间并发读取，通常可提升性能。

-- Enable WAL mode (persists until changed)
PRAGMA journal_mode = WAL;

-- WAL mode benefits:
-- * Readers don't block writers
-- * Writers don't block readers
-- * Better performance for write-heavy workloads
-- * Crash recovery without journal rollback

-- Check WAL file size / checkpoint
PRAGMA wal_checkpoint;             -- passive checkpoint
PRAGMA wal_checkpoint(FULL);       -- wait for all readers, then checkpoint
PRAGMA wal_checkpoint(RESTART);    -- full checkpoint + reset WAL
PRAGMA wal_checkpoint(TRUNCATE);   -- truncate WAL to zero bytes

-- WAL auto-checkpoint threshold (pages)
PRAGMA wal_autocheckpoint = 1000;  -- default 1000 pages

FTS5 全文搜索

-- Create FTS5 virtual table
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content='articles',   -- external content table
  content_rowid='id'
);

-- Populate from content table
INSERT INTO articles_fts(articles_fts) VALUES('rebuild');

-- Basic search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlite performance';

-- Phrase search
SELECT * FROM articles_fts WHERE articles_fts MATCH '"full text"';

-- Column-specific search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:sqlite';

-- Boolean operators
SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlite OR postgresql';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlite NOT mysql';

-- Ranking (BM25)
SELECT *, rank FROM articles_fts
WHERE articles_fts MATCH 'sqlite'
ORDER BY rank;

-- Prefix search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'sqlit*';

常用内置函数