wp数据库之自定义创建数据表

浏览数：234 / 时间：2015年06月12日

wp数据库之自定义创建数据表

      WordPress提供的两个API使创建和使用自定义表没有多么麻烦！最值得注意的是 $wpdb类和dbDelta()函数。虽然如此，但创建自定义表，意味着给Wordpress创建一个陌生的环境，这是相对于Wordpress自身的表来说的，你作为插件（或者主题，本系列文章以插件为例）作者，就有责任让你的代码能够与数据库进行安全高效的交互。所以，你在决定之前，先考虑下是不是必须使用自定义表。
1.在Wordpress中使用自定义表的缺点

如前所述，自定义表在正常的WordPress的框架之外，Wordpress不熟悉它，而且在大多数情况下这一点就是使用自定义表的缺点的根本原因：

1.1 没有Wordpress原生的添加、删除、更新或查询函数，可以在你的自定义表和不锈钢转子泵代码之间交互；

1.2 你需要完全自定义UI；

1.3 你需要自己来考虑禁止和缓存；

1.4 其它插件/主题也不知道这个表的存在，除非你自己写单独的插件或者使用自定义表的插件的附加组件；

1.5 你需要谨慎选择备份插件，因为有些备份插件不支持自定义表的备份，所以我觉得最佳的备份方案是自己去数据库管理界面（通常是phpMyadmin）导出数据库以备份；

1.6 你有责任让你的表的结构最优化、让你的包括SQL语句在内的全部代码没有任何BUG，以不锈钢转子泵高效运行；
2.什么时候适合使用自定义表

这个问题貌似没有正确答案。上一小节已经列出了创建自定义表的一些缺点，所以，如果你觉得没有十分的必要，就不要使用自定义数据表，仅在你的数据有大量的读写或者数据量很大或者数据结构很复杂的时候，才建议你使用自定义数据表。
3.创建自定义表

一旦确定使用自定义表是必要的，我们就要来创建它，创建它之前，我们需要将自定义表的表明存储于$wpdb，这个全局变量包含了与当前Wordpress网站相关的所的信息，如果你启用了Wordpress的多站架构，在切换网站的时候，这个变量才会改变，我们把自定义表名添加到这个变量不是必需的，但是能让我们的代码更高效：

    add_action( ‘init‘, ‘coolwp_register_activity_log_table‘, 1 );
    add_action( ‘switch_blog‘, ‘coolwp_register_activity_log_table‘ );

    function coolwp_register_activity_log_table() {
        global $wpdb;
        $wpdb->coolwp_activity_log = "{$wpdb->prefix}coolwp_activity_log";
    }

上述代码中的$wpdb->prefix给自定义表名添加一个前缀，如果你在安装Wordpress的时候，没有修改的话，这个前缀默认是“wp_”，该前缀可以在wp-config.php中被修改，修改数据库中表的前缀这是为了方便你几个Wordpress网站使用一个数据库，也就是时候，这多个网站的数据在数据库中可以通过表名前缀而加以区分。

在本系列文章中，我们将假设我们要创建一个插件，这个插件用来记录用户的动作（更新或删除的帖子、更改设置、上传图片等）。
3.1 列命名约定

也就是说，在你确定了自定义表的名称之后，就要考虑各个列的名称了。Wordpress核心生成的数据库的表中的列名是小写的，在mysql中，列名是大小写敏感的，所以，我建议你以小写字母来写列名，并用下划线将各个单词（单词数少些好）分隔开，以提高可读性和准确性。在考虑列名的时候，你应该避免使用Mysql的保留字符串，如果你的自定义表中有若干列的数据与外部表（特别是Wordpress核心生成的表）相关联，你最好以外部表中的列名来命名你的列。

在我们的例子中，我们将使用以下列名：

    log_id - 记录ID.
    user_id - 被记录的用户的用户ID.
    activity - 发生的动作.
    object_id - 对象的ID，例如 post ID、 user ID、 comment ID 等.
    object_type - 对象类型，例如‘post‘, ‘user‘, ‘comment‘ 等.
    activity_date - 动作的日期/时间.

3.2确定自定义表中各个列的类型

下一步就是确定列的类型了，列的类型可分大致为三种：字符（串）型、数字型、日期时间型。你可以从这个页面了解更多信息。

正确的选择各个列的数据类型能让你的数据查询更高效，有些数据类型允许你设置一个大小限制，例如：varchar(40)允许你最多存储40个字符（相当于20个中文字符或符号），这个限制是可选的，但是适当使用，是能提高数据查询效率的，所以，你需要预估一下你的自定义表中各个列的最大字符长度，数字类型的列的长度指的是数字的位数而不是数字本身的大小，例如，INT(10)允许存放最多10位的非负整数 - 所以最大是到4,294,967,295 。存储日期时间的话，你需要使用DATETIME类型，该类型的会存储类似于2014-03-05 14:55:10的值，但你可以用一个Wordpress函数 mysql2date() 来格式化日期的显示。

我们的例子中，各个列的数据类型确定如下：

    log_id - bigint(20)
    user_id - bigint(20)
    activity - varchar(20)
    object_id - bigint(20)
    object_type - varchar(20)
    date - datetime

3.3 自定义表的索引

接下来你需要确定这个表的主键（PRIMARY KEY）用于索引这个表了。主键是一个列，每行都有一个唯一的值 - 通常它只是一个自动递增的整数，本质上是‘行号‘ 。

其他索引列的值不必是唯一的，但各个行的这个字段的值应该确定是唯一的。作为主键的索引用来提高查询效率，如果没有索引的搜索就必须通过读整个表来查找匹配的行。主键相当于一本书的页码，如果说表是一本书的话，各个行是每一页的话，虽然这个例子不是太合适。关于索引，你可以通过这个页面了解更多：Mysql如何使用索引。
3.4 创建自定义表

下面的函数结构用于在插件激活/启用时创建数据库的表：

    function coolwp_create_my_table() {
        // 创建表的代码放这里

    }

    // 激活插件时创建表
    register_activation_hook( __FILE__, ‘coolwp_create_my_table‘ );

在这个函数内部，我们需要包含wp-admin/includes/upgrade.php这个文件，原因与这篇文章描述的相似：Call to undefined function is_user_logged_in() ，否则，Wordpress的数据库相关函数： dbDelta()将不会为你工作。请注意，当插件启用的时候，你最好就别用init这个HOOK（这是WP内置的挂钩，翻译为钩子实在难听）了，所以，调用coolwp_register_activity_log_table（）前，需要手工包含这个文件：

    require_once( ABSPATH . ‘wp-admin/includes/upgrade.php‘ );
    global $wpdb;   http://www.swufe.edu.cn/
    global $charset_collate;
    // 手工调用这个函数吧
    coolwp_register_activity_log_table();

全局变量$charset_collate 包含了字符集和Wordpress原生的数据库整理，这是I18n所需的。因为你的插件可能会随Wordpress被用于多种语种。

实例中，我们的SQL语句是这样的：

    $sql_create_table = "CREATE TABLE {$wpdb->wptuts_activity_log} (
              log_id bigint(20) unsigned NOT NULL auto_increment,
              user_id bigint(20) unsigned NOT NULL default ‘0‘,
              activity varchar(20) NOT NULL default ‘updated‘,
              object_id bigint(20) unsigned NOT NULL default ‘0‘,
              object_type varchar(20) NOT NULL default ‘post‘,
              activity_date datetime NOT NULL default ‘0000-00-00 00:00:00‘,
              PRIMARY KEY (log_id),
              KEY user_id (user_id)
         ) $charset_collate; ";

    dbDelta( $sql_create_table );

dbDelta()函数是WP内置的，用来创建这个示例的表。
3.5 调试

如果你启用了你的插件，发现有如下报错信息："你有X个字符的意外输出...",这通常是由SQL语句中的错误引起的，你就需要重新审查下你的SQL语句了。如果你在dbDelta()之后添加wp_die()函数，将不会有报错，但是错误是可能是存在的，所以，尽量不要在这个函数之后添加那个函数。

4. 小结

本文简述了在Wordpress环境中使用自定义数据表的缺点以及如何创建自定义表。本系列的下一篇文章将会介绍如何防止SQL注入，以提高安全性。

本系列文章所用示例的完整代码：

    <?php
    /*
    Plugin Name: WP User Activity Log
    Version: 1.0
    Description: This code complements a series at wp.tutsplus.com on Custom Database Tables
    Author: Stephen Harris
    Author URI: http://www.stephenharris.info
    */



    /**
     * Store our table name in $wpdb with correct prefix
     * Prefix will vary between sites so hook onto switch_blog too
     * @since 1.0
    */
    function coolwp_register_activity_log_table(){
        global $wpdb;
        $wpdb->coolwp_activity_log = "{$wpdb->prefix}coolwp_activity_log";
    }
    add_action( ‘init‘, ‘coolwp_register_activity_log_table‘,1);
    add_action( ‘switch_blog‘, ‘coolwp_register_activity_log_table‘);



    /**
     * Creates our table
     * Hooked onto activate_[plugin] (via register_activation_hook)
     * @since 1.0
    */
    function coolwp_create_activity_log_table(){

        global $wpdb;
        global $charset_collate;

        require_once(ABSPATH . ‘wp-admin/includes/upgrade.php‘);

        //Call this manually as we may have missed the init hook
        coolwp_register_activity_log_table();

        $sql_create_table = "CREATE TABLE {$wpdb->coolwp_activity_log} (
            log_id bigint(20) unsigned NOT NULL auto_increment,
            user_id bigint(20) unsigned NOT NULL default ‘0‘,
            activity varchar(30) NOT NULL default ‘updated‘,
            object_id bigint(20) unsigned NOT NULL default ‘0‘,
            object_type varchar(20) NOT NULL default ‘post‘,
            activity_date datetime NOT NULL default ‘0000-00-00 00:00:00‘,
            PRIMARY KEY (log_id),
            KEY abc (user_id)
            ) $charset_collate; ";

        dbDelta($sql_create_table);
    }
    register_activation_hook(__FILE__,‘coolwp_create_activity_log_table‘);

    function coolwp_get_log_table_columns(){
        return array(
            ‘log_id‘=> ‘%d‘,
            ‘user_id‘=> ‘%d‘,
            ‘activity‘=>‘%s‘,
            ‘object_id‘=>‘%d‘,
            ‘object_type‘=>‘%s‘,
            ‘activity_date‘=>‘%s‘,
        );
    }


    /**
     * Inserts a log into the database
     *
     *@param $data array An array of key => value pairs to be inserted
     *@return int The log ID of the created activity log. Or WP_Error or false on failure.
    */
    function coolwp_insert_log( $data=array() ){
        global $wpdb;

        //Set default values
        $data = wp_parse_args($data, array(
                     ‘user_id‘=> get_current_user_id(),
                     ‘date‘=> current_time(‘timestamp‘),
        ));

        //Check date validity
        if( !is_float($data[‘date‘]) || $data[‘date‘] <= 0 )
            return 0;

        //Convert activity date from local timestamp to GMT mysql format
        $data[‘activity_date‘] = date_i18n( ‘Y-m-d H:i:s‘, $data[‘date‘], true );

        //Initialise column format array
        $column_formats = coolwp_get_log_table_columns();

        //Force fields to lower case
        $data = array_change_key_case ( $data );

        //White list columns
        $data = array_intersect_key($data, $column_formats);

        //Reorder $column_formats to match the order of columns given in $data
        $data_keys = array_keys($data);
        $column_formats = array_merge(array_flip($data_keys), $column_formats);
        $wpdb->insert($wpdb->coolwp_activity_log, $data, $column_formats);

        return $wpdb->insert_id;
    }


    /**
     * Updates an activity log with supplied data
     *
     *@param $log_id int ID of the activity log to be updated
     *@param $data array An array of column=>value pairs to be updated
     *@return bool Whether the log was successfully updated.
    */
    function coolwp_update_log( $log_id, $data=array() ){
        global $wpdb;

        //Log ID must be positive integer
        $log_id = absint($log_id);
        if( emptyempty($log_id) )
             return false;

        //Convert activity date from local timestamp to GMT mysql format
        if( isset($data[‘activity_date‘]) )
             $data[‘activity_date‘] = date_i18n( ‘Y-m-d H:i:s‘, $data[‘date‘], true );

        //Initialise column format array
        $column_formats = coolwp_get_log_table_columns();

        //Force fields to lower case
        $data = array_change_key_case ( $data );

        //White list columns
        $data = array_intersect_key($data, $column_formats);

        //Reorder $column_formats to match the order of columns given in $data
        $data_keys = array_keys($data);
        $column_formats = array_merge(array_flip($data_keys), $column_formats);

        if ( false === $wpdb->update($wpdb->coolwp_activity_log, $data, array(‘log_id‘=>$log_id), $column_formats) ) {
             return false;
        }

        return true;
    }


    /**
     * Retrieves activity logs from the database matching $query.
     * $query is an array which can contain the following keys:
     *
     * ‘fields‘ - an array of columns to include in returned roles. Or ‘count‘ to count rows. Default: empty (all fields).
     * ‘orderby‘ - datetime, user_id or log_id. Default: datetime.
     * ‘order‘ - asc or desc
     * ‘user_id‘ - user ID to match, or an array of user IDs
     * ‘since‘ - timestamp. Return only activities after this date. Default false, no restriction.
     * ‘until‘ - timestamp. Return only activities up to this date. Default false, no restriction.
     *
     *@param $query Query array
     *@return array Array of matching logs. False on error.
    */
    function coolwp_get_logs( $query=array() ){
         global $wpdb;

         /* Parse defaults */
         $defaults = array(
           ‘fields‘=>array(),‘orderby‘=>‘datetime‘,‘order‘=>‘desc‘, ‘user_id‘=>false,
           ‘since‘=>false,‘until‘=>false,‘number‘=>10,‘offset‘=>0
         );
        $query = wp_parse_args($query, $defaults);

        /* Form a cache key from the query */
        $cache_key = ‘coolwp_logs:‘.md5( serialize($query));
        $cache = wp_cache_get( $cache_key );
        if ( false !== $cache ) {
                $cache = apply_filters(‘coolwp_get_logs‘, $cache, $query);
                return $cache;
        }
         extract($query);

        /* SQL Select */
        //Whitelist of allowed fields
        $allowed_fields = coolwp_get_log_table_columns();
        if( is_array($fields) ){

            //Convert fields to lowercase (as our column names are all lower case - see part 1)
            $fields = array_map(‘strtolower‘,$fields);

            //Sanitize by white listing
            $fields = array_intersect($fields, $allowed_fields);

        }else{
            $fields = strtolower($fields);
        }

        //Return only selected fields. Empty is interpreted as all
        if( emptyempty($fields) ){
            $select_sql = "SELECT* FROM {$wpdb->coolwp_activity_log}";
        }elseif( ‘count‘ == $fields ) {
            $select_sql = "SELECT COUNT(*) FROM {$wpdb->coolwp_activity_log}";
        }else{
            $select_sql = "SELECT ".implode(‘,‘,$fields)." FROM {$wpdb->coolwp_activity_log}";
        }

         /*SQL Join */
         //We don‘t need this, but we‘ll allow it be filtered (see ‘coolwp_logs_clauses‘ )
         $join_sql=‘‘;

        /* SQL Where */
        //Initialise WHERE
        $where_sql = ‘WHERE 1=1‘;
        if( !emptyempty($log_id) )
           $where_sql .= $wpdb->prepare(‘ AND log_id=%d‘, $log_id);

        if( !emptyempty($user_id) ){
           //Force $user_id to be an array
           if( !is_array( $user_id) )
               $user_id = array($user_id);

           $user_id = array_map(‘absint‘,$user_id); //Cast as positive integers
           $user_id__in = implode(‘,‘,$user_id);
           $where_sql .= " AND user_id IN($user_id__in)";
        }

        $since = absint($since);
        $until = absint($until);

        if( !emptyempty($since) )
           $where_sql .= $wpdb->prepare(‘ AND activity_date >= %s‘, date_i18n( ‘Y-m-d H:i:s‘, $since,true));

        if( !emptyempty($until) )
           $where_sql .= $wpdb->prepare(‘ AND activity_date <= %s‘, date_i18n( ‘Y-m-d H:i:s‘, $until,true));

        /* SQL Order */
        //Whitelist order
        $order = strtoupper($order);
        $order = ( ‘ASC‘ == $order ? ‘ASC‘ : ‘DESC‘ );
        switch( $orderby ){
           case ‘log_id‘:
                $order_sql = "ORDER BY log_id $order";
           break;
           case ‘user_id‘:
                $order_sql = "ORDER BY user_id $order";
           break;
           case ‘datetime‘:
                 $order_sql = "ORDER BY activity_date $order";
           default:
           break;
        }

        /* SQL Limit */
        $offset = absint($offset); //Positive integer
        if( $number == -1 ){
             $limit_sql = "";
        }else{
             $number = absint($number); //Positive integer
             $limit_sql = "LIMIT $offset, $number";
        }

        /* Filter SQL */
        $pieces = array( ‘select_sql‘, ‘join_sql‘, ‘where_sql‘, ‘order_sql‘, ‘limit_sql‘ );
        $clauses = apply_filters( ‘coolwp_logs_clauses‘, compact( $pieces ), $query );
        foreach ( $pieces as $piece )
              $$piece = isset( $clauses[ $piece ] ) ? $clauses[ $piece ] : ‘‘;

        /* Form SQL statement */
        $sql = "$select_sql $where_sql $order_sql $limit_sql";
        if( ‘count‘ == $fields ){
            return $wpdb->get_var($sql);
        }

        /* Perform query */
        $logs = $wpdb->get_results($sql);

        /* Add to cache and filter */
        wp_cache_add( $cache_key, $logs, 24*60*60 );
        $logs = apply_filters(‘coolwp_get_logs‘, $logs, $query);

        return $logs;
     }


    /**
     * Deletes an activity log from the database
     *
     *@param $log_id int ID of the activity log to be deleted
     *@return bool Whether the log was successfully deleted.
    */
    function coolwp_delete_log( $log_id ){
        global $wpdb;

        //Log ID must be positive integer
        $log_id = absint($log_id);

        if( emptyempty($log_id) )
             return false;

        do_action(‘coolwp_delete_log‘,$log_id);

        $sql = $wpdb->prepare("DELETE from {$wpdb->coolwp_activity_log} WHERE log_id = %d", $log_id);

        if( !$wpdb->query( $sql ) )
             return false;

        do_action(‘coolwp_deleted_log‘,$log_id);

        return true;
    }