MySQL存储过程同步表数据到其他IP数据库

{"title":"MySQL存储过程同步表数据到其他IP数据库","description":"该存储过程用于将MySQL表数据同步到其他IP地址的MySQL数据库中，并提供完整的示例代码。","keywords":"MySQL, 存储过程, 同步数据, 其他IP数据库, 数据同步","content":"CREATE DEFINER=wmsop@10.23.60.218 PROCEDURE sp_copy_arch_cubicscanner_to_wms(\n IN old_table VARCHAR(50),\n IN new_table VARCHAR(50),\n IN sync_table VARCHAR(50),\n IN record_type VARCHAR(50),\n IN target_ip VARCHAR(15),\n IN target_username VARCHAR(50),\n IN target_password VARCHAR(50),\n IN target_database VARCHAR(50)\n)\nBEGIN\n -- 定义变量\n SET @old_table := old_table; -- 数量来源表\n SET @new_table := new_table; -- 数据目的表\n SET @sync_table := sync_table; -- 同步表\n SET @record_type := record_type; -- 同步标识\n SET @target_ip := target_ip; -- 目标数据库IP\n SET @target_username := target_username; -- 目标数据库用户名\n SET @target_password := target_password; -- 目标数据库密码\n SET @target_database := target_database; -- 目标数据库名称\n SET @last_sync_date_time := "1970-01-01 00:00:01";\n\n -- 获取最新同步时间\n SET @get_sync_time_sql := CONCAT("SELECT @last_sync_date_time:= IFNULL(MIN(lastSyncDateTime),@last_sync_date_time) \n FROM ", @sync_table, " WHERE recordType = @record_type");\n PREPARE get_sync_time_sql FROM @get_sync_time_sql;\n\n -- 批量插入新表数据\n SET @insert_new_table_sql := CONCAT("INSERT INTO ", @target_database, ".", @new_table, " \n SELECT * FROM ", @target_database, ".", @old_table, " WHERE lastUpdateData >= @last_sync_date_time LIMIT 1000");\n PREPARE insert_new_table_sql FROM @insert_new_table_sql;\n\n -- 更新同步后数据时间\n SET @update_new_table_sql := CONCAT("SELECT @last_sync_date_time := IFNULL(MAX(lastUpdateData), @last_sync_date_time) \n FROM ", @target_database, ".", @new_table, " WHERE lastUpdateData >= @last_sync_date_time");\n PREPARE update_new_table_sql FROM @update_new_table_sql;\n\n -- 更新sync时间\n SET @update_sync_time_sql := CONCAT("REPLACE INTO ", @target_database, ".", @sync_table, " (recordType, lastSyncDateTime) VALUES (@record_type, @last_sync_date_time)");\n PREPARE update_sync_time_sql FROM @update_sync_time_sql;\n\n -- 连接目标数据库\n SET @connect_target_db_sql := CONCAT("USE ", @target_database);\n PREPARE connect_target_db_sql FROM @connect_target_db_sql;\n\n -- 执行目标数据库操作\n SET @execute_target_db_sql := CONCAT(\n "CALL sp_copy_arch_cubicscanner_to_wms("", @old_table, "", "", @new_table, "", "", @sync_table, "", "", @record_type, "")"\n );\n PREPARE execute_target_db_sql FROM @execute_target_db_sql;\n\n -- 连接目标数据库\n EXECUTE connect_target_db_sql;\n\n -- 获取最新同步时间\n EXECUTE get_sync_time_sql;\n\n -- 批量插入新表数据\n EXECUTE insert_new_table_sql;\n\n -- 更新同步后数据时间\n EXECUTE update_new_table_sql;\n\n -- 更新sync时间\n EXECUTE update_sync_time_sql;\n\n -- 断开目标数据库连接\n DEALLOCATE PREPARE connect_target_db_sql;\n\n -- 同步到其他IP的MySQL数据库中\n SET @sync_to_target_db_sql := CONCAT(\n "CALL sp_copy_arch_cubicscanner_to_wms("", @old_table, "", "", @new_table, "", "", @sync_table, "", "", @record_type, "", "", @target_ip, "", "", @target_username, "", "", @target_password, "", "", @target_database, "")"\n );\n PREPARE sync_to_target_db_sql FROM @sync_to_target_db_sql;\n\n -- 执行同步到其他IP的MySQL数据库中\n EXECUTE sync_to_target_db_sql;\n\n -- 断开目标数据库连接\n DEALLOCATE PREPARE execute_target_db_sql;\nEND