# 1. 水平分表
# 创建多个表
CREATE TABLE fgedu_users_0 (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

CREATE TABLE fgedu_users_1 (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

CREATE TABLE fgedu_users_2 (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

# 插入数据（根据user_id取模分片）
INSERT INTO fgedu_users_0 SELECT * FROM fgedu_users WHERE user_id % 3 = 0;
INSERT INTO fgedu_users_1 SELECT * FROM fgedu_users WHERE user_id % 3 = 1;
INSERT INTO fgedu_users_2 SELECT * FROM fgedu_users WHERE user_id % 3 = 2;

# 2. 水平分库
# 创建多个数据库
CREATE DATABASE fgedu_db0;
CREATE DATABASE fgedu_db1;
CREATE DATABASE fgedu_db2;

# 在每个数据库中创建表
CREATE TABLE fgedu_db0.fgedu_users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

CREATE TABLE fgedu_db1.fgedu_users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

CREATE TABLE fgedu_db2.fgedu_users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

# 3. 垂直分表
# 创建主表
CREATE TABLE fgedu_users_main (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

# 创建扩展表
CREATE TABLE fgedu_users_ext (
user_id INT PRIMARY KEY,
bio TEXT,
avatar BLOB,
FOREIGN KEY (user_id) REFERENCES fgedu_users_main(user_id)
);

# 4. 垂直分库
# 创建用户数据库
CREATE DATABASE fgedu_user_db;

# 创建订单数据库
CREATE DATABASE fgedu_order_db;

# 在用户数据库中创建用户表
CREATE TABLE fgedu_user_db.fgedu_users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

# 在订单数据库中创建订单表
CREATE TABLE fgedu_order_db.fgedu_orders (
order_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2)
);

# 1. RANGE分区
CREATE TABLE fgedu_sales_range (
sale_id INT PRIMARY KEY,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p2027 VALUES LESS THAN (2028),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 2. LIST分区
CREATE TABLE fgedu_sales_list (
sale_id INT PRIMARY KEY,
region_id INT,
amount DECIMAL(10,2)
) PARTITION BY LIST (region_id) (
PARTITION pnorth VALUES IN (1, 2, 3),
PARTITION peast VALUES IN (4, 5, 6),
PARTITION psouth VALUES IN (7, 8, 9),
PARTITION pwest VALUES IN (10, 11, 12)
);

# 3. HASH分区
CREATE TABLE fgedu_sales_hash (
sale_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2)
) PARTITION BY HASH (user_id) PARTITIONS 4;

# 4. KEY分区
CREATE TABLE fgedu_sales_key (
sale_id INT PRIMARY KEY,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY KEY (sale_date) PARTITIONS 4;

# 5. 管理分区
# 查看分区信息
SHOW CREATE TABLE fgedu_sales_range;

# 维护分区
ALTER TABLE fgedu_sales_range REORGANIZE PARTITION pfuture INTO (
PARTITION p2028 VALUES LESS THAN (2029),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 重建分区
ALTER TABLE fgedu_sales_range REBUILD PARTITION p2024, p2025;

# 优化分区
ALTER TABLE fgedu_sales_range OPTIMIZE PARTITION p2024, p2025;

# 分析分区
ALTER TABLE fgedu_sales_range ANALYZE PARTITION p2024, p2025;

# 检查分区
ALTER TABLE fgedu_sales_range CHECK PARTITION p2024, p2025;

# 修复分区
ALTER TABLE fgedu_sales_range REPAIR PARTITION p2024, p2025;

# 截断分区
ALTER TABLE fgedu_sales_range TRUNCATE PARTITION p2024;

# 删除分区
ALTER TABLE fgedu_sales_range DROP PARTITION p2024;

# 添加分区
ALTER TABLE fgedu_sales_range ADD PARTITION (
PARTITION p2029 VALUES LESS THAN (2030)
);