最新消息:把Logo换了一下,虽然丑了点,但至少完全是自己的设计

WordPress文章ID和评论ID重新整理

PHP 宇托 501浏览 暂无评论

最近发现我博客里的文章ID乱得一团糟,历史记录、自动保存记录一堆。因为我的博客URL不是以ID为名的,整理ID也不会影响URL。所以就想整理一下,看看有四百多篇文章,要手动改也太费事了。在网上找了一下,找到一个思路,直接用PHP和SQL来实现更新。

因为他的代码不行执行,所以我修改了一下他的代码,并扩展了一些自己的方法。代码主要实现四个功能。注意,因为以下处理都操作了数据库,请执行前一定要先备份数据库

  • 删除历史记录、自动保存记录
  • 重新整理文章ID
  • 重新整理评论ID
  • 重新整理wp_options的ID
  • 重新整理wp_terms的termsID

下面,一个一个的介绍,你可以选择一个或者多下保存成PHP文件,上传到wordpress目录,然后在浏览器里访问一下就行。

删除历史记录、自动保存记录

这个要操作三个表:wp_postswp_term_relationshipswp_postmeta,主要就是删除wp_postspost_typerevision的数据以及post_statusauto-draft的数据。

其PHP代码如下:

PHP
<?php
require_once( './wp-config.php' );
function delete_revision_autosave_post()
{
    global $wpdb;
    $sql = "delete a,b,c from " . $wpdb->prefix ."posts a "
           . "left join " . $wpdb->prefix ."term_relationships b on (a.id = b.object_id) "
           . "left join " . $wpdb->prefix ."postmeta c on (a.id = c.post_id) "
           . "where a.post_type = 'revision' or post_status='auto-draft'";
    $wpdb->query($sql);
}
delete_revision_autosave_post();

重新整理文章ID

这个要操作四个表:wp_postswp_term_relationshipswp_postmetawp_comments。主要是更新postId。

因为我想把所有页面放到文章前,所以代码多了点。其PHP代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_post_id()
{
    global $wpdb;

    $buffer_count = 10000;
    $add_post_id_query = 'update ' . $wpdb->prefix .'posts set id = id + ' . $buffer_count;
    $wpdb->query($add_post_id_query);
    $add_post_id_query = 'update ' . $wpdb->prefix .'term_relationships set object_id = object_id + ' . $buffer_count;
    $wpdb->query($add_post_id_query);
    $add_post_id_query = 'update ' . $wpdb->prefix .'postmeta set post_id = post_id + ' . $buffer_count;
    $wpdb->query($add_post_id_query);
    $add_post_id_query = 'update ' . $wpdb->prefix .'comments set comment_post_id = comment_post_id + ' . $buffer_count;
    $wpdb->query($add_post_id_query);

    $sql_query = 'select id from ' . $wpdb->prefix .'posts order by post_type, id';
    $all_post_ids = $wpdb->get_results( $sql_query );

    $new_post_id = 1;
    if (is_array($all_post_ids)) {
        foreach($all_post_ids as $post_id_obj) {
            $post_id = $post_id_obj->id;
            $wpdb->query( 'update ' . $wpdb->prefix .'posts set id = ' . $new_post_id . ' where id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_relationships set object_id = ' . $new_post_id . ' where object_id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'postmeta set post_id = ' . $new_post_id . ' where post_id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'comments set comment_post_id = ' . $new_post_id . ' where comment_post_id = ' . $post_id );
            ++$new_post_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'posts AUTO_INCREMENT = ' . $new_post_id);
    }
}
update_post_id();

如果只需要把ID连续,代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_post_id()
{
    global $wpdb;

    $sql_query = 'select id from ' . $wpdb->prefix .'posts order by id';
    $all_post_ids = $wpdb->get_results( $sql_query );

    $new_post_id = 1;
    if (is_array($all_post_ids)) {
        foreach($all_post_ids as $post_id_obj) {
            $post_id = $post_id_obj->id;
            $wpdb->query( 'update ' . $wpdb->prefix .'posts set id = ' . $new_post_id . ' where id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_relationships set object_id = ' . $new_post_id . ' where object_id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'postmeta set post_id = ' . $new_post_id . ' where post_id = ' . $post_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'comments set comment_post_id = ' . $new_post_id . ' where comment_post_id = ' . $post_id );
            ++$new_post_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'posts AUTO_INCREMENT = ' . $new_post_id);
    }
}
update_post_id();

重新整理评论ID

代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_comment_id()
{
    global $wpdb;
    $sql_query = 'select comment_id from ' . $wpdb->prefix .'comments order by comment_id';
    $all_comment_ids = $wpdb->get_results( $sql_query );

    $new_comment_id = 1;
    if (is_array($all_comment_ids)) {
        foreach($all_comment_ids as $comment_id_obj) {
            $comment_id = $comment_id_obj->comment_id;
            $wpdb->query( 'update ' . $wpdb->prefix .'comments set comment_id = ' . $new_comment_id . ' where comment_id = ' . $comment_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'comments set comment_parent = ' . $new_comment_id . ' where comment_parent = ' . $comment_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'commentmeta set comment_id = ' . $new_comment_id . ' where comment_id = ' . $comment_id);
            ++$new_comment_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'comments AUTO_INCREMENT = ' . $new_comment_id);
    }
}
update_comment_id();

重新整理wp_options的ID

代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_option_id()
{
    global $wpdb;
    $sql_query = 'select option_id from ' . $wpdb->prefix .'options order by option_id';
    $all_option_ids = $wpdb->get_results( $sql_query );

    $new_option_id = 1;
    if (is_array($all_option_ids)) {
        foreach($all_option_ids as $option_id_obj) {
            $option_id = $option_id_obj->option_id;
            $wpdb->query( 'update ' . $wpdb->prefix .'options set option_id = ' . $new_option_id . ' where option_id = ' . $option_id );
            ++$new_option_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'options AUTO_INCREMENT = ' . $new_option_id);
    }
}
update_option_id();

重新整理wp_terms的termsID

我想把分类排在标签之前,所以代码多了点。代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_term_id()
{
    global $wpdb;

    $buffer_count = 10000;

    $add_term_id_query = 'update ' . $wpdb->prefix .'terms set term_id = term_id + ' . $buffer_count;
    $wpdb->query($add_term_id_query);
    $add_term_id_query = 'update ' . $wpdb->prefix .'termmeta set term_id = term_id + ' . $buffer_count;
    $wpdb->query($add_term_id_query);
    $add_term_id_query = 'update ' . $wpdb->prefix .'term_taxonomy set term_id = term_id + ' . $buffer_count;
    $wpdb->query($add_term_id_query);
    $add_term_id_query = 'update ' . $wpdb->prefix .'term_taxonomy set parent = parent + ' . $buffer_count . 'where parent > 0';
    $wpdb->query($add_term_id_query);

    $sql_query = 'select t.term_id from ' . $wpdb->prefix .'terms t inner join ' . $wpdb->prefix .'term_taxonomy m '
                 . 'on t.term_id = m.term_id order by m.taxonomy, m.parent, t.name, m.term_id';
    $all_term_ids = $wpdb->get_results( $sql_query );

    $new_term_id = 1;
    if (is_array($all_term_ids)) {
        foreach($all_term_ids as $term_id_obj) {
            $term_id = $term_id_obj->term_id;
            $wpdb->query( 'update ' . $wpdb->prefix .'terms set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'termmeta set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_taxonomy set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_taxonomy set parent = ' . $new_term_id . ' where parent = ' . $term_id );
            ++$new_term_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'terms AUTO_INCREMENT = ' . $new_term_id);
    }
}
update_term_id();

如果仅仅是整理ID,代码如下:

PHP
<?php
require_once( './wp-config.php' );
function update_term_id()
{
    global $wpdb;

    $sql_query = 'select term_id from ' . $wpdb->prefix .'terms order by term_id';
    $all_term_ids = $wpdb->get_results( $sql_query );

    $new_term_id = 1;
    if (is_array($all_term_ids)) {
        foreach($all_term_ids as $term_id_obj) {
            $term_id = $term_id_obj->term_id;
            $wpdb->query( 'update ' . $wpdb->prefix .'terms set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'termmeta set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_taxonomy set term_id = ' . $new_term_id . ' where term_id = ' . $term_id );
            $wpdb->query( 'update ' . $wpdb->prefix .'term_taxonomy set parent = ' . $new_term_id . ' where parent = ' . $term_id );
            ++$new_term_id;
        }
        $wpdb->query('alter table ' . $wpdb->prefix .'terms AUTO_INCREMENT = ' . $new_term_id);
    }
}
update_term_id();

文件下载地址

上面的代码,我写到一个文件里了,下载地址:postid.zip


参考地址:如何给 WordPress文章ID重新排序

转载请注明:宇托的狗窝 » WordPress文章ID和评论ID重新整理

发表我的评论
取消评论

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址