1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
-- 创建分库脚本
-- 库: order_db_0, order_db_1, order_db_2, order_db_3
-- 每个库执行
CREATE DATABASE order_db_0 DEFAULT CHARSET utf8mb4;
CREATE DATABASE order_db_1 DEFAULT CHARSET utf8mb4;
CREATE DATABASE order_db_2 DEFAULT CHARSET utf8mb4;
CREATE DATABASE order_db_3 DEFAULT CHARSET utf8mb4;
-- 订单表结构
CREATE TABLE orders_0 (
order_id BIGINT PRIMARY KEY COMMENT '订单ID',
user_id BIGINT NOT NULL COMMENT '用户ID',
order_no VARCHAR(64) NOT NULL COMMENT '订单号',
total_amount DECIMAL(12,2) NOT NULL COMMENT '订单金额',
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
-- 创建其他分表 (orders_1, orders_2, orders_3 结构相同)
-- 订单明细表 (与订单同分片键)
CREATE TABLE order_items_0 (
item_id BIGINT PRIMARY KEY COMMENT '明细ID',
order_id BIGINT NOT NULL COMMENT '订单ID',
product_id BIGINT NOT NULL COMMENT '商品ID',
product_name VARCHAR(200) NOT NULL COMMENT '商品名称',
price DECIMAL(12,2) NOT NULL COMMENT '单价',
quantity INT NOT NULL COMMENT '数量',
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
|