POSTGRES、MYSQL插入数据的UPDATE_INSERT实践

已于 2023-06-11 11:26:12 修改
阅读量1.3k 收藏
点赞数
文章标签： mysql 数据库 sql postgresql
于 2023-06-08 22:34:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43317111/article/details/131116952
版权

POSTGRES: 

后续操作均建立在如下版本 14.1 基础之上，低版本的POSTGRES还不支持ON CONFLICT 命令，可以通过给表创建 RULE 达到UPDATE_INSERT效果

1、创建表

create table tbl_user(

    id serial PRIMARY KEY,

    name varchar(256),

    addr varchar(256),

    age int,

    score int,

    fav varchar(256)

);

2、创建唯一约束

alter table tbl_user add constraint name_add_age_unique unique(name,addr,age);

3、首先插入两条数据

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10,22,'aaa_fav'),

('bbb','ccc_addr',10,22,'bbb_fav');

4、此时直接INSERT INTO 主键重复的数据，会直接报唯一键冲突，插入失败

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10, 23,'aaa_fav_new'),

('bbb','ccc_addr',10, 23,'bbb_fav_new'),

('ddd','ddd_addr',10, 22,'ccc_fav'),

('eee','eee_addr',10, 22,'ddd_fav');

5、采用UPDATE_INSERT方式，插入成功，并且对冲突列执行指定更新操作

语法说明：

1、ON conflict(唯一约束键列表) DO，用于指定对于冲突的唯一键，可以是联合唯一键

2、excluded.列名 用于获取插入的时入参

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10, 23,'aaa_fav_new'),

('bbb','ccc_addr',10, 23,'bbb_fav_new'),

('ddd','ddd_addr',10, 22,'ccc_fav'),

('eee','eee_addr',10, 22,'ddd_fav')

ON conflict(name,addr,age) DO

UPDATE set score=excluded.score, fav=excluded.fav;

6、在唯一索引列中存在 null 时，会导致前述 UPDATE_INSERT 失效

例如：

1）在tbl_user中先插入一条数据：

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa',null,10, 23,'aaa_fav_new')

ON conflict(name,addr,age) DO

UPDATE set score=excluded.score, fav=excluded.fav;

2）再次执行相同的语句，插入数据，发现出现了两条一样的数据，UPDATE_INSERT 操作失效了，原因在于addr为null,导致前述创建的唯一索引(name,age,addr) 失效了

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa',null,10, 23,'aaa_fav')

ON conflict(name,addr,age) DO

UPDATE set score=excluded.score, fav=excluded.fav;

解决方案：

创建唯一索引的时候通过COALESCE指定索引默认值

例：

1）重新创建表

create table tbl_user(

    id serial PRIMARY KEY,

    name varchar(256),

    addr varchar(256),

    age int,

    score int,

    fav varchar(256)

)

2）创建唯一索引，注意使用COALESCE指定默认值

CREATE UNIQUE INDEX tbl_user_solution_idx

    ON tbl_user (COALESCE(name,''),COALESCE(addr,''),COALESCE(age,-1));

3）插入预置数据，注意构造 索引为null的情况

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa',null,10, 23,'aaa_fav');

4）再次执行相同的空值插入，会报唯一索引冲突, 说明唯一约束生效

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa',null,10, 23,'aaa_fav');

5）使用UPDATE_INSERT进行数据插入

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa',null,10, 23,'aaa_fav_new')

ON conflict(COALESCE(name,''),COALESCE(addr,''),COALESCE(age,-1))

DO

UPDATE set score=excluded.score, fav=excluded.fav;

MYSQL:

后续操作均建立在如下版本 8.0.27 基础之上，其他版本可以查询替换方式

1、创建表

create table tbl_user(

    id int PRIMARY KEY AUTO_INCREMENT,

    name varchar(256),

    addr varchar(256),

    age int,

    score int,

    fav varchar(256)

);

2、创建唯一约束

alter table tbl_user add constraint name_add_age_unique unique(name,addr,age);

3、首先插入两条数据

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10,22,'aaa_fav'),

('bbb','ccc_addr',10,22,'bbb_fav');

4、直接INSERT INTO，会直接报唯一键冲突，插入失败

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10, 23,'aaa_fav_new'),

('bbb','ccc_addr',10, 23,'bbb_fav_new'),

('ddd','ddd_addr',10, 22,'ccc_fav'),

('eee','eee_addr',10, 22,'ddd_fav')

5、采用UPDATE_INSERT方式，插入成功，并且对冲突列执行指定更新操作

语法说明：

1、ON DUPLICATE KEY，用于识别唯一键冲突操作

2、CALUES(列名)， 用于获取插入的时入参

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('aaa','aaa_addr',10, 23,'aaa_fav_new'),

('bbb','ccc_addr',10, 23,'bbb_fav_new'),

('ddd','ddd_addr',10, 22,'ccc_fav'),

('eee','eee_addr',10, 22,'ddd_fav')

ON DUPLICATE KEY

UPDATE score=VALUES(score), fav=VALUES(fav);

6、在唯一索引列中存在 null 时，会导致前述 UPDATE_INSERT 失效

解决方案：才疏学浅，暂时采用存储过程，在插入数据进行判断处理，若有其他更好的方案，请大佬指点

1）在建表之后，创建存储过程

主要逻辑：查询name、addr、age 相同的记录，若是存在相同的记录（唯一性) 则执行update更新，否则执行insert插入

注意：

1、创建存储过程时，为了避免存储过程中分号的影响，需要先声明语句分隔符为反斜线， delimiter // ，在创建完成之后，重新将其声明 delimiter ;

delimiter //

CREATE PROCEDURE update_insert_tbl_user(IN in_name VARCHAR(256),IN in_addr VARCHAR(256), IN in_age INT, IN in_score INT, IN in_fav VARCHAR(256))

BEGIN

  DECLARE total INT;

  select count(*) into total from tbl_user

                        where ((name is null and in_name is null) or name = in_name)

                                and ((addr is null and in_addr is null) or addr = in_addr)

                                and ((age is null and in_age is null) or age = in_age);

  if total > 0 THEN

    update tbl_user set score = in_score, fav = in_fav

                        where ((name is null and in_name is null) or name = in_name)

                                and ((addr is null and in_addr is null) or addr = in_addr)

                                and ((age is null and in_age is null) or age = in_age);

  else

    insert into tbl_user(name, addr, age, score, fav) values(in_name, in_addr, in_age, in_score, in_fav);

  end if;

END;

//

delimiter ;

 2）验证存储过程

先插入两条数据，注意插入数据的addr列为null

INSERT INTO tbl_user

(name, age, score, fav)

VALUES

('aaa',10, 23,'aaa_fav');

INSERT INTO tbl_user

(name, addr, age, score, fav)

VALUES

('bbb', null,10, 23,'bbb_fav');

 通过存储过程执行数据查询：

call update_insert_tbl_user('aaa',null,10,23,'aaa_fav_new');

发现如预期更新了fav字段

Steven-Russell
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中修改数据的命令： INSERT、UPDATA、DELETE。
weixin_49198853的博客
11-15 6958
INSERT:向数据库插入新的记录 INSERT语句的基本语法： INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...); 练习命令： INSERT INTO students (id,class_id,name,gender,score) VALUES(20,2,"诸葛钢铁","女",88); 运行SELECT * FROM students;命令得到： 在本练习中也可以不用列出id字段和对应的值，因为在这题中id字段是一个自增主键，
nim_sqlbuilder：用于Nim查询SQL构建器
02-02
- **InsertStatement**: 插入数据到表中，可以指定插入的列和值。 - **UpdateStatement**: 更新表中的数据，指定要更新的列和新值，以及更新条件。 - **DeleteStatement**: 删除表中的数据，基于特定的WHERE条件。 ...
postgresql insert and update 插入更新
u010154380的专栏
06-26 6590
  &lt;insert id="insertWebMsgCenter" parameterType="com.doordu.soa.service.house.resultvo.WebMsgCenterResVo" &gt;         UPDATE estate.web_msg_center SET json_data = #{jsonData,jdbcType=OTHER}:: json...
PostgreSQL insert update 语句
最新发布
li6909096的博客
05-30 252
这个语法允许你尝试插入一行数据，如果违反了唯一约束（例如主键或唯一索引），则执行一个更新操作。如果你使用的是较旧版本的 PostgreSQL，你可能需要编写更复杂的逻辑（例如使用 INSERT、UPDATE 和 SELECT 的组合）来实现相同的功能。在 ON CONFLICT DO UPDATE 子句中，你可以使用 EXCLUDED 来引用这些数据，以便在更新操作中使用它们。在这个示例中，如果表 students 中已经存在一个名为 ‘Alice’ 的学生，则该学生的 score 将被更新为 90。
Postgre 实现 插入更新
w201615060094的博客
12-09 651
INSERT INTO test.base select * from test.src ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
postgresql 实现插入 更新操作
Cincinnati_De的博客
05-05 3125
INSERT INTO "sip_data"."sip_ip_2m_data" (time_first,time_last,ip,ip_port,port) VALUES('2019/4/6 15:14:29','2019/4/6 14:59:28','203.190.77.198','203.190.77.198|42560','42560') ...
我的MYSQL学习心得（八） 插入 更新 删除
weixin_33980459的博客
06-08 225
我的MYSQL学习心得（八） 插入 更新 删除 我的MYSQL学习心得（一） 简单语法 我的MYSQL学习心得（二） 数据类型宽度 我的MYSQL学习心得（三） 查看字段长度 我的MYSQL学习心得（四） 数据类型 我的MYSQL学习心得（五） 运算符 我的MYSQL学习心得（六） 函数 我的MYSQL学习心得（七） 查询 我的MYSQL学习心得（九） 索引 我的MYSQL学习心...
rdbc：Rust数据库连接（RDBC）：：用于数据库驱动程序的通用Rust API
02-04
3. **执行查询**：使用`Query` trait的方法执行SQL语句，可以是简单的SELECT，也可以是INSERT、UPDATE或DELETE。 4. **处理结果**：如果查询返回结果，可以使用`Row` trait遍历每一行数据。 5. **事务处理**：在需要...
rbatis：Rust ORM框架高性能Rust SQL-ORM（基于JSON）
02-04
例如，创建一个User结构体，并使用rbatis提供的API进行增删查改操作，如`db.insert(&user)`、`db.update(&user)`等。 总结来说，rbatis是Rust开发中的一个强大工具，它利用JSON构建SQL，简化了数据库操作，支持多种...
employee-sample-database-main.zip
05-14
SQL的基本概念包括SELECT语句用于检索数据，INSERT语句用于插入新记录，UPDATE语句用于修改现有记录，以及DELETE语句用于删除记录。此外，你还可以学习JOIN操作，用来合并来自多个表的数据，以及更复杂的子查询和...
python_crud
02-18
例如，创建数据库连接、创建表、插入数据、查询数据、更新数据和删除数据： ```python import sqlite3 # 连接到SQLite数据库 (如果没有，则会自动创建) conn = sqlite3.connect('example.db') # 创建一个...
INSERT / UPDATE / DELETE / NULL / VIEW
12-14
数据更新 CRUD —— 增删改查（Create、Retrieve、Update、Delete） CREATE / ALTER / DROP——对于数据库，表的操作 INSERT / UPDATE / DELETE —— 对数据的操作 1.插入数据——INSERT 包括： 插入元组 INSERT INTO [([,...])] VALUES ([,...]) INTO后面加表名，属性列名可以与表中属性列排列顺序不一致，如【例3.69】，但VALUES后跟的值必须与所给属性列相对应，也可以省去，如【例3.70】，但省去后，VALUES后面跟的元组值必须与表中顺序完全一致。 【例3.69】 IN
mysql update insert_【技术分享】一种新的MySQL下Update、Insert注入方法
weixin_39765209的博客
01-18 210
预估稿费：100RMB投稿方式：发送邮件至linwei#360.cn，或登陆网页版在线投稿前言目前我们一般通过报错和时间盲注来对update和insert语句进行SQL注入，下面我们来讲解一种新的获取数据的方法。首先我们来看一个简单的例子，假设应用会将username字段的结果会返回给我们：$query="UPDATEusersSETusername='$username'WHER...
mysql 用update insert_mysql中update与insert常用详解
weixin_34766614的博客
01-26 123
本文主要内容：update更新，batch批量更新insert 数据到表中，将表中数据插入到另一个表中insert ignore忽略重复数据插入报错问题update更新，batch批量更新set sql_safe_updates=0;# 常规updateupdate springdemo.users set pwd=nickname where id=id;下面给一个stack overflow中...
MySQL 当记录不存在时insert，当记录存在时update
weixin_34279184的博客
05-08 1051
MySQL当记录不存在时insert，当记录存在时更新；网上基本有三种解决方法 第一种： 示例一：insert多条记录 假设有一个主键为 client_id 的 clients 表，可以使用下面的语句： INSERT INTO clients (client_id, client_name, client_type) SELECT supplier_id, supplier_name, 'adv...
如何在存储过程中实现插入更新数据
weixin_34235135的博客
07-03 1858
存储过程的功能非常强大，在某种程度上甚至可以替代业务逻辑层，接下来就一个小例子来说明，用存储过程插入或更新语句。　　1、数据库表结构 　　所用数据库为Sql Server2008。 　　2、创建存储过程 　　（1）实现功能： 　　有相同的数据，直接返回（返回值：0）； 　　有主键相同，但是数据不同的数据，进行更新处理（返回值：2）； 　　没有数据，进行插...
mysql 存储过程 update_MySQL 通过存储过程简化INSERT和UPDATE
weixin_39604685的博客
01-18 331
MySQL 通过存储过程简化INSERT和UPDATE处理目的，当数据表中存在目标记录时，执行UPDATE；当数据表中不存在目标记录时，执行INSERT；从而减少一次查询数据库的过程存储过程设计如下：CREATE PROCEDURE `pro_SaveData`(IN `sinst` varchar(500),IN `supdt` varchar(500))BEGIN#直接更新记录set @v_u...
使用一个存储过程完成数据插入和更新（使用xml）（通用insert和update）(mssql2008以上)
文盲的专栏
08-16 1804
天天维护网站，天天写数据插入insert into。。。。天天写数据更新update .. set 。。。。而且每个页面，每个表，每个数据的结构都不太一样，即便是同一个表，应用不同，应用到的列也不一样，每次都重新写sql指令，好烦躁 于是，琢磨了一下，可不可以用一个存储过程自动完成数据插入和更新呢？ 首先，应该把所有应用到的字段整理一下，作为参数传递给存储过程，比如：id,name,col,s...
C#下创建存储过程用于Insert或者Update数据的Demo
Stay hungry,stay foolish
03-11 2626
开始的时候想用那个批量导入工具，但是后来发现这个批量导入适用于那种第一次导入的情况，假如再次进行操作而且当前数据库中已经有一些数据则不再合适！ 然后我就尝试创建了存储过程，这应该也可以很大程度上提高读写数据库的操作效率！ CREATE PROCEDURE InsertORUpdateDLXX @MPH CHAR(50),@QYDM CHAR(50),@RQ DATETIME,@DBH CHAR
python postgres 插入数据
06-14
以下是一个简单的示例，演示如何使用 psycopg2 在 PostgreSQL 数据库中插入数据： ```python import psycopg2 # 连接到数据库 conn = psycopg2.connect( host="your_host", database="your_database", user=...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值