当前位置:网站首页>Two ways to deal with conflicting data in MySQL and PG Libraries
Two ways to deal with conflicting data in MySQL and PG Libraries
2022-04-23 13:41:00 【Wangcai 2】
mysql Processing mode
1.IGNORE
When using INSERT Statement adds some row data to the table and an error occurs during processing ,INSERT The statement will be aborted , And return an error message . therefore , No rows may be inserted into the table .
however , If you use INSERT INGORE sentence , The line that caused the error is ignored , And insert the remaining rows into the table .
insert IGNORE into table(xxx,xxx) VALUES (xxx,xxx);
insert IGNORE into `test` (`id`,`name`,`times`,`add_time`)
VALUES
('1','woq','3','1529304418'),
('2','woa','4','1529304452'),
('3','woz','5','1529304425'),
('4','wow','6','1529304445');
remarks :IGNORE When , If the data in the original result set is repeated, an error will be reported ,REPLACE The method will not report an error
2.REPLACE INTO
If you find that this row of data already exists in the table ( Based on the primary key or unique index ) Then delete this row of data first , Then insert the new data .
otherwise , Insert new data directly .
It should be noted that : The table that inserts data must have a primary key or a unique index ! Otherwise ,replace into Will insert data directly , This will result in duplicate data in the table .
REPLACE INTO table (xxx,xxx) VALUES (xxx,xxx);
REPLACE into `test` (`id`,`name`,`times`,`add_time`)
VALUES
('1','woq','3','1529304418'),
('2','woa','4','1529304452'),
('3','woz','5','1529304425'),
('4','wow','6','1529304445');
pg Library processing method
1. In case of conflicting data, do nothing
INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'[email protected]'
)
ON CONFLICT (name)
DO NOTHING;
2. Conflicting data update operation encountered
INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'[email protected]'
)
ON CONFLICT (name)
DO
UPDATE
SET email = EXCLUDED.email;
remarks :DO NOTHING When , If there is duplicate data in the original result set, no error will be reported ,DO UPDATE When , If there is duplicate data in the original result set, an error will be reported
版权声明
本文为[Wangcai 2]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230604300353.html
边栏推荐
- playwright控制本地谷歌浏览打开,并下载文件
- POM of SSM integration xml
- Innobackupex incremental backup
- TCP 复位gongji原理和实战复现
- 校园外卖系统 - 「农职邦」微信原生云开发小程序
- RTOS mainstream assessment
- [official announcement] Changsha software talent training base was established!
- Oracle view related
- UEFI learning 01-arm aarch64 compilation, armplatformpripeicore (SEC)
- torch. Where can transfer gradient
猜你喜欢
[point cloud series] foldingnet: point cloud auto encoder via deep grid deformation
顶级元宇宙游戏Plato Farm,近期动作不断利好频频
校园外卖系统 - 「农职邦」微信原生云开发小程序
AI21 Labs | Standing on the Shoulders of Giant Frozen Language Models(站在巨大的冷冻语言模型的肩膀上)
[barycentric coordinate interpolation, perspective correction interpolation] principle and usage opinions
Stack protector under armcc / GCC
【重心坐标插值、透视矫正插值】原理以及用法见解
SHA512 / 384 principle and C language implementation (with source code)
[point cloud series] multi view neural human rendering (NHR)
Campus takeout system - "nongzhibang" wechat native cloud development applet
随机推荐
交叉碳市场和 Web3 以实现再生变革
Comparison and summary of applicable scenarios of Clickhouse and MySQL database
Why do you need to learn container technology to engage in cloud native development
TCP reset Gongji principle and actual combat reproduction
Oracle renames objects
Unified task distribution scheduling execution framework
解决tp6下载报错Could not find package topthink/think with stability stable.
Exemple de méthode de réalisation de l'action d'usinage à point fixe basée sur l'interruption de déclenchement du compteur à grande vitesse ob40 pendant le voyage de tia Expo
Migrating your native/mobile application to Unified Plan/WebRTC 1.0 API
Processbuilder tool class
GDB的使用
Common commands of ADB shell
[point cloud series] deepmapping: unsupervised map estimation from multiple point clouds
What do the raddr and rport in webrtc ice candidate mean?
NPM err code 500 solution
Vscode tips
GDB的使用
Interface idempotency problem
Solve the problem that Oracle needs to set IP every time in the virtual machine
软考系统集成项目管理工程师全真模拟题(含答案、解析)