企业官网建设流程全解析

如果你只在 MySQL 或 PostgreSQL 上写过 SQL，第一次接触 SQLite 语法，心情大概会很复杂。表面上它和主流数据库几乎一样，SELECT、INSERT、UPDATE、DELETE 都是那套熟悉的面孔；可一旦你开始建表、改表、处理日期、拼接字符串，各种报错和诡异结果就来了。5/2在 SQLite 里算出来是 2，MySQL 里也是 2；NOW()在 SQLite 里直接不存在；旧版本想把一列删掉，ALTER TABLE DROP COLUMN甚至不是合法语法。这点陌生感，恰恰是 SQLite 语法的核心特征——它是一门极力保持小体积、兼容老库的实现，而不是对标准 SQL 的完整复刻。

这篇文章不打算做成官方文档的中译版，而是从实际使用者的角度，把 SQLite 语法里最常见的差异点、坑位和推荐写法捋一遍。适合这几类人看：刚把项目从 MySQL 迁到 SQLite 的开发者、准备在桌面端或移动端用轻量数据库的工程师、还有那些"SQL 基础都在但一跑到 SQLite 就怀疑人生"的学习者。我的目标是让你看完后，遇到 SQLite 报错时不再一脸茫然，而是能快速判断"这是类型问题、语法兼容问题，还是锁和事务的问题"。

1. 数据类型与类型亲和：SQLite 语法里最大的暗礁

1.1 存储类与类型亲和的区别

SQLite 没有一个"强制类型"的系统。别的数据库是表结构定了VARCHAR(50)，你在里面塞 1024 个字符就直接报错；SQLite 不是这个脾气。它底层只认五种存储类：NULL、INTEGER、REAL、TEXT、BLOB。你建表时写的VARCHAR(50)、DATETIME、BOOLEAN这些类型名，SQLite 会"参考"它们，但不会严格执行。

这就引出了 SQLite 特有的核心概念：类型亲和性（Type Affinity）。建表时声明的数据类型会影响写入时的存储倾向，但最终决定权在值本身。规则大致是这样：

• 声明类型里含INT，这一列倾向于INTEGER亲和；
• 含CHAR、CLOB、TEXT，倾向于TEXT亲和；
• 含BLOB或者不声明任何类型，倾向于BLOB/NUMERIC亲和；
• 含REAL、FLOA、DOUB，倾向于REAL亲和；
• 其余情况默认NUMERIC亲和。

看起来很抽象，我直接给你看一个实际例子。假设有这样一张表：

CREATE TABLE person ( id INTEGER PRIMARY KEY, name VARCHAR(20), score DOUBLE );

name列声明成了VARCHAR(20)，但如果你往里面插入一个数字 123，SQLite 不会报错，也不会自动截断到 20 个字符。它会把 123 按INTEGER存进去，因为整数和文本亲和性不冲突时，存储类由值自己决定。更麻烦的是长度限制——VARCHAR(20)里的 20 在 SQLite 里就是装饰品，存 200 个字符也不会被拦。

很多从 MySQL 迁过来的人在这里栽的第一个跟头就是：以为VARCHAR(100)会像 MySQL 那样写不进去就报错，结果数据白白超长存进去了，后续拿出来处理才发现问题。所以用 SQLite 时，约束靠 CHECK 和 NOT NULL，长度限制靠应用层自己把关，别指望类型名帮你兜底。

1.2 SQLite 没有真正的布尔值

每一次在 SQLite 里写WHERE flag = TRUE，老手内心都毫无波澜，新手则会盯着结果琢磨很久。SQLite 从 3.23.0 开始确实接受TRUE和FALSE关键字，但它们在语法层面只是1和0的别名。换句话说，布尔值从来都不是独立的存储类，你查出来的flag字段类型永远是INTEGER。

这在写查询时会造成一个直观差异。在 PostgreSQL 里你可以直接写：

SELECT * FROM articles WHERE published = FALSE;

在 SQLite 里这样写也能跑，但底层执行的其实是WHERE published = 0。如果某个老表里存的不是 0/1，而是 'Y'/'N' 或者 'yes'/'no'，你的布尔查询就得靠CASE表达式来转换，例如：

SELECT title, CASE WHEN published = 1 THEN 'yes' ELSE 'no' END AS is_published FROM articles;

这是我的建议：设计 Sqlite 表时布尔字段就老老实实用 0/1，别用字符串。省去后面所有查询和 ORM 映射的麻烦。

1.3 日期时间的推荐写法

SQLite 同样没有真正的DATETIME类型。官方推荐的做法是用TEXT、INTEGER或REAL存日期，具体用哪种看需求：

• TEXT：存 ISO8601 格式字符串，比如'2025-01-01 12:00:00'。优点是可读性强、字典序就是时间序，字符串比较就能做范围查询。
• INTEGER：存 Unix 时间戳，运算方便，但可读性差。
• REAL：存儒略日（Julian Day），主要用来做日期运算。

我个人最推荐TEXT+ ISO8601 字符串。原因很简单：你在命令行里查看数据时一眼能看懂，而且WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31'这种写法会因为字符串排序和实际时间排序一致而正常工作。写入时可以用内置函数：

INSERT INTO logs (message, created_at) VALUES ('hello', datetime('now', 'localtime'));

datetime('now', 'localtime')返回的就是当前本地时间的YYYY-MM-DD HH:MM:SS字符串。如果需要日期运算，就用julianday函数，比如计算两个时间之间差几天：

SELECT julianday('2025-02-01') - julianday('2025-01-01') AS days_span;

很多人喜欢问：那我能在TEXT列上调用DATE()函数吗？可以。SQLite 的日期函数会自动解析 ISO8601 字符串并返回规范化结果：

SELECT date('2025-01-01 12:30:00'); -- 返回 2025-01-01 SELECT strftime('%Y-%m', '2025-01-01 12:30:00'); -- 返回 2025-01

只要统一用TEXT + datetime('now','localtime')写入，日期这块就不会出大乱子。

2. 建表与约束：SQLite 语法里那些"差不多但不一样"的地方

2.1 INTEGER PRIMARY KEY 与 rowid 的微妙关系

SQLite 建表的语法主体和其他数据库没什么大区别：

CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TEXT DEFAULT (datetime('now', 'localtime')) );

但有一个细节特别容易被 MySQL 用户忽略：如果你声明的是INT PRIMARY KEY，而不是INTEGER PRIMARY KEY，这个id列就只是一个普通的整数列，不会自动绑定 SQLite 内部维护的rowid。rowid是 SQLite 每张表都隐式存在的自增列，除非你把这张表定义为WITHOUT ROWID。

INTEGER PRIMARY KEY是rowid的别名，插入数据时不指定id，它会自动分配一个全局唯一直；INT PRIMARY KEY虽然也有主键约束，但它不会自动分配自增值。不信你跑一下：

CREATE TABLE t1 (id INTEGER PRIMARY KEY, v TEXT); INSERT INTO t1 (v) VALUES ('a'); INSERT INTO t1 (v) VALUES ('b'); SELECT id FROM t1; -- 得到 1, 2

换一行：

CREATE TABLE t2 (id INT PRIMARY KEY, v TEXT); INSERT INTO t2 (v) VALUES ('a'); -- 报错：NOT NULL constraint failed

差别就是这么直接。所以建表时请老老实实写INTEGER PRIMARY KEY，不要图省事写INT。

关于自增还有一个常见问题：AUTOINCREMENT要不要加？我的态度是：一般情况下不加。INTEGER PRIMARY KEY自动使用的 rowid 分配算法已经能保证单调递增，唯一做不到的是"坏掉的行号绝不重用"。例如删掉了 id=5 的行，下次插入可能复用 5。如果你有严格需求，要求 id 永不重复使用，才需要声明AUTOINCREMENT。代价是 SQLite 会额外维护一张sqlite_sequence表来记录当前最大自增值，插入开销略高。对于绝大多数场景，普通INTEGER PRIMARY KEY就足够了。

2.2 ALTER TABLE 的限制比想象中多

这是 SQLite 语法和主流数据库差距最大的领域之一。MySQL 里ALTER TABLE加列删列改类型花样百出，SQLite 长期以来只支持非常有限的变更操作。

ADD COLUMN在 SQLite 里不是无所不能的。它有几个硬性限制：

• 不能添加带PRIMARY KEY或UNIQUE约束的新列；
• 不能添加带NOT NULL约束但没给默认值的新列；
• 新列默认值不能是带括号的表达式（但CURRENT_TIMESTAMP这种常量表达式没问题）。

也就是说下面这种写法会直接报错：

ALTER TABLE user ADD COLUMN age INTEGER NOT NULL; -- 报错：Cannot add a NOT NULL column with default value NULL

必须改写成：

ALTER TABLE user ADD COLUMN age INTEGER NOT NULL DEFAULT 0;

至于DROP COLUMN和RENAME COLUMN，分别要到 3.35.0 和 3.25.0 才支持，而且还有限制。删列时，如果这一列被索引、唯一约束、CHECK 约束、外键或视图引用，DROP COLUMN会被拒绝。改列名同理，新名字不能和已有列冲突，也不能直接改索引引用的列。

如果你的 SQLite 版本比较老，或者想绕开这些限制，就只能走一套经典流程：建新表 → 复制数据 → 删旧表 → 把新表改名。这套流程在迁移脚本里很常见，我惯用的写法是这样：

PRAGMA foreign_keys = OFF; CREATE TABLE user_new ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER DEFAULT 0 ); INSERT INTO user_new (id, name) SELECT id, name FROM user; DROP TABLE user; ALTER TABLE user_new RENAME TO user; PRAGMA foreign_keys = ON;

操作之前务必关掉外键检查，否则表重命名时外键绑定关系会一起坏掉。这个经验是我在真实迁移里踩过一次才记住的。

2.3 外键检查默认是关闭的

很多人在 SQLite 里建完带外键的表，发现乱插数据竟然不报错，于是怀疑外键语法写错了。其实语法没问题，只是 SQLite 为了向后兼容，默认不启用外键约束检查。你得在每个数据库连接上执行：

PRAGMA foreign_keys = ON;

这个开关不是全局的，只对当前连接生效。如果你用 ORM 连接 SQLite，每次连接建立后都要让它跑一遍这条 PRAGMA。用 C API 的话，可以在sqlite3_open之后执行sqlite3_exec。

开启外键检查后，删除有子表引用的父表记录会受限制，但不同冲突策略的表现不一样。默认是NO ACTION行为，效果近似 MySQL 的RESTRICT，删不掉就是删不掉。

顺带提一个非常容易误会的点：UNIQUE约束允许多个NULL值。INSERT INTO user (email) VALUES (NULL)可以连续插入很多行，即便email列声明了UNIQUE。因为 SQL 标准里NULL不等于任何值，多个NULL不构成唯一性冲突。如果业务上要求"邮箱要么不填，填了必须唯一"，只能额外加一个部分索引来兜底，比如：

CREATE UNIQUE INDEX idx_user_email_not_null ON user(email) WHERE email IS NOT NULL;

这种带WHERE的部分索引在 SQLite 里是合法的，也是我最推荐的兜底方案。

3. 查询语法：从 SELECT 子句顺序到 SQLite 独有表达式

3.1 子句顺序与 LIMIT/OFFSET 的坑

SQLite 的SELECT语句子句顺序和大多数数据库差不多：

SELECT DISTINCT 列 FROM 表 WHERE 条件 GROUP BY 分组 HAVING 分组条件 WINDOW 窗口定义 ORDER BY 排序 LIMIT 限制;

这个顺序不能乱。曾看到有人把LIMIT写在WHERE前面，在 MySQL 里部分版本可能还硬着头皮跑，在 SQLite 里就是语法错误。这类问题排查起来也不难，报错信息会直接告诉你哪段位置不对。

LIMIT和OFFSET的写法也需要留神。SQLite 官方推荐写法是这样：

SELECT * FROM articles ORDER BY id DESC LIMIT 10 OFFSET 20;

MySQL 用户习惯的LIMIT 20, 10这种缩写，在 SQLite 里并没有被正式文档推荐，新版虽然也能识别部分逗号写法，但我劝你别赌。统一用LIMIT n OFFSET m最稳，搬到哪里都不会出问题。

还有一个加分项：SQLite 的LIMIT后面允许写任意表达式，不只是数字。比如：

SELECT * FROM articles LIMIT (SELECT count(*) FROM articles) / 2;

这个特性在动态拼接分页 SQL 时偶尔能派上用场，标准 SQL 里反而不一定能跑。

3.2 表达式、字符串函数和日期函数

SQLite 的表达式体系相比 MySQL 和 PostgreSQL 确实精简，但核心操作都有。先说字符串拼接，这是迁移过程中的高频差异点。MySQL 里首选CONCAT(a, b)，SQLite 没有这个函数，用的是标准 SQL 的||运算符：

SELECT first_name || ' ' || last_name AS full_name FROM user;

first_name是NULL时，整个拼接结果会是NULL。想和 MySQL 的CONCAT_WS表现一致，就得用COALESCE包一层：

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') FROM user;

常用字符串函数里面，SUBSTR（注意，不是 MySQL 的SUBSTRING）、REPLACE、LENGTH、LOWER、UPPER、TRIM都有，只是大多只支持单参数或双参数形式，没那么灵活。LIKE运算符默认对 ASCII 字符大小写不敏感，对非 ASCII 字符通常区分大小写，这点和 MySQL 的排序规则习惯不一样，做中文场景模糊查询时要注意。

日期函数方面，前面提过的DATE()、TIME()、DATETIME()、JULIANDAY()、STRFTIME()是主力。STRFTIME相当于 MySQL 的DATE_FORMAT，格式化占位符要记几个常用的：

SELECT strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'); SELECT strftime('%Y', 'now') AS year; -- 当前年份 SELECT strftime('%W', 'now') AS week_number; -- 当前周数

CASE表达式也是 SQLite 里的常客，尤其是在处理布尔值和枚举值映射的时候。简单写法是CASE 字段 WHEN 值 THEN 结果 ... END，搜索写法是CASE WHEN 条件 THEN 结果 ... END。两行代码就能把 0/1 映射成 '是/否'，比写一堆IF函数清晰得多。

3.3 窗口函数与递归 CTE

很多教程讲到 SQLite 就止步于基础的增删改查，其实 SQLite 3.25.0 之后已经支持窗口函数，3.34.0 左右又补全了不少。排名场景可以直接写：

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM exam_result;

分组排名用PARTITION BY也一样支持，和 MySQL 8 的窗口函数语法基本一致。老项目如果是被迫在 SQLite 里做复杂报表，这个能力非常值钱，不用把所有数据查出来再在内存里排序。

递归 CTE（Common Table Expression）也是 SQLite 语法的亮点之一。它的标准形式是WITH RECURSIVE，最常见的用途是生成序列、拆解层级菜单和遍历树结构。比如生成 1 到 5：

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt WHERE x < 5 ) SELECT x FROM cnt;

这里必须注意是UNION ALL而不是UNION，否则接上递归时不会保留重复行，递归就直接断了。这个问题我见过不止一个开发者踩到，排查了半天，只怪当时没仔细看文档。

4. 事务、锁与高级对象：BEGIN 之外的关键词

4.1 三种事务启动方式：DEFERRED、IMMEDIATE、EXCLUSIVE

SQLite 的事务语法看起来简单，核心就一句BEGIN TRANSACTION，但背后的锁行为却非常影响并发场景的开发体验。SQLite 支持三种启动方式：

• BEGIN DEFERRED：默认模式。执行事务时先不拿任何锁，直到第一次读才拿共享锁，第一次写才尝试拿写锁。
• BEGIN IMMEDIATE：执行事务一开始就尝试获取写锁。如果拿不到，马上报SQLITE_BUSY。
• BEGIN EXCLUSIVE：更激进的写锁模式，直接阻止其他连接的一切读写。

很多客户端程序卡在 "database is locked" 错误，原因往往就是默认的DEFERRED模式下，事务中途升级写锁失败。典型的场景是一个事务里先写一条数据，然后过了一会儿再写第二条，两次写之间另一个连接插入了一行，导致锁升级时冲突。

面对这种情况，我的习惯是：只要你的事务内确定要写数据，直接BEGIN IMMEDIATE，别省这一步。省下来的代价可能就是莫名其妙的SQLITE_BUSY。例如：

BEGIN IMMEDIATE; UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 100; UPDATE orders SET status = 'paid' WHERE order_id = 500; COMMIT;

一次性把写锁从开始就抓在手里，中间不管间隔多久都不怕另一条连接插手。这里顺带提醒一下：SQLite 是单写者数据库，同一时刻只能有一个连接在写，大批量插入时尽量放在一个事务里，每次提交都意味着一次文件同步和锁竞争，拆分太多太碎反而是性能杀手。

4.2 ON CONFLICT 冲突策略：REPLACE 不是 UPDATE

在 SQLite 语法里，INSERT语句的冲突处理是一个很有分量的特性。你可以写成INSERT OR REPLACE、INSERT OR IGNORE、INSERT OR ABORT等，也可以写成INSERT ... ON CONFLICT ... DO NOTHING/DO UPDATE的 UPSERT 形式（3.24.0 之后支持）。重点要理解不同策略的行为差异：

这里最需要警惕的是REPLACE。新手常以为INSERT OR REPLACE等价于"如果存在就更新"，实际上它是"先把旧行删掉，再插入新行"。如果这张表上有外键约束或者触发器，DELETE引发的副作用会一并触发；如果目标行有未提供的列且该列有默认值，删掉重插后这些默认值会填充进去，而不是保留旧值。

举个例子：一张表有id INTEGER PRIMARY KEY, name TEXT, deleted_at TEXT，你执行INSERT OR REPLACE只传了name，deleted_at就变成 NULL 或默认值了。这在软删除场景里特别容易把删除标记清掉。如果只想更新部分字段，正确做法是用 UPSERT 的DO UPDATE形式：

INSERT INTO user (id, name) VALUES (1, '张三') ON CONFLICT(id) DO UPDATE SET name = excluded.name;

这里excluded指代你尝试插入的行。这样就不会动到deleted_at。

4.3 索引、视图与触发器

索引语法和主流数据库基本一致，但 SQLite 有两个很实用的扩展：部分索引和表达式索引。部分索引就是带WHERE的索引，适合只给某个状态下的数据建索引，节省空间；表达式索引则可以对函数结果建索引。例如：

CREATE INDEX idx_lower_email ON user(LOWER(email));

这样的索引能让WHERE LOWER(email) = 'a@b.com'走索引而不是全表扫描。表达式索引是 SQLite 的强项，在 MySQL 里要绕好多功夫才做得到。

视图语法也很常规：CREATE VIEW view_name AS SELECT ...。略微特殊的是 SQLite 里可以用WITH子句来定义视图，但本质没有区别。视图在 SQLite 里可以理解为"把一条 SELECT 语句当成虚拟表"，用来简化高频查询非常好用。

触发器是 SQLite 语法里最后一个需要认真对待的高级对象。基本语法：

CREATE TRIGGER trg_log_product_updated AFTER UPDATE ON product FOR EACH ROW BEGIN INSERT INTO product_log (product_id, old_price, new_price, changed_at) VALUES (OLD.id, OLD.price, NEW.price, datetime('now', 'localtime')); END;

OLD和NEW是触发器内部的隐式引用，分别代表修改前的行和修改后的行。SQLite 的触发器对递归默认是关闭的，意味着在触发器里面再去更新同一张表通常不会无限递归，但设计逻辑时还是别依赖这点，避免写出脆弱的链路。

5. 从 MySQL 迁到 SQLite：语法坑位逐个排雷

5.1 最常踩的语法差异清单

这些年帮别人做数据迁移时，我总结了一个小清单，凡是 MySQL 迁 SQLite 的项目，先过一遍这个表能省掉大量调试时间：

这张表不是告诉你可以直接照抄，而是提醒你：凡是涉及"类型声明、日期处理、字符串拼接、ALTER TABLE"的操作，SQLite 很可能跟你想的不一样。

5.2 迁移流程的落地经验

从 MySQL 迁到 SQLite 最简单的方式，是把数据导成通用的 SQL 脚本再导入。SQLite 命令行工具自带.dump和.read，适合做小规模数据迁移：

sqlite3 new.db < data.sql

但生产环境的数据量一上来，这种裸 SQL 脚本的效率就很一般。更合理的流程是这样：

1. 先用工具把 MySQL 的表结构转成 SQLite 兼容的建表语句；
2. 导出数据时优先用 CSV 或二进制格式，分批写入；
3. 导入 SQLite 时统一放在事务里，每 1000 条左右一次提交；
4. 导入完成后重建所有索引、触发器、视图，并且一一验证。

我自己在迁移时有个习惯：把外键开关和完整性检查写成一段固定脚本，每次导入完先跑一遍再说：

PRAGMA foreign_keys = ON; PRAGMA integrity_check; PRAGMA foreign_key_check;

integrity_check会扫描整个数据库文件和内部页面，确认有没有损坏；foreign_key_check会逐条检查外键引用是否成立。这两个结果都是 "ok" 时，我才会继续往下走业务联调。

5.3 别忽略 EXPLAIN QUERY PLAN

迁移完成后，SQL 语法能跑通只是第一步，查询性能才是长期要面对的。SQLite 的性能排查手段比 MySQL 简单直接，核心就是EXPLAIN QUERY PLAN：

EXPLAIN QUERY PLAN SELECT * FROM user WHERE email = 'a@b.com';

它不执行真正的查询，只输出执行计划。你会在结果里看到这次查询走了SCAN user（全表扫描）还是SEARCH user USING INDEX idx_lower_email（索引搜索）。看到SCAN的时候，就说明你辛苦建的部分索引没被用上，要回头检查表达式是否和索引表达式完全一致。

这种排查方式对 SQLite 来说比任何 ORM 日志都直接。每当有人抱怨"SQLite 太慢"，我都让他先跑一遍EXPLAIN QUERY PLAN，多半能找出问题出在语法写法上，而不是数据库本身。

最后一个我自己的体会：SQLite 语法并不难，难的是改变思维。你只要接受"它是嵌入式数据库、它在类型和 ALTER 上很保守、它的并发模型是单写者"这三个前提，遇到绝大多数奇怪报错时都不会慌。下次再碰到database is locked、Cannot add a NOT NULL column、no such function: NOW这类错误，先按这个思路排查，大概率比查搜索引擎快。