[关闭]
@liruiyi962464 2025-04-03T07:18:40.000000Z 字数 16160 阅读 28

对接学校数据库 mysql实现数据库传递数据

mysql

mysql -h 202.207.122.44 -u data_sync -p'L$7cx&6W*7zJ##po'

mysql -u root -p'Zc2024rJ'
USE cpoc_sync
SHOW TABLES;
SELECT VERSION();

  1. -- 确保目标表使用兼容的字符集
  2. ALTER TABLE ODS_SXJCSJ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  3. ALTER TABLE ODS_SXBXGMQKSJ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  4. ALTER TABLE ODS_SXBGJLSJ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  5. ALTER TABLE ODS_TYYHRZDLSJ_SXXT CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  7. ALTER TABLE federated_intern_basic_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  8. ALTER TABLE federated_intern_insurance CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  9. ALTER TABLE federated_intern_report CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  10. ALTER TABLE federated_user_auth_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

开启FEDERATED引擎 B数据到A数据数据同步

  1. -- 检查是否支持FEDERATED引擎
  2. SHOW ENGINES;
  4. -- 如果没有启用，需要在my.cnf/my.ini中添加
  5. [mysqld]
  6. federated
  8. -- 开启后 参考sql执行即可

MySQL 定时任务设置方案

要将你的存储过程设置为每天早上6点自动执行，可以使用 MySQL 的事件调度器(Event Scheduler)。以下是完整的实现步骤：

1. 首先确保 MySQL 事件调度器已启用

  1. -- 检查事件调度器状态
  2. SHOW VARIABLES LIKE 'event_scheduler';
  4. -- 如果未启用，执行以下命令(需要SUPER权限)
  5. SET GLOBAL event_scheduler = ON;

2. 创建每日6点执行的定时事件

  1. DELIMITER //
  3. CREATE EVENT daily_sync_event
  4. ON SCHEDULE
  5.     EVERY 1 DAY
  6.     STARTS TIMESTAMP(CURRENT_DATE, '06:00:00')
  7. DO
  8. BEGIN
  9.     CALL sync_all_data_to_server_b();
  10. END //
  12. DELIMITER ;

3. 完整的事件管理方案

查看所有事件

  1. SHOW EVENTS;

修改事件

  1. ALTER EVENT daily_sync_event
  2. ON SCHEDULE
  3.     EVERY 1 DAY
  4.     STARTS TIMESTAMP(CURRENT_DATE, '06:00:00')
  5. DO
  6.     CALL sync_all_data_to_server_b();

禁用/启用事件

  1. -- 禁用事件
  2. ALTER EVENT daily_sync_event DISABLE;
  4. -- 启用事件
  5. ALTER EVENT daily_sync_event ENABLE;

删除事件

  1. DROP EVENT IF EXISTS daily_sync_event;

4. 注意事项

  1. 权限要求：

    • 创建事件需要 EVENT 权限
    • 执行存储过程需要相应表的操作权限

  2. 错误处理：

    • 你的存储过程中已有错误处理逻辑，事件执行失败会记录错误

  3. 服务器时间：

    • 确保MySQL服务器时区设置正确
    • 检查命令：SELECT @@global.time_zone, @@session.time_zone;

  4. 长期运行：

    • 事件会一直存在直到显式删除
    • 重启MySQL服务不会影响已创建的事件

  5. 日志记录：

    • 建议在存储过程中增加更详细的执行日志记录

sql语句

  1. -- 删除实习成绩数据表
  2. DROP TABLE IF EXISTS ODS_SXJCSJ;
  4. -- 删除实习保险购买情况表
  5. DROP TABLE IF EXISTS ODS_SXBXGMQKSJ;
  7. -- 删除实习报告记录表
  8. DROP TABLE IF EXISTS ODS_SXBGJLSJ;
  10. -- 删除统一用户认证登录数据表
  11. DROP TABLE IF EXISTS ODS_TYYHRZDLSJ_SXXT;
  13. -- 在B服务器上执行以下SQL创建目标表
  14. -- 实习基础数据子类表
  15. CREATE TABLE IF NOT EXISTS ODS_SXJCSX (
  16.       `ZJSJWYXBS` varchar(32) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  17.   `XXDM` varchar(10) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  18.   `XH` varchar(20) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  19.   `XSXM` varchar(108) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  20.   `ZYH` varchar(64) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  21.   `ZYMC` varchar(60) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  22.   `BJMC` varchar(60) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  23.   `XZM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  24.   `XQM` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  25.   `SXSFKS` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  26.   `SXSFJS` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  27.   `SXQYHYLBM` varchar(4) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  28.   `SXNR` text COLLATE utf8mb4_general_ci,
  29.   `SFDK` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  30.   `SXAPM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  31.   `SXXSM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  32.   `SXSC` decimal(5,0) DEFAULT NULL,
  33.   `SXCJ` decimal(20,1) DEFAULT NULL,
  34.   `SXKSRQ` varchar(8) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  35.   `SXJSRQ` varchar(8) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  36.   `SXQYMC` varchar(200) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  37.   `SXDDSZSJGBM` varchar(6) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  38.   `SXDDSZSJGMC` varchar(60) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  39.   `SXDDSZSHJGBM` varchar(6) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  40.   `SXDDSZSHJGMC` varchar(60) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  41.   `XNZDJSGH` varchar(20) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  42.   `XNZDJSXM` varchar(36) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  43.   `NZDJSDH` varchar(30) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  44.   `QYZMZDRYXM` varchar(36) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  45.   `QYZMZDRYDH` varchar(30) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  46.   `ZFSXXSSJBC` varchar(10) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  47.   `XJJDZXDH` varchar(30) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  48.   `SFQDSFXY` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  49.   `SFKSSX` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  50.   `SFFGWSX` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  51.   `RXNF` varchar(4) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  52.   `SJCJSJ` varchar(15) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
  53. )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  55. -- 实习保险购买情况表
  56. CREATE TABLE IF NOT EXISTS ODS_SXBXGMQKSX (
  57.     `ZJSJWYXBS` varchar(32) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  58.   `XXDM` varchar(10) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  59.   `XH` varchar(20) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  60.   `XSXM` varchar(36) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  61.   `BXXZMC` varchar(120) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  62.   `BDH` varchar(64) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  63.   `BXFCZFMC` varchar(200) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  64.   `BXGMRQ` varchar(7) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  65.   `BXGMFM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  66.   `GMBXZLM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  67.   `SJCJSJ` varchar(15) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
  68. )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  70. -- 实习报告记录表
  71. CREATE TABLE IF NOT EXISTS ODS_SXBGJLSX (
  72.       `ZJSJWYXBS` varchar(32) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  73.   `XXDM` varchar(10) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  74.   `XH` varchar(20) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  75.   `XSXM` varchar(108) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  76.   `SXQYMC` varchar(180) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  77.   `SXGWMC` varchar(100) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  78.   `SXZDJS` varchar(36) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  79.   `SXBGLBM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  80.   `SXBGNRZY` varchar(500) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  81.   `SXBGNR` varchar(999) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  82.   `SXBGTJRQ` varchar(8) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  83.   `JSPYZTM` varchar(1) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  84.   `SJCJSJ` varchar(15) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
  85. )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  87. -- 统一用户认证登录数据表
  88. CREATE TABLE IF NOT EXISTS ODS_TYYHRZDLSJ_SXXT (
  89. `ZJSJWYXBS` varchar(32) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  90.   `XXDM` varchar(10) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  91.   `DLZH` varchar(30) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  92.   `JS` varchar(60) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  93.   `SZBMMC` varchar(200) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  94.   `DLSJ` varchar(15) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  95.   `DLIP` varchar(128) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  96.   `ZHZTM` varchar(2) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  97.   `SJCJ` varchar(15) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
  98. )DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  102. -- 在A服务器上执行以下SQL创建FEDERATED表
  104. -- 实习基础数据FEDERATED表
  105. CREATE TABLE IF NOT EXISTS federated_intern_basic_data (
  106.     ZJSJWYXBS VARCHAR(32),
  107.     XXDM VARCHAR(10),
  108.     XH VARCHAR(20),
  109.     XSXM VARCHAR(108),
  110.     ZYH VARCHAR(64),
  111.     ZYMC VARCHAR(60),
  112.     BJMC VARCHAR(60),
  113.     XZM VARCHAR(2),
  114.     XQM VARCHAR(1),
  115.     SXSFKS VARCHAR(1),
  116.     SXSFJS VARCHAR(1),
  117.     SXQYHYLBM VARCHAR(4),
  118.     SXNR TEXT(2000),
  119.     SFDK VARCHAR(1),
  120.     SXAPM VARCHAR(2),
  121.     SXXSM VARCHAR(2),
  122.     SXSC DECIMAL(5,0),
  123.     SXCJ DECIMAL(20,1),
  124.     SXKSRQ VARCHAR(8),
  125.     SXJSRQ VARCHAR(8),
  126.     SXQYMC VARCHAR(200),
  127.     SXDDSZSJGBM VARCHAR(6),
  128.     SXDDSZSJGMC VARCHAR(60),
  129.     SXDDSZSHJGBM VARCHAR(6),
  130.     SXDDSZSHJGMC VARCHAR(60),
  131.     XNZDJSGH VARCHAR(20),
  132.     XNZDJSXM VARCHAR(36),
  133.     NZDJSDH VARCHAR(30),
  134.     QYZMZDRYXM VARCHAR(36),
  135.     QYZMZDRYDH VARCHAR(30),
  136.     ZFSXXSSJBC VARCHAR(10),
  137.     XJJDZXDH VARCHAR(30),
  138.     SFQDSFXY VARCHAR(1),
  139.     SFKSSX VARCHAR(1),
  140.     SFFGWSX VARCHAR(1),
  141.     RXNF VARCHAR(4),
  142.     SJCJSJ VARCHAR(15)
  143. ) ENGINE=FEDERATED 
  144. CONNECTION='mysql://data_sync:L$7cx&6W*7zJ##po@202.207.122.44:3306/cpoc_sync/ODS_SXJCSJ'
  145. DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  147. -- 实习保险购买情况FEDERATED表
  148. CREATE TABLE IF NOT EXISTS federated_intern_insurance (
  149.     ZJSJWYXBS VARCHAR(32),
  150.     XXDM VARCHAR(10),
  151.     XH VARCHAR(20),
  152.     XSXM VARCHAR(36),
  153.     BXXZMC VARCHAR(120),
  154.     BDH VARCHAR(64),
  155.     BXFCZFMC VARCHAR(200),
  156.     BXGMRQ VARCHAR(7),
  157.     BXGMFM VARCHAR(2),
  158.     GMBXZLM VARCHAR(2),
  159.     SJCJSJ VARCHAR(15)
  160. ) ENGINE=FEDERATED 
  161. CONNECTION='mysql://data_sync:L$7cx&6W*7zJ##po@202.207.122.44:3306/cpoc_sync/ODS_SXBXGMQKSJ'
  162. DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  164. -- 实习报告记录FEDERATED表
  165. CREATE TABLE IF NOT EXISTS federated_intern_report (
  166.     ZJSJWYXBS VARCHAR(32),
  167.     XXDM VARCHAR(10),
  168.     XH VARCHAR(20),
  169.     XSXM VARCHAR(108),
  170.     SXQYMC VARCHAR(180),
  171.     SXGWMC VARCHAR(100),
  172.     SXZDJS VARCHAR(36),
  173.     SXBGLBM VARCHAR(2),
  174.     SXBGNRZY VARCHAR(500),
  175.     SXBGNR VARCHAR(999),
  176.     SXBGTJRQ VARCHAR(8),
  177.     JSPYZTM VARCHAR(1),
  178.     SJCJSJ VARCHAR(15)
  179. ) ENGINE=FEDERATED 
  180. CONNECTION='mysql://data_sync:L$7cx&6W*7zJ##po@202.207.122.44:3306/cpoc_sync/ODS_SXBGJLSJ'
  181. DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  183. -- 统一用户认证登录数据FEDERATED表
  184. CREATE TABLE IF NOT EXISTS federated_user_auth_data (
  185.     ZJSJWYXBS VARCHAR(32),
  186.     XXDM VARCHAR(10),
  187.     DLZH VARCHAR(30),
  188.     JS VARCHAR(60),
  189.     SZBMMC VARCHAR(200),
  190.     DLSJ VARCHAR(15),
  191.     DLIP VARCHAR(128),
  192.     ZHZTM VARCHAR(2),
  193.     SJCJ VARCHAR(15)
  194. ) ENGINE=FEDERATED 
  195. CONNECTION='mysql://data_sync:L$7cx&6W*7zJ##po@202.207.122.44:3306/cpoc_sync/ODS_TYYHRZDLSJ_SXXT'
  196. DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  199. -- 在A服务器创建同步存储过程
  200. DELIMITER //
  202. -- 同步实习基础数据
  203. CREATE DEFINER=`root`@`%` PROCEDURE `sync_intern_basic_data`()
  204. BEGIN
  205.  -- 新增字符集设置
  206.     SET NAMES 'utf8mb4';
  207.     SET CHARACTER SET utf8mb4;
  208.     -- 先清空远程表
  209.     TRUNCATE TABLE federated_intern_basic_data;
  211.     -- 插入新数据
  212.     INSERT INTO federated_intern_basic_data
  213.     WITH LatestInternshipApplication AS (
  214.         SELECT stu_id, stage_id,is_matching,in_unit,province,city,guidance_teacher,guidance_phone,recruit_way,
  215.                wsch_tea_name,phone,salary_ship,school_hotline,is_tripartitep,is_cross,
  216.                ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY create_time DESC) AS rn
  217.         FROM zc_internship_application
  218.         WHERE process = 6
  219.     ),
  220.     LatestWeek AS (
  221.         SELECT
  222.         ss.user_id,
  223.         ROUND(IFNULL(ws.week_score, 0), 2) AS 周记成绩,
  224.         ROUND(IFNULL(r.report_score, 0), 2) AS 实习报告成绩,
  225.         ROUND(IFNULL(sc.company_one, 0), 2) AS 企业评分,
  226.         ROUND(
  227.             IFNULL(sc.company_one, 0) * 0.7 +
  228.             (IFNULL(ws.week_score, 0) * 0.6 + IFNULL(r.report_score, 0) * 0.4) * 0.3,
  229.             2
  230.         ) AS average_evaluation,
  231.         ss.stage_id
  232.     FROM
  233.         zc_student_info ss
  234.         INNER JOIN zc_major_info m ON ss.major_id = m.id
  235.         INNER JOIN zc_teacher_info tea ON ss.sch_teacher = tea.id
  236.         INNER JOIN zc_pastern zp ON ss.pastern_id = zp.id
  237.         LEFT JOIN (
  238.             SELECT
  239.                 si.id,
  240.                 si.stu_name,
  241.                 w.stage_id,
  242.                 IF(
  243.                     SUM(
  244.                         CASE 
  245.                             WHEN p.teacher_evaluation REGEXP '^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)$' 
  246.                             THEN CAST(p.teacher_evaluation AS DECIMAL(10,2)) 
  247.                             ELSE 0 
  248.                         END
  249.                     ) / 20 > 100, 
  250.                     100, 
  251.                     SUM(
  252.                         CASE 
  253.                             WHEN p.teacher_evaluation REGEXP '^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)$' 
  254.                             THEN CAST(p.teacher_evaluation AS DECIMAL(10,2)) 
  255.                             ELSE 0 
  256.                         END
  257.                     ) / 20
  258.                 ) AS week_score
  259.             FROM
  260.                 zc_week w
  261.                 INNER JOIN zc_process p ON w.id = p.day_id AND p.state = 0
  262.                 INNER JOIN zc_student_info si ON w.create_by = si.user_id
  263.                 INNER JOIN zc_internship_stage i ON w.stage_id = i.id
  264.             WHERE
  265.                 i.is_end = '0' AND i.del_flag = '0'
  266.             GROUP BY
  267.                 si.id, si.stu_name, w.stage_id
  268.         ) ws ON ss.id = ws.id
  269.         LEFT JOIN (
  270.             SELECT
  271.                 ir.student_user_id AS stu_id,
  272.                 ir.stage_id,
  273.                 ir.evaluate_score AS report_score
  274.             FROM
  275.                 zc_internship_report ir
  276.                 INNER JOIN zc_internship_stage i ON ir.stage_id = i.id
  277.             WHERE
  278.                 ir.evaluate_state = 2
  279.             GROUP BY
  280.                 ir.student_user_id, ir.stage_id, ir.evaluate_score
  281.         ) r ON ss.user_id = r.stu_id
  282.         LEFT JOIN zc_student_score sc ON ss.id = sc.stu_id
  283.         INNER JOIN zc_internship_stage i ON ss.stage_id = i.id AND i.is_end = '0' AND i.del_flag = '0'
  284.     )
  285.     SELECT DISTINCT
  286.         MD5(s.id) AS ZJSJWYXBS,
  287.         '4113013397' AS XXDM,
  288.         s.stu_num AS XH,
  289.         s.stu_name AS XSXM,
  290.         '' AS ZYH,
  291.         m.major_name AS ZYMC,
  292.         c.class_name AS BJMC,
  293.         '' AS XZM,
  294.         '' AS XQM,
  295.         CASE
  296.             WHEN NOW() >= i.internship_start_time THEN 1
  297.             ELSE 0
  298.         END AS SXSFKS,
  299.         CASE
  300.             WHEN NOW() > i.internship_end_time THEN 1
  301.             ELSE 0
  302.         END AS SXSFJS,
  303.         '' AS SXQYHYLBM,
  304.         i.stage_name AS SXNR,
  305.         COALESCE(la.is_matching, 0) AS SFDK,
  306.         CASE WHEN la.recruit_way = 0 THEN 2 ELSE la.recruit_way END AS SXAPM,
  307.         '1' AS SXXSM,
  308.         COALESCE(CEILING(DATEDIFF(CURDATE(), i.internship_start_time) / 7.0), 0) AS SXSC,
  309.         COALESCE(lw.average_evaluation, 0) AS SXCJ,
  310.         DATE_FORMAT(i.internship_start_time, '%Y%m%d') AS SXKSRQ,
  311.         DATE_FORMAT(i.internship_end_time, '%Y%m%d') AS SXJSRQ,
  312.         la.in_unit AS SXQYMC,
  313.         '' AS SXDDSZSJGBM,
  314.         la.province AS SXDDSZSJGMC,
  315.         '' AS SXDDSZSHJGBM,
  316.         la.city AS SXDDSZSHJGMC,
  317.         '' AS XNZDJSGH,
  318.         la.guidance_teacher AS XNZDJSXM,
  319.         la.guidance_phone AS NZDJSDH,
  320.         la.wsch_tea_name AS QYZMZDRYXM,
  321.         la.phone AS QYZMZDRYDH,
  322.          CASE
  323.     WHEN la.salary_ship IS NULL THEN 0
  324.     WHEN la.salary_ship = '' THEN 0
  325.     WHEN la.salary_ship REGEXP '^[+-]?([0-9]+([.][0-9]*)?|[.][0-9]+)$' THEN 
  326.         CAST(la.salary_ship AS DECIMAL(10,2))
  327.     ELSE 0 
  328. END AS ZFSXXSSJBC,
  329.         la.school_hotline AS XJJDZXDH,
  330.         la.is_tripartitep AS SFQDSFXY,
  331.         la.is_cross AS SFKSSX,
  332.         '' AS SFFGWSX,
  333.         g.grade_name AS RXNF,
  334.         DATE_FORMAT(NOW(), '%Y%m%d% %H%i%s') AS SJCJSJ
  335.     FROM
  336.         zc_student_info s
  337.     LEFT JOIN zc_grade_info g ON s.grade_id = g.id
  338.     LEFT JOIN zc_pastern p ON s.pastern_id = p.id
  339.     LEFT JOIN zc_major_info m ON s.major_id = m.id
  340.     LEFT JOIN zc_class c ON s.class_id = c.id
  341.     LEFT JOIN zc_internship_stage i ON s.stage_id = i.id
  342.     LEFT JOIN LatestInternshipApplication la ON s.id = la.stu_id AND la.rn = 1 AND la.stage_id = i.id
  343.     LEFT JOIN LatestWeek lw ON lw.user_id = s.user_id AND lw.stage_id = i.id
  344.     WHERE s.del_flag = 0 AND i.is_end = '0' AND i.del_flag = '0'
  345.     ORDER BY p.pastern_name, g.grade_name, m.major_name, c.class_name, s.stu_num;
  347.     SELECT CONCAT('实习基础数据同步完成，影响行数: ', ROW_COUNT()) AS message;
  348. END //
  350. -- 同步实习保险购买情况
  351. CREATE PROCEDURE sync_intern_insurance()
  352. BEGIN
  353.  -- 新增字符集设置
  354.     SET NAMES 'utf8mb4';
  355.     SET CHARACTER SET utf8mb4;
  356.     TRUNCATE TABLE federated_intern_insurance;
  358.     INSERT INTO federated_intern_insurance
  359.     WITH LatestInternshipApplication AS (
  360.         SELECT stu_id, stage_id,insurance_type,policy,insurance_bution,
  361.                ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY create_time DESC) AS rn
  362.         FROM zc_internship_application
  363.     )
  364.     SELECT DISTINCT
  365.         MD5(s.id) AS ZJSJWYXBS,
  366.         '4113013397' AS XXDM,
  367.         s.stu_num AS XH,
  368.         s.stu_name AS XSXM,
  369.         la.insurance_type AS BXXZMC,
  370.         la.policy AS BDH,
  371.         la.insurance_bution AS BXFCZFMC,
  372.         '' AS BXGMRQ,
  373.         '' AS BXGMFM,
  374.         '' AS GMBXZLM,
  375.         DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') AS SJCJSJ
  376.     FROM
  377.         zc_student_info s
  378.     LEFT JOIN zc_grade_info g ON s.grade_id = g.id
  379.     LEFT JOIN zc_pastern p ON s.pastern_id = p.id
  380.     LEFT JOIN zc_major_info m ON s.major_id = m.id
  381.     LEFT JOIN zc_class c ON s.class_id = c.id
  382.     LEFT JOIN zc_internship_stage i ON s.stage_id = i.id
  383.     LEFT JOIN LatestInternshipApplication la ON s.id = la.stu_id AND la.rn = 1 AND la.stage_id = i.id
  384.     WHERE s.del_flag = 0 AND i.is_end = '0' AND i.del_flag = '0'
  385.     ORDER BY p.pastern_name, g.grade_name, m.major_name, c.class_name, s.stu_num;
  387.     SELECT CONCAT('实习保险数据同步完成，影响行数: ', ROW_COUNT()) AS message;
  388. END //
  390. -- 同步实习报告记录
  391. CREATE PROCEDURE sync_intern_report()
  392. BEGIN
  393.  -- 新增字符集设置
  394.     SET NAMES 'utf8mb4';
  395.     SET CHARACTER SET utf8mb4;
  396.     TRUNCATE TABLE federated_intern_report;
  398.     INSERT INTO federated_intern_report
  399.     WITH LatestInternshipApplication AS (
  400.         SELECT stu_id, stage_id, in_unit, internship, guidance_teacher, guidance_phone,
  401.                ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY create_time DESC) AS rn
  402.         FROM zc_internship_application
  403.         WHERE process = 6
  404.     ),
  405.     LatestWeek AS (
  406.     SELECT zw.id,zw.create_by, zw.content, DATE_FORMAT(zw.create_time, '%Y%m%d') AS create_time, zp.state, zw.title,
  407.            zw.stage_id,zp.teacher_comments,zp.teacher_evaluation
  408.     FROM zc_student_info st
  409.     INNER JOIN zc_week zw ON st.user_id = zw.create_by and zw.state != 1
  410.     INNER JOIN zc_internship_stage sta ON st.stage_id = sta.id
  411.     LEFT JOIN zc_process zp ON zp.day_id = zw.id
  412.     WHERE st.del_flag = '0' AND sta.is_end = '0' AND sta.del_flag = '0'
  413.     )
  414.     SELECT 
  415.         MD5(s.id) AS ZJSJWYXBS,
  416.         '4113013397' AS XXDM,
  417.         s.stu_num AS XH,
  418.         s.stu_name AS XSXM,
  419.         la.in_unit AS SXQYMC,
  420.         la.internship AS SXGWMC,
  421.         la.guidance_teacher AS SXZDJS,
  422.         '1' AS SXBGLBM,
  423.         lw.content AS SXBGNRZY,
  424.         lw.content AS SXBGNR,
  425.         DATE_FORMAT(lw.create_time, '%Y%m%d') AS SXBGTJRQ,
  426.         CASE lw.state WHEN 0 THEN '1' WHEN 2 THEN '2' ELSE '2' END AS JSPYZTM,
  427.         DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') AS SJCJSJ
  428. FROM
  429.     zc_student_info s
  430. LEFT JOIN zc_grade_info g ON s.grade_id = g.id
  431. JOIN zc_pastern p ON s.pastern_id = p.id 
  432. LEFT JOIN zc_major_info m ON s.major_id = m.id
  433. LEFT JOIN zc_class c ON s.class_id = c.id
  434. INNER JOIN zc_internship_stage i ON s.stage_id = i.id
  435. INNER JOIN LatestInternshipApplication la ON s.id = la.stu_id AND la.stage_id = i.id
  436. INNER JOIN LatestWeek lw ON lw.create_by = s.user_id 
  437. WHERE s.del_flag = 0 AND i.is_end = '0' AND i.del_flag = '0'
  438. ORDER BY p.pastern_name, g.grade_name, m.major_name, c.class_name, s.stu_num, la.in_unit, la.guidance_teacher, la.internship, i.stage_name;
  440.     SELECT CONCAT('实习报告数据同步完成，影响行数: ', ROW_COUNT()) AS message;
  441. END //
  443. -- 同步统一用户认证登录数据
  444. CREATE PROCEDURE sync_user_auth_data()
  445. BEGIN
  446.  -- 新增字符集设置
  447.     SET NAMES 'utf8mb4';
  448.     SET CHARACTER SET utf8mb4;
  449.     TRUNCATE TABLE federated_user_auth_data;
  451.     INSERT INTO federated_user_auth_data
  452.     WITH LatestSysUserRole AS (
  453.         SELECT
  454.         su.id,
  455.         GROUP_CONCAT(sr.role_name ORDER BY sr.role_name ASC SEPARATOR ',') AS role_names
  456.     FROM
  457.         sys_user su
  458.     JOIN
  459.         sys_user_role sur ON su.id = sur.user_id
  460.     JOIN
  461.         sys_role sr ON sur.role_id = sr.id
  462.     GROUP BY
  463.         su.id
  464.     )
  465.     SELECT distinct
  466.         MD5(su.id) AS ZJSJWYXBS,
  467.         '4113013397' AS XXDM,
  468.         sl.username AS DLZH,
  469.         ls.role_names AS JS,
  470.         zd.dept_name AS SZBMMC,
  471.         DATE_FORMAT(sl.create_time, '%Y%m%d% %H%i%s') AS DLSJ,
  472.         sl.ip AS DLIP,
  473.         1 AS ZHZTM,
  474.         DATE_FORMAT(NOW(), '%Y%m%d% %H%i%s') AS SJCJ
  475.     FROM sys_log sl
  476.         JOIN
  477.         sys_user su ON sl.userid = su.username AND sl.userid != 'admin'
  478.         JOIN
  479.         LatestSysUserRole ls ON su.id = ls.id
  480.         LEFT JOIN
  481.         zc_teacher_info zti ON su.id = zti.user_id
  482.         LEFT JOIN
  483.         zc_depart zd ON zd.id = zti.depart
  484.     where  sl.log_type = 1
  485.     order by sl.create_time desc;
  487.     SELECT CONCAT('用户认证数据同步完成，影响行数: ', ROW_COUNT()) AS message;
  488. END //
  490. -- 创建总控存储过程，同步所有数据
  491. CREATE PROCEDURE sync_all_data_to_server_b()
  492. BEGIN
  493.     DECLARE exit handler for sqlexception
  494.     BEGIN
  495.         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
  496.         @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
  497.         SELECT CONCAT('Error occurred: ', @errno, ' (', @sqlstate, '): ', @text) AS error_message;
  498.         ROLLBACK;
  499.     END;
  501.     START TRANSACTION;
  503.     CALL sync_intern_basic_data();
  504.     CALL sync_intern_insurance();
  505.     CALL sync_intern_report();
  506.     CALL sync_user_auth_data();
  508.     COMMIT;
  510.     SELECT '所有数据同步完成' AS message;
  511. END //
  513. DELIMITER ;
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注