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

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 没问题

34 replies • 2022-03-25 18:43:13 +08:00

CyJaySong

Mar 25, 2022

在套一个 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

Mar 25, 2022

检查一下 sql_mode 。

westoy

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

@westoy 还是一样

CyJaySong

Mar 25, 2022

之前那种销量很低，子查询居然全表查询了，换成这种更好
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

Mar 25, 2022

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

jtwor

Mar 25, 2022

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

encro

Mar 25, 2022

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

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

encro

Mar 25, 2022

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

Mar 25, 2022

sorry 是我看错了。

encro

Mar 25, 2022

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

encro

Mar 25, 2022

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

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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

encro

Mar 25, 2022

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

encro

Mar 25, 2022

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

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

CyJaySong

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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

encro

Mar 25, 2022

@CyJaySong

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

encro

Mar 25, 2022

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

encro

Mar 25, 2022

@CyJaySong

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

CyJaySong

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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

encro

Mar 25, 2022

where account_type = 4 ORDER BY date DESC LIMIT 1

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

CyJaySong

Mar 25, 2022

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

encro

Mar 25, 2022

@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

Mar 25, 2022

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

encro

Mar 25, 2022

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

Mar 25, 2022

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

encro

Mar 25, 2022

@CyJaySong

不知道，前面也加了 isnull 。

encro

Mar 25, 2022

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

CyJaySong

Mar 25, 2022

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