sql: Data Modeling Patterns using mysql
/* ============================================================================
珠宝行业数据建模完整脚本 (MySQL 9.0 / InnoDB / utf8mb4)
============================================================================
数据库: JewelryERP (OLTP业务库) + JewelryDW (数据仓库)
业务场景: 宝石原料 -> 款式BOM -> 加工工单 -> 成品库存 -> 经销商 -> 销售
覆盖模块:
01. 范式建模 1NF ~ 5NF
02. 反范式 Denormalization + 触发器同步
03. SCD 缓慢变化维度 (Type1/2/3)
04. 层次模型 (邻接表 / 嵌套集 / 闭包表)
05. 多租户 Multi-Tenancy
06. 数据校验约束 + 审计日志 + 行版本控制(乐观锁)
07. 业务主表 (销售订单 / 加工工单 / 客户)
08. 批量测试数据
09. 存储过程 (SCD2拉链 / 分类树移动 / 业务补偿)
10. 索引优化 + 分区表
11. 慢查询配置 + 优化器 Hint
12. 事务隔离级别 + 锁分析 (库存并发扣减)
13. 数据仓库 (星型模型 / 雪花模型)
14. ETL 存储过程 (OLTP -> DW 增量抽取, SCD2自动拉链)
15. ETL 数据质量校验 (脏数据捕获)
16. 读写分离 + 主从延迟监控
17. 数据库安全 (账号权限 / 行级安全视图 / 数据脱敏 / 审计加固)
18. 运维脚本 (备份 / binlog / 碎片优化 / 统计信息)
19. BI 报表 SQL (销售 / 库存周转 / 工单良率 / 加盟商分析)
20. 层次模型性能对比 (EXPLAIN)
执行方式: mysql -u root -p < JewelryERP_Full_MySQL9.0.sql
或在 Navicat / MySQL Workbench 中全选执行
============================================================================ */
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
/* ============================================================================
第0部分: 数据库初始化
============================================================================ */
DROP DATABASE IF EXISTS JewelryERP;
CREATE DATABASE JewelryERP DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE JewelryERP;
/* ============================================================================
第1部分: 范式建模 Normalization (1NF ~ 5NF)
============================================================================ */
-- ---------- 1NF: 列原子性，单元格不可拆分，无重复多值 ----------
-- 反例(不执行): GemMaterials 字段存 '钻石,红宝石' 多值，违反1NF
-- 正例: 每个宝石一行
CREATE TABLE Style_Gem_1NF (
Id INT AUTO_INCREMENT PRIMARY KEY,
StyleId INT NOT NULL,
StyleName VARCHAR(100) NOT NULL,
GemName VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='1NF示例: 款式-宝石明细，每行一颗宝石';
INSERT INTO Style_Gem_1NF (StyleId, StyleName, GemName) VALUES
(1, '铂金钻戒', '钻石'),
(1, '铂金钻戒', '红宝石'),
(2, '黄金吊坠', '蓝宝石');
-- ---------- 2NF: 消除部分函数依赖，复合主键时非键字段完全依赖整个主键 ----------
-- 先建主表，再建关联表（外键依赖顺序）
CREATE TABLE Style_2NF (
StyleId INT PRIMARY KEY,
StyleName VARCHAR(100) NOT NULL,
StyleCode VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='2NF主表: 珠宝款式';
CREATE TABLE Gem_2NF (
GemId INT PRIMARY KEY,
GemName VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='2NF主表: 宝石名称字典';
CREATE TABLE Style_Gem_Map_2NF (
StyleId INT NOT NULL,
GemId INT NOT NULL,
Carat DECIMAL(10,3) NOT NULL COMMENT '单颗宝石克拉数',
PRIMARY KEY (StyleId, GemId),
FOREIGN KEY (StyleId) REFERENCES Style_2NF(StyleId),
FOREIGN KEY (GemId) REFERENCES Gem_2NF(GemId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='2NF关联表: 款式BOM宝石用料';
-- ---------- 3NF: 消除传递依赖 + Lookup Table 查找表 ----------
-- GemTypeName 依赖 GemTypeCode（非主键），需拆出查找表
CREATE TABLE GemType_Lookup_3NF (
GemTypeCode VARCHAR(20) PRIMARY KEY,
GemTypeName VARCHAR(50) NOT NULL,
Remark VARCHAR(200) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='3NF Lookup表: 宝石类型字典';
CREATE TABLE GemRaw_3NF (
GemId INT PRIMARY KEY,
GemCode VARCHAR(50) NOT NULL UNIQUE,
GemTypeCode VARCHAR(20) NOT NULL,
WeightCarat DECIMAL(10,3) NOT NULL COMMENT '宝石原石克拉',
FOREIGN KEY (GemTypeCode) REFERENCES GemType_Lookup_3NF(GemTypeCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='3NF主表: 宝石原料';
-- ---------- BCNF: 每个决定因子必须是候选键 ----------
-- 业务规则: 一个工序固定由一位工艺师负责 -> ProcessCode 决定 CraftsmanId
CREATE TABLE Process_BCNF (
ProcessCode VARCHAR(20) PRIMARY KEY,
ProcessName VARCHAR(100) NOT NULL COMMENT '加工工序: 起版/镶石/抛光',
CraftsmanId INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BCNF: 加工工序，ProcessCode为唯一决定因子';
CREATE TABLE Process_Workshop_BCNF (
ProcessCode VARCHAR(20),
WorkshopId INT,
PRIMARY KEY (ProcessCode, WorkshopId),
FOREIGN KEY (ProcessCode) REFERENCES Process_BCNF(ProcessCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BCNF: 工序-车间多对多';
-- ---------- 4NF: 消除独立多值依赖 + Junction Table 关联表 ----------
-- 款式可以有多个宝石、多个工艺；宝石和工艺互相独立，拆成两张关联表
CREATE TABLE Style_Gem_Junction_4NF (
StyleId INT,
GemId INT,
PRIMARY KEY (StyleId, GemId),
FOREIGN KEY (StyleId) REFERENCES Style_2NF(StyleId),
FOREIGN KEY (GemId) REFERENCES Gem_2NF(GemId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='4NF Junction表: 款式-宝石多对多';
CREATE TABLE Style_Process_Junction_4NF (
StyleId INT,
ProcessCode VARCHAR(20),
PRIMARY KEY (StyleId, ProcessCode),
FOREIGN KEY (StyleId) REFERENCES Style_2NF(StyleId),
FOREIGN KEY (ProcessCode) REFERENCES Process_BCNF(ProcessCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='4NF Junction表: 款式-加工工序多对多';
-- ---------- 5NF: 投影-连接范式，三方关系不可无损拆分 ----------
-- 经销商-款式-材质 三方授权: 只能整体存储，不能拆成两两关系
CREATE TABLE Material_Lookup (
MaterialId INT PRIMARY KEY,
MaterialName VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='5NF Lookup表: 材质字典';
CREATE TABLE Dealer_5NF (
DealerId INT PRIMARY KEY,
DealerName VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='5NF主表: 经销商';
CREATE TABLE Dealer_Style_Material_5NF (
DealerId INT NOT NULL,
StyleId INT NOT NULL,
MaterialId INT NOT NULL,
IsAuthorized BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (DealerId, StyleId, MaterialId),
FOREIGN KEY (DealerId) REFERENCES Dealer_5NF(DealerId),
FOREIGN KEY (StyleId) REFERENCES Style_2NF(StyleId),
FOREIGN KEY (MaterialId) REFERENCES Material_Lookup(MaterialId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='5NF三方关系: 经销商-款式-材质销售授权';
/* ============================================================================
第2部分: 反范式 Denormalization + 触发器同步
场景: 库存汇总表冗余款式名/宝石名，减少JOIN，牺牲更新一致性换查询速度
============================================================================ */
CREATE TABLE Inventory_Denormalized (
InventoryId INT AUTO_INCREMENT PRIMARY KEY,
StyleId INT NOT NULL,
StyleName VARCHAR(100) NOT NULL COMMENT '冗余字段(反范式): 款式名称',
GemId INT NOT NULL,
GemName VARCHAR(50) NOT NULL COMMENT '冗余字段(反范式): 宝石名称',
StockQty INT NOT NULL,
UnitCost DECIMAL(18,2) NOT NULL,
UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='反范式表: 成品库存汇总，冗余款式名/宝石名';
-- 触发器: 主表款式名变更时同步冗余字段
DELIMITER //
CREATE TRIGGER trg_Style_Update_Denorm
AFTER UPDATE ON Style_2NF
FOR EACH ROW
BEGIN
IF OLD.StyleName <> NEW.StyleName THEN
UPDATE Inventory_Denormalized
SET StyleName = NEW.StyleName, UpdateTime = NOW()
WHERE StyleId = NEW.StyleId;
END IF;
END //
DELIMITER ;
/* ============================================================================
第3部分: SCD 缓慢变化维度 (珠宝经销商维度)
============================================================================ */
-- ---------- SCD Type1: 直接覆盖，丢失历史 ----------
CREATE TABLE Dim_Dealer_SCD1 (
DealerKey INT AUTO_INCREMENT PRIMARY KEY,
DealerCode VARCHAR(30) NOT NULL UNIQUE,
DealerName VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SCD Type1: 经销商维度，直接覆盖';
-- ---------- SCD Type2: 拉链表，新增行+生效失效日期，保留全量历史(DW最常用) ----------
CREATE TABLE Dim_Dealer_SCD2 (
DealerSurrogateKey INT AUTO_INCREMENT PRIMARY KEY COMMENT '代理键',
DealerCode VARCHAR(30) NOT NULL,
DealerName VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME NULL COMMENT 'NULL=当前有效',
IsCurrent BOOLEAN NOT NULL DEFAULT TRUE,
INDEX idx_dealer_code (DealerCode),
INDEX idx_dealer_current (DealerCode, IsCurrent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SCD Type2: 经销商维度拉链表，保留历史版本';
-- ---------- SCD Type3: 仅保存上一版本地址，新增历史字段 ----------
CREATE TABLE Dim_Dealer_SCD3 (
DealerKey INT AUTO_INCREMENT PRIMARY KEY,
DealerCode VARCHAR(30) NOT NULL UNIQUE,
DealerName VARCHAR(100) NOT NULL,
CurrentAddress VARCHAR(200) NOT NULL,
PreviousAddress VARCHAR(200) NULL,
AddressChangeDate DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SCD Type3: 经销商维度，仅保留上一次地址';
/* ============================================================================
第4部分: 层次模型 Hierarchical Models (珠宝产品分类树)
树结构: 首饰 > 戒指/吊坠/耳饰/手链/项链/手镯 > 钻戒/素圈/彩宝...
============================================================================ */
-- ---------- 4.1 Adjacency List 邻接表 (父ID模式，最常用) ----------
CREATE TABLE ProductCategory_AdjacencyList (
CategoryId INT AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(100) NOT NULL,
ParentId INT NULL,
SortOrder INT NOT NULL DEFAULT 0,
INDEX idx_parent (ParentId),
FOREIGN KEY (ParentId) REFERENCES ProductCategory_AdjacencyList(CategoryId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='层次模型-邻接表: 产品分类父子关系';
-- ---------- 4.2 Nested Set 嵌套集 (左右值模型，适合批量子树查询) ----------
CREATE TABLE ProductCategory_NestedSet (
CategoryId INT AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(100) NOT NULL,
LeftValue INT NOT NULL,
RightValue INT NOT NULL,
INDEX idx_lr (LeftValue, RightValue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='层次模型-嵌套集: 左右值模型，子树查询极快';
-- ---------- 4.3 Closure Table 闭包表 (独立存储祖先-后代关系，复杂树形推荐) ----------
CREATE TABLE ProductCategory_Closure_Main (
CategoryId INT AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='层次模型-闭包表主表: 分类节点';
CREATE TABLE ProductCategory_Closure_Relation (
AncestorId INT NOT NULL,
DescendantId INT NOT NULL,
Depth INT NOT NULL,
PRIMARY KEY (AncestorId, DescendantId),
INDEX idx_desc (DescendantId),
FOREIGN KEY (AncestorId) REFERENCES ProductCategory_Closure_Main(CategoryId),
FOREIGN KEY (DescendantId) REFERENCES ProductCategory_Closure_Main(CategoryId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='层次模型-闭包表关系: 全部祖先-后代对';
/* ============================================================================
第5部分: 多租户 Multi-Tenancy (珠宝集团，多个子品牌/加盟商)
方案: 共享数据库 + 共享Schema + TenantId + 行级安全视图
============================================================================ */
CREATE TABLE Tenant (
TenantId INT AUTO_INCREMENT PRIMARY KEY,
TenantCode VARCHAR(50) NOT NULL UNIQUE,
TenantName VARCHAR(100) NOT NULL,
IsActive BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多租户: 加盟商/品牌租户';
CREATE TABLE Style_MultiTenant (
StyleId INT AUTO_INCREMENT PRIMARY KEY,
TenantId INT NOT NULL,
StyleCode VARCHAR(50) NOT NULL,
StyleName VARCHAR(100) NOT NULL,
CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (TenantId) REFERENCES Tenant(TenantId),
UNIQUE KEY uk_tenant_stylecode (TenantId, StyleCode),
INDEX idx_tenant (TenantId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多租户: 租户私有款式';
/* ============================================================================
第6部分: 数据校验约束 + 审计日志 + 行版本控制(乐观锁)
============================================================================ */
-- ---------- 6.1 CHECK约束 (MySQL 8.0+ 原生支持) ----------
ALTER TABLE GemRaw_3NF
ADD CONSTRAINT chk_gem_carat CHECK (WeightCarat > 0),
ADD CONSTRAINT chk_gem_code CHECK (GemCode <> '');
ALTER TABLE Inventory_Denormalized
ADD CONSTRAINT chk_stock_qty CHECK (StockQty >= 0),
ADD CONSTRAINT chk_cost CHECK (UnitCost >= 0);
-- 行版本号(乐观锁): 防止并发覆盖
ALTER TABLE Inventory_Denormalized
ADD COLUMN RowVersion INT NOT NULL DEFAULT 1 COMMENT '行版本号，乐观锁';
-- ---------- 6.2 审计日志表 (全业务变更审计: 增删改，记录旧值新值JSON) ----------
CREATE TABLE AuditLog (
AuditId BIGINT AUTO_INCREMENT PRIMARY KEY,
TableName VARCHAR(64) NOT NULL COMMENT '被操作表名',
OperateType ENUM('INSERT','UPDATE','DELETE') NOT NULL,
RecordId VARCHAR(64) NOT NULL COMMENT '业务主键ID',
OldValue JSON NULL COMMENT '修改前JSON',
NewValue JSON NULL COMMENT '修改后JSON',
OperatorAccount VARCHAR(64) NOT NULL COMMENT '操作账号',
OperateIP VARCHAR(45) NULL,
OperateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_audit_table (TableName, OperateTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审计日志: 全业务表增删改记录';
-- 审计触发器: 款式表 Style_2NF
DELIMITER //
CREATE TRIGGER trg_Style_Insert_Audit
AFTER INSERT ON Style_2NF FOR EACH ROW
BEGIN
INSERT INTO AuditLog(TableName, OperateType, RecordId, NewValue, OperatorAccount)
VALUES('Style_2NF','INSERT',CAST(NEW.StyleId AS CHAR),
JSON_OBJECT('StyleId',NEW.StyleId,'StyleName',NEW.StyleName,'StyleCode',NEW.StyleCode),
CURRENT_USER());
END //
CREATE TRIGGER trg_Style_Update_Audit
AFTER UPDATE ON Style_2NF FOR EACH ROW
BEGIN
INSERT INTO AuditLog(TableName, OperateType, RecordId, OldValue, NewValue, OperatorAccount)
VALUES('Style_2NF','UPDATE',CAST(NEW.StyleId AS CHAR),
JSON_OBJECT('StyleId',OLD.StyleId,'StyleName',OLD.StyleName,'StyleCode',OLD.StyleCode),
JSON_OBJECT('StyleId',NEW.StyleId,'StyleName',NEW.StyleName,'StyleCode',NEW.StyleCode),
CURRENT_USER());
END //
CREATE TRIGGER trg_Style_Delete_Audit
AFTER DELETE ON Style_2NF FOR EACH ROW
BEGIN
INSERT INTO AuditLog(TableName, OperateType, RecordId, OldValue, OperatorAccount)
VALUES('Style_2NF','DELETE',CAST(OLD.StyleId AS CHAR),
JSON_OBJECT('StyleId',OLD.StyleId,'StyleName',OLD.StyleName,'StyleCode',OLD.StyleCode),
CURRENT_USER());
END //
DELIMITER ;
-- 审计归档表
CREATE TABLE AuditLog_Archive LIKE AuditLog;
-- ---------- 6.3 业务补偿日志表 (订单取消/工单返工/库存回滚) ----------
CREATE TABLE BusinessCompensationLog (
CompId BIGINT AUTO_INCREMENT PRIMARY KEY,
BizType ENUM('OrderCancel','WorkOrderRework','InventoryRollback') NOT NULL,
BizKey VARCHAR(64) NOT NULL COMMENT '业务主键: 订单号/工单号',
BeforeState JSON NULL,
AfterState JSON NULL,
CompQty INT NOT NULL DEFAULT 0 COMMENT '补偿数量(库存增减)',
CompRemark VARCHAR(500) NULL,
Operator VARCHAR(64) NOT NULL,
CompTime DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_bizkey (BizKey),
INDEX idx_biztype (BizType, CompTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务补偿日志: 异常处理流水';
/* ============================================================================
第7部分: 业务主表 (销售订单 / 加工工单 / 客户)
============================================================================ */
CREATE TABLE SalesOrder (
OrderId INT AUTO_INCREMENT PRIMARY KEY,
TenantId INT NOT NULL,
OrderNo VARCHAR(50) NOT NULL UNIQUE COMMENT '销售订单编号',
DealerId INT NOT NULL,
OrderAmount DECIMAL(18,2) NOT NULL,
OrderStatus ENUM('Draft','Confirmed','Producing','Shipped','Completed','Cancelled') NOT NULL DEFAULT 'Draft',
CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (TenantId) REFERENCES Tenant(TenantId),
CONSTRAINT chk_order_amount CHECK (OrderAmount >= 0),
INDEX idx_tenant_status (TenantId, OrderStatus),
INDEX idx_createtime (CreateTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='销售订单';
CREATE TABLE WorkOrder (
WorkOrderId INT AUTO_INCREMENT PRIMARY KEY,
StyleId INT NOT NULL,
OrderId INT NULL,
WorkNo VARCHAR(50) UNIQUE NOT NULL COMMENT '加工工单号',
WorkStatus ENUM('Pending','Working','QC','Finished','Reject') NOT NULL DEFAULT 'Pending',
PlanStartDate DATE,
PlanEndDate DATE,
ActualEndDate DATE NULL,
CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (StyleId) REFERENCES Style_2NF(StyleId),
FOREIGN KEY (OrderId) REFERENCES SalesOrder(OrderId),
INDEX idx_style (StyleId),
INDEX idx_order (OrderId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加工工单';
CREATE TABLE Customer (
CustId INT AUTO_INCREMENT PRIMARY KEY,
TenantId INT NOT NULL,
CustName VARCHAR(50),
Mobile VARCHAR(20) NOT NULL COMMENT '客户手机号(敏感字段)',
GemCertNo VARCHAR(64) NULL COMMENT '宝石GIA证书编号(敏感字段)',
INDEX idx_tenant (TenantId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表(含敏感字段，需脱敏)';
-- 数据脱敏视图: 普通账号只能看到脱敏后的手机号/证书号
CREATE VIEW vw_Customer_Masked AS
SELECT
CustId, TenantId, CustName,
CONCAT(LEFT(Mobile,3),'****',RIGHT(Mobile,4)) AS Mobile,
CONCAT(LEFT(GemCertNo,6),'******',RIGHT(GemCertNo,3)) AS GemCertNo
FROM Customer;
/* ============================================================================
第8部分: 批量测试数据
============================================================================ */
-- 8.1 基础字典数据
INSERT INTO GemType_Lookup_3NF (GemTypeCode, GemTypeName, Remark) VALUES
('D','钻石','天然钻石GIA证书'),
('R','红宝石','缅甸红宝石'),
('S','蓝宝石','斯里兰卡蓝宝石'),
('E','祖母绿','哥伦比亚祖母绿'),
('P','珍珠','南洋白珍珠');
INSERT INTO GemRaw_3NF (GemId, GemCode, GemTypeCode, WeightCarat) VALUES
(1,'D202609001','D',0.52),
(2,'D202609002','D',1.01),
(3,'R202609001','R',2.10),
(4,'S202609001','S',1.55),
(5,'E202609001','E',0.88),
(6,'P202609001','P',8.20);
INSERT INTO Gem_2NF (GemId, GemName) VALUES
(1,'钻石'),(2,'红宝石'),(3,'蓝宝石'),(4,'祖母绿'),(5,'珍珠');
INSERT INTO Style_2NF (StyleId, StyleName, StyleCode) VALUES
(1,'18K白金经典钻戒','ST001'),
(2,'足金素圈戒指','ST002'),
(3,'红宝石吊坠','ST003'),
(4,'蓝宝石耳钉','ST004'),
(5,'珍珠手链','ST005');
INSERT INTO Style_Gem_Map_2NF (StyleId, GemId, Carat) VALUES
(1,1,0.52),(1,2,1.01),(3,3,2.10),(4,4,1.55),(5,5,8.20);
INSERT INTO Process_BCNF (ProcessCode, ProcessName, CraftsmanId) VALUES
('P01','起版',101),('P02','镶石',102),('P03','抛光',103),('P04','铸造',104);
INSERT INTO Material_Lookup (MaterialId, MaterialName) VALUES
(1,'18K白金'),(2,'18K黄金'),(3,'铂金PT950'),(4,'足金999');
INSERT INTO Dealer_5NF (DealerId, DealerName) VALUES
(1,'深圳水贝经销商A'),(2,'广州荔湾经销商B'),(3,'上海豫园经销商C');
INSERT INTO Dealer_Style_Material_5NF (DealerId, StyleId, MaterialId, IsAuthorized) VALUES
(1,1,1,TRUE),(1,1,3,TRUE),(1,2,4,TRUE),(2,1,1,TRUE),(3,3,2,TRUE);
-- 8.2 库存数据
INSERT INTO Inventory_Denormalized (StyleId, StyleName, GemId, GemName, StockQty, UnitCost) VALUES
(1,'18K白金经典钻戒',1,'钻石',50,8800.00),
(1,'18K白金经典钻戒',2,'红宝石',30,12000.00),
(2,'足金素圈戒指',3,'蓝宝石',80,3500.00),
(3,'红宝石吊坠',3,'蓝宝石',40,6600.00),
(4,'蓝宝石耳钉',4,'祖母绿',60,4200.00),
(5,'珍珠手链',5,'珍珠',100,2800.00);
-- 8.3 SCD经销商数据
INSERT INTO Dim_Dealer_SCD1 (DealerCode, DealerName, Address) VALUES
('D001','深圳珠宝经销商A','罗湖区水贝');
INSERT INTO Dim_Dealer_SCD2 (DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent) VALUES
('D001','深圳珠宝经销商A','罗湖区水贝','2024-01-01','2025-06-01',FALSE),
('D001','深圳珠宝经销商A','龙岗区布吉','2025-06-01','2026-01-01',FALSE),
('D001','深圳珠宝经销商A（旗舰店）','福田区CBD','2026-01-01',NULL,TRUE);
INSERT INTO Dim_Dealer_SCD3 (DealerCode, DealerName, CurrentAddress, PreviousAddress, AddressChangeDate) VALUES
('D001','深圳珠宝经销商A','龙岗区布吉','罗湖区水贝','2025-06-01');
-- 8.4 层次模型数据 (同一棵14节点分类树)
-- 邻接表
INSERT INTO ProductCategory_AdjacencyList (CategoryId, CategoryName, ParentId, SortOrder) VALUES
(1,'首饰',NULL,1),(2,'戒指',1,1),(3,'吊坠',1,2),(4,'手链',1,3),
(5,'耳饰',1,4),(6,'项链',1,5),(7,'手镯',1,6),
(8,'钻戒',2,1),(9,'素圈戒指',2,2),(10,'彩宝戒指',2,3),
(11,'钻石吊坠',3,1),(12,'珍珠吊坠',3,2),
(13,'18K金手链',4,1),(14,'钻石耳钉',5,1);
-- 嵌套集 (深度优先遍历计算左右值)
INSERT INTO ProductCategory_NestedSet (CategoryId, CategoryName, LeftValue, RightValue) VALUES
(1,'首饰',1,28),(2,'戒指',2,13),(3,'吊坠',14,19),(4,'手链',20,23),
(5,'耳饰',24,27),(6,'项链',28,29),(7,'手镯',30,31),
(8,'钻戒',3,8),(9,'素圈戒指',9,10),(10,'彩宝戒指',11,12),
(11,'钻石吊坠',15,16),(12,'珍珠吊坠',17,18),
(13,'18K金手链',21,22),(14,'钻石耳钉',25,26);
-- 闭包表主表
INSERT INTO ProductCategory_Closure_Main (CategoryId, CategoryName)
SELECT CategoryId, CategoryName FROM ProductCategory_AdjacencyList;
-- 闭包表关系 (用递归CTE生成全部祖先-后代对)
INSERT INTO ProductCategory_Closure_Relation (AncestorId, DescendantId, Depth)
WITH RECURSIVE CategoryTree AS (
SELECT CategoryId, CategoryId AS AncestorId, 0 AS Depth
FROM ProductCategory_AdjacencyList
UNION ALL
SELECT ct.CategoryId, al.ParentId, ct.Depth + 1
FROM CategoryTree ct
INNER JOIN ProductCategory_AdjacencyList al ON ct.AncestorId = al.CategoryId
WHERE al.ParentId IS NOT NULL
)
SELECT AncestorId, CategoryId, Depth FROM CategoryTree;
-- 8.5 多租户数据
INSERT INTO Tenant (TenantCode, TenantName, IsActive) VALUES
('T01','水贝直营品牌',TRUE),('T02','加盟商A',TRUE),('T03','加盟商B',TRUE);
INSERT INTO Style_MultiTenant (TenantId, StyleCode, StyleName) VALUES
(1,'S001','18K金钻戒'),(1,'S002','足金素圈'),
(2,'S001','加盟商A专属款'),(2,'S002','银饰吊坠'),
(3,'S001','加盟商B铂金款');
-- 8.6 销售订单 + 加工工单
INSERT INTO SalesOrder (TenantId, OrderNo, DealerId, OrderAmount, OrderStatus) VALUES
(1,'ORD2026090001',1,16800.00,'Confirmed'),
(1,'ORD2026090002',1,8900.00,'Producing'),
(1,'ORD2026090003',2,25600.00,'Shipped'),
(2,'ORD2026090004',1,5200.00,'Completed'),
(3,'ORD2026090005',3,12000.00,'Draft');
INSERT INTO WorkOrder (StyleId, OrderId, WorkNo, WorkStatus, PlanStartDate, PlanEndDate) VALUES
(1,1,'WO202609001','Working','2026-09-01','2026-09-15'),
(2,2,'WO202609002','Pending','2026-09-05','2026-09-20'),
(3,3,'WO202609003','QC','2026-09-03','2026-09-10'),
(4,4,'WO202609004','Finished','2026-08-20','2026-09-01'),
(1,5,'WO202609005','Pending','2026-09-10','2026-09-25');
-- 8.7 客户数据
INSERT INTO Customer (TenantId, CustName, Mobile, GemCertNo) VALUES
(1,'张三','13812345678','GIA20260001'),
(1,'李四','13987654321','GIA20260002'),
(2,'王五','13711112222','GIA20260003');
-- 8.8 4NF关联表数据
INSERT INTO Style_Gem_Junction_4NF (StyleId, GemId) VALUES
(1,1),(1,2),(2,3),(3,3),(4,4),(5,5);
INSERT INTO Style_Process_Junction_4NF (StyleId, ProcessCode) VALUES
(1,'P01'),(1,'P02'),(1,'P03'),(2,'P04'),(2,'P03'),(3,'P01'),(3,'P02');
SET FOREIGN_KEY_CHECKS = 1;
/* ============================================================================
第9部分: 存储过程
============================================================================ */
-- ---------- 9.1 批量生成邻接表子分类测试数据 ----------
DELIMITER //
CREATE PROCEDURE sp_GenerateAdjCategoryData(IN p_parentId INT, IN p_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_name VARCHAR(128);
WHILE i <= p_count DO
SET v_name = CONCAT('子分类_',p_parentId,'_',i);
INSERT INTO ProductCategory_AdjacencyList(CategoryName, ParentId, SortOrder)
VALUES (v_name, p_parentId, i);
SET i = i + 1;
END WHILE;
END //
-- ---------- 9.2 SCD2 自动拉链存储过程 (经销商维度) ----------
CREATE PROCEDURE sp_SCD2_UpdateDealer(
IN p_DealerCode VARCHAR(30),
IN p_DealerName VARCHAR(100),
IN p_NewAddress VARCHAR(200)
)
BEGIN
DECLARE v_now DATETIME;
SET v_now = NOW();
START TRANSACTION;
-- 旧记录标记过期
UPDATE Dim_Dealer_SCD2
SET EndDate = v_now, IsCurrent = FALSE
WHERE DealerCode = p_DealerCode AND IsCurrent = TRUE;
-- 插入新版本
INSERT INTO Dim_Dealer_SCD2(DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent)
VALUES (p_DealerCode, p_DealerName, p_NewAddress, v_now, NULL, TRUE);
COMMIT;
END //
-- ---------- 9.3 邻接表分类树移动节点 (防循环引用) ----------
CREATE PROCEDURE sp_MoveCategoryNode(IN p_CategoryId INT, IN p_NewParentId INT)
BEGIN
DECLARE v_cnt INT;
-- 防止把父节点移动到自己的子节点(循环引用)
SELECT COUNT(*) INTO v_cnt
FROM ProductCategory_AdjacencyList AS c
WHERE c.CategoryId = p_NewParentId
AND EXISTS (
WITH RECURSIVE tree AS (
SELECT CategoryId FROM ProductCategory_AdjacencyList WHERE CategoryId = p_CategoryId
UNION ALL
SELECT child.CategoryId FROM ProductCategory_AdjacencyList child
INNER JOIN tree ON child.ParentId = tree.CategoryId
)
SELECT 1 FROM tree WHERE tree.CategoryId = p_NewParentId
);
IF v_cnt > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能将节点移动到自身子节点，循环引用';
END IF;
UPDATE ProductCategory_AdjacencyList SET ParentId = p_NewParentId WHERE CategoryId = p_CategoryId;
END //
-- ---------- 9.4 订单取消 (库存回滚 + 工单终止 + 补偿日志) ----------
CREATE PROCEDURE sp_CancelSalesOrder(
IN p_OrderNo VARCHAR(50),
IN p_Remark VARCHAR(500),
IN p_Operator VARCHAR(64)
)
BEGIN
DECLARE v_OrderId INT;
DECLARE v_OldStatus ENUM('Draft','Confirmed','Producing','Shipped','Completed','Cancelled');
DECLARE v_InvId INT;
DECLARE v_InvQty INT;
DECLARE v_RowVer INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单取消失败，事务回滚';
END;
START TRANSACTION;
SELECT OrderId, OrderStatus INTO v_OrderId, v_OldStatus
FROM SalesOrder WHERE OrderNo = p_OrderNo FOR UPDATE;
IF v_OrderId IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单不存在';
END IF;
IF v_OldStatus IN ('Shipped','Completed') THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单已发货/已完成，不允许取消';
END IF;
UPDATE SalesOrder SET OrderStatus='Cancelled' WHERE OrderId = v_OrderId;
-- 库存回滚(加回)
SELECT InventoryId, StockQty, RowVersion INTO v_InvId, v_InvQty, v_RowVer
FROM Inventory_Denormalized inv
INNER JOIN WorkOrder wo ON wo.StyleId = inv.StyleId
WHERE wo.OrderId = v_OrderId LIMIT 1;
IF v_InvId IS NOT NULL THEN
UPDATE Inventory_Denormalized
SET StockQty = StockQty + 1, RowVersion = RowVersion + 1
WHERE InventoryId = v_InvId AND RowVersion = v_RowVer;
END IF;
UPDATE WorkOrder SET WorkStatus='Reject' WHERE OrderId = v_OrderId;
INSERT INTO BusinessCompensationLog(BizType, BizKey, BeforeState, AfterState, CompQty, CompRemark, Operator)
VALUES ('OrderCancel', p_OrderNo,
JSON_OBJECT('OrderNo',p_OrderNo,'OldStatus',v_OldStatus),
JSON_OBJECT('OrderNo',p_OrderNo,'NewStatus','Cancelled'),
1, p_Remark, p_Operator);
COMMIT;
END //
-- ---------- 9.5 工单返工 (质检不合格，重置工单，原料退回) ----------
CREATE PROCEDURE sp_WorkOrderRework(
IN p_WorkNo VARCHAR(50),
IN p_Remark VARCHAR(500),
IN p_Operator VARCHAR(64)
)
BEGIN
DECLARE v_WorkId INT;
DECLARE v_OldStatus ENUM('Pending','Working','QC','Finished','Reject');
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '工单返工失败，事务回滚';
END;
START TRANSACTION;
SELECT WorkOrderId, WorkStatus INTO v_WorkId, v_OldStatus
FROM WorkOrder WHERE WorkNo = p_WorkNo FOR UPDATE;
IF v_WorkId IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '工单号不存在';
END IF;
IF v_OldStatus <> 'QC' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '只有QC质检状态工单可以返工';
END IF;
UPDATE WorkOrder SET WorkStatus='Working', ActualEndDate=NULL WHERE WorkOrderId = v_WorkId;
-- 宝石原料退回(示例)
UPDATE GemRaw_3NF SET WeightCarat = WeightCarat + 0.1 WHERE GemId = 1;
INSERT INTO BusinessCompensationLog(BizType, BizKey, BeforeState, AfterState, CompQty, CompRemark, Operator)
VALUES ('WorkOrderRework', p_WorkNo,
JSON_OBJECT('WorkNo',p_WorkNo,'OldStatus',v_OldStatus),
JSON_OBJECT('WorkNo',p_WorkNo,'NewStatus','Working'),
1, p_Remark, p_Operator);
COMMIT;
END //
-- ---------- 9.6 通用库存回滚 ----------
CREATE PROCEDURE sp_InventoryRollback(
IN p_InventoryId INT,
IN p_AddQty INT,
IN p_Remark VARCHAR(500),
IN p_Operator VARCHAR(64)
)
BEGIN
DECLARE v_OldQty INT;
DECLARE v_Ver INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存回滚失败';
END;
START TRANSACTION;
SELECT StockQty, RowVersion INTO v_OldQty, v_Ver
FROM Inventory_Denormalized WHERE InventoryId = p_InventoryId FOR UPDATE;
IF v_OldQty IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存记录不存在';
END IF;
UPDATE Inventory_Denormalized
SET StockQty = StockQty + p_AddQty, RowVersion = RowVersion + 1
WHERE InventoryId = p_InventoryId AND RowVersion = v_Ver;
INSERT INTO BusinessCompensationLog(BizType, BizKey, BeforeState, AfterState, CompQty, CompRemark, Operator)
VALUES ('InventoryRollback', CAST(p_InventoryId AS CHAR),
JSON_OBJECT('InventoryId',p_InventoryId,'OldQty',v_OldQty),
JSON_OBJECT('InventoryId',p_InventoryId,'NewQty',v_OldQty + p_AddQty),
p_AddQty, p_Remark, p_Operator);
COMMIT;
END //
-- ---------- 9.7 批量生成销售订单测试数据(万级) ----------
CREATE PROCEDURE sp_BulkGenSalesOrder(IN p_rows INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_orderNo VARCHAR(64);
START TRANSACTION;
WHILE i <= p_rows DO
SET v_orderNo = CONCAT('ORD_BULK_',DATE_FORMAT(NOW(),'%Y%m%d'),'_',i);
INSERT INTO SalesOrder(TenantId, OrderNo, DealerId, OrderAmount, OrderStatus)
VALUES (1, v_orderNo, 1, FLOOR(RAND()*50000)+1000, 'Confirmed');
SET i = i + 1;
END WHILE;
COMMIT;
END //
-- ---------- 9.8 定期ANALYZE更新统计信息 ----------
CREATE PROCEDURE sp_AnalyzeAllTables()
BEGIN
ANALYZE TABLE SalesOrder, WorkOrder, Inventory_Denormalized, Style_2NF, GemRaw_3NF, AuditLog;
END //
DELIMITER ;
/* ============================================================================
第10部分: 索引优化 + 分区表
============================================================================ */
-- 10.1 补充索引
CREATE INDEX idx_scd2_dealer_current ON Dim_Dealer_SCD2(DealerCode, IsCurrent);
CREATE INDEX idx_inv_style ON Inventory_Denormalized(StyleId);
CREATE INDEX idx_inv_update ON Inventory_Denormalized(UpdateTime);
CREATE INDEX idx_tenant_style ON Style_MultiTenant(TenantId);
-- 10.2 分区表: 珠宝成品库存流水事实表 (按月份RANGE分区)
CREATE TABLE Jewelry_Inventory_Fact (
FactId BIGINT AUTO_INCREMENT,
TenantId INT NOT NULL,
StyleId INT NOT NULL,
StockChangeQty INT NOT NULL,
OperateType TINYINT NOT NULL COMMENT '1入库,2出库,3盘点调整',
OperateTime DATETIME NOT NULL,
Remark VARCHAR(500),
PRIMARY KEY (FactId, OperateTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(OperateTime)) (
PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
PARTITION p202605 VALUES LESS THAN (TO_DAYS('2026-06-01')),
PARTITION p202606 VALUES LESS THAN (TO_DAYS('2026-07-01')),
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
/* ============================================================================
第11部分: 慢查询配置 + 优化器 Hint
============================================================================ */
-- 11.1 慢查询日志 (运行时动态设置，无需重启)
-- SET GLOBAL slow_query_log = ON;
-- SET GLOBAL long_query_time = 1.0;
-- SET GLOBAL log_queries_not_using_indexes = ON;
-- 查看: SHOW VARIABLES LIKE 'slow_query%';
-- 11.2 优化器 Hint 示例
-- 强制使用索引
-- SELECT /*+ INDEX(s idx_tenant_style) */ * FROM Style_MultiTenant s WHERE TenantId=1;
-- 禁止使用索引(测试对比)
-- SELECT /*+ NO_INDEX(inv idx_inv_style) */ * FROM Inventory_Denormalized inv WHERE StyleId=1;
-- EXPLAIN ANALYZE (真实执行统计)
-- EXPLAIN ANALYZE SELECT * FROM Inventory_Denormalized inv WHERE StyleId=1;
/* ============================================================================
第12部分: 事务隔离级别 + 锁分析 (库存并发扣减)
============================================================================ */
-- 查看当前隔离级别: SELECT @@transaction_isolation;
-- 设置RC(库存业务推荐，减少间隙锁):
-- SET SESSION transaction_isolation = 'READ-COMMITTED';
-- 悲观锁库存扣减 (防止超卖)
-- START TRANSACTION;
-- SELECT InventoryId, StockQty FROM Inventory_Denormalized WHERE InventoryId=1 FOR UPDATE;
-- UPDATE Inventory_Denormalized SET StockQty = StockQty - 1 WHERE InventoryId=1;
-- COMMIT;
-- 乐观锁库存扣减 (RowVersion版本号)
-- UPDATE Inventory_Denormalized SET StockQty=StockQty-1, RowVersion=RowVersion+1
-- WHERE InventoryId=1 AND RowVersion=1;
-- 锁分析查询
-- SELECT * FROM information_schema.innodb_trx;
-- SELECT * FROM information_schema.innodb_lock_waits;
-- SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
/* ============================================================================
第13部分: 数据仓库 JewelryDW (星型模型 / 雪花模型)
============================================================================ */
CREATE DATABASE IF NOT EXISTS JewelryDW DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE JewelryDW;
-- ---------- 13.1 星型模型 Star Schema ----------
CREATE TABLE Dim_Date (
DateKey INT PRIMARY KEY COMMENT 'YYYYMMDD数字主键',
FullDate DATE NOT NULL UNIQUE,
Year INT NOT NULL,
Quarter INT NOT NULL,
Month INT NOT NULL,
Day INT NOT NULL,
WeekOfYear INT NOT NULL,
WeekName VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DW维度: 时间';
CREATE TABLE Dim_Dealer (
DealerSK INT AUTO_INCREMENT PRIMARY KEY COMMENT '代理键',
DealerCode VARCHAR(30),
DealerName VARCHAR(100),
Address VARCHAR(200),
StartDate DATE,
EndDate DATE,
IsCurrent BOOLEAN,
INDEX idx_dealer_code (DealerCode),
INDEX idx_dealer_current (DealerCode, IsCurrent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DW维度: 经销商 SCD2';
CREATE TABLE Dim_Style (
StyleSK INT AUTO_INCREMENT PRIMARY KEY,
StyleCode VARCHAR(50),
StyleName VARCHAR(100),
MaterialType VARCHAR(50),
CategoryName VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DW维度: 珠宝款式';
CREATE TABLE Fact_Sales (
SalesSK BIGINT AUTO_INCREMENT PRIMARY KEY,
DateKey INT NOT NULL,
DealerSK INT NOT NULL,
StyleSK INT NOT NULL,
SalesQty INT NOT NULL DEFAULT 1,
SalesAmount DECIMAL(18,2) NOT NULL,
FOREIGN KEY (DateKey) REFERENCES Dim_Date(DateKey),
FOREIGN KEY (DealerSK) REFERENCES Dim_Dealer(DealerSK),
FOREIGN KEY (StyleSK) REFERENCES Dim_Style(StyleSK),
INDEX idx_fact_date (DateKey),
INDEX idx_fact_dealer (DealerSK),
INDEX idx_fact_style (StyleSK)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DW事实表: 销售事实(星型)';
-- ---------- 13.2 雪花模型 Snowflake Schema (维度继续拆分子维度) ----------
CREATE TABLE Dim_Category (
CategorySK INT AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(100),
ParentCategoryName VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='雪花子维度: 产品分类';
CREATE TABLE Dim_Material (
MaterialSK INT AUTO_INCREMENT PRIMARY KEY,
MaterialCode VARCHAR(20),
MaterialName VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='雪花子维度: 材质';
CREATE TABLE Dim_Style_Snowflake (
StyleSK INT AUTO_INCREMENT PRIMARY KEY,
StyleCode VARCHAR(50),
StyleName VARCHAR(100),
CategorySK INT NOT NULL,
MaterialSK INT NOT NULL,
FOREIGN KEY (CategorySK) REFERENCES Dim_Category(CategorySK),
FOREIGN KEY (MaterialSK) REFERENCES Dim_Material(MaterialSK)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='雪花维度: 款式(关联分类+材质子维度)';
CREATE TABLE Fact_Sales_Snowflake (
SalesSK BIGINT AUTO_INCREMENT PRIMARY KEY,
DateKey INT NOT NULL,
DealerSK INT NOT NULL,
StyleSK INT NOT NULL,
SalesQty INT NOT NULL,
SalesAmount DECIMAL(18,2) NOT NULL,
FOREIGN KEY (DateKey) REFERENCES Dim_Date(DateKey),
FOREIGN KEY (DealerSK) REFERENCES Dim_Dealer(DealerSK),
FOREIGN KEY (StyleSK) REFERENCES Dim_Style_Snowflake(StyleSK)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DW事实表: 销售事实(雪花)';
-- ---------- 13.3 ETL控制表 + 错误日志表 ----------
CREATE TABLE ETL_Control (
ETLId INT AUTO_INCREMENT PRIMARY KEY,
ETLName VARCHAR(100) NOT NULL,
LastExtractTime DATETIME NOT NULL,
UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ETL控制: 记录最后抽取时间';
INSERT INTO ETL_Control (ETLName, LastExtractTime) VALUES ('FactSales','2026-01-01 00:00:00');
CREATE TABLE ETL_ErrorLog (
ErrorId BIGINT AUTO_INCREMENT PRIMARY KEY,
ETLTaskName VARCHAR(100) NOT NULL,
SourceTable VARCHAR(64) NOT NULL,
BizKey VARCHAR(100) NULL,
ErrorField VARCHAR(64) NULL,
ErrorMsg VARCHAR(500) NOT NULL,
RawData JSON NULL,
CaptureTime DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_etl_err_task (ETLTaskName, CaptureTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ETL错误日志: 脏数据捕获';
-- 主从监控表(在从库使用)
CREATE TABLE IF NOT EXISTS ReplicationMonitorLog (
MonitorId BIGINT AUTO_INCREMENT PRIMARY KEY,
DelaySeconds INT NULL,
IO_Running VARCHAR(20),
SQL_Running VARCHAR(20),
ErrorMsg VARCHAR(500) NULL,
MonitorTime DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_monitor_time (MonitorTime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='主从复制延迟监控';
/* ============================================================================
第14部分: ETL 存储过程 (OLTP JewelryERP -> JewelryDW 增量抽取)
============================================================================ */
USE JewelryDW;
DELIMITER //
-- 14.1 预生成日期维度
CREATE PROCEDURE sp_LoadDimDate(IN p_startDate DATE, IN p_endDate DATE)
BEGIN
DECLARE v_curr DATE;
DECLARE v_key INT;
SET v_curr = p_startDate;
START TRANSACTION;
WHILE v_curr <= p_endDate DO
SET v_key = CAST(DATE_FORMAT(v_curr,'%Y%m%d') AS UNSIGNED);
INSERT IGNORE INTO Dim_Date(DateKey, FullDate, Year, Quarter, Month, Day, WeekOfYear, WeekName)
VALUES(v_key, v_curr, YEAR(v_curr), QUARTER(v_curr), MONTH(v_curr),
DAY(v_curr), WEEK(v_curr), DAYNAME(v_curr));
SET v_curr = DATE_ADD(v_curr, INTERVAL 1 DAY);
END WHILE;
COMMIT;
END //
-- 14.2 SCD2 经销商维度增量拉链 (自动对比变更，新增版本)
CREATE PROCEDURE sp_ETL_SCD2_DimDealer()
BEGIN
DECLARE v_etl_date DATE;
SET v_etl_date = CURDATE();
CREATE TEMPORARY TABLE tmp_oltp_dealer (
DealerCode VARCHAR(30), DealerName VARCHAR(100), Address VARCHAR(200),
PRIMARY KEY(DealerCode)
);
INSERT INTO tmp_oltp_dealer(DealerCode, DealerName, Address)
SELECT DealerCode, DealerName, Address FROM JewelryERP.Dim_Dealer_SCD2 WHERE IsCurrent = TRUE;
-- 标记旧版本过期(数据发生变更的)
UPDATE JewelryDW.Dim_Dealer dw
INNER JOIN tmp_oltp_dealer src ON dw.DealerCode = src.DealerCode
SET dw.EndDate = v_etl_date, dw.IsCurrent = FALSE
WHERE dw.IsCurrent = TRUE
AND (dw.DealerName <> src.DealerName OR dw.Address <> src.Address);
-- 插入新增/变更后的新版本
INSERT INTO JewelryDW.Dim_Dealer(DealerCode, DealerName, Address, StartDate, EndDate, IsCurrent)
SELECT src.DealerCode, src.DealerName, src.Address, v_etl_date, NULL, TRUE
FROM tmp_oltp_dealer src
LEFT JOIN JewelryDW.Dim_Dealer dw ON src.DealerCode = dw.DealerCode AND dw.IsCurrent = TRUE
WHERE dw.DealerSK IS NULL;
DROP TEMPORARY TABLE tmp_oltp_dealer;
END //
-- 14.3 增量加载款式维度
CREATE PROCEDURE sp_ETL_DimStyle()
BEGIN
INSERT IGNORE INTO JewelryDW.Dim_Style(StyleCode, StyleName, CategoryName)
SELECT s.StyleCode, s.StyleName, c.CategoryName
FROM JewelryERP.Style_2NF s
LEFT JOIN JewelryERP.ProductCategory_AdjacencyList c ON 1=1
WHERE s.StyleCode NOT IN (SELECT StyleCode FROM JewelryDW.Dim_Style);
END //
-- 14.4 增量抽取销售事实表
CREATE PROCEDURE sp_ETL_FactSalesIncrement()
BEGIN
DECLARE v_last_extract DATETIME;
SELECT LastExtractTime INTO v_last_extract FROM JewelryDW.ETL_Control WHERE ETLName='FactSales';
CREATE TEMPORARY TABLE tmp_sales_incr (
OrderNo VARCHAR(50), OrderAmount DECIMAL(18,2), CreateTime DATETIME,
DealerCode VARCHAR(30), StyleCode VARCHAR(50), DateKey INT
);
INSERT INTO tmp_sales_incr
SELECT o.OrderNo, o.OrderAmount, o.CreateTime, d.DealerCode, s.StyleCode,
DATE_FORMAT(o.CreateTime,'%Y%m%d')
FROM JewelryERP.SalesOrder o
LEFT JOIN JewelryERP.Dim_Dealer_SCD2 d ON o.DealerId = d.DealerSurrogateKey
LEFT JOIN JewelryERP.Style_2NF s ON 1=1
WHERE o.CreateTime > v_last_extract;
INSERT INTO JewelryDW.Fact_Sales(DateKey, DealerSK, StyleSK, SalesQty, SalesAmount)
SELECT t.DateKey, dwd.DealerSK, dws.StyleSK, 1, t.OrderAmount
FROM tmp_sales_incr t
INNER JOIN JewelryDW.Dim_Dealer dwd ON t.DealerCode = dwd.DealerCode AND dwd.IsCurrent = TRUE
INNER JOIN JewelryDW.Dim_Style dws ON t.StyleCode = dws.StyleCode;
UPDATE JewelryDW.ETL_Control SET LastExtractTime = NOW() WHERE ETLName='FactSales';
DROP TEMPORARY TABLE tmp_sales_incr;
END //
-- 14.5 ETL总入口
CREATE PROCEDURE sp_ETL_All()
BEGIN
START TRANSACTION;
CALL sp_ETL_SCD2_DimDealer();
CALL sp_ETL_DimStyle();
CALL sp_ETL_FactSalesIncrement();
COMMIT;
END //
DELIMITER ;
/* ============================================================================
第15部分: ETL 数据质量校验 (脏数据捕获写入ETL_ErrorLog)
============================================================================ */
USE JewelryDW;
DELIMITER //
CREATE PROCEDURE sp_ETL_DataQuality_Check_FactSales()
BEGIN
CREATE TEMPORARY TABLE tmp_etl_src_sales (
OrderNo VARCHAR(50), OrderAmount DECIMAL(18,2), CreateTime DATETIME,
DealerCode VARCHAR(30), StyleCode VARCHAR(50), RawJSON JSON
);
INSERT INTO tmp_etl_src_sales
SELECT o.OrderNo, o.OrderAmount, o.CreateTime, d.DealerCode, s.StyleCode,
JSON_OBJECT('OrderNo',o.OrderNo,'OrderAmount',o.OrderAmount,'CreateTime',o.CreateTime)
FROM JewelryERP.SalesOrder o
LEFT JOIN JewelryERP.Dim_Dealer_SCD2 d ON o.DealerId = d.DealerSurrogateKey
LEFT JOIN JewelryERP.Style_2NF s ON 1=1;
-- 规则1: 订单号为空
INSERT INTO ETL_ErrorLog(ETLTaskName, SourceTable, BizKey, ErrorField, ErrorMsg, RawData)
SELECT 'FactSales','SalesOrder',OrderNo,'OrderNo','订单编号为空',RawJSON
FROM tmp_etl_src_sales WHERE OrderNo IS NULL OR OrderNo = '';
-- 规则2: 订单金额负数
INSERT INTO ETL_ErrorLog(ETLTaskName, SourceTable, BizKey, ErrorField, ErrorMsg, RawData)
SELECT 'FactSales','SalesOrder',OrderNo,'OrderAmount','订单金额小于0',RawJSON
FROM tmp_etl_src_sales WHERE OrderAmount < 0;
-- 规则3: 创建时间为未来时间
INSERT INTO ETL_ErrorLog(ETLTaskName, SourceTable, BizKey, ErrorField, ErrorMsg, RawData)
SELECT 'FactSales','SalesOrder',OrderNo,'CreateTime','订单创建时间不能大于当前时间',RawJSON
FROM tmp_etl_src_sales WHERE CreateTime > NOW();
-- 规则4: 款式编码为空
INSERT INTO ETL_ErrorLog(ETLTaskName, SourceTable, BizKey, ErrorField, ErrorMsg, RawData)
SELECT 'FactSales','SalesOrder',OrderNo,'StyleCode','款式编码为空',RawJSON
FROM tmp_etl_src_sales WHERE StyleCode IS NULL OR StyleCode='';
-- 规则5: 经销商编码为空
INSERT INTO ETL_ErrorLog(ETLTaskName, SourceTable, BizKey, ErrorField, ErrorMsg, RawData)
SELECT 'FactSales','SalesOrder',OrderNo,'DealerCode','经销商编码为空',RawJSON
FROM tmp_etl_src_sales WHERE DealerCode IS NULL OR DealerCode='';
-- 干净数据供后续ETL加载
CREATE TEMPORARY TABLE tmp_etl_clean_sales AS
SELECT * FROM tmp_etl_src_sales
WHERE OrderNo IS NOT NULL AND OrderNo <> ''
AND OrderAmount >= 0 AND CreateTime <= NOW()
AND StyleCode IS NOT NULL AND StyleCode <> ''
AND DealerCode IS NOT NULL AND DealerCode <> '';
DROP TEMPORARY TABLE tmp_etl_src_sales;
END //
DELIMITER ;
/* ============================================================================
第16部分: 读写分离 + 主从延迟监控
============================================================================ */
-- 架构: Master主库承担OLTP写入; Slave从库承担报表查询+ETL抽取
-- 主库my.cnf: server-id=1, log_bin=mysql-bin, binlog_format=ROW
-- 从库my.cnf: server-id=2, read_only=ON, super_read_only=ON
-- 业务层路由: 写操作->Master; 非实时报表/ETL->Slave; 库存实时扣减->Master
USE JewelryDW;
DELIMITER //
CREATE PROCEDURE sp_MonitorReplicationDelay(IN p_Threshold INT)
BEGIN
DECLARE v_delay INT;
DECLARE v_io_run VARCHAR(20);
DECLARE v_sql_run VARCHAR(20);
DECLARE v_err VARCHAR(500);
SELECT Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running,
CONCAT(Last_IO_Error, ' ', Last_SQL_Error)
INTO v_delay, v_io_run, v_sql_run, v_err
FROM INFORMATION_SCHEMA.REPLICATION_STATUS;
INSERT INTO ReplicationMonitorLog(DelaySeconds, IO_Running, SQL_Running, ErrorMsg)
VALUES (v_delay, v_io_run, v_sql_run, v_err);
IF v_delay > p_Threshold THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('主从复制延迟告警，延迟：',v_delay,'秒，阈值：',p_Threshold);
END IF;
END //
DELIMITER ;
/* ============================================================================
第17部分: 数据库安全 (账号权限 / 行级安全视图 / 数据脱敏 / 审计加固)
============================================================================ */
-- 注意: 以下账号创建需在root账号下执行，脚本中默认注释，按需取消注释
-- CREATE USER 'etl_user'@'%' IDENTIFIED BY 'StrongETL@2026';
-- GRANT SELECT ON JewelryERP.* TO 'etl_user'@'%';
-- GRANT INSERT,UPDATE,SELECT ON JewelryDW.* TO 'etl_user'@'%';
-- CREATE USER 'report_user'@'%' IDENTIFIED BY 'Report@2026Read';
-- GRANT SELECT ON JewelryERP.* TO 'report_user'@'%';
-- CREATE USER 'erp_app'@'%' IDENTIFIED BY 'ERPApp@2026';
-- GRANT SELECT,INSERT,UPDATE,DELETE ON JewelryERP.* TO 'erp_app'@'%';
-- REVOKE DROP,ALTER ON JewelryERP.* FROM 'erp_app'@'%';
-- FLUSH PRIVILEGES;
-- 行级安全: MySQL通过视图+会话变量实现(MySQL无原生CREATE POLICY)
-- 业务层设置 SET @current_tenant_id = 1; 后查询视图自动过滤
USE JewelryERP;
CREATE OR REPLACE VIEW vw_SalesOrder_Tenant AS
SELECT * FROM SalesOrder WHERE TenantId = @current_tenant_id;
CREATE OR REPLACE VIEW vw_Inventory_Tenant AS
SELECT inv.* FROM Inventory_Denormalized inv
INNER JOIN Style_MultiTenant sm ON inv.StyleId = sm.StyleId
WHERE sm.TenantId = @current_tenant_id;
-- 审计加固: 审计日志表禁止业务账号UPDATE/DELETE
-- REVOKE UPDATE,DELETE ON JewelryERP.AuditLog FROM 'erp_app'@'%';
-- GRANT INSERT,SELECT ON JewelryERP.AuditLog TO 'erp_app'@'%';
/* ============================================================================
第18部分: 运维脚本 (备份 / binlog / 碎片优化 / 统计信息)
============================================================================ */
-- 18.1 mysqldump全量备份 (Shell脚本，需在Linux命令行执行)
-- #!/bin/bash
-- BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
-- BACKUP_DIR="/data/backup/jewelry"
-- mkdir -p ${BACKUP_DIR}
-- mysqldump -ubackup_user -pBackupPass --single-transaction --routines --triggers --events JewelryERP \
-- > ${BACKUP_DIR}/JewelryERP_full_${BACKUP_DATE}.sql
-- find ${BACKUP_DIR} -name "JewelryERP_full_*.sql" -mtime +7 -delete
-- 18.2 表碎片优化 (业务低峰期执行，会锁表)
-- OPTIMIZE TABLE SalesOrder;
-- OPTIMIZE TABLE Inventory_Denormalized;
-- OPTIMIZE TABLE AuditLog;
-- OPTIMIZE TABLE BusinessCompensationLog;
-- 18.3 查看表碎片
-- SELECT TABLE_NAME, DATA_LENGTH/1024/1024 AS DATA_MB,
-- INDEX_LENGTH/1024/1024 AS IDX_MB, DATA_FREE/1024/1024 AS FREE_MB
-- FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='JewelryERP';
-- 18.4 定时事件: 每日凌晨更新统计信息
SET GLOBAL event_scheduler = ON;
CREATE EVENT IF NOT EXISTS evt_analyze_table_stats
ON SCHEDULE EVERY 1 DAY STARTS '2026-09-16 01:00:00'
DO CALL JewelryERP.sp_AnalyzeAllTables();
-- 18.5 定时事件: 每日凌晨ETL
CREATE EVENT IF NOT EXISTS evt_etl_daily
ON SCHEDULE EVERY 1 DAY STARTS '2026-09-16 02:00:00'
DO CALL JewelryDW.sp_ETL_All();
/* ============================================================================
第19部分: BI 报表 SQL (销售分析 / 库存周转 / 工单良率 / 加盟商分析)
============================================================================ */
USE JewelryERP;
-- 报表1: 按月+款式统计销售金额、订单数量
-- SELECT DATE_FORMAT(o.CreateTime,'%Y-%m') AS OrderMonth, s.StyleName,
-- COUNT(o.OrderId) AS OrderCount, SUM(o.OrderAmount) AS TotalSalesAmount
-- FROM SalesOrder o
-- LEFT JOIN WorkOrder wo ON o.OrderId = wo.OrderId
-- LEFT JOIN Style_2NF s ON wo.StyleId = s.StyleId
-- WHERE o.OrderStatus NOT IN ('Cancelled')
-- GROUP BY OrderMonth, StyleName ORDER BY OrderMonth DESC, TotalSalesAmount DESC;
-- 报表2: 库存周转天数分析
-- SELECT s.StyleName, inv.StockQty, inv.UnitCost,
-- inv.StockQty * inv.UnitCost AS StockTotalCost,
-- COALESCE(SUM(o.OrderAmount),0) AS SalesAmount_30d,
-- CASE WHEN COALESCE(SUM(o.OrderAmount),0)>0
-- THEN (inv.StockQty*inv.UnitCost)/SUM(o.OrderAmount)*30 ELSE 999 END AS TurnoverDays
-- FROM Inventory_Denormalized inv
-- LEFT JOIN Style_2NF s ON inv.StyleId=s.StyleId
-- LEFT JOIN WorkOrder wo ON wo.StyleId=s.StyleId
-- LEFT JOIN SalesOrder o ON o.OrderId=wo.OrderId AND o.CreateTime>=DATE_SUB(NOW(),INTERVAL 30 DAY)
-- GROUP BY inv.StyleId,s.StyleName,inv.StockQty,inv.UnitCost;
-- 报表3: 加工工单良率
-- SELECT DATE_FORMAT(wo.CreateTime,'%Y-%m') AS WorkMonth, s.StyleName,
-- COUNT(wo.WorkOrderId) AS TotalCnt,
-- SUM(CASE WHEN wo.WorkStatus='Finished' THEN 1 ELSE 0 END) AS FinishedCnt,
-- SUM(CASE WHEN wo.WorkStatus='Reject' THEN 1 ELSE 0 END) AS RejectCnt,
-- ROUND(SUM(CASE WHEN wo.WorkStatus='Finished' THEN 1 ELSE 0 END)*100.0/COUNT(wo.WorkOrderId),2) AS YieldRatePct
-- FROM WorkOrder wo INNER JOIN Style_2NF s ON wo.StyleId=s.StyleId
-- GROUP BY WorkMonth,StyleName ORDER BY WorkMonth DESC,YieldRatePct DESC;
-- 报表4: 加盟商多维度经营分析
-- SELECT t.TenantName, COUNT(o.OrderId) AS TotalOrderCount,
-- SUM(o.OrderAmount) AS TotalSalesAmount,
-- SUM(CASE WHEN o.OrderStatus='Cancelled' THEN 1 ELSE 0 END) AS CancelCnt,
-- ROUND(SUM(CASE WHEN o.OrderStatus='Cancelled' THEN 1 ELSE 0 END)*100.0/COUNT(o.OrderId),2) AS CancelRatePct
-- FROM SalesOrder o INNER JOIN Tenant t ON o.TenantId=t.TenantId
-- WHERE o.CreateTime>=DATE_SUB(NOW(),INTERVAL 90 DAY)
-- GROUP BY t.TenantName ORDER BY TotalSalesAmount DESC;
/* ============================================================================
第20部分: 层次模型性能对比 (EXPLAIN)
场景: 查询"戒指"分类下所有子节点
============================================================================ */
USE JewelryERP;
-- 方案1: 邻接表 - 递归CTE
-- EXPLAIN
-- WITH RECURSIVE CategoryTree AS (
-- SELECT CategoryId,CategoryName,ParentId,CAST(CategoryName AS CHAR(500)) AS Path,0 AS Lvl
-- FROM ProductCategory_AdjacencyList WHERE CategoryName='戒指'
-- UNION ALL
-- SELECT c.CategoryId,c.CategoryName,c.ParentId,CONCAT(ct.Path,'/',c.CategoryName),ct.Lvl+1
-- FROM ProductCategory_AdjacencyList c INNER JOIN CategoryTree ct ON c.ParentId=ct.CategoryId
-- )
-- SELECT * FROM CategoryTree ORDER BY Path;
-- 方案2: 嵌套集 - BETWEEN查询(无递归)
-- EXPLAIN
-- SELECT child.* FROM ProductCategory_NestedSet parent
-- INNER JOIN ProductCategory_NestedSet child ON child.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
-- WHERE parent.CategoryName='戒指' ORDER BY child.LeftValue;
-- 方案3: 闭包表 - JOIN查询
-- EXPLAIN
-- SELECT m.*, r.Depth FROM ProductCategory_Closure_Relation r
-- INNER JOIN ProductCategory_Closure_Main m ON r.DescendantId=m.CategoryId
-- WHERE r.AncestorId=(SELECT CategoryId FROM ProductCategory_Closure_Main WHERE CategoryName='戒指')
-- ORDER BY r.Depth, m.CategoryId;
/* ============================================================================
附录: DDD 领域模型说明
============================================================================
聚合(Aggregate):
1. 产品聚合 Product: 聚合根Style_2NF, 实体Style_Gem_Map_2NF/ProductCategory,
值对象: 材质/宝石参数(克拉/颜色/净度)
2. 宝石原料聚合 GemRaw: 聚合根GemRaw_3NF, 实体GemType_Lookup_3NF(查找表)
3. 销售订单聚合 SalesOrder: 聚合根SalesOrder, 值对象: 订单金额/状态/编号
4. 加工工单聚合 WorkOrder: 聚合根WorkOrder, 值对象: 工单号/计划日期/质检状态
5. 库存聚合 Inventory: 聚合根Inventory_Denormalized, 值对象: 库存数量/成本/RowVersion
6. 多租户聚合 Tenant: 聚合根Tenant, 实体Style_MultiTenant
7. 审计聚合 AuditLog: 跨领域事件记录
实体(Entity): Style, SalesOrder, GemRaw, WorkOrder (有唯一业务ID，生命周期可变更)
值对象(Value Object): 克拉重量、金额、日期、地址 (无独立ID，依附聚合根)
ER关系:
Tenant 1--* Style_MultiTenant
Style_2NF 1--* Style_Gem_Map_2NF
GemRaw_3NF 1--* Style_Gem_Map_2NF
GemType_Lookup_3NF 1--* GemRaw_3NF
ProductCategory_AdjacencyList 自引用 1--* (父子分类)
SalesOrder 1--* WorkOrder
Style_2NF 1--* WorkOrder
Dim_Dealer_SCD2 1--* SalesOrder
AuditLog: 所有主表INSERT/UPDATE/DELETE触发写入
范式策略:
- OLTP JewelryERP: 主业务表3NF，BOM/字典用Lookup/Junction表
- 库存汇总表反范式Denormalization，触发器同步冗余字段
- 层次分类: 邻接表用于业务端(频繁增删)，嵌套集/闭包表用于只读分析
- DW JewelryDW: 星型模型优先，SCD2拉链维度，事实表增量ETL
============================================================================ */
SELECT '=== 珠宝行业数据建模完整脚本执行完毕 ===' AS Result;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号