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行。