跳到主要内容

SQL模板

SQL模板是什么​

SQL 模板为开发者提供了在 MySQL 类型的数据模型上，直接运行 SQL 命令的方式。通过 SQL 模板，可以更容易通过 SQL 命令查询、操作数据库；在数据模型方法不满足复杂查询、多表联表查询等的情况下，可以通过 SQL 模板来解决这类问题。

另外，SQL 模板本身在运行时由于限定了参数，同时默认带有权限条件，因此可以在小程序端、Web端也发起调用，相比直接的 SQL 命令，适用场景更多，使用更便利。

使用方式​

当前 SQL 模板与数据模型关联，相关 SQL 操作的主表即为所关联数据模型对应的表。

在数据模型的管理页面，可以进入 SQL 模板页面，创建及管理 SQL 模板。

创建​

在创建 SQL 模板时，需要填写模板名称及 SQL 命令，也可以填写描述用于记录模板相关信息。

  • 模板名称：支持英文、数字及下划线，模板名称将用于后续调用，需要保证全局唯一。
  • SQL 命令：支持 SELECT、INSERT INTO、UPDATE、DELETE 和 REPLACE INTO 命令。
  • 描述：用于记录描述 SQL 模板相关内容。

测试​

在创建时，或创建后，均可以测试运行 SQL 模板。在测试运行时，需要根据 SQL 命令中的参数格式，填写测试用的参数值。

  • SQL语句中的自定义参数均需要填写入参后才可运行。
  • 测试时选择SQL执行的环境，体验数据及正式数据：如果您的环境支持区分体验及正式数据，在测试时将可以选择在体验数据上测试或在正式数据上测试；测试将直接操作及影响到数据库表中的数据，请您谨慎选择运行。

测试运行后，可以通过结果页面，查看到实际运行结果输出。

管理​

您可以通过 SQL 模板的页面查询到当前模型对应的 SQL 模板，可以针对 SQL 模板进行修改、测试、删除。

修改或删除 SQL 模板可能影响到调用 SQL 模板的页面组件、页面 Query、代码等，请谨慎操作。

调用​

  1. 通过 SDK 中模型方法即可调用模型下的 SQL 模板。

方法使用前需要进行 SDK 初始化，更多 SDK 详情可以查询 SDK 文档

控制台 SQL 模版定义及执行示例

alt text


-- student_select2 对应 SQL

SELECT * 
FROM `{{ model.tableName() }}` 
WHERE  `start_date` > {{ d_start }}  AND `end_date` < {{ d_end }}

SDK 执行对应模版：


const res = await app.models.student.runSQLTemplate({
      templateName: 'student_select2',
      envType: "pre",  
      params: {
        "d_start": "2025-01-01",
        "d_end": "2026-01-01"
      }
    })
console.log(res)

方法说明：

  • 调用方法：models.< modelName >.runSqlTemplate， 其中 modelName 需要替换为具体的数据模型标识。
  • 参数：templateName，填写为 SQL 模板名称
  • 参数：params ，以 key-value 形式提供变量名与变量值的对应；其中变量名对应为 SQL 模板中写入的参数。变量值则是 SQL 模板执行时自动填入的值。
  • 参数：envType， 体验数据或正式数据操作指示，可选参数，默认值为 prod，可选填：prod，pre。
  1. 通过Http Api调用，参考 http api 执行模型sql模版

SQL 语句​

支持命令​

当前 SQL 模板中仅支持如下的数据操作命令，不支持表操作、库操作相关命令：

  • SELECT
  • INSERT INTO
  • REPLACE INTO
  • UPDATE
  • DELETE

约定​

  • 一个 SQL 模板中仅可以写入一条 SQL 命令，不支持通过“;“引入多条 SQL 命令。
  • SQL 模版中的主表必须是当前模型的主表，不允许跨模型访问，但联表时的子表可以是同库下的任意表，如：当前模型标识是 student时，可以使用 SELECT * FROM student，不能使用 SELECT * FROM user，INSERT等其他指令同理。
  • 当插入数据时（INSERT / REPLACE）， 为了保证数据安全，一定要写入系统字段 owner 和 _openid，可以采用函数表达式赋值( user.userId()、user.openId() )

参数​

SQL 语句中，支持通过 {{param}} 的方式引入变量参数。变量参数用于在实际调用和运行 SQL 模板时传递变量值。

参数写法使用{{ }} 符号进行包裹。

内置函数​

为了结合系统内生逻辑使用 SQL 模版，系统提供了内置函数，使用户在使用 SQL 模版时可以完成模型部分内生逻辑，如：行权限，数据id生成、用户标识等。

函数类别函数表达式说明
模型model.tableName()获得 SQL 模版所属模型对应的表名，自动识别体验和生产
模型model.tableName('my_model')获得指定模型对应的表名
模型model.dataId()自动生成系统风格的数据ID
用户user.userId()在运行时，获取当前登录人的用户标识
用户user.openId()在运行时，获取当前登录人的openId，如果是小程序登录用户，则对应微信openId
权限auth.rowPermission()在运行时，获取当前登录人对应角色的模型行权限，模型为sql模版绑定模型
权限auth.rowPermission('my_model')在运行时，获取当前登录人对应角色的模型行权限，模型由用户指定
系统system.currentEpoch()获取当前 10 位秒级时间戳
系统system.currentEpochMillis()获取当前 13 位毫秒级时间戳

以上是平台提供的系统级函数，除此以外还可以选择使用 MYSQL 原生函数，参考 mysql内置函数

模型函数​

  • model.tableName()
  • model.tableName('my_model_name')

说明：获得模型对应的表名，自动识别体验和生产

当 SQL 模版所属模型标识是 xyz 时，model.tableName() 等价于 model.tableName('xyz')

背景：

  1. MySQL类型的数据模型，底层使用 MySQL 表作为存储载体，在数据模型演进中，模型对应表名的命名规则有版本差异，使用者可以在“云开发平台 -> 数据库 -> 模型 -> 基础信息”中查看模型表名，在 SQL 中直接使用，也可以不关心表名规则，通过函数方式直接操作模型表。

  2. 数据模型有staging概念，即体验环境和生产环境，两个环境数据是表维度隔离的，不同环境使用不同的表，通过以上函数可以动态的根据执行环境选择对应的表，使用者可以只关心执行环境类型而不用关心环境下模型表名的映射关系，系统自动完成。

控制台模型名指引

示例：

-- 直接使用表名
SELECT * 
FROM `student-preview`;

-- 使用 SQL 模版绑定的当前模型数据
SELECT * 
FROM {{ model.tableName() }};

-- 使用模型student和class联表数据，当前绑定student模型
SELECT student.*,  class.*
FROM {{ model.tableName() }} as student
INNER JOIN {{ model.tableName('class_info') }} as class
ON class.student_id = student._id;

  • model.dataId()

说明：自动生成系统风格的数据ID

背景：每个模型都有主键标识，命名为 "_id"（系统字段），用户在操作模型创建数据时，系统会自动为"_id"分配一个字符串主键，是全局唯一的标识，在 SQL 模版中写入数据时，可以手动指定数据标识，也可以使用此函数交给系统自动创建。

示例：

-- 手动写入数据标识
INSERT INTO `my_model_table` (`_id`, `name`, `age`)
VALUES ( 'xyz', '张三', 18);

-- SELECT * FROM `my_model_table`
-- ('xyz', '张三', 18)

-- 函数写入数据标识
INSERT INTO `my_model_table` (`_id`, `name`, `age`)
VALUES ( {{ model.dataId() }}, '张三', 18);

-- SELECT * FROM `my_model_table`
-- ('B3PVJN4U2C', '张三', 18)

权限函数​

  • auth.rowPermission()
  • auth.rowPermission('my_model_name')

说明：在运行时，获取当前登录人对应角色的模型行权限，可以指定模型的行权限，也可以默认使用 SQL 模版所属的模型行权限。

当 SQL 模版所属模型标识是 xyz 时，auth.rowPermission() 等价于 auth.rowPermission('xyz')

背景：模型支持根据登录人角色设置读写行权限，用来约束登录人对该模型的访问行为。

示例： 先设置合理的模型权限，在通过 SQL 模版使用行权限

模型权限

读权限


-- 单表读权限
SELECT * 
FROM 'student' 
WHERE `price` > 100 AND {{ auth.rowPermission() }}
-- auth.rowPermission() 表示默认模型'student'的行权限


-- 联表读权限
SELECT * 
FROM 'model1_table' AS table1 
LEFT JOIN 'model2_table' AS table2 
ON table2.relate_id = table1._id AND {{ auth.rowPermission('model2') }}
WHERE table1.`price` > 100 AND {{ auth.rowPermission() }}
-- auth.rowPermission() 表示默认模型'model1'的行权限
-- auth.rowPermission('model2') 表示指定模型'model2'的行权限

写权限

-- 仅删除当前登录人有写权限的数据
DELETE FROM 'student' 
WHERE `age` > 10 AND {{ auth.rowPermission() }}

-- 仅更新当前登录人有写权限的数据
UPDATE 'student' 
SET `name` = '张三' 
WHERE {{ auth.rowPermission() }}

用户函数​

  • user.userId()
  • user.openId()

说明：获取当前登录人的用户标识，userId 和 openId 都是用户标识，用在不同场景中。通俗而言：

  1. 当登录用户来自小程序时，openId 表示小程序中的 openId，openId 与 userId 不同，userId 表示系统用户标识。
  2. 当登录用户来自非小程序时，多数场景下 userId 和 openId 同值，表示系统用户标识。
  3. 在模型的系统字段中，owner、createBy、updateBy 数据来自 user.userId(), _openid 数据来自 user.openId()

示例：


-- 约定：插入数据时写入owner和_openId
INSERT INTO 'my_model_table' (`_id`,`name`,`age`, `owner`, `_openid`)
VALUES ('xyz', '张三', 18, {{ user.userId() }}, {{ user.openId() }})

系统函数​

  • system.currentEpoch()
  • system.currentEpochMillis()

说明：返回 10 位秒级时间戳和 13 位毫秒级时间戳

背景：在 MySQL 内置函数中，表示 10 位秒级时间戳为 UNIX_TIMESTAMP(NOW()) ， 表示 13 位毫秒级时间戳为 UNIX_TIMESTAMP(NOW(3)) * 1000。

在系统中，多数情况下都使用 13 位毫秒级时间戳，模型系统字段 createdAt、updatedAt 采用该方式存储。为了简化操作，可以使用系统函数自动完成。

示例：


UPDATE 'student' 
SET `name` = '张三',  `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = 'xyz';

最佳实践​

推荐用法​

  1. 表名使用函数表达式：如果使用 SQL 模版归属模型的表，使用函数 model.tableName() 表示；如果使用当前环境下其他的表（联表），使用函数 model.tableName('your_model_name')

SELECT * FROM {{ model.tableName() }} as t1
LEFT JOIN {{ model.tableName('other_model_name') }} as t2
ON t2.relate_id = t1._id;

  1. 创建数据时：
  • 允许手动给主键 _id 赋值，推荐使用函数赋值 model.dataId()
  • 必填系统字段 owner、_openid ，使用函数 user.userId()、user.openId()赋值
  • 根据业务需要，选填系统字段 createBy、updateBy（使用函数 user.userId()）createdAt、updatedAt（使用函数 system.currentEpochMillis()）

INSERT INTO {{ model.tableName() }} 
(`_id`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})

  1. 更新、删除时，记录修改时间 updatedAt，使用函数 system.currentEpochMillis()

UPDATE 'student'
SET `name` = '张三', `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = 'xyz';

  1. 读写数据带行权限，使用变量 auth.rowPermission() 或者 auth.rowPermission('model_name')

写权限仅约束更新和删除，创建不受约束


-- 写权限
UPDATE 'student' 
SET `name` = '张三' 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}


-- 读权限
SELECT * FROM {{ model.tableName() }}
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}

案例分享​

接下来，我们结合具体场景，分享如何使用 SQL 模版进行数据库操作。 假设现在有以下模型：

模型标识：teacher_info

字段说明类型系统字段
name教师姓名文本否
age年龄数字否
is_head_teacher是否班主任布尔否
gender性别文本否
class_id所属班级文本否
owner所有人关联关系是
_openid记录创建者文本是
createBy创建人关联关系是
updateBy修改人关联关系是
createdAt创建时间日期时间是
updatedAt更新时间日期时间是

创建数据​

新建教师信息

  • 定义 SQL 模版, 模版标识: insert_teacher_info_01

INSERT INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})

  • sdk 调用

使用模型标识 teacher_info，SQL模版标识 insert_teacher_info_01，提供自定义变量入参

const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'insert_teacher_info_01',
    envType: "pre",
    params: {
      "name": "张老师",
      "age": 30,
      "is_head_teacher": true,
      "gender": "男"
    }
  })

创建或更新数据​

更新教师信息，如果教师不存在则新建 (依据主键或唯一键是否存在，选择新建或更新)

注意： 如果主键或唯一字段存在时，MySQL执行机制是先删除再创建。

  • 定义 SQL 模版, 模版标识: replace_teacher_info_01

REPLACE INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ id }}, {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})

  • sdk 调用

使用模型标识 teacher_info，SQL模版标识 replace_teacher_info_01，提供自定义变量入参

const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'replace_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456", // 如果 id 数据存在则更新，不存在则新建
      "name": "张老师",
      "age": 28,
      "is_head_teacher": false,
      "gender": "男"
    }
  })

语法糖​

当插入数据入参过多时，可以使用语法糖简化 SQL 模版入参定义，通过单一变量数组类型完成。

  • 定义 SQL 模版, 模版标识: insert_teacher_info_02

INSERT INTO {{ model.tableName() }} 
(`_id`, `name`, `age`, `is_head_teacher`, `gender`, `owner`, `_openid`, `createBy`, `updateBy`, `createdAt`, `updatedAt`)
VALUES
({{ model.dataId() }}, {{ myParam }}, {{ user.userId() }}, {{ user.openId() }}, {{ user.userId() }}, {{ user.userId() }}, {{ system.currentEpochMillis() }}, {{ system.currentEpochMillis() }})

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'insert_teacher_info_02',
    envType: "pre",
    params: {
       "myParam": ["王老师", 31, true, "女"]
    }
  })

注意： 语法糖入参，数组的长度必须与 SQL 模版中字段的数量一致，且按照插入字段顺序依次写入值。

此时，SQL 模版中变量 {{ myParam }} 等价于 {{ name }}, {{ age }}, {{ is_head_teacher }}, {{ gender }}

更新数据​

修改教师信息 （仅当前登录用户有权限时可写）

  • 定义 SQL 模版, 模版标识: update_teacher_info_01

UPDATE {{ model.tableName() }} 
SET `name` = {{ name }}, `age` = {{ age }}, `updatedAt` = {{ system.currentEpochMillis() }}
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'update_teacher_info_01',
    envType: "pre",
    params: {
      "name": "李老师",
      "age": 30,
      "id": "123456"
    }
  })

删除数据​

删除教师信息 （仅当前登录用户有权限时可写）

  • 定义 SQL 模版, 模版标识: delete_teacher_info_01

DELETE FROM {{ model.tableName() }} 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'delete_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456"
    }
  })

单表查询​

通过主键 _id 查询单条数据（仅当前登录用户有权限时可见）

  • 定义 SQL 模版, 模版标识: get_teacher_info_01

SELECT * 
FROM {{ model.tableName() }} 
WHERE `_id` = {{ id }} AND {{ auth.rowPermission() }}

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_01',
    envType: "pre",
    params: {
      "id": "123456"
    }
  })

查询所有年龄大于 20 岁的教师信息（仅当前登录用户有权限时可见）

  • 定义 SQL 模版, 模版标识: get_teacher_info_02

SELECT * 
FROM {{ model.tableName() }} 
WHERE `age` > {{ age }} AND {{ auth.rowPermission() }}

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_02',
    envType: "pre",
    params: {
      "age": 20
    }
  })

联表查询​

模糊查询张姓的教师信息，并关联查询其对应的班级信息（仅当前登录用户有权限时可见）

  • 定义 SQL 模版, 模版标识: get_teacher_info_03

SELECT teacher.* , class.*
FROM {{ model.tableName() }} AS teacher
LEFT JOIN {{ model.tableName('class_info') }} AS class
ON teacher.class_id = class._id
WHERE teacher.`name` LIKE {{ var_name }} AND {{ auth.rowPermission() }}
ORDER BY teacher.`age` DESC
LIMIT 20 OFFSET 0

  • sdk 调用
const res = await app.models.teacher_info.runSQLTemplate({
    templateName: 'get_teacher_info_03',
    envType: "pre",
    params: {
      "var_name": "张%"
    }
  })

复杂查询：多对一/一对多/多对多​

  • 多模型结构 ER 图 模型 ER 图

  • 数据准备

学生模型 student

班级保存模型班级的数据标识，学籍保存模型学籍的数据标识，班主任保存模型教师的数据标识

数据标识 _id姓名 name年龄 age性别 gender班级 class_id学籍 student_status_id班主任 teacher_id
B3PXVMR2D6曹操6男B3PVJ8NGUQB3PS640MAQB3PV593ASU
B3PY0AXLA4刘备7男B3PVJ8NGUQB3PS6P92Y0B3PV593ASU
B3PY3ZCNC0孙权8男B3PVJN4U2CB3PS7JHT9SB3PV8FA8PJ
B60ZHCMB60貂蝉6女B3PVJN4U2CB3PS8ABTL1B3PV8FA8PJ

班级模型 class_info

数据标识 _id班级名称 class_name年级 grade
B3PVJ8NGUQ1班一年级
B3PVJN4U2C2班一年级

学籍模型 student_status

数据标识 _id编号 identifier状态 status是否毕业 graduation
B3PS640MAQs001正常否
B3PS6P92Y0s002正常否
B3PS7JHT9Ss003正常否
B3PS8ABTL1s004异常否

教师模型 teacher_info

数据标识 _id姓名 name年龄 age性别 gender班主任 is_head_teacher任课 teach_course
B3PV593ASU张老师40男是语文
B3PV6XZXQU李老师36女否数学
B3PV8FA8PJ王老师32女是英语

课程模型 course_info

数据标识 _id课程编号 course_id课程名称 course_name学分 credit
B3NEBZQU0Yc001语文6
B3NDVZZD0Lc002数学6
B3NECZ5SX2c003英语4

学生-课程模型 student_course

通过中间表实现学生模型和课程模型的多对多关系, 学生id保存学生模型数据标识，课程id保存课程模型数据标识

数据标识 _id学生id student_id课程id course_id
B3PYR0XVX1B3PXVMR2D6B3NEBZQU0Y
B3PYR0XVX2B3PXVMR2D6B3NDVZZD0L
B3PYR0XVX3B3PXVMR2D6B3NECZ5SX2
B3PYR0XVX4B3PY0AXLA4B3NEBZQU0Y
B3PYR0XVX5B3PY0AXLA4B3NDVZZD0L
B3PYR0XVX6B3PY3ZCNC0B3NEBZQU0Y
B3PYR0XVX7B3PY3ZCNC0B3NECZ5SX2
  • 定义联表 SQL 模版，模版标识: get_student_by_class

查询一年级 1 班的所有学生信息，包括学生姓名、年龄、性别、班主任姓名和学籍编号，并且展示学生所选修的全部课程信息，结果以年龄倒序排序


SELECT 
  stud.name AS name, stud.age AS age, stud.gender AS gender, 
  class.class_name AS class_name, class.grade AS grade,
  teacher.name AS teacher_name,
  status.identifier AS status_identifier,
  course.course_name AS course_name, course.credit AS course_credit
FROM
  {{ model.tableName() }} AS stud
INNER JOIN {{ model.tableName('class_info') }} AS class ON stud.class_id = class._id
LEFT JOIN {{ model.tableName('teacher_info') }} AS teacher ON stud.teacher_id = teacher._id
LEFT JOIN {{ model.tableName('student_status') }} AS status ON stud.student_status_id = status._id
LEFT JOIN {{ model.tableName('student_course') }} AS sc ON stud._id = sc.student_id
LEFT JOIN {{ model.tableName('course_info') }} AS course ON sc.course_id = course._id
WHERE
  class.grade = {{ grade }} AND class.class_name = {{ class_name }}
ORDER BY
  stud.age DESC

  • sdk 调用
const res = await app.models.student.runSQLTemplate({
    templateName: 'get_student_by_class',
    envType: "pre",
    params: {
      "grade": "一年级",
      "grclass_nameade": "1班"
    }
  })

补充​

  1. 在当前场景下，无法使用 MYSQL 关键字 "IS" 和 "IS NOT"，可以用以下语法替代
-- 原始语法：不支持
select * from `your_table` WHERE your_column IS NULL
-- 替代语法：支持
select * from `your_table` WHERE your_column <=> NULL


-- 原始语法：不支持
select * from `your_table` WHERE your_column IS NOT NULL
-- 替代语法：支持
select * from `your_table` WHERE !(your_column <=> NULL)