这是我参与更文挑战的第10天，活动详情查看：更文挑战

本篇主要参考翻译自Working with Databases in WordPress

开箱即用，WordPress提供了非常多的函数用于和数据库交互。大多数情况下，WP_Query类和相关函数如wp_insert_post、update_post_meta、get_posts足够满足工作的需要。

但是，仍然有很多需要做的事，WordPress原生并没有提供出来，特别是当需要处理自定义表时。

本教程，我们将研究在WordPress中处理数据库最重要的类-wpdb，包括开发工作流中可以用到的一些提示和技巧。还会接触到dbDelta，它可以在插件中用于创建自定义表。本教程不会介绍创建WordPress原始表的基础。但是可以随时查看这个教程从cPanel创建数据库

使用`wpdb`类

wpdb可能是我们需要直接处理数据库时使用的最重要的单个类。它基于Justin Vincent编写的ezSQL类，改写适用于WordPress。

wpdb类的方法和属性，官方文档已经介绍的很清楚了。下面介绍一些WordPress开发者可能犯的常见错误，如何修正它们，以及使用wpdb类可以应用的最佳实践。

在SQL查询中不要硬编码表名

一些开发者通常假设表前缀不会改变并使用默认值wp_。这样的错误方式的一个基本示例如下面的片段：

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM wp_posts LIMIT 10');

当然，这是对插件实际要做的事的过度简化，但是此示例显示了出错是多么地快。如果用户将表前缀更改了怎么办？通过prefix提供的实际的属性替换wp_字符串，可以很容易的修正。

上面的代码通过下面的更改可以变得更方便：

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix . 'posts LIMIT 10');

甚至可以更好。如果处理WordPress的默认表，可以跳过前缀部分，替代地，直接使用wpdb的属性。每一个默认WordPress表都可以由wpdb类的一个自定义属性表示，属性名和没有前缀的表名相同。

例如，假设表前缀为wp_：

$wpdb->posts对应于wp_posts表
$wpdb->postmeta对应于wp_postmeta表
$wpdb->users对应于wp_users表

等等。

上面的代码可以进一步改进，因为我们是通过这种方式来查询posts表的：

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->posts . ' LIMIT 10');

使用特定的辅助方法(`Helper Method`)用于数据库操作

尽管query方法旨在处理任何SQL查询，但最好使用更合适的帮助程序方法(helper methods)。通常由诸如insert，update，get_row等方法提供。除了针对我们的用例进行特定处理外，它还可以更安全地处理转义和其他繁重的工作。

$global wpdb;

$post_id    = $_POST['post_id'];
$meta_key   = $_POST['meta_key'];
$meta_value = $_POST['meta_value'];

$wpdb->query("INSERT INTO  $wpdb->postmeta
                ( post_id, meta_key, meta_value )
                VALUES ( $post_id, $meta_key, $meta_value )"
            );

除了该代码段的不安全特性外，还应使用适当的值来正常运行。但是，可以改为使用insert方法来进一步改善此代码段。可以将上面的代码更改为如下所示：

$global wpdb;

$post_id    = $_POST['post_id'];
$meta_key   = $_POST['meta_key'];
$meta_value = $_POST['meta_value'];

$wpdb->insert(
            $wpdb->postmeta,
            array(
                'post_id'    => $_POST['post_id'],
                'meta_key'   => $_POST['meta_key'],
                'meta_value' => $_POST['meta_value']
            )
        );

如果我们未提供格式作为insert方法的第三个参数，则第二个参数中提供的所有数据都将以字符串形式转义。另外，由于方法名更清晰，我们一眼就能知道该代码的作用。

合适的数据库查询调试(`Debugging`)

默认情况下，错误报告处于关闭状态。但是，wpdb提供了两种可用于切换错误报告状态的方法。

要打开错误报告(error reporting)功能，只需运行以下代码。

$wpdb->show_errors();

以及关闭它：

$wpdb->hide_errors();

要注意的另一件事是，如果将WP_DEBUG和WP_DEBUG_DISPLAY都设置为true，则会自动调用show_errors方法。

可以使用另一种有用的方法来处理错误，即print_error：

$wpdb->print_error();

顾名思义，它将仅显示最近查询的错误，而不管错误报告的状态如何。

另一个巧妙的技巧是在wp-config.php中启用SAVEQUERIES。这会将所有运行的数据库查询，运行时间以及最初从何处调用等信息存储在wpdb类的queries属性中。

为了获取该数据，可以执行下面代码：

print_r( $wpdb->queries );

注意，这将对我们的网站产生性能影响，因此仅在必要时使用它。

大多数时候，这些功能足以调试我们的代码出了什么问题。但是，要进行更广泛的调试和报告，Query Monitor插件可以比数据库查询更多地帮助调试。

确保查询免受潜在攻击

为了确保我们的代码免受SQL注入，wpdb还提供了另一种有用的方法prepare，它将处理SQL语句的字符串和需要转义的数据。每当我们使用诸如query或get_results之类的方法时，都应该使用prepare。

$wpdb->prepare( $sql, $format... );

prepare方法同时支持sprintf和vsprintf的语法。第一个参数$sql是一个由占位符填充的SQL语句。这些占位符可以有三种不同的格式：

%s表示string
%d表示integer
%f表示float

$format可以是用于替换$sql中占位符的sprintf的一系列参数（例如语法）或参数数组。该方法将返回带有正确转义数据的SQL。

让我们看一下如何实现删除wp_postmeta中特定post ID的meta_key的处理：

该处理不是正确的方式

$global wpdb;

$post_id = $_POST['post_id'];
$key     = $_POST['meta_key'];

$wpdb->query(
                "DELETE FROM $wpdb->postmeta
                WHERE post_id = $post_id
                AND meta_key = $key"
        );

请注意，这不是使用wpdb删除数据库中记录的推荐方法。因为我们将代码开放给了SQL注入：用户输入没有正确地转义并直接在DELETE语句中使用。

但是，此问题很容易解决！我们仅在进行实际查询之前引入prepare方法，以便生成可以安全使用的SQL。可以在下面的代码段中进行说明：

$global wpdb;

$post_id = $_POST['post_id'];
$key     = $_POST['meta_key'];

$wpdb->query(
            $wpdb->prepare(
                "DELETE FROM $wpdb->postmeta
                WHERE post_id = %d
                AND meta_key = %s",
                $post_id,
                $key
            )
        );

连接到单独的数据库(`Connecting to Separate Databases`)

默认的，$wpdb变量是wpdb类的实例，该类连接到wp-config.php中定义的WordPress数据库。如果要与其他数据库进行交互，则可以实例化wpdb类的另一个实例。这使我们受益匪浅，因为可以使用诸如insert，update和get_results之类的方法。

wpdb类在构造中接受四个参数，分别是username，password，database name和database host。这是一个例子：

$mydb = new wpdb( 'username', 'password', 'my_database', 'localhost' );

// At this point, $mydb has access to the database and all methods
// can be used as usual

// Example query
$mydb->query('DELETE FROM external_table WHERE id = 1');

如果我们使用相同的用户名，密码和数据库主机，而只需要更改选定的数据库，则在全局$wpdb变量上有一个方便的方法select。这是通过在内部使用mysql_select_db/mysqli_select_db函数实现的。

$wpdb->select('my_database');

当我们想切换到另一个WordPress数据库，但仍想保留诸如get_post_custom等函数的功能时，将特别有用。

get_post_custom( int $post_id ) Retrieve post meta fields, based on post ID.

使用自定义数据库表

WordPress默认表通常足以处理大多数复杂的操作。通过将自定义post types与post metadata，自定义taxonomies与term metadata结合使用，我们几乎可以执行任何操作，而无需使用自定义表。

但是，当我们想要对插件可以处理的数据进行更好的控制时，自定义表可能会很有用。使用自定义表的好处包括：

完全控制数据结构(Total control of the data structure)——并非所有类型的数据都适合post的结构，因此当我们要存储对自定义post type没有任何意义的数据时，自定义表可能是一个更好的选择。
关注点分离(Separation of concerns)——由于我们的数据存储在自定义表中，因此与我们使用自定义post type相比，它不会干扰wp_posts或wp_postmeta表。将数据迁移到另一个平台更加容易，因为它不限于WordPress结构的数据方式。
高效(Efficiency)——从我们的特定表中查询数据绝对比在wp_posts表中进行搜索要快得多，wp_posts表还包含与插件无关的数据。使用自定义post type存储大量元数据时可能会使wp_postmeta表膨胀，这是一个明显的问题。

dbDelta进行救援(`dbDelta to the Rescue`)

建议不要使用wpdb创建自定义数据库表，而使用dbDelta来处理所有的表创建初始化以及表架构更新。它是可靠的，因为WordPress核心也使用此功能来处理任何版本之间的数据库架构更新(如果有)。

为了在插件安装最初创建一个自定义表，我们需要将函数挂钩到register_activation_hook函数。假设主插文件是plugin-name目录中的plugin-name.php，可以直接将以下行放入其中：

register_activation_hook( __FILE__, 'prefix_create_table' );

接下来，我们需要创建函数prefix_create_table，该函数在插件激活时进行实际的表创建。

例如，我们可以创建一个名为my_custom_table的自定义表，用于存储简单的客户数据，例如名字，姓氏及其电子邮件地址。

    function prefix_create_table() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE my_custom_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            first_name varchar(55) NOT NULL,
            last_name varchar(55) NOT NULL,
            email varchar(55) NOT NULL,
            UNIQUE KEY id (id)
        ) $charset_collate;";

        if ( ! function_exists('dbDelta') ) {
            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        }

        dbDelta( $sql );
    }

为了最大化兼容性，我们从wpdb中检索数据库字符集归类(database charset collate)。另外，SQL语句需要遵守一些规则以确保其按预期工作。下面是直接从使用插件创建表的Codex page上获取：

必须在SQL语句中将每个字段放在自己的行上。
在单词PRIMARY KEY和主键定义之间必须有两个空格。
必须使用关键字KEY而不是其同义词INDEX，并且必须至少包含一个KEY。
不得在字段名称周围使用任何撇号(apostrophes)或反引号(backticks)。
字段类型必须全部为小写。
SQL关键字(例如CREATE TABLE和UPDATE)必须为大写。
必须指定所有接受长度参数的字段的长度。例如int(11)。

The dbdelta function can also be used to create the table and update its schema, but it's a very finnicky and unforgiving function, that expects a CREATE TABLE statements, with extremely specific requirements about formatting, and zero wiggle room. E.g, if you don't put 2 spaces after the PRIMARY keyword, it won't work, and plenty of other restrictions.

dbdelta函数还可以用于创建表并更新其架构，但这是一个非常细腻而又不可原谅的函数，它期望使用CREATE TABLE语句，该语句对格式的要求非常明确，并且摆动空间为零。例如，如果您没有在PRIMARY关键字后放置2个空格，则它将不起作用，并且还有许多其他限制。

通常，将数据库版本存储在options表中也是一个好主意，以便我们在插件更新期间自定义表需要更新时，可以对它们进行比较。为此，只需在使用dbDelta函数创建表之后立即添加以下行：

add_option( 'prefix_my_plugin_db_version', '1.0' );

更新表架构

使用与上述相同的示例，

假设在开发过程中，我们改变了主意，希望将客户电话号码存储在表中。我们可以做的是在插件更新期间触发表架构更新。

由于在插件更新期间不会触发register_activation_hook，因此我们可以改而挂钩到plugin_loaded操作中，进行数据库版本检查，并在必要时更新表架构。

首先，我们将自定义升级函数添加到plugin_loaded钩子：

add_action( 'plugin_loaded', 'prefix_update_table' );

实际的函数需要做几件事：

获取存储在的数据库版本
比较升级的版本和当前存储的版本
如果较新，则再次执行dbDelta函数
最后存储更新后的数据库版本到option表

在大多数情况下，我们实际上可以像上面所做的那样重用prefix_create_table函数，但需要进行一些小的修改：

function prefix_update_table() {
    // Assuming we have our current database version in a global variable
    global $prefix_my_db_version;

    // If database version is not the same
    if ( $prefix_my_db_version != get_option('prefix_my_plugin_db_version' ) {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE my_custom_table (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            first_name varchar(55) NOT NULL,
            last_name varchar(55) NOT NULL,
            phone varchar(32) DEFAULT '' NOT NULL, //new column
            email varchar(55) NOT NULL,
            UNIQUE KEY id (id)
        ) $charset_collate;";

        if ( ! function_exists('dbDelta') ) {
            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        }

        dbDelta( $sql );

        update_option( 'prefix_my_plugin_db_version', $prefix_my_db_version );
    }
}

注意，我们不需要使用ALTER语句，因为dbDelta将使用我们的SQL语句，将其与现有表进行比较并进行相应的修改。很方便！

总结

WordPress不仅限于创建简单的网站，还因为它正迅速迁移到成熟的应用程序框架中。通过自定义post types和自定义taxonomies扩展WordPress应该是我们的首要任务。但是，当我们需要更好地控制数据时，可以放心地知道WordPress本身提供了wpdb等各种函数和类供开发人员使用。这就是使WordPress成为成熟解决方案的原因。

WordPress数据库系列：在WordPress中使用数据库的常见错误和最佳实践

使用wpdb类

在SQL查询中不要硬编码表名

使用特定的辅助方法(Helper Method)用于数据库操作

合适的数据库查询调试(Debugging)