自动排序Typecho文章、评论、绑定分类

弄了个SQL脚本，可以直接排序typecho_contents表里的cid文章id，然后自动让typecho_comments表里的cid绑定排序好的cid，typecho_relationships表里的文章分类绑定也会绑定排序好的cid

不会串文章，评论和文章分类，非常牛波一

直接复制执行就行，全自动，无需一条一条复制执行

以下是SQL脚本

-- 开始事务，确保原子性
START TRANSACTION;

-- 1. 备份原表
CREATE TABLE IF NOT EXISTS typecho_contents_backup AS SELECT * FROM typecho_contents;
CREATE TABLE IF NOT EXISTS typecho_comments_backup AS SELECT * FROM typecho_comments;
CREATE TABLE IF NOT EXISTS typecho_relationships_backup AS SELECT * FROM typecho_relationships;
CREATE TABLE IF NOT EXISTS typecho_fields_backup AS SELECT * FROM typecho_fields;

-- 2. 重新排序typecho_contents的cid
SET @i = 0;
UPDATE typecho_contents SET cid = (@i := @i + 1) ORDER BY cid;
ALTER TABLE typecho_contents AUTO_INCREMENT = 1;

-- 3. 创建cid映射表
CREATE TABLE IF NOT EXISTS cid_mapping (
    old_cid INT PRIMARY KEY,
    new_cid INT
);

-- 清空映射表（如果已存在）
TRUNCATE TABLE cid_mapping;

-- 4. 填充映射关系
INSERT INTO cid_mapping (old_cid, new_cid)
SELECT 
    t1.cid as old_cid,
    t2.cid as new_cid
FROM typecho_contents_backup t1
JOIN typecho_contents t2 ON t1.title = t2.title AND t1.created = t2.created;

-- 5. 更新typecho_comments表
UPDATE typecho_comments c
JOIN cid_mapping m ON c.cid = m.old_cid
SET c.cid = m.new_cid;

-- 6. 更新typecho_fields表
UPDATE typecho_fields f
JOIN cid_mapping m ON f.cid = m.old_cid
SET f.cid = m.new_cid;

-- 7. 处理typecho_relationships表的主键冲突
-- 创建临时表存储去重后的数据
CREATE TEMPORARY TABLE IF NOT EXISTS temp_relationships AS
SELECT DISTINCT m.new_cid as cid, r.mid
FROM typecho_relationships r
JOIN cid_mapping m ON r.cid = m.old_cid;

-- 清空原表并插入去重后的数据
TRUNCATE TABLE typecho_relationships;

INSERT INTO typecho_relationships (cid, mid)
SELECT cid, mid FROM temp_relationships;

-- 8. 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_relationships;
DROP TABLE IF EXISTS cid_mapping;
DROP TABLE IF EXISTS typecho_contents_backup;
DROP TABLE IF EXISTS typecho_comments_backup;
DROP TABLE IF EXISTS typecho_relationships_backup;
DROP TABLE IF EXISTS typecho_fields_backup;

-- 提交事务
COMMIT;

-- 9. 验证结果
SELECT '文章表排序结果:' as '';
SELECT cid, title FROM typecho_contents ORDER BY cid;

SELECT '评论数量统计:' as '';
SELECT cid, COUNT(*) as comment_count 
FROM typecho_comments 
GROUP BY cid 
ORDER BY cid;

SELECT '字段扩展数量统计:' as '';
SELECT cid, COUNT(*) as field_count 
FROM typecho_fields 
GROUP BY cid 
ORDER BY cid;

SELECT '分类关系数量统计:' as '';
SELECT cid, COUNT(*) as category_count 
FROM typecho_relationships 
GROUP BY cid 
ORDER BY cid;

-- 10. 详细验证各表关联关系
SELECT '详细关联验证 - 文章与评论:' as '';
SELECT c.cid, co.title, COUNT(c.coid) as comment_count 
FROM typecho_contents co
LEFT JOIN typecho_comments c ON co.cid = c.cid
GROUP BY co.cid, co.title
ORDER BY co.cid;

SELECT '详细关联验证 - 文章与字段:' as '';
SELECT f.cid, co.title, COUNT(f.name) as field_count 
FROM typecho_contents co
LEFT JOIN typecho_fields f ON co.cid = f.cid
GROUP BY co.cid, co.title
ORDER BY co.cid;

SELECT '详细关联验证 - 文章与分类:' as '';
SELECT r.cid, co.title, COUNT(r.mid) as category_count 
FROM typecho_contents co
LEFT JOIN typecho_relationships r ON co.cid = r.cid
GROUP BY co.cid, co.title
ORDER BY co.cid;