5

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

 2 years ago
source link: https://www.v2ex.com/t/842853
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

V2EX  ›  MySQL

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

  CyJaySong · 20 小时 8 分钟前 · 1441 次点击 
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 没问题


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK