93 lines
4.3 KiB
SQL
93 lines
4.3 KiB
SQL
-- =========================================
|
|
-- 分布式事务示例业务表结构
|
|
-- 订单服务 + 库存服务 + 财务服务
|
|
-- =========================================
|
|
|
|
-- 订单服务数据库
|
|
CREATE DATABASE IF NOT EXISTS order_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
|
USE order_db;
|
|
|
|
CREATE TABLE IF NOT EXISTS `orders` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`order_no` VARCHAR(64) NOT NULL UNIQUE COMMENT '订单编号',
|
|
`customer_id` BIGINT NOT NULL COMMENT '客户ID',
|
|
`product_id` BIGINT NOT NULL COMMENT '商品ID',
|
|
`quantity` INT NOT NULL DEFAULT 1 COMMENT '购买数量',
|
|
`total_amount` DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
|
|
`status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '订单状态: PENDING/CREATED/CANCELLED',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
KEY `idx_customer_id` (`customer_id`),
|
|
KEY `idx_product_id` (`product_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
|
|
|
|
-- 库存服务数据库
|
|
CREATE DATABASE IF NOT EXISTS inventory_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
|
USE inventory_db;
|
|
|
|
CREATE TABLE IF NOT EXISTS `inventory` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`product_id` BIGINT NOT NULL UNIQUE COMMENT '商品ID',
|
|
`product_name` VARCHAR(128) NOT NULL COMMENT '商品名称',
|
|
`stock` INT NOT NULL DEFAULT 0 COMMENT '当前库存',
|
|
`reserved_stock` INT NOT NULL DEFAULT 0 COMMENT '预留库存',
|
|
`available_stock` INT GENERATED ALWAYS AS (stock - reserved_stock) STORED COMMENT '可用库存',
|
|
`warehouse` VARCHAR(64) DEFAULT 'MAIN' COMMENT '仓库',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
KEY `idx_product_name` (`product_name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存表';
|
|
|
|
CREATE TABLE IF NOT EXISTS `inventory_log` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`product_id` BIGINT NOT NULL,
|
|
`order_no` VARCHAR(64) NOT NULL,
|
|
`change_type` VARCHAR(32) NOT NULL COMMENT 'RESERVE/RELEASE/CONSUME',
|
|
`quantity` INT NOT NULL,
|
|
`before_stock` INT NOT NULL,
|
|
`after_stock` INT NOT NULL,
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY `idx_order_no` (`order_no`),
|
|
KEY `idx_product_id` (`product_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='库存变动日志';
|
|
|
|
-- 财务服务数据库
|
|
CREATE DATABASE IF NOT EXISTS finance_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
|
USE finance_db;
|
|
|
|
CREATE TABLE IF NOT EXISTS `account` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`customer_id` BIGINT NOT NULL UNIQUE COMMENT '客户ID',
|
|
`balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
|
|
`frozen_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '冻结金额',
|
|
`available_amount` DECIMAL(12,2) GENERATED ALWAYS AS (balance - frozen_amount) STORED COMMENT '可用金额',
|
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'CNY',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
KEY `idx_customer_id` (`customer_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账户表';
|
|
|
|
CREATE TABLE IF NOT EXISTS `transaction_log` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
`tx_no` VARCHAR(64) NOT NULL UNIQUE COMMENT '交易流水号',
|
|
`order_no` VARCHAR(64) NOT NULL COMMENT '关联订单号',
|
|
`customer_id` BIGINT NOT NULL COMMENT '客户ID',
|
|
`amount` DECIMAL(12,2) NOT NULL COMMENT '交易金额',
|
|
`type` VARCHAR(32) NOT NULL COMMENT 'FREEZE/UNFREEZE/CONSUME/REFUND',
|
|
`status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '交易状态',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`complete_time` DATETIME,
|
|
KEY `idx_order_no` (`order_no`),
|
|
KEY `idx_customer_id` (`customer_id`),
|
|
KEY `idx_type` (`type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='财务流水表';
|
|
|
|
-- 初始化测试数据
|
|
INSERT INTO `inventory_db`.`inventory` (`product_id`, `product_name`, `stock`, `reserved_stock`, `warehouse`) VALUES
|
|
(1, 'iPhone 15 Pro', 100, 0, 'MAIN'),
|
|
(2, 'MacBook Pro', 50, 0, 'MAIN'),
|
|
(3, 'AirPods Pro', 200, 0, 'MAIN');
|
|
|
|
INSERT INTO `finance_db`.`account` (`customer_id`, `balance`, `frozen_amount`) VALUES
|
|
(1001, 50000.00, 0.00),
|
|
(1002, 30000.00, 0.00);
|