MySQL 关于 INSERT INTO...ON DUPLICATE KEY UPDATE 的使用

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 |
+----+------+------+------------------------+--------------+-------------------------+