本節涵蓋多數 SQL 教科書略過的主題：參數化查詢與繫結參數（bind parameter）。

繫結參數——也叫動態參數或繫結變數——是把資料傳給資料庫的另一種方式。你不把值直接寫進 SQL 敘述，而是使用 ?、:name、@name 之類的佔位符，再透過另一次 API 呼叫提供實際的值。

為什麼要用繫結參數#

把值直接寫進臨時（ad-hoc）敘述本身沒什麼不好，但在程式中使用繫結參數有兩個充分理由：

  • 安全性：繫結變數是防止 SQL 注入（SQL injection）最好的方式。
  • 效能：像 SQL Server 與 Oracle 這類具有執行計畫快取的資料庫，在多次執行同一敘述時能重用執行計畫，省下重建計畫的功夫——但前提是 SQL 敘述完全相同。若把不同的值寫進敘述，資料庫會視為不同敘述並重建執行計畫。使用繫結參數時敘述不會隨值改變，因此得以重用。

例外：當資料量取決於實際值#

當受影響的資料量取決於實際值時，情況就不同了。查詢小子公司：

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20;
-- 99 rows selected.
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES   |   99 |   70 |
|*2 | INDEX RANGE SCAN            | EMPLOYEE_PK |   99 |    2 |
---------------------------------------------------------------

查詢大子公司：

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30;
-- 1000 rows selected.
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT  |           | 1000 |  478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1000 |  478 |
----------------------------------------------------

小子公司用索引查找最快，大子公司則是全表掃描勝出。這正是 SUBSIDIARY_ID 直方圖發揮作用的地方：最佳化工具用它判斷 SQL 中該子公司 ID 的出現頻率，因而對兩段查詢得到不同的列數估算、不同的成本值，最後選出成本最低的計畫。

繫結參數對最佳化工具是不透明的#

使用繫結參數時，最佳化工具手上沒有具體的值可用來判斷頻率，於是它只能假設均勻分布，永遠得到相同的列數估算與成本值——最終永遠選出同一個執行計畫。

欄位直方圖在值分布不均時最有用。對分布均勻的欄位，通常只要用「相異值數量除以資料表列數」就足夠了；這個方法在使用繫結參數時同樣適用。

把最佳化工具類比為編譯器的話：

  • 繫結變數像是程式變數。
  • 直接寫進敘述的值則更像常數——資料庫能在最佳化期間利用它們，就像編譯器能在編譯期求出常數運算式。

繫結參數對最佳化工具而言不可見，正如變數的執行期值對編譯器而言未知。

不使用繫結參數，就像每次執行程式都重新編譯一次。

兩難：專用計畫 vs. 通用計畫#

從上述角度看，「繫結參數能改善效能」聽起來有點矛盾——畢竟不用繫結參數才能讓最佳化工具每次都挑到最佳計畫。但代價是什麼？產生並評估所有計畫變體是巨大的工夫，若最後結果都一樣，那就完全不划算。

資料庫因此陷入兩難：

  • 為每次執行評估所有可能的計畫變體，永遠拿到最佳計畫——但付出最佳化開銷。
  • 省下開銷、盡可能重用快取的計畫——但承擔使用次佳計畫的風險。

尷尬之處在於：不真的跑完整套最佳化，資料庫就無從得知完整最佳化會不會產出不同的計畫。各家廠商試圖用啟發式方法解決，成效相當有限。

身為開發者，你可以刻意運用繫結參數來化解這個兩難：除了「該影響執行計畫」的值以外，一律使用繫結參數。

哪些值該影響執行計畫？

  • 分布極不均勻的狀態碼，例如 todo 與 done——done 的筆數常比 todo 高出一個數量級，只有搜尋 todo 時用索引才有意義。
  • 分割（partitioning）：當表與索引被切分到多個儲存區時，實際值會影響需要掃描哪些分割區。
  • LIKE 查詢的效能也可能因繫結參數而受害（見下一節）。

現實中，實際值會影響執行計畫的情況其實很少。因此猶豫時就用繫結參數——至少能防止 SQL 注入。

佔位符的語法#

問號 ? 是 SQL 標準唯一定義的佔位符字元，屬於位置參數：由左至右編號，繫結值時必須指定編號。但這在實務上很不方便——新增或移除佔位符會讓編號整個位移。許多資料庫因此提供具名參數的專有擴充，例如 @name（SQL Server）或 :name（Oracle）。

繫結參數不能改變 SQL 敘述的結構，因此不能用於資料表名或欄位名。以下寫法無效：

String sql = prepare("SELECT * FROM ? WHERE ?");
sql.execute('employees', 'employee_id = 1');

若需在執行期改變敘述結構，請使用動態 SQL（dynamic SQL）。

各語言的繫結參數寫法

C#

// 不用繫結參數
int subsidiary_id;
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name"
                 + " from employees"
                 + " where subsidiary_id = " + subsidiary_id
                 , connection);

// 使用繫結參數
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name"
                 + " from employees"
                 + " where subsidiary_id = @subsidiary_id"
                 , connection);
cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);

參考 SqlParameterCollection 類別文件。

Java

// 不用繫結參數
Statement command = connection.createStatement(
                      "select first_name, last_name"
                    + " from employees"
                    + " where subsidiary_id = " + subsidiary_id);

// 使用繫結參數
PreparedStatement command = connection.prepareStatement(
                      "select first_name, last_name"
                    + " from employees"
                    + " where subsidiary_id = ?");
command.setInt(1, subsidiary_id);

參考 PreparedStatement 類別文件。

Perl

# 不用繫結參數
my $sth = $dbh->prepare(
                  "select first_name, last_name"
               . " from employees"
               . " where subsidiary_id = $subsidiary_id");
$sth->execute();

# 使用繫結參數
my $sth = $dbh->prepare(
                  "select first_name, last_name"
               . " from employees"
               . " where subsidiary_id = ?");
$sth->execute($subsidiary_id);

參考《Programming the Perl DBI》。

PHP（MySQL）

// 不用繫結參數
$mysqli->query("select first_name, last_name"
             . " from employees"
             . " where subsidiary_id = " . $subsidiary_id);

// 使用繫結參數
if ($stmt = $mysqli->prepare("select first_name, last_name"
                           . " from employees"
                           . " where subsidiary_id = ?"))
{
   $stmt->bind_param("i", $subsidiary_id);
   $stmt->execute();
} else {
  /* handle SQL error */
}

參考 mysqli_stmt::bind_param 文件，以及 PDO 文件中的「Prepared statements and stored procedures」。

Ruby

# 不用繫結參數
dbh.execute("select first_name, last_name"
          + " from employees"
          + " where subsidiary_id = {subsidiary_id}");

# 使用繫結參數
dbh.prepare("select first_name, last_name"
          + " from employees"
          + " where subsidiary_id = ?");
dbh.execute(subsidiary_id);

參考 Ruby DBI Tutorial 的「Quoting, Placeholders, and Parameter Binding」。

延伸：游標共享與自動參數化

最佳化工具與 SQL 查詢越複雜，執行計畫快取就越重要。SQL Server 與 Oracle 都有「自動把 SQL 字串中的字面值替換成繫結參數」的功能，分別叫做 CURSOR_SHARING（Oracle）與強制參數化（forced parameterization，SQL Server）。

這兩個功能都是為「完全不使用繫結參數的應用程式」提供的權宜之計。啟用它們會讓開發者無法刻意使用字面值。

Backward 函式 範圍搜尋 Forward