mysql - SQL 如果不存在则插入新记录，否则更新现有记录

Question

我有 2 个表 Staging 和 Main Table 都有 SKU 和 Price 列。记录从那里插入到临时表中，具体取决于记录决定是插入新记录还是更新现有记录。我编写了多个查询，这些查询运行良好，但花费的时间比正常情况要长。我正在做的是：

Staging Table where data initially inserted into 

+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
| indexid  |  Persisted   |    sku    | price |                                                           Logic                                                           |
+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+
|        1 | No           |   4567456 | 10.99 | This row doesn't exists in main table so need to be inserted and update the persisted to NewProduct                       |
|        2 | No           |   5463454 |  5.99 | This row exists and price matching then this simply need to be updated the persisted to PriceMatched                      |
|        3 | No           |   5645654 |  4.75 | This row exists but price not matching, so new row to be inserted, updated existing record in both tables to PriceUpdated |
|        4 | PriceUpdated |   2222222 |  3.00 |                                                                                                                           |
|        5 | NewProduct   |   5555555 |  4.00 |                                                                                                                           |
|        6 | PriceMatched |   7685765 |  5.00 |                                                                                                                           |
|        7 | PriceUpdated |   6574567 |  3.99 |                                                                                                                           |
+----------+--------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------+

+---------+--------------+---------+-------+
| indexid |    status    |   sku   | price |
+---------+--------------+---------+-------+
|       1 | Active       | 5635674 | 10.99 |
|       2 | Active       | 5463454 |  5.99 |
|       3 | Active       | 5645654 |  2.75 |
|       4 | PriceUpdated | 2222222 |  3.00 |
|       5 | PriceUpdated | 5555555 |  4.00 |
|       6 | PriceMatched | 7685765 |  5.00 |
|       7 | PriceUpdated | 6574567 |  3.99 |
+---------+--------------+---------+-------+

如果 Product 在主表中不存在，则插入新记录并使用 persisted = 'NewProduct' 更新临时表

我正在从暂存表中选择所有记录，其中persisted = 'No' 然后插入新记录

UPDATE stagingTable SET persisted = 'NewProduct' WHERE indexid = '${indexID}';

Else 检查现有产品价格是否匹配，然后更新持久化 = 'PriceMatched'

UPDATE stagingTable SET persisted = 'PriceMatched' WHERE indexid = '${indexID}' ;

如果价格不匹配，则将新记录插入主表并更新现有记录状态 = 'PriceUpdated' 并且暂存表持久化 = 'PriceUpdated'

UPDATE mainTable SET productstatus = 'PriceUpdated' WHERE indexid = '${indexID}';
UPDATE stagingTable SET persisted = 'PriceUpdated' WHERE indexid = '${indexID}';

插入工作正常，但更新需要相当长的 10000 条记录，大约需要 15-20 分钟我通过批量插入和更新插入和更新。

这是我的逻辑

标签： mysqlsql-updatequery-performancemysql-slow-query-log