Syncing Wangdiantong Sales Orders into MySQL: An Incremental Landing Practice on Qeasy

What This Strategy Solves

In e-commerce operations, Wangdiantong handles order aggregation and pre-fulfillment work, while downstream BI, ERP, and warehouse accounting systems need a stable, time-replayable snapshot of sales orders. This single strategy answers one question only: incrementally pull Wangdiantong sales orders by modified time and land them into the enterprise MySQL — one header table plus one line-item table — for downstream consumption. It does not handle shipping or refund reconciliation; it simply moves the order snapshot into MySQL so that downstream systems do not have to repeatedly call the upstream API.

Data Flow and Field Mapping

Data originates from the Wangdiantong WebAPI (wdt.trade.query), passes through the Qeasy middleware for field cleansing, status code normalization, and null-value handling, and is then written to MySQL via two SQL statements: the header table jry_wdt_sales_trade for the master record, and jry_wdt_sales_trade_goods_list for the 1:N line items.

Key field mapping (excerpt):

How to Configure on Qeasy

1. Source platform: choose Wangdiantong·Enterprise Qimen, pick API wdt.trade.query, fix the incremental field to modified, and use start_time / end_time as the time window.
2. Target platform: choose MySQL, action type EXECUTE, and use REPLACE INTO for the main statement — not INSERT. This lets an updated upstream record overwrite in place without a SELECT-then-decide cycle. The child table uses the 1:N extended parameter goods_list, and the sub-statement also uses REPLACE INTO.
3. Encoding and mapping: keep all numeric code fields such as trade_status managed centrally in the Qeasy field-mapping layer rather than hardcoded in SQL. A pattern we see across many Qeasy retail customers is to push enums like "order status" and "refund status" into a single dictionary table and JOIN it beside the main SQL, so that code changes touch one place.
4. Null handling: for fields that legitimately allow empty values such as warehouse_no and customer_no, explicitly convert empty strings to NULL in the middleware layer so that dirty data does not pollute foreign keys.
5. Scheduling: source crontab */13 * * * *, target crontab 3-59/13 * * * *, staggered by 3 minutes so both sides never compete for the same window.

Implementation Steps

1. Initial full load: set start_time to a point far earlier than the business origin, dump all historical orders into MySQL in a one-shot run, then close that entry point.
2. Incremental anchor: take the end of the initial full load as LAST_SYNC_TIME, then every 13 minutes pull orders whose modified falls within the current window.
3. Dual-track transition: once the incremental job is running, keep the full-load script as a safety net and continuously compare trade_no counts on both sides; only retire the full-load job after one stable week. Many Qeasy retail customers adopt this "incremental + full-load dual-track" handover rhythm.
4. Phased header and body: stabilize the header first (verify by deduplicating and counting on trade_id), then release the line-item child table. Avoid opening both at once — that is a classic way to crash goods_list.
5. Scheduling frequency: 13 minutes per round is enough in steady state. Before peak hours you can temporarily shorten to 5 minutes, but never go below the source-side rate limit.

Pitfall Review

• The cost of REPLACE: REPLACE INTO deletes the old row and inserts a new one when the key collides, so auto-increment IDs shift. If any downstream chain partitions messages by auto-increment ID, the deduplication key must be the business key (such as trade_id + rec_id), not the auto-increment ID.
• Lost orders inside the time window: Wangdiantong's modified is the last-modified time. If the same order is modified twice inside one sync window, only the final state is captured and intermediate states are lost. For status-machine-sensitive consumers (for example finance), also land modified on the line-item child table so you can reconstruct later.
• Empty string vs. NULL: in MySQL, '' and NULL behave differently under unique indexes. Fields like receiver mobile and ID card number, once stored as '', will not be matched by WHERE receiver_mobile IS NOT NULL. Always normalize empty strings to NULL in the middleware layer.
• Dangling foreign keys: if shop_no and warehouse_no land in the header table before the dictionary tables do, you will get dangling foreign keys. The safer pattern is to mark shop, warehouse and other dimension sync strategies as upstream dependencies and run them ahead of orders.
• Rate limit and timeout: the Wangdiantong API caps how many rows it returns per call; a 13-minute round easily gets truncated during peak. Inside Qeasy, drive pagination to completion until has_next is false before committing the batch — do not assume one call returns everything.

Applicable and Non-Applicable Scenarios

Applicable: order data must be reused by downstream BI, ERP, or warehouse accounting; the source is Wangdiantong and the target is MySQL; a 13-minute-level lag is acceptable. Not applicable: scenarios with strong-consistency requirements on real-time inventory deduction (use a dedicated inventory strategy), end-to-end refund reconciliation (this strategy only lands order snapshots), and scenarios where order volume far exceeds MySQL's write capacity and therefore needs a message queue for back-pressure.