Drupal 7 引入 Database API 查詢建構器,提供資料庫抽象層,支援 MySQL、PostgreSQL、SQLite 等多種資料庫,避免撰寫原生 SQL 的可攜性與安全性問題。本文完整涵蓋 CRUD、合併、條件、事務等核心操作。
<?php
// settings.php 已設定 $databases
// 取得預設連線
$db = Database::getConnection();
// 切換其他資料庫(若在 settings.php 定義多個 key)
$db = Database::getConnection('external', 'default');
<?php
$nid = db_insert('node')
->fields([
'type' => 'article',
'title' => 'Hello Drupal',
'uid' => 1,
'created' => REQUEST_TIME,
'status' => 1,
])
->execute(); // 回傳自增 ID (lastInsertId)
echo "新節點 ID: $nid";
<?php
$values = [
['title' => 'Article 1', 'type' => 'article', 'uid' => 1],
['title' => 'Article 2', 'type' => 'article', 'uid' => 2],
['title' => 'Article 3', 'type' => 'article', 'uid' => 3],
];
$query = db_insert('node')->fields(['title', 'type', 'uid']);
foreach ($values as $record) {
$query->values($record);
}
$lastId = $query->execute(); // 回傳最後一筆 ID
// 或使用 database 驅動層級的 multi-insert(更快)
// 但 db_insert()->values() 迴圈已自動優化為單一多值 INSERT 语句
<?php
$updated = db_update('node')
->condition('nid', 123)
->fields([
'title' => 'Updated Title',
'changed' => REQUEST_TIME,
])
->execute(); // 回傳影響行數
echo "更新了 $updated 筆記錄";
// 複雜條件
db_update('users')
->condition('status', 1)
->condition('last_login', REQUEST_TIME - 86400*30, '<')
->fields(['status' => 0]) // 停用 30 天未登入用戶
->execute();
<?php
$deleted = db_delete('node')
->condition('type', 'spam')
->condition('status', 0)
->execute();
echo "刪除了 $deleted 筆垃圾內容";
// 子查詢刪除
db_delete('field_data_body')
->condition('entity_id', db_select('node', 'n')
->fields('n', ['nid'])
->condition('n.type', 'deleted_type')
)
->execute();
<?php
// 類似 MySQL INSERT ... ON DUPLICATE KEY UPDATE
// 或 PostgreSQL INSERT ... ON CONFLICT
db_merge('users')
->key(['mail' => 'user@example.com']) // 唯一鍵判斷依據
->insertFields([
'name' => 'newuser',
'mail' => 'user@example.com',
'created' => REQUEST_TIME,
])
->updateFields([
'name' => 'updateduser',
'login' => REQUEST_TIME,
])
->execute();
// 等效邏輯:
// 如果 mail='user@example.com' 存在 → 更新 name、login
// 不存在 → 插入新記錄
<?php
$query = db_select('node', 'n')
->fields('n', ['nid', 'title', 'created'])
->condition('n.type', 'article')
->condition('n.status', 1)
->range(0, 10)
->orderBy('n.created', 'DESC');
$results = $query->execute()->fetchAllAssoc('nid');
foreach ($results as $nid => $record) {
echo "$nid: {$record->title}\n";
}
<?php
$query = db_select('node', 'n');
$query->addField('n', 'title', 'node_title'); // 別名 node_title
$query->addField('n', 'uid', 'author_uid');
$query->join('users', 'u', 'n.uid = u.uid');
$query->addField('u', 'name', 'author_name');
$results = $query->execute()->fetchAll();
// 每筆包含: node_title, author_uid, author_name
<?php
$query = db_select('node', 'n')
->fields('n', ['nid', 'title'])
->condition(
db_or()
->condition('n.title', '%keyword%', 'LIKE')
->condition('n.body', '%keyword%', 'LIKE')
)
->condition('n.status', 1);
// 等效:WHERE (title LIKE '%keyword%' OR body LIKE '%keyword%') AND status = 1
<?php
// 找有評論的節點
$subquery = db_select('comment', 'c')
->fields('c', ['nid'])
->condition('c.status', 0)
->distinct();
$query = db_select('node', 'n')
->fields('n', ['nid', 'title'])
->where("n.nid IN ($subquery)") // 子查詢
->execute();
<?php
// 總數
$count = db_select('node', 'n')
->condition('n.type', 'article')
->countQuery()
->execute()
->fetchField();
// 聚合
$stats = db_select('node', 'n')
->fields('n', ['type'])
->addExpression('COUNT(*)', 'count')
->addExpression('MAX(created)', 'latest')
->groupBy('n.type')
->execute()
->fetchAllKeyed(); // type => count
<?php
$transaction = db_transaction();
try {
$nid = db_insert('node')->fields([...])->execute();
db_insert('field_data_body')->fields([...])->execute();
db_insert('field_revision_body')->fields([...])->execute();
$transaction->commit(); // 明確提交(可選,析構時自動提交)
} catch (Exception $e) {
$transaction->rollback(); // 任何異常自動回滾
watchdog_exception('mymodule', $e);
throw $e;
}
| 優勢 | 說明 |
|---|---|
| SQL 注入防護 | 所有值自動參數化,`condition('field', $value)` 安全 |
| 跨資料庫相容 | 同一代碼支援 MySQL/PostgreSQL/SQLite |
| 前綴自動處理 | `{table}` 自動展開為 `prefix_table` |
| 可讀性/維護性 | 鏈式呼叫、IDE 自動完成、重構友善 |