php - 在为 WordPress 插件创建的表上创建索引时出现问题

Question

当我为新的 Wordpress 插件创建表时，在创建一些索引时遇到问题。

这是代码:

global $wpdb;
$em_posts = $wpdb->prefix . "em_collab_posts";
$em_groups = $wpdb->prefix . "em_collab_groups";
$em_users = $wpdb->prefix . "em_collab_users";
if($wpdb->get_var("SHOW TABLES LIKE '$em_users'") != $em_users) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_users` (
                `cuID` bigint(20) NOT NULL,
                `groupID` bigint(20) NOT NULL,
                `userID` bigint(20) NOT NULL,
                `cuType` int(11) NOT NULL,
                `cuSettings` longtext NOT NULL,
                `cuCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cuUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_users`
            ADD PRIMARY KEY (`cuID`), ADD KEY `groupID` (`groupID`), ADD KEY `userID` (`userID`), ADD KEY `cuType` (`cuType`); ");
    dbDelta("ALTER TABLE `$em_users`
            MODIFY `cuID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_posts'") != $em_posts) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_posts` (
                `cpID` bigint(20) NOT NULL,
                `groupID` bigint(20) NOT NULL,
                `post_id` bigint(20) NOT NULL,
                `cpSettings` longtext NOT NULL,
                `cpCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cpUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_posts`
                ADD PRIMARY KEY (`cpID`), ADD KEY `groupID` (`groupID`), ADD KEY `post_id` (`post_id`);");
    dbDelta("ALTER TABLE `$em_posts`
                MODIFY `cpID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_groups'") != $em_groups) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_groups` (
                `groupID` bigint(20) NOT NULL,
                `gTitle` varchar(50) NOT NULL,
                `gDescription` longtext NOT NULL,
                `gSettings` longtext NOT NULL,
                `gCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `gUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_groups`
                ADD PRIMARY KEY (`groupID`), ADD KEY `gTitle` (`gTitle`);");
    dbDelta("ALTER TABLE `$em_groups`
                MODIFY `groupID` bigint(20) NOT NULL AUTO_INCREMENT;");
}

我还在实际创建中使用ALTER TABLE语句进行了尝试，表本身被创建，但索引没有创建。

我做错了什么？或者有其他方法可以做到这一点吗？

Answer 1

需要更改初始查询

更新的代码 - 有效

$em_posts = $wpdb->prefix . "em_collab_posts";
$em_groups = $wpdb->prefix . "em_collab_groups";
$em_users = $wpdb->prefix . "em_collab_users";
if($wpdb->get_var("SHOW TABLES LIKE '$em_users'") != $em_users) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_users` (
                `cuID` bigint(20) NOT NULL auto_increment,
                `groupID` bigint(20) NOT NULL,
                `userID` bigint(20) NOT NULL,
                `cuType` int(11) NOT NULL,
                `cuSettings` longtext NOT NULL,
                `cuCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cuUpdated` datetime DEFAULT NULL,
                PRIMARY KEY (`cuID`),
                KEY `groupID` (`groupID`),
                KEY `userID` (`userID`),
                KEY `cuType` (`cuType`)
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_posts'") != $em_posts) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_posts` (
                `cpID` bigint(20) NOT NULL auto_increment,
                `groupID` bigint(20) NOT NULL,
                `post_id` bigint(20) NOT NULL,
                `cpSettings` longtext NOT NULL,
                `cpCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cpUpdated` datetime DEFAULT NULL,
                PRIMARY KEY (`cpID`),
                KEY `groupID` (`groupID`),
                KEY `post_id` (`post_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_groups'") != $em_groups) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_groups` (
            `groupID` bigint(20) NOT NULL auto_increment,
                `gTitle` varchar(50) NOT NULL,
                `gDescription` longtext NOT NULL,
                `gSettings` longtext NOT NULL,
                `gCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `gUpdated` datetime DEFAULT NULL,
                PRIMARY KEY (`groupID`)
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
}