最近有一需求，要同步对方数据库数据到我们自己这边测试，mysql--> mysql的方式,但是对方数据库数据更新有几大特点
1：表稍微小点的，每天直接干掉所有数据后重新插入数据
2：大表会把某个时间段的数据全部干掉然后重新重新插入数据
3：有的表字段不设置默认值
除了上面几个问题，实际业务中也存在一些数据类型的问题，最后只能在字段中做处理，处理办法如下面最终的sql
SELECT
IFNULL(penalty_id, 0) penalty_id,
IFNULL(company_id, 0) company_id,
IFNULL(org_company_id, 0) org_company_id,
IFNULL(company_code, 0) company_code,
IFNULL(company_name, 0) company_name,
IFNULL(company_type, 0) company_type,
IFNULL(company_industry, 0) company_industry,
CAST(IFNULL(current_score, 0) AS CHAR(50)) AS current_score,
CAST(
IFNULL(deducting_score, 0) AS CHAR(50)
) AS deducting_score,
DATE_FORMAT(
IFNULL(
deducting_timepoint,
"1991-01-01 00:00:00"
),
"%Y-%m-%d"
) deducting_timepoint,
IFNULL(deducting_type, 0) deducting_type,
IFNULL(deducting_content, 0) deducting_content,
IFNULL(related_announcement, 0) related_announcement,
IFNULL(related_announcement_url, 0) related_announcement_url,
IFNULL(related_law_name, 0) related_law_name,
IFNULL(related_law, 0) related_law,
DATE_FORMAT(
IFNULL(
create_time,
"1991-01-01 00:00:00"
),
"%Y-%m-%d"
) create_time,
DATE_FORMAT(
IFNULL(
update_time,
"1991-01-01 00:00:00"
),
"%Y-%m-%d"
) update_time,
IFNULL(batch_no, 0) batch_no,
IFNULL(audit_state, 0) audit_state,
IFNULL(audit_state_desc, 0) audit_state_desc,
DATE_FORMAT(
IFNULL(
audit_time,
"1991-01-01 00:00:00"
),
"%Y-%m-%d"
) audit_time,
IFNULL(rule, 0) rule,
IFNULL(note, 0) note,
IFNULL(extend_1, 0) extend_1,
IFNULL(extend_2, 0) extend_2,
IFNULL(extend_3, 0) extend_3
FROM
cfcm_fortune_penalty_score
WHERE TO_DAYS(NOW()) - TO_DAYS(update_time) = 0
没有采用binlog去同步数据是因为考虑到上面数据问题外,还存在其他一些问题，因为采用这种低效的方式
