印刻数据库

外观

SQLite 函数与存储过程 ​

函数概述 ​

SQLite 支持两种主要函数类型：内置函数和自定义函数。与传统关系型数据库不同，SQLite 不直接支持存储过程，但可以通过自定义函数和触发器的组合实现类似功能。理解 SQLite 函数的使用方法和限制，对于高效开发和优化 SQLite 应用至关重要。

内置函数 ​

SQLite 提供了丰富的内置函数，涵盖字符串处理、数值计算、日期时间操作和数据聚合等多个方面。这些函数经过优化，性能优异，是开发过程中的首选。

字符串函数 ​

函数名描述示例版本要求
LENGTH(string)返回字符串长度LENGTH('SQLite') → 6所有版本
UPPER(string)转换为大写UPPER('sqlite') → 'SQLITE'所有版本
LOWER(string)转换为小写LOWER('SQLite') → 'sqlite'所有版本
SUBSTR(string, start, length)截取子字符串SUBSTR('SQLite', 2, 3) → 'QLi'所有版本
REPLACE(string, old, new)替换字符串REPLACE('SQLite', 'i', 'I') → 'SQLIte'所有版本
TRIM(string)去除首尾空格TRIM(' SQLite ') → 'SQLite'所有版本
LIKE(pattern, string)模式匹配'SQLite' LIKE 'SQL%' → 1所有版本
GLOB(pattern, string)全局匹配'SQLite' GLOB 'SQL*' → 1所有版本

数值函数 ​

函数名描述示例版本要求
ABS(number)绝对值ABS(-10) → 10所有版本
ROUND(number, digits)四舍五入ROUND(3.14159, 2) → 3.14所有版本
CEIL(number)向上取整CEIL(3.1) → 4所有版本
FLOOR(number)向下取整FLOOR(3.9) → 3所有版本
RANDOM()随机整数RANDOM() → 随机整数所有版本
MAX(value1, value2)最大值MAX(10, 20) → 20所有版本
MIN(value1, value2)最小值MIN(10, 20) → 10所有版本
TOTAL(expression)求和（忽略 NULL）TOTAL(amount) FROM orders所有版本

日期时间函数 ​

函数名描述示例版本要求
DATE(time_value)获取日期DATE('2023-12-25 14:30:00') → '2023-12-25'所有版本
TIME(time_value)获取时间TIME('2023-12-25 14:30:00') → '14:30:00'所有版本
DATETIME(time_value)获取日期时间DATETIME('now') → 当前日期时间所有版本
JULIANDAY(time_value)儒略日JULIANDAY('2023-12-25') → 2460305.5所有版本
STRFTIME(format, time_value)格式化日期时间STRFTIME('%Y-%m-%d', 'now') → '2023-12-25'所有版本
DATE(date, interval)日期计算DATE('2023-12-25', '+1 day') → '2023-12-26'所有版本

聚合函数 ​

函数名描述示例版本要求
COUNT(*)计数COUNT(*) FROM users → 用户总数所有版本
SUM(expression)求和SUM(amount) FROM orders → 订单总金额所有版本
AVG(expression)平均值AVG(price) FROM products → 平均价格所有版本
MAX(expression)最大值MAX(created_at) FROM posts → 最新帖子时间所有版本
MIN(expression)最小值MIN(stock) FROM products → 最低库存所有版本
GROUP_CONCAT(expression, separator)分组连接GROUP_CONCAT(name, ',') FROM tags → 标签列表所有版本

自定义函数 ​

自定义函数是 SQLite 的强大扩展机制，允许开发者使用编程语言（如 C、Python、Java 等）扩展 SQLite 功能，实现内置函数无法满足的需求。

自定义函数概述 ​

  • 自定义函数需要通过 SQLite API 注册到数据库连接
  • 可以在 SQL 查询中像内置函数一样使用
  • 支持多种编程语言实现
  • 可以返回 SQLite 支持的任何数据类型

Python 自定义函数示例 ​

以下示例展示了如何在 Python 中创建和使用自定义函数，实现数据加密和 JSON 处理：

python
import sqlite3
import hashlib
import json

# 连接到数据库
conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# 创建自定义函数：计算 SHA-256 哈希值
def sha256_hash(text):
    if text is None:
        return None
    return hashlib.sha256(text.encode()).hexdigest()

# 创建自定义函数：JSON 序列化
def json_serialize(data):
    if data is None:
        return None
    return json.dumps(data)

# 注册自定义函数
conn.create_function('sha256', 1, sha256_hash)
conn.create_function('json_serialize', 1, json_serialize)

# 创建用户表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        password_hash TEXT,
        profile TEXT
    )
''')

# 使用自定义函数插入数据
user_data = {
    'name': 'John Doe',
    'email': 'john@example.com',
    'age': 30
}

cursor.execute('''
    INSERT INTO users (username, password_hash, profile)
    VALUES (?, sha256(?), json_serialize(?))
''', ('john_doe', 'secure_password', user_data))

# 查询数据
cursor.execute('SELECT * FROM users WHERE username = ?', ('john_doe',))
user = cursor.fetchone()
print(f"User: {user}")

# 关闭连接
conn.commit()
conn.close()

自定义函数生产实践 ​

生产场景：电商平台需要对用户敏感数据进行加密存储，并支持 JSON 格式的用户配置

解决方案：

  1. 创建自定义加密函数，对敏感数据进行加密
  2. 创建 JSON 序列化/反序列化函数，方便存储和查询 JSON 数据
  3. 在触发器中使用自定义函数，自动处理数据加密和格式转换

自定义函数最佳实践 ​

  • 保持简洁：复杂逻辑应在应用层实现，避免在数据库层处理
  • 性能优先：自定义函数会增加查询开销，应尽量优化实现
  • 错误处理：完善的错误处理机制，避免函数崩溃导致查询失败
  • 命名规范：使用清晰、描述性的函数名，避免与内置函数冲突
  • 版本兼容：考虑不同 SQLite 版本的兼容性，避免使用特定版本特性

窗口函数 ​

从 SQLite 3.25.0 版本开始，SQLite 支持窗口函数，允许在查询结果集的特定窗口上执行聚合操作，增强了数据分析能力。

窗口函数类型 ​

排名函数 ​

函数名描述版本要求
ROW_NUMBER()为结果集中的每一行分配唯一序号SQLite 3.25.0+
RANK()为结果集中的行分配排名，相同值排名相同，后续排名跳过SQLite 3.25.0+
DENSE_RANK()为结果集中的行分配排名，相同值排名相同，后续排名连续SQLite 3.25.0+
NTILE(n)将结果集划分为 n 个大致相等的组，并为每行分配组号SQLite 3.25.0+

聚合窗口函数 ​

函数名描述版本要求
SUM(expression) OVER(...)窗口内的求和SQLite 3.25.0+
AVG(expression) OVER(...)窗口内的平均值SQLite 3.25.0+
MAX(expression) OVER(...)窗口内的最大值SQLite 3.25.0+
MIN(expression) OVER(...)窗口内的最小值SQLite 3.25.0+
COUNT(expression) OVER(...)窗口内的计数SQLite 3.25.0+

窗口函数生产案例 ​

生产场景：电商平台需要分析每日销售额趋势，计算累计销售额和移动平均值

解决方案：使用窗口函数实现复杂的数据分析

sql
-- 示例数据：销售记录表
CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 1000.00),
('2023-01-02', 1500.00),
('2023-01-03', 1200.00),
('2023-01-04', 1800.00),
('2023-01-05', 2000.00),
('2023-01-06', 1600.00),
('2023-01-07', 1900.00);

-- 使用窗口函数分析销售数据
SELECT 
    sale_date,
    amount AS daily_sales,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales,
    AVG(amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS 3_day_moving_avg,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS day_number
FROM sales;

查询结果：

sale_datedaily_salescumulative_sales3_day_moving_avgday_number
2023-01-011000.001000.001000.001
2023-01-021500.002500.001250.002
2023-01-031200.003700.001233.333
2023-01-041800.005500.001500.004
2023-01-052000.007500.001666.675
2023-01-061600.009100.001800.006
2023-01-071900.0011000.001833.337

触发器与函数结合实现存储过程功能 ​

虽然 SQLite 不直接支持存储过程，但可以通过触发器和自定义函数的组合实现类似功能。以下是一个生产环境中的实际案例：

生产场景：需要记录所有数据变更的审计日志

解决方案：

sql
-- 创建审计日志表
CREATE TABLE IF NOT EXISTS audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    record_id INTEGER,
    old_data TEXT,
    new_data TEXT,
    action_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    performed_by TEXT
);

-- 假设已通过编程语言注册了 json_serialize 自定义函数

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建插入触发器
CREATE TRIGGER users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, action, record_id, new_data, performed_by
    ) VALUES (
        'users', 'INSERT', NEW.id, json_serialize(NEW), 'system'
    );
END;

-- 创建更新触发器
CREATE TRIGGER users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, action, record_id, old_data, new_data, performed_by
    ) VALUES (
        'users', 'UPDATE', NEW.id, json_serialize(OLD), json_serialize(NEW), 'system'
    );
END;

-- 创建删除触发器
CREATE TRIGGER users_after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, action, record_id, old_data, performed_by
    ) VALUES (
        'users', 'DELETE', OLD.id, json_serialize(OLD), 'system'
    );
END;

版本差异 ​

SQLite 3.7.11 及以上 ​

  • 支持 CREATE FUNCTION 语法用于创建 SQL 函数（通过扩展实现）
  • 增强了内置函数的错误处理

SQLite 3.8.0 及以上 ​

  • 优化了字符串函数的性能
  • 增加了对 JSON 数据的基本支持

SQLite 3.25.0 及以上 ​

  • 引入窗口函数支持，包括排名函数和聚合窗口函数
  • 增强了 GROUP_CONCAT 函数的功能

SQLite 3.31.0 及以上 ​

  • 支持 UPDATE FROM 语法，增强了数据更新能力
  • 优化了自定义函数的执行性能

SQLite 3.35.0 及以上 ​

  • 支持 MERGE 语句（UPSERT 功能），简化了插入/更新操作
  • 增强了窗口函数的功能和性能

SQLite 3.38.0 及以上 ​

  • 引入了内置的 JSON 函数，如 json()、json_extract() 等
  • 减少了自定义函数的调用开销

生产运维最佳实践 ​

函数使用建议 ​

  1. 优先使用内置函数：内置函数经过优化，性能优于自定义函数
  2. 避免在 WHERE 子句中使用函数：这会导致索引失效，影响查询性能
  3. 合理使用窗口函数：窗口函数功能强大，但复杂的窗口定义会影响性能
  4. 定期更新 SQLite 版本：新版本通常包含性能优化和功能增强

自定义函数管理 ​

  1. 集中管理自定义函数：在应用启动时统一注册所有自定义函数
  2. 添加版本检查：在使用特定版本特性的自定义函数前，检查 SQLite 版本
  3. 监控函数性能：对频繁调用的自定义函数进行性能监控
  4. 编写单元测试：确保自定义函数在各种情况下都能正确工作

安全考虑 ​

  1. 验证自定义函数输入：防止 SQL 注入和恶意输入
  2. 限制函数权限：避免在自定义函数中执行危险操作
  3. 加密敏感数据：使用自定义加密函数保护敏感数据
  4. 定期审计函数使用：检查是否有不必要或危险的函数调用

常见问题 (FAQ) ​

SQLite 支持存储过程吗？ ​

SQLite 不直接支持传统意义上的存储过程，但可以通过自定义函数和触发器的组合实现类似功能。对于复杂的业务逻辑，建议在应用层实现。

如何查看 SQLite 支持哪些内置函数？ ​

可以使用 .help 命令在 sqlite3 命令行工具中查看所有内置函数，或访问 SQLite 官方文档 获取详细信息。

自定义函数会影响性能吗？ ​

自定义函数的性能取决于其实现。简单的自定义函数性能影响较小，而复杂的自定义函数可能会显著影响查询性能。在生产环境中，应谨慎使用自定义函数，并进行充分的性能测试。

窗口函数在低版本 SQLite 中如何替代实现？ ​

对于不支持窗口函数的 SQLite 版本，可以使用子查询或自连接实现类似功能，但性能会有所下降。建议升级到 SQLite 3.25.0 或以上版本，以获得更好的性能和功能支持。

如何调试自定义函数？ ​

调试自定义函数的方法取决于实现语言：

  • Python：可以使用打印语句或调试器
  • C/C++：可以使用调试器或日志记录
  • Java：可以使用日志记录或调试器

自定义函数可以在多个连接之间共享吗？ ​

自定义函数是注册到特定数据库连接的，不能在多个连接之间共享。每个新的数据库连接都需要重新注册自定义函数。

如何优化频繁调用的自定义函数？ ​

  • 优化函数实现，减少计算复杂度
  • 考虑使用内置函数替代自定义函数
  • 对于计算密集型函数，考虑缓存结果
  • 升级到最新版本的 SQLite，享受性能优化

总结 ​

SQLite 提供了丰富的函数功能，包括内置函数、自定义函数和窗口函数，虽然不直接支持传统存储过程，但可以通过触发器和自定义函数的组合实现类似功能。在实际开发中，应根据具体需求选择合适的函数类型，并遵循最佳实践，以确保性能和可维护性。

随着 SQLite 版本的不断更新，其函数功能也在不断增强，特别是窗口函数和 JSON 函数的引入，使得 SQLite 能够处理更复杂的数据处理和分析场景。在生产环境中，建议定期更新 SQLite 版本，以享受最新的功能和性能优化。

通过合理使用 SQLite 函数，可以简化 SQL 查询，提高开发效率，并实现复杂的业务逻辑，为应用程序提供强大的数据处理能力。