使用dbDelta函数在Wordpress中创建表格

Question

我需要在wordpress中为插件创建一个自定义表格。我遵循几个在线文本并创建了表格，但发现无法访问。当试图从表中选择所有内容时，将不会返回任何内容，并且当尝试使用数据库浏览器插件查看表时，我收到此错误：“您的SQL语法中有错误;请检查与您的MySQL服务器相对应的手册版本的正确语法，以响应插件的查询（“SELECT SQL_CALC_FOUND_ROWS FROM wp-typeEvents LIMIT 0，100 ;;”）在'FROM wp-typeEvents LIMIT 0，100'at line 1'附近使用。

总之，我试图用dbDelta来创建一个表。该表已创建，但存在某些问题，导致无法添加行或读取其内容。

我读过dbDelta可以finnicky功能，所以我试图坚持自己的三条黄金法则：

在新行
-Putting主键之间的两个空格-Putting每个字段其定义
-Having至少一个关键

下面的代码：

global $wpdb; 

$tablename = "wp-typeEvents"; 
$query = "CREATE TABLE `" . $tablename . "` (
    `id` mediumint(9) NOT NULL AUTO_INCREMENT, 
    `eventName` varchar(60) NOT NULL, 
    `location` varchar(60) DEFAULT '' NULL, 
    `price` double NOT NULL, 
    `description` text NOT NULL, 
    `paypal` varchar(60) NOT NULL, 
    PRIMARY KEY (`id`) 
    );"; 

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

任何想法？

Answer 1

以及你发现自己的问题，你似乎是硬编码表前缀。你不应该这样做。您应该使用以下手册： -

$wpdb->prefix 

这存储了前缀，并且应该预先添加到表名中。这允许改变前缀的可能性。

http://codex.wordpress.org/Creating_Tables_with_Plugins

Answer 2

如果它是一个插件里面，放在require_once（）路径是错误的。应该是：

require_once('/includes/upgrade.php'); 

应该更正为能够加载dbDelta（）函数。

希望这会有所帮助。

Answer 3

不要硬编码表名称，使用$ wpdb->前缀（如其他人注意到的）。

请勿在字段名称周围使用引号。

还有其他有用的“规则”，以及，他们都在这里列出： http://codex.wordpress.org/Creating_Tables_with_Plugins

Note that the dbDelta function is rather picky, however. For instance:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
• You must not use any apostrophes or backticks around field names.
• Field types must be all lowercase.
• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.

Answer 4

还拿注意到，当前dbDelta()功能无法解析排序关键字ASC |上唯一键DESC它将引用的列的名称，它会将约束添加到剩余的索引，因此它将尝试添加触发Unique Key错误的约束。看下面的例子：

尝试创建这个questrong textry会触发一个错误。

CREATE TABLE wp_test (
     id int(11) NOT NULL AUTO_INCREMENT, 
     email varchar(100) NOT NULL, 
     PRIMARY KEY (stcr_id), 
     UNIQUE KEY uk_email (subscriber_email ASC)) 
ENGINE = InnoDB 
DEFAULT CHARACTER SET utf8; 

默认情况下，排序类型是ASC，所以摆脱这一点将起作用。

CREATE TABLE wp_test (
     id int(11) NOT NULL AUTO_INCREMENT, 
     email varchar(100) NOT NULL, 
     PRIMARY KEY (stcr_id), 
     UNIQUE KEY uk_email (subscriber_email)) 
ENGINE = InnoDB 
DEFAULT CHARACTER SET utf8; 

我测试了这个WordPress的4.1.1

Answer 5

同时使用wordpress的dbDelta核心功能我也面临着一些问题，并决定为它创建一个功能：

/** 
* Prevents unnecessary re-creating index and repetitive altering table operations when using WordPress dbDelta function 
* 
* Usage Example: 
* 
* $table_name  = "ratings"; 
* 
* $table_columns = "id INT(6) UNSIGNED AUTO_INCREMENT, 
*     rate tinyint(1) NOT NULL, 
*     ticket_id bigint(20) NOT NULL, 
*     response_id bigint(20) NOT NULL, 
*     created_at TIMESTAMP"; 
* 
* $table_keys  = "PRIMARY KEY (id), 
*     KEY ratings_rate (rate), 
*     UNIQUE KEY ratings_response_id (response_id)"; 
* 
* create_table($table_name, $table_columns, $table_keys); 
* 
* Things that need to be considered when using dbDelta function : 
* 
* You must put each field on its own line in your SQL statement. 
* You must have two spaces between the words PRIMARY KEY and the definition of your primary key. 
* You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY. 
* You must not use any apostrophes or backticks around field names. 
* Field types must be all lowercase. 
* SQL keywords, like CREATE TABLE and UPDATE, must be uppercase. 
* You must specify the length of all fields that accept a length parameter. int(11), for example. 
* 
* Further information can be found on here: 
* 
* http://codex.wordpress.org/Creating_Tables_with_Plugins 
* 
* @param $table_name 
* @param $table_columns 
* @param null $table_keys 
* @param null $charset_collate 
* @version 1.0.1 
* @author Ugur Mirza Zeyrek 
*/ 
function create_table($table_name, $table_columns, $table_keys = null, $db_prefix = true, $charset_collate = null) { 
    global $wpdb; 

    if($charset_collate == null) 
     $charset_collate = $wpdb->get_charset_collate(); 
    $table_name = ($db_prefix) ? $wpdb->prefix.$table_name : $table_name; 
    $table_columns = strtolower($table_columns); 

    if($table_keys) 
     $table_keys = ", $table_keys"; 

    $table_structure = "($table_columns $table_keys)"; 

    $search_array = array(); 
    $replace_array = array(); 

    $search_array[] = "`"; 
    $replace_array[] = ""; 

    $table_structure = str_replace($search_array,$replace_array,$table_structure); 

    $sql = "CREATE TABLE $table_name $table_structure $charset_collate;"; 

    // Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/includes/upgrade.php (we'll have to load this file, as it is not loaded by default) 
    require_once (ABSPATH . 'wp-admin/includes/upgrade.php'); 

    // The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary 
    return dbDelta($sql); 
} 

https://github.com/mirzazeyrek/wordpress_create_table