WordPress:Creating Tables with Plugins

If you are writing a plugin for WordPress, you will almost certainly find that you need to store some information in the WordPress database. There are two types of information you could store:

如果你正在为WordPress编写一个插件，你肯定会发现你需要向WordPress数据库储存一些信息。你可以储存两种类型的信息：

• Setup information -- user choices that are entered when the user first sets up your plugin, and don't tend to grow much beyond that (for example, in a tag-related plugin, the user's choices regarding the format of the tag cloud in the sidebar). Setup information will generally be stored using the WordPress options mechanism.

• 设置 信息 –用户首次设置你的插件的时候，会输入用户选择，而且选择不会增加（例如，在标签相关的插件中，用户对标签cloud形式相关的选择出现在边栏中）。设置信息，一般是使用WordPress 选项 机制创建的。

• Data -- information that is added as the user continues to use your plugin, which is generally expanded information related to posts, categories, uploads, and other WordPress components (for example, in a tag-related plugin, the tag names and correspondence between tags and articles). Data will generally be stored in a separate MySQL table, which will have to be created.

• 数据 –用户继续使用你的插件，就会增加有关文章，类别，上传内容，和其它的WordPress成分方面的信息（例如，在标签相关的插件中，有标签名，和标签和文章之间的相关信息）。数据一般储存在单独的MySQL表格中，这个表格需要创建。

This article describes how to have your plugin automatically create a MySQL table to store its data. Note that as an alternative to following the steps here, you could have the plugin user run an install script when they install your plugin. Another approach would be to have the user execute an SQL query on their own, using something like WordPress:phpMyAdmin. But neither of those options is very satisfactory, since a user could easily forget to run the install script or screw up the query (and they might not have phpMyAdmin available).

这篇文章描述了怎样使你的插件自动创建MySQL表格储存数据。注意，还可以使用另一种方法，遵循下面的步骤，用户安装你的插件的时候，你可以让用户运行安装脚本。另一种方法是使用户使用，如phpMyAdmin自己执行SQL查询。但是这两种方法都不能让人感到很满意，因此用户容易忘记运行安装脚本或者会混乱查询（而且用户可能没有phpMyAdmin）。

So, it is recommended that you follow the steps below to have your plugin automatically create its database tables:

因此，建议你遵循下面的步骤，使得插件自动地创建数据库表格：

1. Write a PHP function that creates the table.

1. 编写创建表格的PHP函数。

1. Ensure that WordPress calls the function when the plugin is activated.

1. 确定插件激活的时候，WordPress调用了函数。

1. Create an upgrade function, if a new version of your plugin needs to have a different table structure.

1. 如果你的插件的新版本需要不同的表格结构，创建升级函数。

创建数据库表格

The first step in making your plugin create database tables automatically is to create a PHP function within your plugin that adds a table or tables to the WordPress MySQL database. For purposes of this article, we'll assume you want to call this function jal_install.

使得你的插件自动创建数据库表格，首先在插件内创建PHP函数，向WordPress MySQL数据库添加一个或者多个表格。鉴于本篇文章的目的，我们假定你称这个函数为jal_install。

数据库表格前缀

In the wp-config.php file, a WordPress site owner can define a database table prefix. By default, the prefix is "wp_", but you'll need to check on the actual value and use it to define your database table name. This value is found in the $wpdb->prefix variable. (If you're developing for a version of WordPress older than 2.0, you'll need to use the $table_prefix global variable, which is deprecated in version 2.1).

在wp-config.php文件中，WordPress站点所有人可以定义数据库表格前缀。默认情况下，前缀是"wp_"，但是你需要查看真正的参数值并且使用这个参数值定义你的数据库表格名称。可以在$wpdb->prefix变数中找到这个参数值的名称。（如果你为WordPress2.0之前的版本创建，你需要使用$table_prefix全局变数，这个变数在2.1版本时，已取消）。 So, if you want to create a table called (prefix)liveshoutbox, the first few lines of your table-creation function will be:

因此，如果你想要创建称为(prefix)liveshoutbox的表格，你创建表格的函数的前几行是：

function jal_install () {
   global $wpdb;

   $table_name = $wpdb->prefix . "liveshoutbox";

function jal_install () {
   global $wpdb;

   $table_name = $wpdb->prefix . "liveshoutbox";

表格已经存在吗?

The next step in creating the table is to see if the table has already been created. The following if statement runs a SHOW TABLES SQL query to try to find the table, and then compares the result to our table name:

其次，是要查看表格是否已经创建好了。下面的if声明运行SHOW TABLES SQL查询，尝试找到表格，然后将结果与我们的表格名称相比较：

    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

创建或者更新表格

The next step is to actually create the database table. Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/upgrade-functions.php (we'll have to load this file, as it is not loaded by default). The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). Note that the dbDelta function is rather picky, however. For instance:

接着是要真正的创建数据库表格。不直接地执行SQL查询，我们在wp-admin/upgrade-functions.php中使用dbDelta函数（我们需要载入这个文件，因为默认不会载入这个文件）。dbDelta函数检查了当前表格结构，将这个结构与理想的表格结构相比较，添加或者更改表格，这样表格会便于更新（查看wp-admin/upgrade-schema.php，关于怎样使用dbDelta的更多的例子）。注意，dbDelta函数非常地特别，例如：

• You have to put each field on its own line in your SQL statement.

• 你需要将每个栏放入你的SQL声明中各自的行中。

• You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.

• 在单词PRIMARY KEY和你的primary key定义之间需要有两个空格。

• You must use the key word KEY rather than its synonym INDEX

• 你必须使用key word KEY而不是其近义词INDEX

With those caveats, here are the next lines in our function, which will actually create or update the table. You'll need to substitute your own table structure in the $sql variable:

下面是我们的函数中的另外几行，会真正地创建或者更新表格。你需要在$sql变数中取代你自己的表格结构。

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

添加初始数据

Finally, you may want to add some data to the table you just created. Here is an example of how to do that:

最后，你肯能需要为你刚刚创建的表格添加一些数据。下面是关于怎样操作的例子：

  $welcome_name = "Mr. Wordpress";
  $welcome_text = "Congratulations, you just completed the installation!";

  $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

  $results = $wpdb->query( $insert );

  $welcome_name = "Mr. Wordpress";
  $welcome_text = "Congratulations, you just completed the installation!";

  $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

  $results = $wpdb->query( $insert );

NOTE: Even though we defined $welcome_name and $welcome_text in this function and know that there are no SQL special characters in them, it's still a good idea to always run a variable through the $wpdb->escape function before passing it to the database to prevent security problems and random bugs.

注:即使我们在这个函数中定义了$welcome_name 和$welcome_text而且知道其中没有特别的SQL字符，通过$wpdb->escape函数运行变数，然后再将变数传递到数据库，仍然是个好注意，这样可以避免一些安全问题以及一些随意的程序错误。

版本选项

Another excellent idea is to add an option to record a version number for your database table structure, so you can use that information later if you need to update the table:

另一个好主意是，添加选项，记录你的数据库表格结构的版本数字，这样如果随后你需要更新表格，你可以使用这个信息：

add_option("jal_db_version", "1.0");

add_option("jal_db_version", "1.0");

整个函数

This function is done. Let's see it all in one piece. Note that the version number is now stored in a global variable.

这个函数完成了。让我们看看这个整体。注意版本数字现在储存在全局变数中。

$jal_db_version = "1.0";

function jal_install () {
   global $wpdb;
   global $jal_db_version;

   $table_name = $wpdb->prefix . "liveshoutbox";
   if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
      
      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      $welcome_name = "Mr. Wordpress";
      $welcome_text = "Congratulations, you just completed the installation!";

      $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

      $results = $wpdb->query( $insert );
 
      add_option("jal_db_version", $jal_db_version);

   }
}

$jal_db_version = "1.0";

function jal_install () {
   global $wpdb;
   global $jal_db_version;

   $table_name = $wpdb->prefix . "liveshoutbox";
   if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
      
      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      $welcome_name = "Mr. Wordpress";
      $welcome_text = "Congratulations, you just completed the installation!";

      $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

      $results = $wpdb->query( $insert );
 
      add_option("jal_db_version", $jal_db_version);

   }
}

调用函数

Now that we have the initialization function defined, we want to make sure that WordPress calls this function when the plugin is activated by a WordPress administrator. To do that, we will use the activate_ action hook. If your plugin file is wp-content/plugins/plugindir/pluginfile.php, you'll add the following line to the main body of your plugin:

既然我们已经定义了初始化函数，我们希望确定当插件由WordPress管理员激活的时候，WordPress调用了这个函数。要做到这一点，我们将要使用activate_ action hook。如果你的插件文件是wp-content/plugins/plugindir/pluginfile.php，你将会将下面这些行添加到你的插件的主要部分中。

register_activation_hook(__FILE__,'jal_install');

register_activation_hook(__FILE__,'jal_install');

See WordPress:Function_Reference/register_activation_hook for more details. 更多详细信息，请看看 Function_Reference/register_activation_hook。

添加升级函数

Over the lifetime of your plugin, you may find that you need to change the plugin's database structure in an upgraded version. To do that, you will need to create update code within your plugin that will detect that a new version has been installed, and upgrade the database structure. The easiest thing to do is to add the code to the jal_install function we just created.

在你的插件的使用寿命之内，你可能会发现你需要在已升级的版本中，更改插件的数据库结构。你需要在插件内创建更新代码，这个代码会探测到已经安装了新的版本，并且升级数据库结构。最简单的方法就是向我们刚刚创建的jal_install函数，添加代码。

Note that you will need to make sure that the function gets called. So tell your plugin users that when they upgrade, they need to deactivate the plugin, copy in the new plugin file, and then activate the plugin again.

现在你需要确定函数已经得到调用。告诉你的插件用户什么时候升级，用户取药取消运行插件，在新的插件文件中复制，然后再激活插件。

So, let's assume that the function above was used to create database version 1.0 of your plugin, and you are now upgrading to version 1.1 so that the URL field can be wider (100 characters instead of 55). You will need to add the following lines to the end of your jal_install function, to check the version and upgrade if necessary:

因此，我们假定上述的函数是用户创建你的插件1.0版本的数据库的，现在你升级到1.1版本，这样URL field会更宽（100个字符而不是55个字符）。你需要在你的jal_install函数的结尾部分添加下面的行，查看版本，如果必要，就升级：

   $installed_ver = get_option( "jal_db_version" );

   if( $installed_ver != $jal_db_version ) {

      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      update_option( "jal_db_version", $jal_db_version );
  }

   $installed_ver = get_option( "jal_db_version" );

   if( $installed_ver != $jal_db_version ) {

      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      update_option( "jal_db_version", $jal_db_version );
  }

You'll also need to change the global $jal_db_version variable at the top of the file, and of course you'll want to change the initialization section created above to use the new table structure.

在文件的顶上方，你也需要更改全局$jal_db_version变数，当前，你想要更改上面创建的初始化部分，使用新的表格结构。

资源

For further reading on plugin development, check out WordPress:Plugin Resources, a comprehensive list of plugin resources. You may also find this post from the wp-hackers mailing list to be helpful: WordPress Hackers Mailing List: Answer to Plugin Requires Additional Tables

要深入地阅读插件发展内容，请查看插件资源，插件资源的完整列表。你也可以在wp-hackers mailing list中发现这篇文章很有用：WordPress Hackers Mailing 列表: Answer to Plugin Requires Additional Tables