Accessing a MySQL Cloud Database

Download
Mode fokus
Ukuran font
Terakhir diperbarui: 2026-05-13 11:40:19
When a MySQL cloud database is used, in addition to using the data model's access methods to read and write data, we also provide methods and APIs to directly read and write data through SQL commands.
Use Cases
The data model SDK provides a model-based data operation API to further meet users' needs in specific scenarios:
Use specific database features or optimization strategies.
Data model SDK features not yet provided.
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 command query methods:
$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, but cannot be called directly from Mini Programs or web clients.
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.
﻿
Bantuan dan Dukungan

Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan
tencent cloud

CloudBase

Accessing a MySQL Cloud Database

Use Cases

Usage

Using SQL Templates

Using SQL Commands

Prepared Statement Mode `$runSQL`

Example

Raw Mode `$runSQLRaw`

Example:

Preventing SQL Injections

Bantuan dan Dukungan
