mysql分片建表语句

-- 创建第一个分片表
CREATE TABLE user_shard_0 (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建第二个分片表
CREATE TABLE user_shard_1 (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 根据 user_id 的哈希值决定插入哪个分片
DELIMITER $$
CREATE FUNCTION get_shard_id(user_id BIGINT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE shard_id INT;
    SET shard_id = user_id % 2;
    RETURN shard_id;
END$$
DELIMITER ;

-- 插入数据时使用函数选择分片
INSERT INTO user_shard_ (SELECT get_shard_id(user_id)) (user_id, username, email)
VALUES (1, 'user1', 'user1@example.com');