mysql分表3种方法

-- 原表结构
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    address VARCHAR(200),
    phone VARCHAR(20),
    last_login TIMESTAMP
);

-- 垂直分表后
CREATE TABLE user_basic_info (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

CREATE TABLE user_detailed_info (
    id INT PRIMARY KEY,
    address VARCHAR(200),
    phone VARCHAR(20),
    last_login TIMESTAMP
);

-- 原表结构
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

-- 水平分表后
CREATE TABLE user_0 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

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

-- 插入数据时根据ID的哈希值选择表
INSERT INTO user_{id % 2} (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- 创建分区表
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50),
    created_at TIMESTAMP
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 插入数据
INSERT INTO user (id, name, email, created_at) VALUES (1, 'Alice', 'alice@example.com', '2015-01-01');