mysql 大数据分表

-- 假设有两个表：user_info 和 user_order
SELECT u.user_id, u.username, o.order_id, o.order_amount
FROM user_info u
JOIN user_order o ON u.user_id = o.user_id
WHERE u.user_id = 123;

-- 假设按用户ID范围分表
DELIMITER //
CREATE PROCEDURE InsertUser(IN user_id INT, IN username VARCHAR(255))
BEGIN
    DECLARE target_table VARCHAR(255);
    IF user_id BETWEEN 1 AND 1000 THEN
        SET target_table = 'user_info_1';
    ELSEIF user_id BETWEEN 1001 AND 2000 THEN
        SET target_table = 'user_info_2';
    ELSE
        SET target_table = 'user_info_3';
    END IF;
    SET @sql = CONCAT('INSERT INTO ', target_table, ' (user_id, username) VALUES (', user_id, ', ''', username, ''')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 假设需要将数据从 user_info_1 迁移到 user_info_2
START TRANSACTION;
INSERT INTO user_info_2 (user_id, username)
SELECT user_id, username FROM user_info_1 WHERE user_id BETWEEN 1001 AND 2000;
DELETE FROM user_info_1 WHERE user_id BETWEEN 1001 AND 2000;
COMMIT;