Accessing a Self-Owned MySQL Database

Download
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-05-13 11:40:52
When using a self-owned MySQL database, in addition to the access method of the data model for read-write operations, we also provide methods and APIs to access data through SQL commands directly. Meanwhile, you can also implement database access through conventional database SDKs in cloud functions.
Use Cases
Routine queries or data operations: Use data model methods.
Complex queries, multi-table or join queries: Use database query capabilities in model methods.
Complex writing, insertion, and data update operations: Connect and operate the database through common database operation methods in cloud functions.
Usage
For models with an underlying MySQL database, we provide native MySQL database query statements using two methods:
Use SQL templates.
Use SQL commands.
Using these two methods, SQL templates can be called through front-end Mini Programs or back-end cloud functions and TCBR services; SQL commands can only be called from the server, including cloud functions, TCBR, or other backend services.
Using SQL Templates
For how to use SQL templates, see SQL template description.
Using SQL Commands
For MySQL database models, the data model SDK provides two query modes:
$runSQL: Prepared statement mode uses a parameterized query to avoid SQL injection risks.
$runSQLRaw: Raw mode, a more flexible mode where SQL statements are treated as raw strings for queries, posing SQL injection risks.
Note:
1. runSQL and runSQLRaw APIs are only supported for server-side calls, such as cloud functions, TCBR, and servers, and are not supported for direct calls from Mini Programs or the web.
2. It is recommended to prioritize the prepared statement mode to avoid SQL injection risks.
3. Currently only SELECT statements are enabled. If you need other SQL statements, contact us through the official community.
Prepared Statement Mode $runSQL
The prepared statement mode uses a parameterized query design, combining static template syntax and dynamic runtime parameters to achieve flexible data interaction.
Developers are allowed to embed static parameters directly in SQL queries through Mustache variable binding syntax ({{ }}), and dynamically passing parameters at runtime through the $runSQL() method is supported, avoiding the risk of SQL injection caused by direct string concatenation.
Example:
1. Query records with the title "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":"Beijing","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
2. Query records with reading count greater than 1,000:
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. Query records with the last update time after a certain specified timestamp (For example, 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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
4. Query records with specific banner images:
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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
5. Query records where the author's contact number starts with 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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
6. Query and count the number of records with a published status of 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. Query and return the titles and read counts of all records:
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"}
Raw Mode $runSQLRaw
In some cases (such as dynamic table names), you may want to disable prepared statement mode. We also support executing SQL by directly passing raw SQL statements. In this case, you need to handle SQL injection prevention yourself.
Example:
1. Query records with the title "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":"Beijing","updatedAt":1719475245475}],"backendExecute":"27"},"requestId":"16244844-19fe-4946-8924-d35408ced576"}
2. Query records with reading count greater than 1,000:
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. Query records with the last update time after a certain specified timestamp (For example, 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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
4. Query records with specific banner images:
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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
5. Query records where the author's contact number starts with 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":"Beijing","updatedAt":1719475245475}],"backendExecute":"28"},"requestId":"0d4c98c3-a3ff-4c55-93cc-d0f5c835f82c"}
6. Query and count the number of records with a published status of 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. Query and return the titles and read counts of all records:
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"}
Preventing SQL Injections
When using the raw mode $runSQLRaw, since SQL statements are treated as raw strings for queries, developers should manually ensure SQL statement security to avoid risks of SQL injection. The following are some prevention measures for SQL injection:
1. Use prepared statement mode: When possible, prioritize using $runSQL prepared statement mode and leverage parameterized queries to avoid SQL injection.
2. Validate user input: Before using user input data in SQL statements, strictly validate and filter user input data to ensure the legality of input data.
3. Use allowlist verification: For values that users can input, use an allowlist to validate and only predefined safe values are allowed.
4. Escape special characters: For cases where parameterized queries cannot be used, ensure to escape user input data, especially special characters in SQL statements such as single quotes '.
5. Error handling: Handle database query errors appropriately to avoid exposing detailed error information to users. Otherwise, database structure details may be leaked and risks of SQL injection are increased.
﻿
도움말 및 지원

문제 해결에 도움이 되었나요?
더 자세한 내용은 문의하기 또는 티겟 제출 을 통해 문의할 수 있습니다.
피드백
tencent cloud

CloudBase

Accessing a Self-Owned MySQL Database

Use Cases

Usage

Using SQL Templates

Using SQL Commands

Prepared Statement Mode `$runSQL`

Raw Mode `$runSQLRaw`

Preventing SQL Injections

도움말 및 지원
