赵忠林
07bc994eba
- 修改所有实体类@TableName注解的表名，添加gxwebsoft_core数据库前缀 - 更新各XML Mapper中的SQL语句表名，统一加上gxwebsoft_core前缀 - 调整关联查询和多个子查询中的表名对应关系，确保与实体类一致 - 涉及实体类包括Company、CompanyComment、Dict、User等多个核心业务表 - 保持代码逻辑和功能不变，确保数据库访问路径更新规范化
125 lines
5.5 KiB
SQL
125 lines
5.5 KiB
SQL
-- =====================================================================
|
||
-- 检查 gxwebsoft_core 库缺失的系统表（sys_ 前缀）
|
||
-- 用途：代码已把所有 @TableName / Mapper SQL 指向 gxwebsoft_core.sys_xxx，
|
||
-- 本脚本用于确认 gxwebsoft_core 库里这 31 张系统表是否都已建好。
|
||
-- 用法：在能访问 gxwebsoft_core 的 MySQL 连接里执行。
|
||
-- =====================================================================
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 方式一（推荐）：一次性列出「缺失的表」。结果集为空 = 全部齐全。
|
||
-- ---------------------------------------------------------------------
|
||
SELECT t.table_name AS missing_table
|
||
FROM (
|
||
SELECT 'sys_company' AS table_name UNION ALL
|
||
SELECT 'sys_company_comment' UNION ALL
|
||
SELECT 'sys_company_content' UNION ALL
|
||
SELECT 'sys_company_git' UNION ALL
|
||
SELECT 'sys_company_parameter' UNION ALL
|
||
SELECT 'sys_company_url' UNION ALL
|
||
SELECT 'sys_dict' UNION ALL
|
||
SELECT 'sys_dict_data' UNION ALL
|
||
SELECT 'sys_dictionary' UNION ALL
|
||
SELECT 'sys_dictionary_data' UNION ALL
|
||
SELECT 'sys_domain' UNION ALL
|
||
SELECT 'sys_email_record' UNION ALL
|
||
SELECT 'sys_file_record' UNION ALL
|
||
SELECT 'sys_login_record' UNION ALL
|
||
SELECT 'sys_menu' UNION ALL
|
||
SELECT 'sys_operation_record' UNION ALL
|
||
SELECT 'sys_organization' UNION ALL
|
||
SELECT 'sys_payment' UNION ALL
|
||
SELECT 'sys_plug' UNION ALL
|
||
SELECT 'sys_role' UNION ALL
|
||
SELECT 'sys_role_menu' UNION ALL
|
||
SELECT 'sys_setting' UNION ALL
|
||
SELECT 'sys_tenant' UNION ALL
|
||
SELECT 'sys_user' UNION ALL
|
||
SELECT 'sys_user_balance_log' UNION ALL
|
||
SELECT 'sys_user_collection' UNION ALL
|
||
SELECT 'sys_user_file' UNION ALL
|
||
SELECT 'sys_user_referee' UNION ALL
|
||
SELECT 'sys_user_role' UNION ALL
|
||
SELECT 'sys_user_role_extend' UNION ALL
|
||
SELECT 'sys_user_verify'
|
||
) t
|
||
LEFT JOIN information_schema.tables ist
|
||
ON ist.table_schema = 'gxwebsoft_core'
|
||
AND ist.table_name = t.table_name
|
||
WHERE ist.table_name IS NULL
|
||
ORDER BY t.table_name;
|
||
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 方式二：逐张列出状态（EXISTS / MISSING），一目了然看全貌。
|
||
-- ---------------------------------------------------------------------
|
||
SELECT
|
||
t.table_name,
|
||
CASE WHEN ist.table_name IS NULL THEN 'MISSING' ELSE 'EXISTS' END AS status
|
||
FROM (
|
||
SELECT 'sys_company' AS table_name UNION ALL
|
||
SELECT 'sys_company_comment' UNION ALL
|
||
SELECT 'sys_company_content' UNION ALL
|
||
SELECT 'sys_company_git' UNION ALL
|
||
SELECT 'sys_company_parameter' UNION ALL
|
||
SELECT 'sys_company_url' UNION ALL
|
||
SELECT 'sys_dict' UNION ALL
|
||
SELECT 'sys_dict_data' UNION ALL
|
||
SELECT 'sys_dictionary' UNION ALL
|
||
SELECT 'sys_dictionary_data' UNION ALL
|
||
SELECT 'sys_domain' UNION ALL
|
||
SELECT 'sys_email_record' UNION ALL
|
||
SELECT 'sys_file_record' UNION ALL
|
||
SELECT 'sys_login_record' UNION ALL
|
||
SELECT 'sys_menu' UNION ALL
|
||
SELECT 'sys_operation_record' UNION ALL
|
||
SELECT 'sys_organization' UNION ALL
|
||
SELECT 'sys_payment' UNION ALL
|
||
SELECT 'sys_plug' UNION ALL
|
||
SELECT 'sys_role' UNION ALL
|
||
SELECT 'sys_role_menu' UNION ALL
|
||
SELECT 'sys_setting' UNION ALL
|
||
SELECT 'sys_tenant' UNION ALL
|
||
SELECT 'sys_user' UNION ALL
|
||
SELECT 'sys_user_balance_log' UNION ALL
|
||
SELECT 'sys_user_collection' UNION ALL
|
||
SELECT 'sys_user_file' UNION ALL
|
||
SELECT 'sys_user_referee' UNION ALL
|
||
SELECT 'sys_user_role' UNION ALL
|
||
SELECT 'sys_user_role_extend' UNION ALL
|
||
SELECT 'sys_user_verify'
|
||
) t
|
||
LEFT JOIN information_schema.tables ist
|
||
ON ist.table_schema = 'gxwebsoft_core'
|
||
AND ist.table_name = t.table_name
|
||
ORDER BY status DESC, t.table_name;
|
||
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 方式三：统计汇总（应有 31 张，看实际存在几张）。
|
||
-- ---------------------------------------------------------------------
|
||
SELECT
|
||
31 AS expected_count,
|
||
COUNT(*) AS existing_count,
|
||
31 - COUNT(*) AS missing_count
|
||
FROM information_schema.tables
|
||
WHERE table_schema = 'gxwebsoft_core'
|
||
AND table_name IN (
|
||
'sys_company','sys_company_comment','sys_company_content','sys_company_git',
|
||
'sys_company_parameter','sys_company_url','sys_dict','sys_dict_data',
|
||
'sys_dictionary','sys_dictionary_data','sys_domain','sys_email_record',
|
||
'sys_file_record','sys_login_record','sys_menu','sys_operation_record',
|
||
'sys_organization','sys_payment','sys_plug','sys_role','sys_role_menu',
|
||
'sys_setting','sys_tenant','sys_user','sys_user_balance_log',
|
||
'sys_user_collection','sys_user_file','sys_user_referee','sys_user_role',
|
||
'sys_user_role_extend','sys_user_verify'
|
||
);
|
||
|
||
|
||
-- ---------------------------------------------------------------------
|
||
-- 附：确认连接账号能访问 gxwebsoft_core（库是否存在 + 是否有权限）。
|
||
-- 若这条查不到结果，说明库不存在或当前账号无权限。
|
||
-- ---------------------------------------------------------------------
|
||
SELECT schema_name
|
||
FROM information_schema.schemata
|
||
WHERE schema_name = 'gxwebsoft_core';
|