当前位置:网站首页>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
边栏推荐
- Is Hongmeng system plagiarism? Or the future? Professional explanation that can be understood after listening in 3 minutes
- Example of specific method for TIA to trigger interrupt ob40 based on high-speed counter to realize fixed-point machining action
- Solve the problem of Oracle Chinese garbled code
- [point cloud series] Introduction to scene recognition
- playwright控制本地谷歌浏览打开,并下载文件
- Logstash数据处理服务的输入插件Input常见类型以及基本使用
- Common analog keys of ADB shell: keycode
- XML
- 解决tp6下载报错Could not find package topthink/think with stability stable.
- TIA博途中基於高速計數器觸發中斷OB40實現定點加工動作的具體方法示例
猜你喜欢
The interviewer dug a hole for me: what's the use of "/ /" in URI?
[official announcement] Changsha software talent training base was established!
Isparta is a tool that generates webp, GIF and apng from PNG and supports the transformation of webp, GIF and apng
Explanation of input components in Chapter 16
[quick platoon] 215 The kth largest element in the array
面试官给我挖坑:URI中的 “//” 有什么用?
[point cloud series] deepmapping: unsupervised map estimation from multiple point clouds
Esp32 vhci architecture sets scan mode for traditional Bluetooth, so that the device can be searched
“湘见”技术沙龙 | 程序员&CSDN的进阶之路
为什么从事云原生开发需要学习容器技术
随机推荐
Lenovo Savior y9000x 2020
Uninstall MySQL database
Short name of common UI control
SAP UI5 应用开发教程之七十二 - SAP UI5 页面路由的动画效果设置
TCP reset Gongji principle and actual combat reproduction
Oracle view related
Django::Did you install mysqlclient?
RTOS mainstream assessment
TIA博途中基於高速計數器觸發中斷OB40實現定點加工動作的具體方法示例
Lpddr4 notes
Machine learning -- naive Bayes
XML
How to build a line of code with M4 qprotex
Database transactions
AI21 Labs | Standing on the Shoulders of Giant Frozen Language Models(站在巨大的冷冻语言模型的肩膀上)
Software test system integration project management engineer full truth simulation question (including answer and analysis)
GDB的使用
CSDN College Club "famous teacher college trip" -- Hunan Normal University Station
The interviewer dug a hole for me: how many concurrent TCP connections can a single server have?
为什么从事云原生开发需要学习容器技术