85083ab962
完善官网、管理后台与后端的多语言内容编辑和发布流程，调整站点页面及相关接口，并同步数据库脚本。 新增容器部署配置，改用环境变量注入数据库凭据；补充仓库忽略规则，并停止跟踪本地环境文件和 Maven 构建产物。
94 lines
3.8 KiB
SQL
94 lines
3.8 KiB
SQL
-- =====================================================================
|
||
-- 修复库结构/数据常见问题（基于 2026-05-09 导出库核对）
|
||
-- 1) sys_menu 根节点被误写成「菜单管理」且 parent_id=1，导致侧栏树异常
|
||
-- 2) 缺少 menu_id=2/3/102，监控/工具目录与菜单管理入口悬空
|
||
-- 3) huayang_site_config 重复 contact_* 键（保存联系我们页时重复 INSERT）
|
||
-- 4) huayang_i18n.i18n_value 仍为 varchar(500) 时扩展为 LONGTEXT
|
||
-- 执行环境：MySQL 8+
|
||
-- 幂等：可重复执行
|
||
-- =====================================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- ---------- 1. 修复 sys_menu 根目录 ----------
|
||
UPDATE `sys_menu`
|
||
SET
|
||
`menu_name` = '系统管理',
|
||
`parent_id` = 0,
|
||
`order_num` = 1,
|
||
`path` = 'system',
|
||
`component` = NULL,
|
||
`perms` = '',
|
||
`icon` = 'system',
|
||
`menu_type` = 'M',
|
||
`remark` = '系统管理目录'
|
||
WHERE `menu_id` = 1;
|
||
|
||
INSERT INTO `sys_menu` (
|
||
`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`,
|
||
`query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`,
|
||
`perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`
|
||
)
|
||
SELECT
|
||
2, '系统监控', 0, 2, 'monitor', NULL,
|
||
'', '', 1, 0, 'M', '0', '0',
|
||
'', 'monitor', 'admin', NOW(), '', NULL, '系统监控目录'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `menu_id` = 2);
|
||
|
||
INSERT INTO `sys_menu` (
|
||
`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`,
|
||
`query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`,
|
||
`perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`
|
||
)
|
||
SELECT
|
||
3, '系统工具', 0, 3, 'tool', NULL,
|
||
'', '', 1, 0, 'M', '0', '0',
|
||
'', 'tool', 'admin', NOW(), '', NULL, '系统工具目录'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `menu_id` = 3);
|
||
|
||
INSERT INTO `sys_menu` (
|
||
`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`,
|
||
`query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`,
|
||
`perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`
|
||
)
|
||
SELECT
|
||
102, '菜单管理', 1, 3, 'menu', 'system/menu/index',
|
||
'', '', 1, 0, 'C', '0', '0',
|
||
'system:menu:list', 'tree-table', 'admin', NOW(), '', NULL, '菜单管理菜单'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `menu_id` = 102);
|
||
|
||
-- 普通角色补挂目录与菜单管理（按钮权限 1012~1015 的父菜单）
|
||
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
SELECT 2, 2 FROM DUAL
|
||
WHERE EXISTS (SELECT 1 FROM `sys_role` WHERE `role_id` = 2)
|
||
AND NOT EXISTS (SELECT 1 FROM `sys_role_menu` WHERE `role_id` = 2 AND `menu_id` = 2);
|
||
|
||
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
SELECT 2, 3 FROM DUAL
|
||
WHERE EXISTS (SELECT 1 FROM `sys_role` WHERE `role_id` = 2)
|
||
AND NOT EXISTS (SELECT 1 FROM `sys_role_menu` WHERE `role_id` = 2 AND `menu_id` = 3);
|
||
|
||
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
|
||
SELECT 2, 102 FROM DUAL
|
||
WHERE EXISTS (SELECT 1 FROM `sys_role` WHERE `role_id` = 2)
|
||
AND NOT EXISTS (SELECT 1 FROM `sys_role_menu` WHERE `role_id` = 2 AND `menu_id` = 102);
|
||
|
||
-- ---------- 2. 去重站点配置 contact_*（保留最小 config_id） ----------
|
||
DELETE t FROM `huayang_site_config` t
|
||
INNER JOIN (
|
||
SELECT `config_key`, MIN(`config_id`) AS keep_id
|
||
FROM `huayang_site_config`
|
||
WHERE `config_key` IN ('contact_phone', 'contact_email', 'contact_address')
|
||
GROUP BY `config_key`
|
||
HAVING COUNT(*) > 1
|
||
) d ON t.`config_key` = d.`config_key` AND t.`config_id` > d.keep_id;
|
||
|
||
-- ---------- 3. i18n 富文本字段（与 patch_i18n_value_to_longtext.sql 一致） ----------
|
||
ALTER TABLE `huayang_i18n`
|
||
MODIFY COLUMN `i18n_value` LONGTEXT
|
||
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
|
||
NOT NULL COMMENT '翻译值（支持富文本，HTML/Markdown 等）';
|