實(shí)操M(fèi)ySQL+PostgreSQL批量插入更新insertOrUpdate
一、百度百科
1、MySQL
MySQL聲稱自己是最流行的開(kāi)源數(shù)據(jù)庫(kù)。LAMP中的M指的就是MySQL。構(gòu)建在LAMP上的應(yīng)用都會(huì)使用MySQL，如WordPress、Drupal等大多數(shù)php開(kāi)源程序。
MySQL最初是由MySQL AB開(kāi)發(fā)的，然后在2008年以10億美金的價(jià)格賣給了Sun公司，Sun公司又在2010年被Oracle收購(gòu)。Oracle支持MySQL的多個(gè)版本：Standard、Enterprise、Classic、Cluster、Embedded與Community。其中有一些是免費(fèi)下載的，另外一些則是收費(fèi)的。
其核心代碼基于GPL許可，由于MySQL被控制在Oracle，社區(qū)擔(dān)心會(huì)對(duì)MySQL的開(kāi)源會(huì)有影響，所以開(kāi)發(fā)了一些分支，比如： MariaDB和Percona。
2、PostgreSQL
PostgreSQL標(biāo)榜自己是世界上最先進(jìn)的開(kāi)源數(shù)據(jù)庫(kù)。
PostgreSQL的一些粉絲說(shuō)它能與Oracle相媲美，而且沒(méi)有那么昂貴的價(jià)格和傲慢的客服。
最初是1985年在加利福尼亞大學(xué)伯克利分校開(kāi)發(fā)的，作為Ingres數(shù)據(jù)庫(kù)的后繼。PostgreSQL是完全由社區(qū)驅(qū)動(dòng)的開(kāi)源項(xiàng)目。
它提供了單個(gè)完整功能的版本，而不像MySQL那樣提供了多個(gè)不同的社區(qū)版、商業(yè)版與企業(yè)版。
PostgreSQL基于自由的BSD/MIT許可，組織可以使用、復(fù)制、修改和重新分發(fā)代碼，只需要提供一個(gè)版權(quán)聲明即可。
3、PostgreSQL相對(duì)于MySQL的優(yōu)勢(shì)
（1）不僅僅是關(guān)系型數(shù)據(jù)庫(kù)，還可以存儲(chǔ)：
array，不管是一位數(shù)組還是多為數(shù)組均支持json(hStore)和jsonb，相比使用text存儲(chǔ)接送要高效很多
（2）支持地理信息處理擴(kuò)展
（3）可以快速構(gòu)建REST API
（4）支持R-trees這樣可擴(kuò)展的索引類型，可以更方便地處理一些特殊數(shù)據(jù)。MySQL 處理樹(shù)狀的設(shè)計(jì)會(huì)很復(fù)雜, 而且需要寫很多代碼, 而 PostgreSQL 可以高效處理樹(shù)結(jié)構(gòu)。
（5）更好的外部數(shù)據(jù)源支持
（6）字符串沒(méi)有長(zhǎng)度限制
等等...
二、postgres中insertOrUpdate代碼實(shí)例
1、創(chuàng)建user表
CREATE TABLE public.t_user (
username varchar(100) NOT NULL,
age int4 NOT NULL DEFAULT 0,
"password" varchar(100) NULL,
deleted int4 NULL,
created_time timestamp NULL
);
CREATE UNIQUE INDEX t_user_union_name_age_password ON public.t_user USING btree (username, password, age);
2、簡(jiǎn)單的方式實(shí)現(xiàn)
insert
into
public.t_user (username , password,age,created_time)
values ('zs', '123', 18,now()), ('ls', '123456', 19,now()),('ww', '123', 20,now())
on conflict (username, age,password) do update set username = excluded.username,age = excluded.age,password = excluded.password,created_time = excluded.created_time
3、利用unnest函數(shù)實(shí)現(xiàn)
insert
into
public.t_user (username , password,age,created_time)
values (unnest(array['zs', 'ls', 'ww']), unnest(array['123', '123', '123456']),unnest(array[18, 19, 20]), unnest(array[now(), now(), now()]))
on conflict (username, age,password) do update set username = excluded.username,age = excluded.age,password = excluded.password,created_time = excluded.created_time
4、如果數(shù)據(jù)已存在，就就什么也不做
三、相關(guān)重點(diǎn)函數(shù)簡(jiǎn)介
1、unnest(anyarray)
unnest函數(shù)將輸入的數(shù)組轉(zhuǎn)換成一個(gè)表，這個(gè)表的每一列都代表相應(yīng)的一個(gè)數(shù)組中的元素。
如果unnest與其他字段一起出現(xiàn)在select中，就相當(dāng)于其他字段進(jìn)行了一次join。
主要用于完成行轉(zhuǎn)列的場(chǎng)景。
INSERT ON CONFLICT實(shí)現(xiàn)PostgreSQL插入更新特性。
EXCLUDED虛擬表，其包含我們要更新的記錄
四、userMapper.xml寫法
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.guor.dao.UserMapper">
<!-- 批量插入 -->
<insert id="batchInsert" parameterType="java.util.HashMap">
<include refid="batchInsertSql"></include>
</insert>
<sql id="batchInsertSql">
INSERT INTO ${map.tableInfo.schemaName}.${map.tableInfo.tableName}
(
"table_id",
"file_name",
"create_time",
<foreach collection="map.list.get(0)" index="key" item="value"
separator=",">
"${key}"
</foreach>
)
VALUES
<foreach collection="map.list" item="list" separator=",">
(
${map.tableInfo.tableId},
#{map.tableInfo.fileName},
now(),
<foreach collection="list" index="key" item="value"
separator=",">
<choose>
<when test="map.varcharList.contains(key)">#{value}
</when>
<when test="map.dateList.contains(key)">TO_TIMESTAMP(#{value},'yyyy-MM-dd hh24:mi:ss')
</when>
<otherwise>${value}
</otherwise>
</choose>
</foreach>
)
</foreach>
</sql>
<!-- 批量插入更新 -->
<insert id="batchInsertOrUpdate" parameterType="java.util.HashMap">
<include refid="batchInsertSql"></include>
on conflict (
file_name, table_id
<if test="map.tableInfo.flag">
, "id_number"
</if>
) do update
set
"table_id" = excluded."table_id",
"file_name" = excluded."file_name",
"create_time" = excluded."create_time",
<foreach collection="map.list.get(0)" index="key" separator=",">
"${key}" = excluded."${key}"
</foreach>
</insert>
</mapper>
五、MySQL中insertOrUpdate代碼實(shí)例
1、建表語(yǔ)句
CREATE TABLE `t_user` ( `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(0) NULL DEFAULT NULL, `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, `version` int(0) NOT NULL, UNIQUE INDEX `user_union_index`(`username`, `password`, `age`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2、普通方式
INSERT INTO t_user
(username,password,age,create_time)
VALUES('張三' ,'123456',18,NOW())
ON DUPLICATE KEY UPDATE
username='張三',
password='123456',
create_time=now()
3、ON DUPLICATE KEY UPDATE
insert into on duplicate key update表示插入更新數(shù)據(jù)，當(dāng)記錄中有PrimaryKey，或者unique索引的話，如果數(shù)據(jù)庫(kù)已經(jīng)存在數(shù)據(jù)，則用新數(shù)據(jù)更新（update），如果沒(méi)有數(shù)據(jù)效果則和insert into一樣。
INSERT INTO t_user (username,password,age,create_time,update_time,version) VALUES( 'zs' ,'123',10,now(),now(),1) ,( 'ls' ,'123456',20,now(),now(),1) ,( 'ww' ,'123',30,now(),now(),1) ON DUPLICATE KEY UPDATE username= VALUES(username) ,password=VALUES(password) ,age=VALUES(age) ,update_time=VALUES(update_time) ,version = version + 1
4、REPLACE INTO
replace into表示插入替換數(shù)據(jù)，當(dāng)記錄中有PrimaryKey，或者unique索引的話，如果數(shù)據(jù)庫(kù)已經(jīng)存在數(shù)據(jù)，則用新數(shù)據(jù)替換（先delete再insert），如果沒(méi)有數(shù)據(jù)效果則和insert into一樣。
REPLACE INTO t_user (username,password,age,create_time,update_time,version) VALUES ( 'zs' ,'123',10,now(),now(),1)
5、INSERT IGNORE INTO
insert ignore into表示盡可能的忽略沖突，暴力插入。
INSERT IGNORE INTO t_user (username,password,age,create_time,update_time,version) VALUES ( 'zs' ,'123',10,now(),now(),1) , ( '哪吒' ,'123',30,now(),now(),2)
6、小結(jié)
insert into values 或 insert into select批量插入時(shí)，都滿足事務(wù)的原子性與一致性，但要注意insert into select的加鎖問(wèn)題。
replace into與insert into on duplicate key update都可以實(shí)現(xiàn)批量的插入更新，具體是更新還是插入取決與記錄中的pk或uk數(shù)據(jù)在表中是否存在。
如果存在，前者是先delete后insert，后者是update。
insert ignore into會(huì)忽略很多數(shù)據(jù)上的沖突與約束，平時(shí)很少使用。
到此這篇關(guān)于如何實(shí)現(xiàn)MySQL + PostgreSQL批量插入更新insertOrUpdate的文章就介紹到這了,更多相關(guān)MySQL + PostgreSQL批量插入更新insertOrUpdate內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信