284 lines
14 KiB
SQL
284 lines
14 KiB
SQL
-- =============================================
|
||
-- 资金服务平台 - 系统服务数据库初始化脚本
|
||
-- Database: fund_sys
|
||
-- Version: 2.0
|
||
-- Author: fundplatform team
|
||
-- Created: 2026-02-17
|
||
-- Updated: 2026-03-02 (主键类型改为VARCHAR雪花ID)
|
||
-- =============================================
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS fund_sys DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE fund_sys;
|
||
|
||
-- =============================================
|
||
-- 1. 用户表 (sys_user)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_user (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
username VARCHAR(64) NOT NULL COMMENT '用户名',
|
||
password VARCHAR(128) NOT NULL COMMENT '密码 (MD5)',
|
||
real_name VARCHAR(64) COMMENT '真实姓名',
|
||
phone VARCHAR(20) COMMENT '手机号',
|
||
email VARCHAR(128) COMMENT '邮箱',
|
||
dept_id VARCHAR(32) COMMENT '部门ID',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
avatar VARCHAR(255) COMMENT '头像URL',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_username (tenant_id, username, deleted),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_dept_id (dept_id),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统用户表';
|
||
|
||
-- =============================================
|
||
-- 2. 角色表 (sys_role)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_role (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
role_code VARCHAR(64) NOT NULL COMMENT '角色编码',
|
||
role_name VARCHAR(128) NOT NULL COMMENT '角色名称',
|
||
data_scope TINYINT NOT NULL DEFAULT 1 COMMENT '数据权限: 1-全部, 2-本部门及子部门, 3-仅本部门, 4-仅本人',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
sort_order INT DEFAULT 0 COMMENT '排序号',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_code (tenant_id, role_code, deleted),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统角色表';
|
||
|
||
-- =============================================
|
||
-- 3. 用户角色关联表 (sys_user_role)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_user_role (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
user_id VARCHAR(32) NOT NULL COMMENT '用户ID',
|
||
role_id VARCHAR(32) NOT NULL COMMENT '角色ID',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_user_role (tenant_id, user_id, role_id),
|
||
KEY idx_user_id (user_id),
|
||
KEY idx_role_id (role_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色关联表';
|
||
|
||
-- =============================================
|
||
-- 4. 菜单表 (sys_menu)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_menu (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
parent_id VARCHAR(32) NOT NULL DEFAULT '0' COMMENT '父菜单ID, 0表示根菜单',
|
||
menu_name VARCHAR(128) NOT NULL COMMENT '菜单名称',
|
||
menu_type TINYINT NOT NULL DEFAULT 1 COMMENT '菜单类型: 1-目录, 2-菜单, 3-按钮',
|
||
menu_path VARCHAR(255) COMMENT '路由路径',
|
||
menu_icon VARCHAR(128) COMMENT '菜单图标',
|
||
component VARCHAR(255) COMMENT '组件路径',
|
||
permission VARCHAR(128) COMMENT '权限标识',
|
||
sort_order INT DEFAULT 0 COMMENT '排序号',
|
||
visible TINYINT NOT NULL DEFAULT 1 COMMENT '是否可见: 0-隐藏, 1-显示',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_parent_id (parent_id),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统菜单表';
|
||
|
||
-- =============================================
|
||
-- 5. 角色菜单关联表 (sys_role_menu)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_role_menu (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
role_id VARCHAR(32) NOT NULL COMMENT '角色ID',
|
||
menu_id VARCHAR(32) NOT NULL COMMENT '菜单ID',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_role_menu (tenant_id, role_id, menu_id),
|
||
KEY idx_role_id (role_id),
|
||
KEY idx_menu_id (menu_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色菜单关联表';
|
||
|
||
-- =============================================
|
||
-- 6. 部门表 (sys_dept)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_dept (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
parent_id VARCHAR(32) NOT NULL DEFAULT '0' COMMENT '父部门ID, 0表示根部门',
|
||
dept_code VARCHAR(64) NOT NULL COMMENT '部门编码',
|
||
dept_name VARCHAR(128) NOT NULL COMMENT '部门名称',
|
||
dept_leader VARCHAR(64) COMMENT '部门负责人',
|
||
phone VARCHAR(20) COMMENT '联系电话',
|
||
email VARCHAR(128) COMMENT '邮箱',
|
||
sort_order INT DEFAULT 0 COMMENT '排序号',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_code (tenant_id, dept_code, deleted),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_parent_id (parent_id),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统部门表';
|
||
|
||
-- =============================================
|
||
-- 7. 数据字典表 (sys_dict)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_dict (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
dict_type VARCHAR(64) NOT NULL COMMENT '字典类型',
|
||
dict_label VARCHAR(128) NOT NULL COMMENT '字典标签',
|
||
dict_value VARCHAR(128) NOT NULL COMMENT '字典值',
|
||
sort_order INT DEFAULT 0 COMMENT '排序号',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_type_value (tenant_id, dict_type, dict_value, deleted),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_dict_type (dict_type),
|
||
KEY idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统字典表';
|
||
|
||
-- =============================================
|
||
-- 8. 系统配置表 (sys_config)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_config (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
config_key VARCHAR(128) NOT NULL COMMENT '配置键',
|
||
config_value TEXT COMMENT '配置值',
|
||
config_type VARCHAR(64) DEFAULT 'string' COMMENT '配置类型: string/number/boolean/json',
|
||
description VARCHAR(500) COMMENT '配置描述',
|
||
is_system TINYINT NOT NULL DEFAULT 0 COMMENT '是否系统配置: 0-否, 1-是(系统配置不参与多租户隔离)',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
group_code VARCHAR(64) COMMENT '分组编码',
|
||
group_name VARCHAR(128) COMMENT '分组名称',
|
||
sort_order INT DEFAULT 0 COMMENT '排序号',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_config_key (config_key, deleted),
|
||
KEY idx_status (status),
|
||
KEY idx_group_code (group_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表(不参与租户隔离)';
|
||
|
||
-- =============================================
|
||
-- 9. 操作日志表 (sys_log)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_log (
|
||
id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
|
||
tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
|
||
user_id VARCHAR(32) COMMENT '操作用户ID',
|
||
username VARCHAR(64) COMMENT '操作用户名',
|
||
operation VARCHAR(128) COMMENT '操作描述',
|
||
method VARCHAR(255) COMMENT '请求方法',
|
||
params TEXT COMMENT '请求参数',
|
||
ip VARCHAR(64) COMMENT '请求IP',
|
||
location VARCHAR(255) COMMENT 'IP归属地',
|
||
execute_time INT COMMENT '执行时长(ms)',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-失败, 1-成功',
|
||
error_msg TEXT COMMENT '错误信息',
|
||
trace_id VARCHAR(64) COMMENT '链路追踪ID',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (id),
|
||
KEY idx_tenant_id (tenant_id),
|
||
KEY idx_user_id (user_id),
|
||
KEY idx_created_time (created_time),
|
||
KEY idx_trace_id (trace_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统操作日志表';
|
||
|
||
-- =============================================
|
||
-- 10. 租户表 (sys_tenant)
|
||
-- =============================================
|
||
CREATE TABLE IF NOT EXISTS sys_tenant (
|
||
id VARCHAR(32) NOT NULL COMMENT '租户ID（雪花算法）',
|
||
tenant_code VARCHAR(50) NOT NULL COMMENT '租户编码',
|
||
tenant_name VARCHAR(100) NOT NULL COMMENT '租户名称',
|
||
contact VARCHAR(50) COMMENT '联系人',
|
||
phone VARCHAR(20) COMMENT '联系电话',
|
||
email VARCHAR(100) COMMENT '邮箱',
|
||
address VARCHAR(200) COMMENT '地址',
|
||
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用, 1-启用',
|
||
expire_time DATETIME COMMENT '到期时间',
|
||
max_users INT NOT NULL DEFAULT 10 COMMENT '最大用户数',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
|
||
created_by VARCHAR(32) COMMENT '创建人',
|
||
created_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by VARCHAR(32) COMMENT '更新人',
|
||
updated_time DATETIME ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tenant_code (tenant_code, deleted)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='租户表';
|
||
|
||
-- =============================================
|
||
-- 初始化数据 (使用雪花ID)
|
||
-- =============================================
|
||
|
||
-- 插入默认租户
|
||
INSERT INTO sys_tenant (id, tenant_code, tenant_name, contact, phone, status, max_users, remark, created_time)
|
||
VALUES ('1', 'DEFAULT', '默认租户', '管理员', '13800138000', 1, 100, '系统默认租户', NOW())
|
||
ON DUPLICATE KEY UPDATE tenant_code=tenant_code;
|
||
|
||
-- 插入超级管理员用户 (租户ID=1, 密码: admin123, MD5: 0192023a7bbd73250516f069df18b500)
|
||
INSERT INTO sys_user (id, tenant_id, username, password, real_name, phone, status, created_by, created_time)
|
||
VALUES ('1', '1', 'admin', '0192023a7bbd73250516f069df18b500', '超级管理员', '13800138000', 1, '1', NOW())
|
||
ON DUPLICATE KEY UPDATE username=username;
|
||
|
||
-- 插入超级管理员角色
|
||
INSERT INTO sys_role (id, tenant_id, role_code, role_name, data_scope, status, created_by, created_time)
|
||
VALUES ('1', '1', 'admin', '超级管理员', 1, 1, '1', NOW())
|
||
ON DUPLICATE KEY UPDATE role_code=role_code;
|
||
|
||
-- 关联超级管理员用户和角色
|
||
INSERT INTO sys_user_role (id, tenant_id, user_id, role_id, created_by, created_time)
|
||
VALUES ('1', '1', '1', '1', '1', NOW())
|
||
ON DUPLICATE KEY UPDATE user_id=user_id;
|
||
|
||
-- 插入根部门
|
||
INSERT INTO sys_dept (id, tenant_id, parent_id, dept_code, dept_name, status, created_by, created_time)
|
||
VALUES ('1', '1', '0', 'ROOT', '根部门', 1, '1', NOW())
|
||
ON DUPLICATE KEY UPDATE dept_code=dept_code;
|
||
|
||
-- =============================================
|
||
-- 脚本执行完成
|
||
-- =============================================
|