同样是 MySQL 8，这个我问题我硬是没搞明白会这样，希望大家帮我分析一下

[CyJaySong]

有一个表

CREATE TABLE `trade_account_total_daily_record`
(
    `date`         date           NOT NULL COMMENT '日期',
    `account_type` int            NOT NULL COMMENT '账户类型',
    `balance`      decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '账户余额',
    `created_at`   datetime       NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`   datetime       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`date`, `account_type`)
) ;

我执行

SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 1, IFNULL(SUM(balance),0) + 10
FROM trade_account_total_daily_record WHERE account_type = 1 ORDER BY date DESC LIMIT 1 

能正确给出结果, 但是执行

INSERT INTO trade_account_total_daily_record (`date`, `account_type`, `balance`)
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 1, IFNULL(SUM(balance),0) + 10
FROM trade_account_total_daily_record WHERE account_type = 4 ORDER BY date DESC LIMIT 1
ON DUPLICATE KEY UPDATE `balance`=VALUES(`balance`) 

就报 Column 'date' cannot be null ，奇怪的是阿里云 RDS 数据库(mysql-8.0.25)会这样，我本地的 8.0.28 没问题

[CyJaySong]

在套一个 SELECT 就可以了。。。好奇怪
```
INSERT INTO trade_account_total_daily_record (`date`, `account_type`, `balance`)
SELECT tmp.* FROM(SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 4, IFNULL(SUM(balance),0) + 10
FROM trade_account_total_daily_record AS tmp WHERE account_type = 4 ORDER BY date DESC LIMIT 1) AS tmp
ON DUPLICATE KEY UPDATE `balance`=VALUES(`balance`)
```

[wps353]

检查一下 sql_mode 。

[westoy]

ORDER BY date => ORDER BY `date`试试?

[CyJaySong]

@wps353 看了一下，阿里云和本地的一样

[CyJaySong]

@westoy 还是一样

[CyJaySong]

之前那种销量很低，子查询居然全表查询了，换成这种更好
INSERT INTO trade_account_total_daily_record (`date`, `account_type`, `balance`)
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 4, IFNULL(SUM(tmp.balance),0) + 10,FROM (SELECT balance FROM trade_account_total_daily_record WHERE account_type = 4 ORDER BY `date` DESC LIMIT 1) AS tmp
ON DUPLICATE KEY UPDATE `balance`=VALUES(`balance`)

[northernlights0]

搞不好是 RDS 的 bug 。阿里云数据库并不是简单地跑一个 mysql 实例给你用，为了利用好资源，底层有很多他们自己实现的东西，甚至可能查询引擎都和 mysql 默认的不一样。

[jtwor]

是不是 DATE_FORMAT 转成的是字符串 而不是 date 类型报的？

[encro]

这么简单的问题。。。。。
错误提示这么明显了。。。

PRIMARY KEY (`date`, `account_type`) 重复了。。。。。

[encro]

SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 1, IFNULL(SUM(balance),0) + 10
FROM trade_account_total_daily_record WHERE account_type = 1 ORDER BY date DESC LIMIT 1


这条语句写的莫名其妙。

[encro]

sorry 是我看错了。

[encro]

DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d') 直接换成 CURRENT_DATE 试试？

[encro]

mysql replace into 应该比你这个更好用。
SUM(balance) + LIMIT 1 我看不出这是想干啥？

看起来希望：

replace into trade_account_total_daily_record set `balance`=balance+10 where date=CURRENT_DATE and account_type = 4 ORDER BY date DESC LIMIT 1

[CyJaySong]

@encro 确实有性能问题，EXPLAIN 显示全表查询了。用#6 那种就不会了

[CyJaySong]

@encro 回复#13 ，不是为了替换，这个表主要是统计每个账户类型的每日总日结余额，每次资金变动时，更新总日结余额。

[encro]

哈哈，你需要一个物化视图。
果然 pg 才是最好选择。

[encro]

replace into trade_account_total_daily_record set `balance`=balance+10 where date=CURRENT_DATE and account_type = 4

更新日结用我这个就可以了。

[CyJaySong]

@encro 回复#17 : REPLACE INTO 效率没得 ON DUPLICATE KEY UPDATE 高，而且你这句没达到我要的目的🤣

[CyJaySong]

@northernlights0 回复#7 大概是吧，不过通过多套一个子查询解决了,反而提高了性能

[CyJaySong]

@jtwor 回复#8 问题不在这儿，大概如#7 说的那样

[encro]

@CyJaySong

明白了，你是想不存在查前一天的，再累加。

[encro]

我是想每天都只加当天的，没必要去加前一天的。

[encro]

@CyJaySong

ORDER BY date DESC LIMIT 1 ，一条语句主键，谈什么性能?

[CyJaySong]

@encro #回复 21,22 是的，这样可以生成各账户类型的每日总结余额

[CyJaySong]

@encro 回复#23 期初我也这样么认为的，但是即便是 LIMIT 1 情况下，SUM 函数会导致全表扫描，就很皮

[encro]

where account_type = 4 ORDER BY date DESC LIMIT 1

索引顺序导致用不了主键索引。。。

[CyJaySong]

@encro 回复#26, 用#6 就很快哦

[encro]

@CyJaySong

你删掉当天存在的值再试试，说不定没跑查询或者命中 cache 呢。

INSERT INTO trade_account_total_daily_record (`date`, `account_type`, `balance`)
SELECT DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d'), 4, IFNULL(SUM(tmp.balance),0) + 10,FROM (SELECT balance FROM trade_account_total_daily_record WHERE account_type = 4 ORDER BY `date` DESC LIMIT 1) AS tmp
ON DUPLICATE KEY UPDATE `balance`=VALUES(`balance`)


我还没琢磨透 SUM(tmp.balance) 和 WHERE account_type = 4 ORDER BY `date` DESC LIMIT 1 的用意。。。。

[encro]

LIMIT 1 不是只有一行结果吗？还需要 sum?

[encro]

INSERT INTO trade_account_total_daily_record (`date`, `account_type`, `balance`)
SELECT CURRENT_DATE , 4, IFNULL((SELECT balance FROM trade_account_total_daily_record WHERE account_type = 4 ORDER BY `date` DESC LIMIT 1),0) + 10
ON DUPLICATE KEY UPDATE `balance`=`balance`

?

[CyJaySong]

@encro 回复 #29 如果执行的时候数据库没有 account_type = 4 的数据呢，你看看结果会怎样

[encro]

@CyJaySong

不知道，前面也加了 isnull 。

[encro]

就是看同表更新好像是需要建立临时表。不知道 8.0 了。

[CyJaySong]

@encro 回复#32 在没有复合 account_type = 4 的数据的情况下，光有 isnull 没用