MySQL 關於 INSERT INTO...ON DUPLICATE KEY UPDATE 的使用

Convict發表於2024-08-15
MySql

1. 構建測試資料

-- 建表
DROP TABLE IF EXISTS user_score;
CREATE TABLE user_score
(
    id          INT AUTO_INCREMENT,
    uid         INT(11)             NOT NULL,
    coin        VARCHAR(30)         NOT NULL COMMENT '貨幣',
    size        DECIMAL(38, 10)     NOT NULL COMMENT '數量',
    amount      DECIMAL(38, 10)     NOT NULL COMMENT '買入成本(美元)',
    cost_price  DECIMAL(38, 10)     NOT NULL COMMENT '成本價(美元)',
    PRIMARY KEY(`id`),
    UNIQUE KEY `uidx_user_coin` (`uid`,`coin`)
);

-- 插入測試資料
INSERT INTO user_score(uid, coin, size, amount, cost_price)
VALUES
(1001, 'AAA', 1, 10, 10);

-- 檢視測試資料
mysql> select * from user_score;
+----+------+------+--------------+---------------+---------------+
| id | uid  | coin | size         | amount        | cost_price    |
+----+------+------+--------------+---------------+---------------+
|  1 | 1001 | AAA  | 1.0000000000 | 10.0000000000 | 10.0000000000 |
+----+------+------+--------------+---------------+---------------+

2. 需求

新置入AAA貨幣，如果原本沒有持有則插入；如果已經持有，則再重新計算成本價。
過程：

  • 累加 size
  • 累加 amount
  • 更新 avg_price 為 最新amount除以最新size得到的值

按照預期，最終 size = 2，amount = 22，cost_price = (10 + 12) / (1 + 1) = 11。

3. 實現

3.1 先舉個錯誤演示

INSERT INTO user_score(uid, coin, size, amount, cost_price) VALUES(1001, 'AAA', 1, 12, 12)
ON DUPLICATE KEY UPDATE
size = size + VALUES(size),
amount = amount + VALUES(amount),
cost_price = (amount + VALUES(amount)) / (size + VALUES(size));

檢視執行結果：

mysql> select * from user_score;
+----+------+------+--------------+---------------+---------------+
| id | uid  | coin | size         | amount        | cost_price    |
+----+------+------+--------------+---------------+---------------+
|  1 | 1001 | AAA  | 2.0000000000 | 22.0000000000 | 11.3333333333 |
+----+------+------+--------------+---------------+---------------+

顯然 cost_price 是不對的，預期是 11。

在這個語句中，執行順序為：

  1. 執行size = size + VALUES(size)值為 1 + 1 = 2
  2. 執行amount = amount + VALUES(amount)值為 10 + 12 = 22
  3. 執行cost_price = (amount + VALUES(amount)) / (size + VALUES(size))值為 (22 + 12) / (2 + 1) = 11.3333333333
  4. 最後的 cost_price 取的amount，已經是加過的值了，再加上VALUES(amount)等於又加了一遍，同理size也是

3.2 正確寫法

先重新執行步驟1，把資料還原：

mysql> select * from user_score;
+----+------+------+--------------+---------------+---------------+
| id | uid  | coin | size         | amount        | cost_price    |
+----+------+------+--------------+---------------+---------------+
|  1 | 1001 | AAA  | 1.0000000000 | 10.0000000000 | 10.0000000000 |
+----+------+------+--------------+---------------+---------------+

執行sql：

INSERT INTO user_score(uid, coin, size, amount, cost_price) VALUES(1001, 'AAA', 1, 12, 12)
ON DUPLICATE KEY UPDATE
size = size + VALUES(size),
amount = amount + VALUES(amount),
cost_price = amount / size;

檢視執行結果：

mysql> select * from user_score;
+----+------+------+--------------+---------------+---------------+
| id | uid  | coin | size         | amount        | cost_price    |
+----+------+------+--------------+---------------+---------------+
|  1 | 1001 | AAA  | 2.0000000000 | 22.0000000000 | 11.0000000000 |
+----+------+------+--------------+---------------+---------------+

此時資料就是預期中的了。

4. 其它場景

在這個案例中，如果是超精度的情況，就會導致除以0的情況出現。

-- 建表
DROP TABLE IF EXISTS user_score;
CREATE TABLE user_score
(
    id          INT AUTO_INCREMENT,
    uid         INT(11)             NOT NULL,
    coin        VARCHAR(30)         NOT NULL COMMENT '貨幣',
    size        DECIMAL(38, 10)     NOT NULL COMMENT '數量',
    amount      DECIMAL(38, 10)     NOT NULL COMMENT '買入成本(美元)',
    cost_price  DECIMAL(38, 10)     NOT NULL COMMENT '成本價(美元)',
    PRIMARY KEY(`id`),
    UNIQUE KEY `uidx_user_coin` (`uid`,`coin`)
);

-- 插入測試資料
INSERT INTO user_score(uid, coin, size, amount, cost_price)
VALUES
(1001, 'AAA', 0, 0, 10);

-- 檢視測試資料
mysql> select * from user_score;
+----+------+------+--------------+--------------+---------------+
| id | uid  | coin | size         | amount       | cost_price    |
+----+------+------+--------------+--------------+---------------+
|  1 | 1001 | AAA  | 0.0000000000 | 0.0000000000 | 10.0000000000 |
+----+------+------+--------------+--------------+---------------+

此時size跟amount都為0，執行sql：

INSERT INTO user_score(uid, coin, size, amount, cost_price) VALUES(1001, 'AAA', 0.00000000001, 1, 99)
ON DUPLICATE KEY UPDATE
size = size + VALUES(size),
amount = amount + VALUES(amount),
cost_price = amount / size;

執行報錯：

ERROR 1365 (22012): Division by 0

原因：插入了一條size為0.00000000001的記錄，小數長度為11位，已經超過10位，導致size已經被截斷成0了，就出現了除0的異常。

修復：擴充套件小數位數

ALTER TABLE user_score MODIFY COLUMN size DECIMAL(38, 20);

此時表結構為：

CREATE TABLE `user_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uid` int NOT NULL,
  `coin` varchar(30) NOT NULL COMMENT '貨幣',
  `size` decimal(38,20) DEFAULT NULL,
  `amount` decimal(38,10) NOT NULL COMMENT '買入成本(美元)',
  `cost_price` decimal(38,10) NOT NULL COMMENT '成本價(美元)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_user_coin` (`uid`,`coin`)
)

接著再執行sql，就正常了

INSERT INTO user_score(uid, coin, size, amount, cost_price) VALUES(1001, 'AAA', 0.00000000001, 1, 99)
ON DUPLICATE KEY UPDATE
size = size + VALUES(size),
amount = amount + VALUES(amount),
cost_price = amount / size;

檢視資料，也正常更新了

mysql> select * from user_score;
+----+------+------+------------------------+--------------+-------------------------+
| id | uid  | coin | size                   | amount       | cost_price              |
+----+------+------+------------------------+--------------+-------------------------+
|  1 | 1001 | AAA  | 0.00000000001000000000 | 1.0000000000 | 100000000000.0000000000 |
+----+------+------+------------------------+--------------+-------------------------+

相關文章