mysql 数据分表

-- 创建原始表
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 创建分表
CREATE TABLE user_0 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE user_1 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 插入数据时进行分片
DELIMITER $$
CREATE PROCEDURE insert_user(IN p_id INT, IN p_name VARCHAR(50), IN p_age INT)
BEGIN
    DECLARE table_name VARCHAR(50);
    SET table_name = CONCAT('user_', p_id % 2);
    SET @sql = CONCAT('INSERT INTO ', table_name, ' (id, name, age) VALUES (', p_id, ', ''', p_name, ''', ', p_age, ')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 调用存储过程插入数据
CALL insert_user(1, 'Alice', 30);
CALL insert_user(2, 'Bob', 25);