chuanqi/tools/clean_zones_and_fix_uk.py

# -*- coding: utf-8 -*-
"""清理 zones 重复行 + 把 UNIQUE KEY 改成 (server_id, zone_id, open_date)。
- 旧唯一键 (server_id, zone_id, open_time) 在 B2 bug 期间产生了重复行
  (同一 zone_id 一天两个 open_time: 真实开服时间 vs DEFAULT NOW())
- 改键后: 每天每个 zone_id 一行
- 步骤: 删旧脏行 -> 改键 -> 验证
"""
import pymysql

CONN = dict(host="47.99.164.66", port=3306, user="root",
            password="hn02le.34lkdLKD", database="996cq",
            charset="utf8mb4", autocommit=True, connect_timeout=10)

DRY = False  # 改成 True 试运行


def main():
    conn = pymysql.connect(**CONN)
    try:
        with conn.cursor() as cur:
            print("== 1. 找重复行 (同一 server_id+zone_id+date 多行) ==")
            cur.execute("""
                SELECT server_id, zone_id, open_date, COUNT(*) cnt
                FROM zones
                GROUP BY server_id, zone_id, open_date
                HAVING cnt > 1
            """)
            dups = cur.fetchall()
            print(f"  {len(dups)} 个 (server,zone,date) 组合存在重复")
            for sid, zid, od, cnt in dups[:20]:
                cur.execute("""
                    SELECT id, open_time, status, api_state FROM zones
                    WHERE server_id=%s AND zone_id=%s AND open_date=%s
                    ORDER BY id
                """, (sid, zid, od))
                print(f"  server={sid} zone_id={zid} date={od}:")
                for r in cur.fetchall():
                    print(f"    id={r[0]:<3} open_time={r[1]} status={r[2]} api_state={r[3]}")

            if not dups:
                print("  (无重复, 跳过清理)")
            else:
                # 保留每组中 id 最大的(最后写入,字段最新), 删 id 小的脏行
                to_delete = []
                for sid, zid, od, cnt in dups:
                    cur.execute("""
                        SELECT id, open_time, api_state, fetched_at FROM zones
                        WHERE server_id=%s AND zone_id=%s AND open_date=%s
                        ORDER BY id DESC
                    """, (sid, zid, od))
                    rows = cur.fetchall()
                    keep = rows[0]  # 最新
                    for r in rows[1:]:
                        to_delete.append((r[0], sid, zid, od, r[1], keep[0]))

                print(f"\n== 2. 将删除 {len(to_delete)} 条脏行 (保留 id 最大的) ==")
                for did, sid, zid, od, ot, keep_id in to_delete:
                    print(f"  delete id={did} (server={sid} zone={zid} date={od} open_time={ot}, keeping id={keep_id})")
                if not DRY:
                    cur.executemany(
                        "DELETE FROM zones WHERE id = %s",
                        [(d[0],) for d in to_delete])

            print("\n== 3. 改 UNIQUE KEY -> (server_id, zone_id, open_date) ==")
            cur.execute("SHOW INDEX FROM zones")
            idx = cur.fetchall()
            uniq = [r for r in idx if r[2] == "uniq_zone"]
            for r in uniq:
                print(f"  当前: key={r[2]} col={r[4]}")
            if not DRY:
                cur.execute("ALTER TABLE zones DROP INDEX uniq_zone")
                cur.execute("""
                    ALTER TABLE zones
                    ADD UNIQUE KEY uk_server_zone_date (server_id, zone_id, open_date)
                """)
                print("  已替换为 uk_server_zone_date")

            if not DRY:
                print("\n== 4. 验证: 重复行 = 0, 当天记录 = 9 ==")
                cur.execute("""
                    SELECT COUNT(*) FROM (
                      SELECT server_id, zone_id, open_date, COUNT(*) cnt
                      FROM zones GROUP BY server_id, zone_id, open_date HAVING cnt>1
                    ) t
                """)
                print(f"  重复组合数: {cur.fetchone()[0]}")
                cur.execute("""
                    SELECT COUNT(*) FROM zones WHERE server_id=8 AND open_date='2026-09-17'
                """)
                print(f"  server=8, date=2026-09-17 行数: {cur.fetchone()[0]}")
                print("\n  today zones (干净后):")
                cur.execute("""
                    SELECT id, zone_id, zone_name, open_time, open_time_of_day, api_state, status
                    FROM zones WHERE server_id=8 AND open_date='2026-09-17'
                    ORDER BY open_time
                """)
                for r in cur.fetchall():
                    print(f"    id={r[0]:<3} {r[1]} {r[2]:<22} {r[3]} time={r[4]} state={r[5]} status={r[6]}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()