124 lines
10 KiB
SQL
124 lines
10 KiB
SQL
-- ====================================================================
|
||
-- 字典管理功能数据库脚本
|
||
-- 创建时间：2025-09-26
|
||
-- 作者：Leocoder
|
||
-- 功能描述：字典类型表和字典数据表的创建及初始化数据
|
||
-- ====================================================================
|
||
|
||
-- ----------------------------
|
||
-- 创建字典类型表 sys_dict_type
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_dict_type`;
|
||
CREATE TABLE `sys_dict_type` (
|
||
`dict_id` bigint NOT NULL AUTO_INCREMENT COMMENT '字典主键',
|
||
`dict_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '字典名称',
|
||
`dict_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '字典类型',
|
||
`dict_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '状态[0-正常 1-停用]',
|
||
`remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '备注',
|
||
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`dict_id`) USING BTREE,
|
||
UNIQUE KEY `dict_type` (`dict_type`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='字典类型表';
|
||
|
||
-- ----------------------------
|
||
-- 创建字典数据表 sys_dict_data
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_dict_data`;
|
||
CREATE TABLE `sys_dict_data` (
|
||
`dict_id` bigint NOT NULL AUTO_INCREMENT COMMENT '字典编码',
|
||
`dict_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '字典类型',
|
||
`dict_label` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '字典名称',
|
||
`dict_value` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '字典键值',
|
||
`dict_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '状态[0启用 1停用]',
|
||
`dict_tag` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'primary' COMMENT 'ElementPlus官方颜色[默认-primary]',
|
||
`dict_color` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'tags自定义背景颜色[有值会进行覆盖ElementPlus官方颜色](例如：16进制或者pink等)',
|
||
`sorted` int DEFAULT '0' COMMENT '显示顺序',
|
||
`remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '备注',
|
||
`create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`dict_id`) USING BTREE,
|
||
KEY `idx_dict_type` (`dict_type`) USING BTREE COMMENT '字典类型索引'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='字典数据表';
|
||
|
||
-- ----------------------------
|
||
-- 初始化字典类型数据
|
||
-- ----------------------------
|
||
INSERT INTO `sys_dict_type` (`dict_id`, `dict_name`, `dict_type`, `dict_status`, `remark`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES
|
||
(1, '系统开关', 'sys_switch_status', '0', '系统开关列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(2, '用户性别', 'sys_user_sex', '0', '用户性别列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(3, '用户类型', 'sys_user_type', '0', '用户类型列表[1-系统用户，2-注册用户，3-微信小程序用户]', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(4, '菜单类型', 'sys_menu_type', '0', '菜单类型列表[1-目录，2-菜单，3-按钮]', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(5, '操作类型', 'sys_oper_type', '0', '操作类型列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(6, '通知类型', 'sys_notice_type', '0', '通知类型列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(7, '标签类型', 'sys_tag_type', '0', 'el-tag的类型列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(8, '通用状态', 'sys_common_status', '0', '通用状态列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(9, '是否标识', 'sys_yes_no', '0', '是否标识列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(10, '文件服务', 'sys_file_service', '0', '文件服务类型列表', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00');
|
||
|
||
-- ----------------------------
|
||
-- 初始化字典数据
|
||
-- ----------------------------
|
||
INSERT INTO `sys_dict_data` (`dict_id`, `dict_type`, `dict_label`, `dict_value`, `dict_status`, `dict_tag`, `dict_color`, `sorted`, `remark`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES
|
||
-- 系统开关
|
||
(1, 'sys_switch_status', '启用', '0', '0', 'primary', '', 1, '正常状态', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(2, 'sys_switch_status', '停用', '1', '0', 'danger', '', 2, '停用状态', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 用户性别
|
||
(3, 'sys_user_sex', '男', '1', '0', 'primary', '', 1, '性别男', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(4, 'sys_user_sex', '女', '2', '0', 'danger', '', 2, '性别女', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(5, 'sys_user_sex', '未知', '3', '0', 'info', '', 3, '性别未知', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 用户类型
|
||
(13, 'sys_user_type', '系统用户', '1', '0', 'primary', '', 1, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(14, 'sys_user_type', '注册用户', '2', '0', 'warning', '', 2, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(15, 'sys_user_type', '微信用户', '3', '0', 'success', '', 3, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 菜单类型
|
||
(16, 'sys_menu_type', '目录', '1', '0', 'primary', '', 1, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(17, 'sys_menu_type', '菜单', '2', '0', 'warning', '', 2, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(18, 'sys_menu_type', '按钮', '3', '0', 'success', '', 3, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 操作类型
|
||
(19, 'sys_oper_type', '其他', '0', '0', 'info', '', 99, '其他操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(20, 'sys_oper_type', '新增', '1', '0', 'info', '', 1, '新增操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(21, 'sys_oper_type', '修改', '2', '0', 'info', '', 2, '修改操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(22, 'sys_oper_type', '删除', '3', '0', 'danger', '', 3, '删除操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(23, 'sys_oper_type', '授权', '4', '0', 'primary', '', 4, '授权操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(24, 'sys_oper_type', '导出', '5', '0', 'warning', '', 5, '导出操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(25, 'sys_oper_type', '导入', '6', '0', 'warning', '', 6, '导入操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(26, 'sys_oper_type', '强退', '7', '0', 'danger', '', 7, '强退操作', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 通知类型
|
||
(11, 'sys_notice_type', '通知', '1', '0', 'primary', '', 1, '消息通知', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(12, 'sys_notice_type', '公告', '2', '0', 'warning', '', 2, '消息公告', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 标签类型
|
||
(6, 'sys_tag_type', 'primary', '1', '0', 'primary', '', 1, 'el-tag的primary类型', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(7, 'sys_tag_type', 'warning', '2', '0', 'warning', '', 2, 'el-tag的warning类型', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(8, 'sys_tag_type', 'success', '3', '0', 'success', '', 3, 'el-tag的success类型', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(9, 'sys_tag_type', 'danger', '4', '0', 'danger', '', 4, 'el-tag的danger类型', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(10, 'sys_tag_type', 'info', '5', '0', 'info', '', 5, 'el-tag的info类型', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 通用状态
|
||
(33, 'sys_common_status', '初始化', '0', '0', 'primary', '', 1, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(34, 'sys_common_status', '成功', '1', '0', 'success', '', 2, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(35, 'sys_common_status', '失败', '2', '0', 'danger', '', 3, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 是否标识
|
||
(36, 'sys_yes_no', '是', '0', '0', 'primary', '', 1, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(37, 'sys_yes_no', '否', '1', '0', 'danger', '', 2, '', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
|
||
-- 文件服务
|
||
(38, 'sys_file_service', 'LOCAL', '1', '0', 'primary', '', 1, '本地存储', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(39, 'sys_file_service', 'MINIO', '2', '0', 'info', '', 2, 'MINIO存储', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00'),
|
||
(40, 'sys_file_service', 'OSS', '3', '0', 'warning', '', 3, '阿里云OSS存储', 'Leocoder', '2025-09-26 00:00:00', 'Leocoder', '2025-09-26 00:00:00');
|
||
|
||
-- ====================================================================
|
||
-- 脚本执行完成
|
||
-- 功能：字典管理数据库表和初始数据已创建完成
|
||
-- ==================================================================== |