最近让AI帮忙做的一些事

#注意！这一版会插入数据前自动清空数据库，请备份和再执行
import json
import re
import mysql.connector
from datetime import datetime, timezone
from urllib.parse import urlparse, unquote, quote

# ========== 配置 ==========
JSONL_FILE = "Comment.0.jsonl"          # 你的 JSONL 文件
DB_CONFIG = {
    "host": "你的数据库地址",
    "user": "数据库用户名",
    "password": "数据库密码",
    "database": "数据库名",
    "charset": "utf8mb4"
}
TABLE_PREFIX = "wp_"
# ==========================

conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor()

# 1. 清空并确保字段
cursor.execute(f"TRUNCATE TABLE {TABLE_PREFIX}comments")
cursor.execute(f"ALTER TABLE {TABLE_PREFIX}comments MODIFY comment_content LONGTEXT")

# 2. 获取文章 slug 映射
cursor.execute(
    f"SELECT ID, post_name FROM {TABLE_PREFIX}posts "
    f"WHERE post_type IN ('post','page') AND post_status='publish'"
)
rows = cursor.fetchall()
print(f"查询到 {len(rows)} 篇文章")
slug_to_id = {row[1].lower(): row[0] for row in rows}

def get_candidate_slugs(url_str):
    path = urlparse(url_str).path.strip("/")
    if not path: return []
    raw = path.split("/")[-1]
    try:
        decoded = unquote(raw)
        encoded = quote(decoded, safe='').lower()
    except:
        encoded = raw.lower()
    candidates = [encoded]
    m = re.match(r"^\d{2}-\d{2}-\d{4}_(.+)", encoded)
    if m:
        candidates.append(m.group(1))
    return candidates

# 3. 读取并解析所有评论，按时间升序排序
all_comments = []
skipped = []
with open(JSONL_FILE, "r", encoding="utf-8") as f:
    for line in f:
        line = line.strip()
        if not line: continue
        c = json.loads(line)

        candidates = get_candidate_slugs(c.get("url", ""))
        post_id = None
        for slug in candidates:
            post_id = slug_to_id.get(slug)
            if post_id: break

        if not post_id:
            skipped.append((c.get("url", ""), candidates))
            continue

        # 时间解析
        try:
            ts = c.get("createdAt", "").replace("Z", "+00:00")
            dt = datetime.fromisoformat(ts)
            gmt = dt.strftime('%Y-%m-%d %H:%M:%S')
            local = dt.astimezone().strftime('%Y-%m-%d %H:%M:%S')
        except:
            now = datetime.now()
            gmt = now.strftime('%Y-%m-%d %H:%M:%S')
            local = gmt

        user_id = 1 if c.get("mail", "").lower() == "xty@2broear.com" else 0

        all_comments.append({
            "post_id": post_id,
            "author": c.get("nick", "")[:200],
            "email": c.get("mail", "")[:100],
            "url": c.get("link", "")[:200],
            "content": c.get("comment", "")[:200000],
            "date": local,
            "date_gmt": gmt,
            "approved": '1',
            "agent": c.get("ua", "")[:255],
            "oid": c.get("objectId"),
            "pid": c.get("pid", ""),          # ⚠️ 只用 pid
            "user_id": user_id,
            "created_dt": dt                   # 用于排序
        })

# 按时间升序排列（确保父评论必在子评论前）
all_comments.sort(key=lambda x: x["created_dt"])

# 4. 插入
oid_to_new_id = {}
insert_sql = (
    f"INSERT INTO {TABLE_PREFIX}comments "
    "(comment_post_ID, comment_author, comment_author_email, comment_author_url, "
    "comment_content, comment_date, comment_date_gmt, comment_approved, comment_agent, "
    "comment_parent, user_id) "
    "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
)

for c in all_comments:
    parent_id = 0
    pid = c["pid"]
    if pid:  # 有直接父评论
        parent_id = oid_to_new_id.get(pid, 0)   # 此时父评论一定已插入
        # 如果 pid 不在映射中（理论上不会），就降为顶级
    data = (
        c["post_id"], c["author"], c["email"], c["url"],
        c["content"], c["date"], c["date_gmt"], c["approved"],
        c["agent"], parent_id, c["user_id"]
    )
    cursor.execute(insert_sql, data)
    new_id = cursor.lastrowid
    oid_to_new_id[c["oid"]] = new_id

# 5. 更新评论数
cursor.execute(
    f"UPDATE {TABLE_PREFIX}posts p "
    f"SET comment_count = (SELECT COUNT(*) FROM {TABLE_PREFIX}comments c "
    f"WHERE c.comment_post_ID = p.ID AND c.comment_approved = '1') "
    f"WHERE p.post_type IN ('post','page')"
)

conn.commit()
cursor.close()
conn.close()
print(f"导入 {len(all_comments)} 条评论完成！")
if skipped:
    print(f"\n未匹配的评论数: {len(skipped)} 条")
    print("详细信息：")
    for url, cands in skipped:
        print(f"  URL: {url}")
        print(f"  尝试过的 slug: {cands}")
        print()

UPDATE wp_posts p 
SET comment_count = (
    SELECT COUNT(*) FROM wp_comments c 
    WHERE c.comment_post_ID = p.ID AND c.comment_approved = '1'
)
WHERE p.post_type IN ('post','page');