mysql批量插入、更新

骑猪看日落 2021-12-24 02:41 464阅读 0赞

# 1、创建表 #

CREATE TABLE `user`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
      `age` int(11) NOT NULL COMMENT '年龄',
      `status` int(11) NOT NULL DEFAULT 0 COMMENT '状态',
      `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE INDEX `name`(`name`, `age`) USING BTREE
    )

添加数据

INSERT INTO `user` VALUES (296, '李四0', 12, 0, '2019-06-25 19:33:26', '2019-06-25 20:42:40');
    INSERT INTO `user` VALUES (297, '李四1', 13, 1, '2019-06-25 19:33:26', '2019-06-25 20:42:40');
    INSERT INTO `user` VALUES (298, '李四2', 14, 0, '2019-06-25 19:49:55', '2019-06-25 20:42:40');

# 2、批量更新方式 #

## 2.1 使用case when then ##

>UPDATE USER 
    	SET NAME =
    CASE
    	id 
    	WHEN 296 THEN
    	"李四10" 
    	WHEN 297 THEN
    	"李四11" 
    	WHEN 298 THEN
    	"李四12" 
    	END,
    	age =
    CASE
    	id 
    	WHEN 296 THEN
    	22 
    	WHEN 297 THEN
    	23
    	WHEN 298 THEN
    	24
    END 
    WHERE
    	id IN ( 296, 297, 298 );
    > Affected rows: 3
    > 时间: 0.001s

**where的作用：**即是将条件语句写在了一起。这里的where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

**mybatis示例：**

UPDATE user
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name!=null">
                    when id=#{item.id} then #{item.dateStart}
                </if>
            </foreach>
        </trim>
        <trim prefix="class_name =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.className!=null">
                    when id=#{item.id} then #{item.className}
                </if>
            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.id}
    </foreach>

## 2.2 replace into（不建议） ##

>REPLACE INTO USER ( NAME, age, `status` )
    VALUES
    	( '李四10', 22, 110 ),
    	( '李四11', 23, 111 ),
    	( '李四13', 24, 112 );
    > Affected rows: 5
    > 时间: 0.002s

先检测该记录是否存在(PRIMARY KEY或一个UNIQUE索引)，如果存在，先删除（delete），在插入（insert），受影响为2行；如果不存在，插入（insert），受影响为1行。

### 存在的问题 ###

*  如果记录存在，每次执行完，主键自增id就变了（相当于重新insert了一条），对于联表查询，如果主表的id变了，其它子表没做好同步，会造成数据不一致。
 *  自增id会跳跃自增，增长过快。

## 2.3 on duplicate key update ##

>INSERT INTO USER ( NAME, age, STATUS, update_time )
    VALUES
    	( '李四10', 22, 11, now( ) ),
    	( '李四11', 23, 12, now( ) ),
    	( '李四15', 25, 15, now( ) ) 
    	ON DUPLICATE KEY UPDATE STATUS =
    VALUES
    	( STATUS ),
    	update_time =
    VALUES
    	( update_time );
    > Affected rows: 5
    > 时间: 0.003s

先检测该记录是否存在(PRIMARY KEY或一个UNIQUE索引)，如果存在，更新（update）,只更新需要更新的字段，受影响为2行；如果不存在，插入（insert），受影响为1行。

### 存在问题 ###

*  自增id会跳跃自增，增长过快。建议id使用如下类型

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT

## 2.4 insert ignore into（避免重复添加） ##

>INSERT IGNORE INTO USER ( NAME, age, `status` )
    VALUES
    	( '李四10', 22, 110 ),
    	( '李四11', 23, 111 ),
    	( '李四16', 26, 116 );
    > Affected rows: 1
    > 时间: 0.002s

先检测该记录是否存在(PRIMARY KEY或一个UNIQUE索引)，如果存在，忽略，受影响为0行；如果不存在，插入（insert），受影响为1行。