“This is my 39th day of participating in the First Challenge 2022. For more details: First Challenge 2022.”

I. Baidu Baike

1, MySQL,

MySQL claims to be the most popular open source database. The M in LAMP stands for MySQL. Applications built on LAMP use MySQL, like WordPress, Drupal, and most open source PHP programs. MySQL was originally developed by MySQL AB and sold for $1 billion in 2008 to Sun, 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, others are for a fee. Its core code is licensed under the GPL. The community is worried about the impact of MySQL being open source because MySQL is controlled by Oracle, so some branches have been developed, such as MariaDB and Percona.

2, PostgreSQL

PostgreSQL bills itself as the most advanced open source database in the world. Some fans of PostgreSQL say it can compete with Oracle without the high price and arrogant customer service. It was originally developed in 1985 at the University of California, Berkeley, as a successor to the Ingres database. PostgreSQL is a completely community-driven open source project. It offers a single full-featured version, rather than multiple community, business, and enterprise versions like MySQL. PostgreSQL is under a free BSD/MIT license that allows organizations to use, copy, modify, and redistribute code as long as they provide a copyright notice.

PostgreSQL’s advantages over MySQL

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

Json (hStore) and JSONB are supported for array, whether it is a one-bit array or a multi-bit array, which is much more efficient than using text storage. (2) Support geographic information processing extension

(3) You can quickly build REST apis

(4) Support r-trees such extensible index types, can be more convenient to deal with some special data. MySQL can be complex and require a lot of code to design trees, whereas PostgreSQL can handle trees efficiently.

(5) Better support for external data sources

(6) The string has no length limit

Postgres insertOrUpdate code instance

Create table 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);
Copy the code

2, simple way to implement

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
Copy the code

3. Use unnest function to achieve

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
Copy the code

4. If the data exists, do nothing

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 nothing
Copy the code

Iii. Brief introduction of relevant 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 a SELECT with other fields, the other fields are joined.

It is mainly used to complete the row to column scenario.

select 'Joe' as name, unnest(Array['Chinese'.'mathematics'.'English']) as course;
 name | course
------+--------Zhang SAN|Chinese zhang SAN|Zhang SAN mathematics|English (3 rows)
Copy the code

INSERT ON CONFLICT Implements PostgreSQL INSERT update features.

The EXCLUDED virtual table, which contains the records we want to update

4, 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>The ${value}
                    </otherwise>
                </choose>
            </foreach>
            )
        </foreach>
    </sql>
 
    <!-- Insert updates in batches -->
    <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>
Copy the code

MySQL insertOrUpdate code example

1. Build a predicate sentence

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;
Copy the code

2. Common way

INSERT INTO t_user
(username,password,age,create_time) 
VALUES('Joe' ,'123456'.18,NOW())
ON DUPLICATE KEY UPDATE 
username='Joe',
password='123456',
create_time=now()
Copy the code

3. ON DUPLICATE KEY UPDATE

Insert into on duplicate key UPDATE (duplicate key update, duplicate key update, duplicate key update) 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
Copy the code

4, REPLACE INTO

Replace into inserts a PrimaryKey or unique index into the database. If there is a PrimaryKey or unique index in the database, replace it with a new index if it already exists. If there is no PrimaryKey in the database, replace it with a new index.

REPLACE INTO t_user 
(username,password,age,create_time,update_time,version) 
VALUES 
( 'zs' ,'123'.10,now(),now(),1) 
Copy the code

INSERT IGNORE INTO

Insert ignore into indicates to ignore conflicts and violent inserts as much as possible.

INSERT IGNORE INTO t_user 
(username,password,age,create_time,update_time,version) 
VALUES 
( 'zs' ,'123'.10,now(),now(),1), ('which zha' ,'123'.30,now(),now(),2) 
Copy the code

6, summary

When insert into values or INSERT into SELECT is batch inserted, atomicity and consistency of transactions are met, but pay attention to insert into SELECT lock problem. Replace into and INSERT into on duplicate key update can both implement batch insert updates. The specific update or insert depends on whether pk or UK data in the record exists in the table. If present, the former is delete followed by INSERT and the latter is update. Insert Ignore into ignores many data conflicts and constraints and is rarely used.