跳到主要内容

SQL模板算字

概述​

SQL模板算字是一个强大的动态SQL生成工具，类似于MyBatis中的SQL模板功能。它支持使用{{}}语法进行参数替换，并提供了丰富的功能来处理动态SQL生成，同时具备完善的安全防护机制。

核心特性​

  • ✅ MyBatis风格语法: 使用{{}}标记参数位置
  • ✅ 嵌套属性访问: 支持user.name、user.profile.email等深层属性访问
  • ✅ 默认值支持: {{param:defaultValue}}语法设置默认值
  • ✅ 修饰符功能: 支持数据类型转换和格式化修饰符
  • ✅ SQL注入防护: 内置安全检查，防止SQL注入攻击
  • ✅ 严格模式: 可选的严格参数检查模式
  • ✅ 空白字符清理: 自动优化SQL格式
  • ✅ 详细的执行报告: 提供参数替换详情和性能统计

基本用法​

算字配置​

参数类型默认值说明
enableSafeModebooleantrue启用SQL注入防护
strictModebooleanfalse严格模式：未找到参数时抛出错误
nullValuestring'NULL'空值的替换字符
trimWhitespacebooleantrue自动清理多余空白字符

输入参数​

参数类型必需说明
templatestring✅SQL模板字符串，使用{{}}标记参数位置
paramsobject❌参数对象，包含要替换的参数值

输出结果​

字段类型说明
sqlstring生成的SQL语句
originalTemplatestring原始模板字符串
parametersobject输入的参数对象
replacedParamsarray参数替换详情列表
unusedParamsarray未使用的参数列表
executedAtstring执行时间戳
executionTimenumber执行耗时（毫秒）
templateLengthnumber模板长度
sqlLengthnumber生成SQL长度

使用示例​

1. 基本参数替换​

{
  "template": "SELECT * FROM users WHERE id = {{userId}} AND name = {{userName}}",
  "params": {
    "userId": 123,
    "userName": "张三"
  }
}

输出结果:

SELECT * FROM users WHERE id = 123 AND name = 张三

2. 使用默认值​

{
  "template": "SELECT * FROM users WHERE status = {{status:active}} LIMIT {{limit:10}}",
  "params": {
    "status": "inactive"
  }
}

输出结果:

SELECT * FROM users WHERE status = inactive LIMIT 10

3. 嵌套属性访问​

{
  "template": "SELECT * FROM users WHERE email = {{user.profile.email}} AND age > {{user.age}}",
  "params": {
    "user": {
      "age": 25,
      "profile": {
        "email": "user@example.com"
      }
    }
  }
}

输出结果:

SELECT * FROM users WHERE email = user@example.com AND age > 25

4. 修饰符功能​

字符串修饰符​

{
  "template": "SELECT * FROM users WHERE name = {{name|upper|quote}} AND city = {{city|lower|trim}}",
  "params": {
    "name": "john doe",
    "city": "  BEIJING  "
  }
}

输出结果:

SELECT * FROM users WHERE name = 'JOHN DOE' AND city = beijing

类型转换修饰符​

{
  "template": "UPDATE users SET age = {{age|int}}, is_active = {{active|bool}} WHERE id = {{id}}",
  "params": {
    "age": "25",
    "active": "true",
    "id": 123
  }
}

输出结果:

UPDATE users SET age = 25, is_active = 1 WHERE id = 123

数组处理修饰符​

{
  "template": "SELECT * FROM users WHERE id IN ({{ids|join}}) AND tags LIKE {{tags|join:|}}",
  "params": {
    "ids": [1, 2, 3, 4],
    "tags": ["tech", "java", "sql"]
  }
}

输出结果:

SELECT * FROM users WHERE id IN (1,2,3,4) AND tags LIKE tech|java|sql

修饰符参考​

基础修饰符​

修饰符说明示例
upper转换为大写{{name|upper}} → JOHN
lower转换为小写{{name|lower}} → john
trim去除首尾空白{{name|trim}} → john
quote添加单引号并转义{{name|quote}} → 'john'
doublequote添加双引号并转义{{name|doublequote}} → "john"

类型转换修饰符​

修饰符说明示例
int转换为整数{{age|int}} → 25
float转换为浮点数{{price|float}} → 99.99
bool转换为布尔值{{active|bool}} → 1 或 0
json转换为JSON字符串{{data|json}} → {"key":"value"}

数组/字符串修饰符​

修饰符说明示例
length获取长度\{\{items|length\}\} → 3
reverse反转数组或字符串\{\{items|reverse\}\} → [3,2,1]
join数组连接（默认逗号）`{{items
join:separator使用指定分隔符连接\{\{items|join:|\}\} → 1|2|3

参数化修饰符​

修饰符说明示例
substr:start:length字符串截取{{text|substr:0:5}} → hello
pad:length:char字符串填充{{id|pad:5:0}} → 00123
replace:search:replace字符串替换{{text|replace: :_}} → hello_world

高级功能​

1. 复杂查询示例​

{
  "template": "SELECT u.*, p.email, p.phone FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.status = {{status:active}} AND u.created_at >= {{startDate}} ORDER BY u.{{sortBy:created_at}} {{sortOrder:DESC}} LIMIT {{limit:20}}",
  "params": {
    "status": "verified",
    "startDate": "2024-01-01",
    "sortBy": "name",
    "sortOrder": "ASC",
    "limit": 50
  }
}

2. 动态更新语句​

{
  "template": "UPDATE users SET name = {{user.name|quote}}, email = {{user.email|lower|quote}}, age = {{user.age|int}}, updated_at = NOW() WHERE id = {{user.id|int}}",
  "params": {
    "user": {
      "id": "123",
      "name": "张三",
      "email": "ZHANG@EXAMPLE.COM",
      "age": "30"
    }
  }
}

3. 条件插入语句​

{
  "template": "INSERT INTO user_logs (user_id, action, details, created_at) VALUES ({{userId|int}}, {{action|quote}}, {{details|json|quote}}, {{timestamp:NOW()}})",
  "params": {
    "userId": 123,
    "action": "login",
    "details": {
      "ip": "192.168.1.1",
      "userAgent": "Mozilla/5.0..."
    }
  }
}

安全特性​

SQL注入防护​

SQL模板算字内置了多层SQL注入防护：

  1. 危险关键字检测: 自动检测DROP、DELETE、UPDATE等危险SQL关键字
  2. 特殊字符转义: 自动转义单引号、分号等危险字符
  3. 系统函数拦截: 检测xp_、sp_等系统存储过程调用

示例:

{
  "template": "SELECT * FROM users WHERE name = {{name}}",
  "params": {
    "name": "'; DROP TABLE users; --"
  }
}

安全输出:

SELECT * FROM users WHERE name = ''; DROP TABLE users; --

严格模式​

启用严格模式时，未找到的参数会导致执行失败：

{
  "enableSafeMode": true,
  "strictMode": true,
  "template": "SELECT * FROM users WHERE id = {{unknownParam}}",
  "params": {}
}

错误输出:

{
  "error": "参数 'unknownParam' 未找到且没有默认值",
  "failed": true
}

工作流集成​

在工作流中使用​

  1. 添加SQL模板节点

    • 从算字库中选择"SQL模板"
    • 配置模板字符串和参数

  2. 连接数据源

    • 将前置节点的输出作为参数输入
    • 支持动态参数绑定

  3. 后续处理

    • 将生成的SQL传递给数据库执行节点
    • 或用于日志记录、审计等场景

与其他算字配合​

{
  "workflow": [
    {
      "type": "data-source",
      "output": "userData"
    },
    {
      "type": "sql-template",
      "inputs": {
        "template": "SELECT * FROM orders WHERE user_id = {{userData.id}} AND status = {{status:pending}}",
        "params": "{{userData}}"
      },
      "output": "sqlQuery"
    },
    {
      "type": "database-executor",
      "inputs": {
        "sql": "{{sqlQuery.sql}}"
      }
    }
  ]
}

性能优化​

最佳实践​

  1. 模板缓存: 相同的模板会被自动优化
  2. 参数预处理: 提前进行数据类型转换
  3. 合理使用修饰符: 避免过长的修饰符链
  4. 批量处理: 对于大量相似查询，考虑批量处理

性能指标​

  • 处理速度: 通常在1-5毫秒内完成
  • 内存使用: 支持最大1MB模板大小
  • 并发支持: 支持高并发场景

错误处理​

常见错误类型​

错误类型原因解决方案
模板为空未提供template参数检查输入参数
参数未找到严格模式下缺少必需参数提供参数或设置默认值
修饰符错误使用了不支持的修饰符检查修饰符拼写和语法
类型转换失败无法转换数据类型确保数据格式正确

错误响应格式​

{
  "error": "错误描述信息",
  "originalTemplate": "原始模板",
  "parameters": {},
  "executedAt": "2024-01-01T00:00:00.000Z",
  "failed": true
}

故障排除​

调试技巧​

  1. 检查参数替换详情: 查看replacedParams字段
  2. 关注未使用参数: 检查unusedParams可能的拼写错误
  3. 启用详细日志: 查看控制台输出的调试信息
  4. 测试简单模板: 从基本功能开始逐步复杂化

常见问题​

Q: 为什么我的参数没有被替换？ A: 检查参数名拼写、嵌套路径是否正确，以及是否启用了严格模式。

Q: 如何处理包含特殊字符的数据？ A: 使用quote修饰符自动转义，或启用安全模式进行自动处理。

Q: 生成的SQL格式不整齐怎么办？ A: 启用trimWhitespace选项自动清理空白字符。

版本信息​

  • 当前版本: 1.0.0
  • 兼容性: Node.js 14+
  • 更新日志: 查看项目更新记录

相关资源​