自有 MySQL 数据库访问

Download
聚焦模式
字号
最后更新时间： 2026-05-13 11:41:07
在选择自有 MySQL 数据库的情况下，除了使用数据模型的访问方法读写数据外，我们也提供了直接通过 SQL 命令读写数据的方法和接口，同时您也可以在云函数中，通过常规的数据库 SDK 实现数据库访问。
使用场景
常规查询或数据操作：使用数据模型方法。
复杂查询，多表或联表查询：使用模型方法中的数据库查询能力。
复杂的写入、插入、更新数据：通过云函数中的通用数据库操作方法连接并操作数据库。
使用方法
对于底层为 MySQL 数据库类型的模型，我们提供了 MySQL 类型的原生数据库查询语句，包含了两种如下方式：
使用 SQL 模板。
使用 SQL 命令。
这两种模式，SQL 模板可以通过前端小程序或后端云函数、云托管服务调用；SQL 命令仅可以从服务端调用，包括云函数、云托管或其他后端服务。
使用 SQL 模板
SQL 模板的使用方式，可以查看 SQL 模板说明。
使用 SQL 命令
MySQL 数据库类型的模型，数据模型 SDK 提供两种模式的查询方法：
$runSQL: 预编译模式, 通过参数化查询来避免 SQL 注入风险。
$runSQLRaw 原始模式, 更加灵活的模式，SQL 语句会当做原始字符串进行查询，存在 SQL 注入的风险。
注意：
1. runSQL、runSQLRaw 接口仅支持在服务端调用，如云函数/云托管/服务器等场景，不支持小程序/Web 端直接调用。
2. 建议优先采用预编译模式，避免 SQL 注入风险。
3. 当前仅开放了 SELECT 语句，如果有其他 SQL 语句需求，请通过官方社群联系我们。
预编译模式 $runSQL
预编译模式下使用参数化查询设计，结合静态模板语法和动态运行时参数，以实现灵活的数据交互。
允许开发者通过 Mustache 变量绑定语法（{{ }}）直接在 SQL 查询中嵌入静态参数，同时也支持在运行时通过 $runSQL() 方法执行时动态传递参数，可以避免直接拼接字符串导致 SQL 注入的风险。
例如：
1. 查询标题为"hello"的记录：
const result = await models.$runSQL(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE title = {{title}} limit 10",
  {
    title: "hello",
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
2. 查询阅读次数大于 1000 的记录：
const result = await models.$runSQL(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE read_num > {{num}} limit 10",
  {
    num: 1000,
  }
);
﻿
console.log(result);
// {"data":{"total":0,"executeResultList":[],"backendExecute":"23"},"requestId":"2f06b68f-e869-45cb-bb0d-82d50b3dcde0"}
3. 查询最后更新时间在某个特定时间戳之后的记录（例如：2024-06-01 00:00:00）：
const result = await models.$runSQL(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE updatedAt > UNIX_TIMESTAMP({{timestamp}})",
  {
    timestamp: "2024-06-01 00:00:00",
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
4. 查询拥有特定 banner 图片的记录：
const result = await models.$runSQL(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE banner = '{{url}}';",
  {
    url: "cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png",
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
5. 查询作者联系电话以"1858"开头的记录：
const result = await models.$runSQL(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE author_tel LIKE '{{tel}}';",
  {
    tel: "1858%",
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
6. 查询并计算发布状态为 true 的记录数量：
const result = await models.$runSQL(
  "SELECT COUNT(*) FROM `lcap-wzcs_iuujo7p` WHERE is_published = {{isPublished}};",
  {
    isPublished: true,
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"COUNT(*)":1}],"backendExecute":"1717"},"requestId":"f323d96a-8863-48db-a132-ed0fb3fbc727"}
7. 查询并返回所有记录的标题和阅读次数：
const result = await models.$runSQL(
  "SELECT read_num,title FROM `lcap-wzcs_iuujo7p`"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"read_num":997,"title":"hello"}],"backendExecute":"1916"},"requestId":"845d3fd4-05ce-4277-9a73-2cdd9b5ce04f"}
原始模式 $runSQLRaw
在某些情况（例如动态表名等）下可能希望关闭预编译模式，我们也支持直接传入原始的 SQL 语句的方式来执行 SQL，这种情况下需要自行处理 SQL 注入的防范。
示例：
1. 查询标题为"hello"的记录：
const result = await models.$runSQLRaw(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE title = 'hello' limit 10"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
2. 查询阅读次数大于 1000 的记录：
const result = await models.$runSQLRaw(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE read_num > 1000 limit 10"
);
﻿
console.log(result);
// {"data":{"total":0,"executeResultList":[],"backendExecute":"23"},"requestId":"2f06b68f-e869-45cb-bb0d-82d50b3dcde0"}
3. 查询最后更新时间在某个特定时间戳之后的记录（例如：2024-06-01 00:00:00）：
const result = await models.$runSQLRaw(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE updatedAt > UNIX_TIMESTAMP('2024-06-01 00:00:00')"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
4. 查询拥有特定 banner 图片的记录：
const result = await models.$runSQLRaw(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE banner = 'cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png';"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
5. 查询作者联系电话以"1858"开头的记录：
const result = await models.$runSQLRaw(
  "SELECT * FROM `lcap-wzcs_iuujo7p` WHERE author_tel LIKE '1858%';"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"owner":"1739272568342245378","is_published":true,"author_web":"https://qq.com","banner":"cloud://lowcode-0gr8x3i8cd1c6771.6c6f-lowcode-0gr8x3i8cd1c6771-1307578329/weda-uploader/ec687de371d4ad064efd0a424a69e969-logo (1).png","auto_no":"1000","body":"<p>hello world</p>","title":"hello","type":"[\\"test\\",\\"test\\"]","author_tel":"18588881111","createdAt":1719475245475,"createBy":"1739272568342245378","read_num":997,"updateBy":"1739272568342245378","_openid":"1739272568342245378","extra":"{}","markdown":"# aa\\n\\n\\n\\n","author_email":"a@qq.com","json":"{\\"a\\":\\"1\\"}","_id":"9JXU7BWFZJ","region":"北京市","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
6. 查询并计算发布状态为 true 的记录数量：
const result = await models.$runSQLRaw(
  "SELECT COUNT(*) FROM `lcap-wzcs_iuujo7p` WHERE is_published = TRUE;",
  {
    isPublished: true,
  }
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"COUNT(*)":1}],"backendExecute":"1717"},"requestId":"f323d96a-8863-48db-a132-ed0fb3fbc727"}
7. 查询并返回所有记录的标题和阅读次数：
const result = await models.$runSQLRaw(
  "SELECT read_num,title FROM `lcap-wzcs_iuujo7p`"
);
﻿
console.log(result);
// {"data":{"total":1,"executeResultList":[{"read_num":997,"title":"hello"}],"backendExecute":"1916"},"requestId":"845d3fd4-05ce-4277-9a73-2cdd9b5ce04f"}
SQL 注入防范
在使用 $runSQLRaw 原始模式时，由于 SQL 语句会被当做原始字符串进行查询，因此开发者需要自行确保 SQL 语句的安全性，避免 SQL 注入的风险。以下是一些防范 SQL 注入的措施：
1. 使用预编译模式：如非必要，优先使用 $runSQL 预编译模式，利用参数化查询来避免 SQL 注入。
2. 对用户输入进行验证：在将用户输入的数据用于 SQL 语句之前，进行严格的验证和过滤，确保输入数据的合法性。
3. 使用白名单验证：对于用户可以输入的值，使用白名单来验证，只允许预定义的安全值。
4. 转义特殊字符：对于无法使用参数化查询的情况，确保对用户输入的数据进行转义，特别是 SQL 语句中的特殊字符，如单引号 '。
5. 错误处理：合理处理数据库查询错误，避免将详细的错误信息暴露给用户，这可能会泄露数据库结构信息，增加 SQL 注入的风险。
﻿
帮助和支持

本页内容是否解决了您的问题？
您也可以联系销售或提交工单以寻求帮助。
填写满意度调查问卷，共创更好文档体验。
文档反馈
tencent cloud

云开发 CloudBase

自有 MySQL 数据库访问

使用场景

使用方法

使用 SQL 模板

使用 SQL 命令

预编译模式 `$runSQL`

原始模式 `$runSQLRaw`

SQL 注入防范

帮助和支持
