Practical MySQL + PostgreSQL batch insert update insertOrUpdate

1. Baidu Encyclopedia

1. MySQL

MySQL claims to be the most popular open source database. The M in LAMP refers to MySQL. Applications built on LAMP will use MySQL, such as WordPress, Drupal and most other PHP open source programs.

MySQL was originally developed by MySQL AB, then sold to Sun for $1 billion in 2008, which was acquired by Oracle in 2010. Oracle supports multiple versions of MySQL: Standard, Enterprise, Classic, Cluster, Embedded, and Community. Some of them are free to download, while others are paid.

Its core code is based on the GPL license. Since MySQL is controlled by Oracle, the community is worried that it will affect the open source of MySQL, so some branches have been developed, such as MariaDB and Percona.

2. PostgreSQL

PostgreSQL bills itself as the world's most advanced open source database.

Some fans of PostgreSQL say it's comparable to Oracle without the high price tag and overbearing customer service.

It was originally developed at the University of California, Berkeley in 1985 as a successor to the Ingres database. PostgreSQL is a completely community-driven open source project.

It provides a single fully functional version, unlike MySQL which provides multiple different community, business, and enterprise editions.

PostgreSQL is based on the liberal BSD/MIT license, which allows organizations to use, copy, modify, and redistribute the code as long as they provide a copyright notice.

3. Advantages of PostgreSQL over MySQL

(1) Not only a relational database, but also can store:

array, whether it is a one-bit array or a multi-bit array, supports json (hStore) and jsonb, which is much more efficient than using text storage.

(2) Support geographic information processing extension

(3) REST API can be quickly built

(4) Support for scalable index types such as R-trees makes it easier to process some special data. It is very complicated for MySQL to handle tree-like designs, and it requires a lot of code to write, but PostgreSQL can handle tree structures efficiently.

(5) Better support for external data sources

(6) There is no length limit for strings

etc...

2. InsertOrUpdate code example in postgres

1. Create the user table

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. Simple way to achieve

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. Use the unnest function to implement

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. If the data already exists, do nothing

3. Introduction to related key functions

1. unnest(anyarray)

The unnest function converts the input array into a table where each column represents an element in the corresponding array.
If unnest appears in select together with other fields, it is equivalent to a join with other fields.

Mainly used to complete the scenario of row to column conversion.

INSERT ON CONFLICT implements the PostgreSQL insert update feature.

The EXCLUDED virtual table contains the records we want to update

4. Writing 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">
 
    <!-- Batch Insert -->
    <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>
 
    <!-- Batch insert update -->
    <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>

V. InsertOrUpdate code example in MySQL

1. Create table statement

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. Ordinary method

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 means inserting and updating data. When there is a PrimaryKey or a unique index in the record, if the database already has data, it will be updated with the new data. If there is no data, the effect is the same as 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 means inserting and replacing data. When there is a PrimaryKey or a unique index in the record, if the database already has data, it will be replaced with the new data (delete first and then insert). If there is no data, the effect is the same as 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 means ignoring conflicts as much as possible and inserting violently.

INSERT IGNORE INTO t_user 
(username,password,age,create_time,update_time,version) 
VALUES 
( 'zs' ,'123',10,now(),now(),1) ,
('Nezha','123',30,now(),now(),2)

6. Summary

When inserting into values ​​or insert into select in batches, the atomicity and consistency of the transaction are met, but attention should be paid to the locking issue of insert into select.
Both replace into and insert into on duplicate key update can implement batch insert and update. Whether it is update or insert depends on whether the pk or uk data in the record exists in the table.

If it exists, the former is to delete first and then insert, and the latter is to update.
insert ignore into will ignore many data conflicts and constraints and is rarely used.

This is the end of this article on how to implement MySQL + PostgreSQL batch insert or update. For more information about MySQL + PostgreSQL batch insert or update, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!