Prepare (SQL)

建議将此條目或章節併入參數化查詢。（討論）

在数据库管理系统（DBMS）中，预处理语句（Prepared Statement，又称参数化语句）是一种将SQL代码预先编译并存储，从而实现代码与数据分离的特性。其主要优势包括：^[1]

• 效率：由于无需重复编译，可以多次重复使用。
• 安全：通过减少或消除SQL注入攻击来提高安全性。

预处理语句采取预编译模板的形式，在每次执行时替换其中的常量值。它通常用于INSERT、SELECT或 UPDATE等DML语句。

预处理语句常见工作流：

1. 准备 (Prepare)：应用程序创建语句模板并发送给DBMS。某些值保持未指定状态，称为参数、占位符或绑定变量（如下文中的?）：

   INSERT INTO products (name, price) VALUES (?, ?);

2. 编译 (Compile)：DBMS对模板进行编译（解析、优化和翻译），存储结果但不执行。
3. 执行 (Execute)：应用程序为参数提供（或绑定）具体值，DBMS 执行该语句。应用程序可以多次使用不同值请求执行。例如，先传入 "bike" 和 "10900"，随后传入 "shoes" 和 "7400"。

如果不使用预处理语句，则直接调用合并了代码与数据的 SQL。与上述例子等价的实现：

INSERT INTO products (name, price) VALUES ('bike', '10900');

预处理语句不能在编译时完成所有优化，原因有二：最佳执行计划可能取决于参数的具体值，且执行计划会随表和索引的变化而改变。^[2]

此外，如果查询只执行一次，由于增加了与服务器的往返次数，预处理语句可能更慢。^[3]实现限制也会导致性能惩罚，例如一些版本的MySQL并不缓存预处理查询的结果。^[4]与存储过程不同，预处理语句通常不使用过程化语言编写，无法使用控制流结构（如 IF、LOOP），而是依赖声明式查询语言。

大部分DBMS, 包括SQLite^[5]、MySQL^[6]、Oracle^[7]、IBM DB2^[8]、Microsoft SQL Server^[9]、PostgreSQL^[10]均支持此功能。预处理语句通常通过非SQL的二进制协议（non-SQL binary protocol）来执行，以实现高效率并防止SQL注入。但在某些数据库管理系统（如MySQL）中，为了调试目的，也可以使用SQL语法来操作预处理语句。^[11]

许多编程语言（如Java JDBC^[12]、Perl的DBI,^[13]、Python DB-API^[14]、PHP的PDO^[1]）在标准库中支持预处理语句，甚至在数据库不支持时进行客户端模拟。对于仅执行一次的查询，客户端模拟（Client-side emulation）可以通过减少与服务器的往返次数（round trips）来提高速度；但对于需要多次执行的查询，它的速度通常较慢。在抵御 SQL 注入攻击方面，客户端模拟具有同样有效的防护能力。

通过“禁用字面量（disabling literals）”可以消除多种类型的SQL注入攻击，这实际上强制要求必须使用预处理语句；截至2007年，仅有H2数据库支持这一特性。^[15]

C++ MySQL Connector/C++ X DevAPI^[16][17], 预处理语句通过库的API隐式实现。

import <mysqlx/xdevapi.h>
import std;

using mysqlx::Schema;
using mysqlx::Session;
using mysqlx::Table;

int main() {
    try {
        Session session("localhost", 33060, "user", "password");
        Schema db = session.getSchema("testdb");
        Table users = db.getTable("users");

        users.insert("name", "age")
            .values("Alice", 30)
            .execute();
    } catch (const mysqlx::Error& e) {
        std::println(stderr, "MySQL error: {}", e.what());
    }
}

下述例子使用C#和ADO.NET:

namespace Wikipedia.Examples;

using System;
using System.Data;

using Microsoft.Data.SqlClient;

public class Example
{
    static void Main(string[] args)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
            command.Parameters.AddWithValue("@username", username);
            command.Parameters.AddWithValue("@room", room);

            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                // ...
            }
        }
    }
}

ADO.NET中的SqlCommand可以为AddWithValue的value参数接受任何类型，且类型转换是自动进行的。请注意，这里使用的是“命名参数”（例如"@username"）而非占位符"?"——这允许你在查询命令文本中多次使用同一个参数，且无需考虑参数出现的先后顺序。

然而，不应将AddWithValue方法用于变长数据类型（如varchar和nvarchar）。这是因为.NET会将参数长度假设为当前所赋数值的实际长度，而不是通过反射从数据库获取定义的实际长度。其后果是：对于每一个不同的数据长度，数据库都会编译并存储一个不同的查询计划（Query Plan）。通常情况下，这类“重复”执行计划的最大数量，是数据库中指定的各变长列长度的乘积。基于上述原因，对于变长列，务必使用标准的 Add 方法： command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue; 其中，“ParamLength”应为数据库中定义的长度。

由于变长类型必须使用标准Add方法，养成对所有参数类型都使用该方法的习惯是一个良好的编程实践。

// Define a BookModel type which wraps a sql.DB connection pool.
type BookModel struct {
	DB *sql.DB
}

// This will insert a new book into the database.
func (m *BookModel) Insert(title, author string) (int, error) {
	stmt := "INSERT INTO book (title, author, created) VALUES(?, ?, UTC_TIMESTAMP())"
    
    // The "Exec" function will automatically prepare the statement for you,
    // which requires an additional round-trip to the database.
    //
    // It is possible to avoid prepared statements, if you are sure they are not needed.
    // See ExecerContext for details. https://pkg.go.dev/database/sql/driver#ExecerContext
    //
    // Other functions such as "Query" work the same way,
    // and have an equivalent interface.
	result, err := m.DB.Exec(stmt, title, author)
	if err != nil {
		return 0, err
	}

	id, err := result.LastInsertId() // Not supported in the Postgres driver -- use RETURNING instead.
	if err != nil {
		return 0, err
	}

	// The ID returned has the type int64, so we convert it to an int type
	// before returning.
    //
    // Keep in mind, on 32-bit machines, this can potentially truncate the value.
    // It is usually more safe to return int64 from your function directly,
    // which is still supported on 32-bit machines.
	return int(id), nil
}

占位符参数的语法因数据库而异。MySQL、SQL Server 和 SQLite 使用 ? 符号，而 PostgreSQL 则使用 $N 符号。例如，如果你使用的是 PostgreSQL，你会这样写：

_, err := m.DB.Exec("INSERT INTO ... VALUES ($1, $2, $3)", ...)

package org.wikipedia.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class Main {

    public static void main(String[] args) throws SQLException {
        MysqlDataSource ds = new MysqlDataSource();
        ds.setDatabaseName("mysql");
        ds.setUser("root");

        try (Connection conn = ds.getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
            }

            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
                stmt.setString(1, "bike");
                stmt.setInt(2, 10900);
                stmt.executeUpdate();
                stmt.setString(1, "shoes");
                stmt.setInt(2, 7400);
                stmt.executeUpdate();
                stmt.setString(1, "phone");
                stmt.setInt(2, 29500);
                stmt.executeUpdate();
            }

            try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
                stmt.setString(1, "shoes");
                ResultSet rs = stmt.executeQuery();
                rs.next();
                System.out.println(rs.getInt(2));
            }
        }
    }
}

Java PreparedStatement对所有主要内建数据类型使用"setters" (setInt(int), setString(String), setDouble(double),等等)。

PHP和PDO的示例：^[18]

<?php

// Connect to a database named "mysql", with the password "root"
$connection = new PDO('mysql:host=127.0.0.1;dbname=test;charset=utf8mb4', 'root');

// Execute a request on the connection, which will create
// a table "products" with two columns, "name" and "price"
$connection->exec('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');

// Prepare a query to insert multiple products into the table
$statement = $connection->prepare('INSERT INTO products VALUES (?, ?)');
$products  = [
    ['bike', 10900],
    ['shoes', 7400],
    ['phone', 29500],
];

// Iterate through the products in the "products" array, and
// execute the prepared statement for each product
foreach ($products as $product) {
    $statement->execute($product);
}

// Prepare a new statement with a named parameter
$statement = $connection->prepare('SELECT * FROM products WHERE name = :name');
$statement->execute([
    ':name' => 'shoes',
]);

// Use array destructuring to assign the product name and its price
// to corresponding variables
[ $product, $price ] = $statement->fetch();

// Display the result to the user
echo "The price of the product {$product} is \${$price}.";

This example uses Perl and DBI:

#!/usr/bin/env perl -w
use strict;
use DBI;

my ($db_name, $db_user, $db_password) = ('my_database', 'moi', 'Passw0rD');
my $dbh = DBI->connect("DBI:mysql:database=$db_name", $db_user, $db_password,
    { RaiseError => 1, AutoCommit => 1})
    or die "ERROR (main:DBI->connect) while connecting to database $db_name: " .
        $DBI::errstr . "\n";

$dbh->do('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');

my $sth = $dbh->prepare('INSERT INTO products VALUES (?, ?)');
$sth->execute(@$_) foreach ['bike', 10900], ['shoes', 7400], ['phone', 29500];

$sth = $dbh->prepare("SELECT * FROM products WHERE name = ?");
$sth->execute('shoes');
print "$$_[1]\n" foreach $sth->fetchrow_arrayref;
$sth->finish;

$dbh->disconnect;

import mysql.connector

with mysql.connector.connect(database="mysql", user="root") as conn:
    with conn.cursor(prepared=True) as cursor:
        cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)")
        params: list[tuple[str, int]] = [("bike", 10900), ("shoes", 7400), ("phone", 29500)]
        cursor.executemany("INSERT INTO products VALUES (%s, %s)", params)
        params = ("shoes",)
        cursor.execute("SELECT * FROM products WHERE name = %s", params)
        print(cursor.fetchall()[0][1])

1. ^ ^{1.0 1.1} The PHP Documentation Group. Prepared statements and stored procedures. PHP Manual. [25 September 2011]. （原始内容存档于2022-04-08）. 
2. ^ Petrunia, Sergey. MySQL Optimizer and Prepared Statements. Sergey Petrunia's blog. 28 April 2007 [25 September 2011]. （原始内容存档于2018-02-05）. 
3. ^ Zaitsev, Peter. MySQL Prepared Statements. MySQL Performance Blog. 2 August 2006 [25 September 2011]. （原始内容存档于2014-03-23）. 
4. ^ 7.6.3.1. How the Query Cache Operates. MySQL 5.1 Reference Manual. Oracle. [26 September 2011]. （原始内容存档于2011-09-25）. 
5. ^ Prepared Statement Objects. SQLite. 18 Oct 2021 [2026-03-13]. （原始内容存档于2022-05-07）. 
6. ^ Oracle. 20.9.4. C API Prepared Statements. MySQL 5.5 Reference Manual. [27 March 2012]. 
7. ^ 13 Oracle Dynamic SQL. Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. [25 September 2011]. （原始内容存档于2011-10-26）. 
8. ^ SQL: Pengertian, Sejarah, Fungsi, dan Jenis Perintah SQL. （原始内容存档于2024-03-01）. 
9. ^ SQL Server 2008 R2: Preparing SQL Statements. MSDN Library. Microsoft. [25 September 2011]. （原始内容存档于2017-07-05）. 
10. ^ PREPARE. PostgreSQL 9.5.1 Documentation. PostgreSQL Global Development Group. [27 February 2016]. （原始内容存档于2018-03-09）. 
11. ^ Oracle. 12.6. SQL Syntax for Prepared Statements. MySQL 5.5 Reference Manual. [27 March 2012]. （原始内容存档于2019-07-16）. 
12. ^ Using Prepared Statements. The Java Tutorials. Oracle. [25 September 2011]. （原始内容存档于2011-11-12）. 
13. ^ Bunce, Tim. DBI-1.616 specification. CPAN. [26 September 2011]. （原始内容存档于2026-01-07）. 
14. ^ Python PEP 289: Python Database API Specification v2.0. [2026-03-13]. （原始内容存档于2022-03-03）. 
15. ^ SQL Injections: How Not To Get Stuck. The Codist. 8 May 2007 [February 1, 2010]. （原始内容存档于2011-07-16）. 
16. ^ Oracle Corporation. [dev.mysql.com/doc/dev/connector-cpp/latest/ MySQL Connector/C++ Documentation] 请检查|url=值 (帮助). dev.mysql.com. Oracle Corporation. 20 January 2026. 
17. ^ Oracle Corporation. X DevAPI User Guide. dev.mysql.com. Oracle Corporation. [1 March 2026]. 
18. ^ PHP manual entry for PDO::prepare(). [2026-03-13]. （原始内容存档于2026-02-25）.