最近让AI帮忙做的一些事

# 执行代码后，会根据你的数据量等待一段时间，完成后控制台输出迁移完成
import json
import re
import mysql.connector
from datetime import datetime, timezone
from urllib.parse import urlparse, unquote, quote

# ========== 配置（修改为你的信息） ==========
JSONL_FILE = "comment.jsonl"
DB_CONFIG = {
    "host": "你的数据库地址",
    "user": "数据库用户名",
    "password": "数据库密码",
    "database": "数据库名",
    "charset": "utf8mb4"
}
TABLE_PREFIX = "wp_"        # 你的表前缀
# =========================================

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

# 1. 确保 comment_content 是 LONGTEXT
try:
    cursor.execute(f"ALTER TABLE {TABLE_PREFIX}comments MODIFY comment_content LONGTEXT")
    print("comment_content 已改为 LONGTEXT")
except Exception as e:
    print("修改字段备注:", e)

# 2. 获取所有文章 slug -> ID
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 = {}
for post_id, post_name in rows:
    slug_to_id[post_name.lower()] = post_id

# 3. 生成候选 slug 列表的函数
def get_candidate_slugs(url_str):
    """
    从 Valine 的 URL 中提取可能的 slug 候选。
    1. 先取路径最后一段（原始 slug）
    2. 如果原始 slug 包含日期前缀（如 25-09-2019_），去掉前缀生成第二个候选
    返回候选 slug 列表，按优先级排序。
    """
    path = urlparse(url_str).path.strip("/")
    if not path:
        return []

    raw_slug = path.split("/")[-1]   # 最后一段
    # 统一解码并转小写（WP 风格）
    try:
        decoded = unquote(raw_slug)
        encoded = quote(decoded, safe='').lower()
    except:
        encoded = raw_slug.lower()

    candidates = [encoded]

    # 匹配形如 "DD-MM-YYYY_" 的前缀
    match = re.match(r"^\d{2}-\d{2}-\d{4}_(.+)", encoded)
    if match:
        candidates.append(match.group(1))  # 去掉日期前缀后的部分

    return candidates

# 4. 读取 JSONL 并分类
oid_to_new_id = {}
top_level, children = [], []
skipped_slugs = []

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_slugs.append((c.get("url", ""), candidates))
            continue

        # ---------- 时间解析（使用 createdAt） ----------
        created_str = c.get("createdAt", "")
        try:
            if created_str.endswith("Z"):
                created_str = created_str[:-1] + "+00:00"
            dt = datetime.fromisoformat(created_str)
            # 统一存为 UTC 的 GMT 时间（WordPress 内部用 GMT）
            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 判断 ----------
        user_id = 1 if c.get("mail", "").lower() == "xty@2broear.com" else 0

        comment = {
            "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"),
            "rid": c.get("rid", ""),
            "user_id": user_id
        }

        if not c.get("rid"):
            top_level.append(comment)
        else:
            children.append(comment)

# 5. 打印部分未匹配的样本，便于排查
if skipped_slugs:
    print(f"\n未匹配的评论数: {len(skipped_slugs)} 条")
    print("前 10 个未匹配示例:")
    for url, cands in skipped_slugs[:10]:
        print(f"  URL: {url}")
        print(f"  候选 slug: {cands}")
    print()

# 6. 插入数据库（加入 user_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, 0, %s)"
)

# 顶级评论
for t in top_level:
    data = (
        t["post_id"], t["author"], t["email"], t["url"],
        t["content"], t["date"], t["date_gmt"], t["approved"], t["agent"],
        t["user_id"]
    )
    cursor.execute(insert_sql, data)
    oid_to_new_id[t["oid"]] = cursor.lastrowid

# 子评论
for child in children:
    parent_id = oid_to_new_id.get(child["rid"], 0)
    data = (
        child["post_id"], child["author"], child["email"], child["url"],
        child["content"], child["date"], child["date_gmt"], child["approved"], child["agent"],
        child["user_id"]
    )
    cursor.execute(insert_sql, data)
    new_id = cursor.lastrowid
    oid_to_new_id[child["oid"]] = new_id
    if parent_id != 0:
        cursor.execute(
            f"UPDATE {TABLE_PREFIX}comments SET comment_parent = %s WHERE comment_ID = %s",
            (parent_id, new_id)
        )

# 7. 更新文章评论数
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("迁移完成！")