跳转到内容
搜索文档
↵选择
↑↓导航
ESC关闭

预编译语句方法

最后更新 查看 MarkdownAgent 设置

本章记录准备语句后运行和检索查询结果的多种方式。

方法

bind()

将参数绑定到预编译语句。

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
some_variable = "Bs Beverages"
stmt = self.env.DB.prepare(
  "SELECT * FROM Customers WHERE CompanyName = ?"
).bind(some_variable)

参数

  • Variable: string
    • 要附加到预编译语句的变量。请参阅下面的指南。

返回值

  • D1PreparedStatement: Object
    • 输入参数已包含在语句中的 D1PreparedStatement。

指南

  • D1 遵循 SQLite 约定 ↗进行预编译语句参数绑定。目前，D1 仅支持有序（?NNNN）和匿名（?）参数。将来，D1 也将支持命名参数。

    语法 类型 描述
    ?NNN Ordered 问号后跟数字 NNN 为第 NNN 个参数保留位置。NNN 必须在 1 和 SQLITE_MAX_VARIABLE_NUMBER 之间
    ? Anonymous 未后跟数字的问号创建编号比已分配最大参数编号大 1 的参数。如果这意味着参数编号大于 SQLITE_MAX_VARIABLE_NUMBER，则为错误。提供此参数格式是为了与其他数据库引擎兼容。但由于容易数错问号，不鼓励使用此参数格式。建议程序员使用以下符号格式之一或上面的 ?NNN 格式。

    要绑定参数，使用 .bind 方法。

    有序和匿名示例：

    const stmt = db.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind("");
    stmt = db.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind("")
    const stmt = db
	.prepare("SELECT * FROM Customers WHERE CompanyName = ? AND CustomerId = ?")
	.bind("Alfreds Futterkiste", 1);
    stmt = db.prepare(
"SELECT * FROM Customers WHERE CompanyName = ? AND CustomerId = ?"
).bind("Alfreds Futterkiste", 1)
    const stmt = db
	.prepare(
  "SELECT * FROM Customers WHERE CompanyName = ?2 AND CustomerId = ?1"
).bind(1, "Alfreds Futterkiste");
    stmt = db.prepare("SELECT * FROM Customers WHERE CompanyName = ?2 AND CustomerId = ?1").bind(1, "Alfreds Futterkiste")

静态语句

D1 API 支持静态语句。静态语句是变量已硬编码的 SQL 语句。编写静态语句时，您手动在语句字符串中输入变量。

动态绑定值的预编译语句示例：

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
// A variable (someVariable) will replace the placeholder '?' in the query.
// `stmt` is a prepared statement.
some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
# A variable (some_variable) will replace the placeholder '?' in the query.
# `stmt` is a prepared statement.

静态语句示例：

const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = 'Bs Beverages'");
// "Bs Beverages" is hard-coded into the query.
// `stmt` is a static statement.
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = 'Bs Beverages'")
# "Bs Beverages" is hard-coded into the query.
# `stmt` is a static statement.

run()

运行预编译查询（或查询）并返回结果。返回的结果包括元数据。

const returnValue = await stmt.run();
return_value = await stmt.run()

参数

  • None.

返回值

  • D1Result: Object
    • 包含成功状态、meta 对象和包含查询结果的对象数组。
    • 有关对象的更多信息，请参阅 D1Result。

Example of return values

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
const returnValue = await stmt.run();
return Response.json(returnValue);
from workers import Response

some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
return_value = await stmt.run()
return Response.json(return_value)
{
  "success": true,
  "meta": {
    "served_by": "miniflare.db",
    "duration": 1,
    "changes": 0,
    "last_row_id": 0,
    "changed_db": false,
    "size_after": 8192,
    "rows_read": 4,
    "rows_written": 0
  },
  "results": [
    {
      "CustomerId": 11,
      "CompanyName": "Bs Beverages",
      "ContactName": "Victoria Ashworth"
    },
    {
      "CustomerId": 13,
      "CompanyName": "Bs Beverages",
      "ContactName": "Random Name"
    }
  ]
}

指南

  • 对于 UPDATE、DELETE 或 INSERT 等写入操作，results 为空。
  • 使用 TypeScript 时，可以向 D1PreparedStatement::run 传递类型参数以返回类型化结果对象。
  • D1PreparedStatement::run 在功能上等同于 D1PreparedStatement::all，可视为别名。
  • 您可以通过简单地返回返回对象的 results 属性，仅提取您期望的语句结果。

仅返回 results 的示例

return Response.json(returnValue.results);
from workers import Response

return Response.json(return_value.results)
[
  {
    "CustomerId": 11,
    "CompanyName": "Bs Beverages",
    "ContactName": "Victoria Ashworth"
  },
  {
    "CustomerId": 13,
    "CompanyName": "Bs Beverages",
    "ContactName": "Random Name"
  }
]

raw()

运行预编译查询（或查询），并将结果作为数组的数组返回。返回的结果不包括元数据。

默认情况下，结果集不包含列名。要将列名作为结果数组的第一行，设置 .raw({columnNames: true})。

const returnValue = await stmt.raw();
return_value = await stmt.raw()

参数

  • columnNames: ObjectOptional
    • 布尔对象，将列名作为结果数组的第一行。

返回值

  • Array: Array
    • 数组的数组。每个子数组代表一行。

Example of return values

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
const returnValue = await stmt.raw();
return Response.json(returnValue);
from workers import Response

some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
return_value = await stmt.raw()
return Response.json(return_value)
[
  [11, "Bs Beverages",
    "Victoria Ashworth"
  ],
  [13, "Bs Beverages",
    "Random Name"
  ]
]

参数 columnNames: true：

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
const returnValue = await stmt.raw({columnNames:true});
return Response.json(returnValue)
from workers import Response

some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
return_value = await stmt.raw(columnNames=True)
return Response.json(return_value)
[
  [
    "CustomerId",
    "CompanyName",
    "ContactName"
  ],
  [11, "Bs Beverages",
    "Victoria Ashworth"
  ],
  [13, "Bs Beverages",
    "Random Name"
  ]
]

指南

first()

运行预编译查询（或查询），并将查询结果的第一行作为对象返回。这不返回任何元数据，而是直接返回对象。

const values = await stmt.first();
values = await stmt.first()

参数

  • columnName: StringOptional
    • 指定 columnName 以从查询结果第一行的特定列返回值。
  • None.
    • 不传递参数以获取第一行的所有列。

返回值

  • firstRow: ObjectOptional

    • 包含查询结果第一行的对象。
    • 如果指定了 columnName，返回值将进一步过滤到特定属性。

  • null: null

    • If the query returns no rows.

Example of return values

获取第一行的所有列：

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
const returnValue = await stmt.first();
return Response.json(returnValue)
from workers import Response

some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
return_value = await stmt.first()
return Response.json(return_value)
{
  "CustomerId": 11,
  "CompanyName": "Bs Beverages",
  "ContactName": "Victoria Ashworth"
}

从第一行获取特定列：

const someVariable = `Bs Beverages`;
const stmt = env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(someVariable);
const returnValue = await stmt.first("CustomerId");
return Response.json(returnValue)
from workers import Response

some_variable = "Bs Beverages"
stmt = self.env.DB.prepare("SELECT * FROM Customers WHERE CompanyName = ?").bind(some_variable)
return_value = await stmt.first("CustomerId")
return Response.json(return_value)
11

指南

这篇文档对您有帮助吗？