mysql 插入带更新

日期:2020-06-11 浏览:93次

因为需求是拼多多批量更新带插入新数据,只能采用第一和第二种。第一种更新的时候,是删除存在咋更新,会导致改数据的其他无需更新的字段清空为默认值。所以采用第二种方法。

例子:更新10万数据,唯一索引给50000,导致有一半的数据都是重复的,总计耗时为5秒。

/**
 * 测试10万数据插入
 */
public function index()
{
    echo '我是开始'. date('Y-m-d H:i:s').'<br / >';
    $data   = [];
    for ($i=0;$i<10;$i++){
        for ($ii=0;$ii<10000;$ii++){
            $num    = rand(1,50000);
            $data[] = "('刘备',$num,33,33)";
        }
    }

    //入库
    $sql = 'insert into test (`name`,`openid`,`sex`,`age`) values ' . join(',',$data). 'on duplicate key update ' . 'name=values(name),openid=values(openid),sex=values(sex),age=values(age)' ;
    $res    =  Db::execute($sql);
    echo '我是结束'. date('Y-m-d H:i:s').'<br / >';
}

数据表:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`openid` int(10) UNSIGNED NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(10) NULL DEFAULT NULL,
`desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ctime` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `openid`(`openid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

页面打印:
我是开始2020-06-11 17:00:36
我是结束2020-06-11 17:00:40

 

mysql 批量更新共有以下四种办法
1、.replace into 批量更新


1
<span class="hljs-keyword">replace</span> <span class="hljs-keyword">into</span> test_tbl (<span class="hljs-keyword">id</span>,dr) <span class="hljs-keyword">values</span> (<span class="hljs-number">1</span>,<span class="hljs-string">'2'</span>),(<span class="hljs-number">2</span>,<span class="hljs-string">'3'</span>),...(x,<span class="hljs-string">'y'</span>);

2、insert into …on duplicate key update批量更新

insert into test_tbl (id,dr) values (1,’2′),(2,’3′),…(x,’y’) on duplicate key update dr=values(dr);

3.创建临时表,先更新临时表,然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,’gone’), (1,’xx’),…(m,’yy’);
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
注意:这种方法需要用户有temporary 表的create 权限。

4、使用mysql 自带的语句构建批量更新
mysql 实现批量 可以用点小技巧来实现:

UPDATE yoiurtable
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)

没有评论

发表评论