子比主题网站会员数据迁移

用子比主题做了两个几乎一样的资源站，一个海外的A（没备案），一个国内的B（备案了），A由于没备案

被某些城市在电信或者移动端给封了（不让访问），考虑到用户体验与网站管理，我就把A站点和B站点合并了，但涉及到A站点的用户、会员及订单数据，要一起迁移到A，所以处理了一下。

步骤1：将A站点的users、user_meta、zibpay_order表导出，导入到了B站点（重命名为yx_users、yx_usermeta、yx_zibpay_order），然后让AI辅助写了个存储过程，直接执行就可以了，代码如下：

code

DROP PROCEDURE IF EXISTS MigrateVipUsers;

CREATE PROCEDURE MigrateVipUsers()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE old_user_id BIGINT;
    DECLARE new_user_id BIGINT;
    DECLARE old_user_login VARCHAR(255);
    
    -- 声明游标
    DECLARE user_cursor CURSOR FOR 
        SELECT u.ID, u.user_login
        FROM yx_users AS u
        JOIN yx_usermeta AS um ON u.ID = um.user_id
        WHERE um.meta_key = 'vip_level' AND um.meta_value IN ('1', '2')
        AND u.user_login IS NOT NULL
        ORDER BY u.ID ASC;
    
    -- 声明游标处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建一个临时表来存储新旧ID的映射关系
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_vip_user_map (
        old_id BIGINT,
        new_id BIGINT
    );

    -- 开启事务
    START TRANSACTION;
    
    -- 打开游标
    OPEN user_cursor;
    
    -- 循环遍历符合条件的用户
    read_loop: LOOP
        FETCH user_cursor INTO old_user_id, old_user_login;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 1. 检查用户是否已存在于wp_users中，并解决排序规则冲突
        IF NOT EXISTS (SELECT 1 FROM wp_users WHERE user_login COLLATE utf8mb4_unicode_ci = old_user_login COLLATE utf8mb4_unicode_ci) THEN
            INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
            SELECT user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name
            FROM yx_users
            WHERE ID = old_user_id;
            
            -- 获取新插入用户的ID
            SET new_user_id = LAST_INSERT_ID();
            
            -- 2. 将新旧ID映射关系存入临时表
            INSERT INTO temp_vip_user_map (old_id, new_id) VALUES (old_user_id, new_user_id);
        ELSE
            -- 如果用户已存在，则获取其ID并存入映射表
            SELECT ID INTO new_user_id
            FROM wp_users
            WHERE user_login COLLATE utf8mb4_unicode_ci = old_user_login COLLATE utf8mb4_unicode_ci;
            
            INSERT INTO temp_vip_user_map (old_id, new_id) VALUES (old_user_id, new_user_id);
        END IF;
    END LOOP;
    
    -- 关闭游标
    CLOSE user_cursor;

    -- 3. 导入usermeta数据，通过临时表进行ID映射
    INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
    SELECT
        m.new_id,
        um.meta_key,
        um.meta_value
    FROM yx_usermeta AS um
    JOIN temp_vip_user_map AS m ON um.user_id = m.old_id;

    -- 4. 导入订单数据，通过临时表进行ID映射
    -- 移除 'id' 列，让它自动生成新值
    INSERT INTO wp_zibpay_order (
        user_id, ip_address, product_id, post_id, order_num, order_price, order_type, create_time, pay_num, pay_type, pay_price, pay_time, referrer_id, rebate_price, rebate_status, rebate_detail, status, other, post_author, income_price, income_status, income_detail, pay_detail
    )
    SELECT
        m.new_id, o.ip_address, o.product_id, o.post_id, o.order_num, o.order_price, o.order_type, o.create_time, o.pay_num, o.pay_type, o.pay_price, o.pay_time, o.referrer_id, o.rebate_price, o.rebate_status, o.rebate_detail, o.status, o.other, o.post_author, o.income_price, o.income_status, o.income_detail, o.pay_detail
    FROM yx_zibpay_order AS o
    JOIN temp_vip_user_map AS m ON o.user_id = m.old_id;
    
    -- 提交事务
    COMMIT;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_vip_user_map;

END

云轩技术分享

评论 (0)

发表评论