引子

当你第一次在SQLite中创建表时，可能会被DEFAULT这个关键字深深吸引——这个看起来人畜无害的语法糖，既能简化数据插入操作，又能保持数据完整性。但就像学骑自行车总要先摔几次，开发者在实践中总会在某个加班的深夜，对着DEFAULT的异常行为陷入深深的困惑：默认值为什么不生效？动态函数调用为何失效？今天咱们就拨开迷雾，通过真实场景案例来聊聊其中奥妙。

一、基础用法：正确声明默认值的方法

技术栈：SQLite 3.37.0

1.1 静态默认值示范

-- 创建用户表，设置默认角色为普通用户
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    role TEXT DEFAULT 'user',  -- 文本型默认值
    created_at TEXT DEFAULT '2023-01-01',  -- 固定日期
    login_count INTEGER DEFAULT 0  -- 数字型默认值
);

当插入数据时若未指定对应字段：

INSERT INTO users (username) VALUES ('码农小李');
-- 实际存储值：('码农小李', 'user', '2023-01-01', 0)

1.2 表达式动态默认值

-- 创建订单表，带动态默认时间戳
CREATE TABLE orders (
    order_id TEXT PRIMARY KEY,
    total_amount REAL CHECK(total_amount > 0),
    status TEXT DEFAULT 'pending',
    created_time TEXT DEFAULT (datetime('now'))  -- 使用函数动态生成
);

INSERT INTO orders (order_id, total_amount) 
VALUES ('OD202307051234', 99.9);
-- 系统将自动填入当前UTC时间到created_time字段

二、容易踩坑的五个典型案例

2.1 NULL的诡异魔法

CREATE TABLE null_trap (
    id INTEGER PRIMARY KEY,
    name TEXT DEFAULT '无名氏',
    age INTEGER DEFAULT NULL  -- 显式声明NULL默认值
);

INSERT INTO null_trap (id) VALUES (1);
SELECT * FROM null_trap;  -- 得到(1, '无名氏', NULL)

-- 但当这样执行时：
INSERT INTO null_trap (id, name, age) VALUES (2, NULL, NULL);
-- name字段将存储NULL而非默认的'无名氏'

核心规律：只要在插入语句中显式提供字段（哪怕给的是NULL），就会覆盖默认值。记住：DEFAULT仅在字段完全未指定时生效。

2.2 动态函数的定时陷阱

CREATE TABLE timestamp_log (
    event_id INTEGER PRIMARY KEY,
    log_time TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%S','now'))
);

-- 批量插入测试
INSERT INTO timestamp_log DEFAULT VALUES;
INSERT INTO timestamp_log DEFAULT VALUES;

SELECT * FROM timestamp_log;
-- 两个记录的时间相同？！
-- 因为事务内部的now()仅执行一次，要获取实时值需改用CURRENT_TIMESTAMP

2.3 连锁反应的默认顺序

CREATE TABLE dependencies (
    a INTEGER DEFAULT 5,
    b INTEGER DEFAULT (a * 2)  -- 依赖前字段的默认值
);

INSERT INTO dependencies (a) VALUES (10);
SELECT b FROM dependencies;  -- 20 ✔️

-- 但如果字段顺序倒置：
CREATE TABLE reverse_dep (
    b INTEGER DEFAULT (a * 2),
    a INTEGER DEFAULT 5
);

INSERT INTO reverse_dep DEFAULT VALUES;
-- 触发错误！因为a还未初始化

4.4 动态默认值的表结构变更陷阱

-- 初始表结构
CREATE TABLE configs (
    key TEXT PRIMARY KEY,
    value TEXT DEFAULT 'unset'
);

-- 修改表结构增加字段
ALTER TABLE configs ADD COLUMN last_modified TEXT DEFAULT datetime('now');

-- 现有记录的last_modified字段不会自动填充！
-- 必须显式更新已有数据

三、关联技术的组合应用

3.1 触发器与默认值联合作战

-- 带版本号的用户表
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,
    profile_data TEXT,
    version INTEGER DEFAULT 1
);

-- 创建触发器自动递增版本号
CREATE TRIGGER update_version 
AFTER UPDATE ON user_profiles
BEGIN
    UPDATE user_profiles 
    SET version = version + 1 
    WHERE user_id = OLD.user_id;
END;

-- 更新操作将触发版本号自增
UPDATE user_profiles SET profile_data = 'new data' WHERE user_id = 1;

3.2 CHECK约束增强守卫

CREATE TABLE temperature_records (
    record_time TEXT DEFAULT (datetime('now')),
    celsius REAL DEFAULT 20.0 CHECK(celsius BETWEEN -50 AND 100)
);

-- 插入非法值将失败
INSERT INTO temperature_records (celsius) VALUES (150);
-- 违反CHECK约束

四、应用场景分析

4.1 自动审计追踪场景

对于需要记录创建/修改时间的业务表，DEFAULT CURRENT_TIMESTAMP能有效减少重复代码，但要注意批量操作的颗粒度。

4.2 多租户系统字段默认值

在SaaS系统中，可以通过DEFAULT设置租户ID字段：

CREATE TABLE tenant_data (
    id INTEGER PRIMARY KEY,
    data_content TEXT,
    tenant_id TEXT DEFAULT current_tenant_id()  -- 假设存在获取当前租户的自定义函数
);

4.3 系统配置项管理

通过DEFAULT设置配置项的初始值，结合ALTER TABLE支持动态扩展：

-- 新增开关配置项
ALTER TABLE system_config 
ADD COLUMN auto_update_flag INTEGER DEFAULT 1 CHECK(auto_update_flag IN (0,1));

五、技术优缺点对照表

5.1 优势特性

  • 简洁性：相比触发器实现更轻量
  • 原子性保障：与事务操作完美整合
  • 动态能力：支持表达式和函数调用

5.2 已知局限性

  • 字段变更困难：ALTER TABLE无法修改现有DEFAULT值
  • 时间精度缺陷：datetime默认只到秒级
  • 表达式复杂度受限：不能包含子查询
  • 跨版本不兼容：旧版本不支持生成列（GENERATED ALWAYS）

六、六大避坑守则

  1. NULL显式声明陷阱：想清楚是否需要显式设置NULL默认值
  2. 时间函数时区困惑：建议始终使用UTC时间避免本地时区干扰
  3. 字段顺序依赖：避免反向引用未初始化的字段
  4. 数据库版本特性：确认使用的SQLite版本是否支持某些高级功能
  5. 迁移兼容性检查：表结构变更时需重新审视默认值逻辑
  6. 索引优化影响：默认值不会提高查询性能，仍需要适当索引

七、总结思考

作为嵌入式数据库的典范，SQLite的DEFAULT机制在简化开发方面表现出色，但也需要开发者理解其设计哲学。在实践中，要注意区分默认值与约束的适用场景：默认值是温柔的填充者，而CHECK约束才是严格的守卫者。当遇到需要复杂逻辑的场景时，应当及时引入触发器或应用层校验，让DEFAULT回归其最初的设计定位——辅助性的字段初始化工具。