想请教一个SQL相关的问题

Skyzc

大概要实现的功能：SpringBoot使用Mybatis批量插入数据，如果主键存在则更新，不存在则插入。

部分数据有多行如下面这种格式，需要将其在MySQL里合并为一行：
数据格式：

('609933531757547520','a', null, null),
('609933531757547520',null,'b',null),
('609933531757547520',null,null,'c')

数据库所要效果：

目前找了两种方法来进行插入：

# 插入数据 replace 存在则删除之前的再插入新的，会丢失之前的数据
REPLACE INTO t_test SET id='609933531757547520',col_a='a';
REPLACE INTO t_test SET id='609933531757547520',col_b='b';
REPLACE INTO t_test SET id='609933531757547520',col_c='c';

# 插入数据 ON DUPLICATE KEY UPDATE 同样会丢失数据
 INSERT INTO t_test(id, col_a, col_b, col_c)
 VALUES
        ('609933531757547520','a', null, null),
        ('609933531757547520',null,'b',null),
        ('609933531757547520',null,null,'c')
 ON DUPLICATE KEY UPDATE
   col_a=values(col_a),
   col_b=values(col_b),
   col_c=values(col_c);

最后效果都无法实现要求，想请教一下还有没有别的办法来实现这个要求

LittleboyHarry

”主键存在则更新“ 可以用 UPDATE 语句配合 WHERE 子句

https://www.runoob.com/mysql/mysql-update-query.html

条件判断执行不同的操作可以写 存储过程 实现

0x709394

https://mariadb.com/kb/zh-cn/insert-on-duplicate-key-update/

0x709394

0x709394 https://mariadb.com/kb/zh-cn/insert-on-duplicate-key-update/

就是像你方法一那樣子分開寫

DROP TABLE IF EXISTS tt;
CREATE TABLE tt (
    id char(18) PRIMARY KEY ,
    col_a text,
    col_b text,
    col_c text
);

-- ('609933531757547520','a', null, null),
-- ('609933531757547520',null,'b',null),
-- ('609933531757547520',null,null,'c')

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520','a', null, null)
ON DUPLICATE KEY UPDATE col_a='a';

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520',null,'b',null)
ON DUPLICATE KEY UPDATE col_b='b';

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520', null, null, 'c')
ON DUPLICATE KEY UPDATE col_c='c';

Skyzc

0x709394 分开写的确可以，但是现在需要使用批量插入，数据有点多，单条单条插入的话效率速度有点慢==

Miigon

假设数据是('609933531757547520', 'a', null, 'c')

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520', 'a', null, 'c')
ON DUPLICATE KEY UPDATE
    col_a=COALESCE('a', col_a)
    col_b=COALESCE(null, col_b)
    col_c=COALESCE('c', col_c);

COALESCE返回列表中第一个非 null 项。

EDIT: MySQL 8.0.19 以上可以这么写：

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520', 'a', null, 'c') AS new
ON DUPLICATE KEY UPDATE
    col_a=COALESCE(new.col_a, col_a)
    col_b=COALESCE(new.col_b, col_b)
    col_c=COALESCE(new.col_c, col_c);

或者也可以：

INSERT INTO tt(id, col_a, col_b, col_c)
VALUES
('609933531757547520', 'a', null, 'c')
ON DUPLICATE KEY UPDATE
    col_a=COALESCE(VALUES(col_a), col_a)
    col_b=COALESCE(VALUES(col_b), col_b)
    col_c=COALESCE(VALUES(col_c), col_c);

在 ON DUPLICATE KEY UDPATE 中，VALUES()指向假设未发生冲突时，会被插入的数据。