mysql where 批量修改，mysql批量修改

素颜马尾好姑娘i 2023-03-13 05:19 4阅读 0赞

update  product\_image set image='a.jpeg' where product\_id = 100

update  product\_image set image='b.jpeg' where product\_id = 200

循环去修改这样的语句性能很差的

测试的mysql版本是：5.5.48 和 5.7.24

mysql 批量修改例子表结构：

CREATE TABLE `asc_product_image`  (
      `product_image_id` int(11) NOT NULL AUTO_INCREMENT,
      `product_id` int(11) NOT NULL,
      `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `sort_order` int(3) NOT NULL DEFAULT 0,
      PRIMARY KEY (`product_image_id`) USING BTREE,
      INDEX `product_id`(`product_id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 12257 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

数据如下：

![20200511142141844.png][]

### mysql where 批量修改一个字段 image字段：条件：product\_image\_id IN (7111,7112,7113) ###

**语句解析：**

更新product\_image\_id = 7111 的 image='catalog/product4.jpeg'

更新product\_image\_id = 7112 的 image='catalog/product5.jpeg'

更新product\_image\_id = 7113 的 image='catalog/product6.jpeg'

UPDATE asc_product_image 
        SET `image` = CASE product_image_id 
            WHEN 7111 THEN 'catalog/product4.jpeg'
            WHEN 7112 THEN 'catalog/product5.jpeg'
            WHEN 7113 THEN 'catalog/product6.jpeg'
        END
    WHERE product_image_id IN (7111,7112,7113)

### mysql where 批量修改多个字段就，image字段和sort\_order字段 ###

**语句解析：**

更新product\_image\_id = 7111 的 image='catalog/product4.jpeg',sort\_order=6

更新product\_image\_id = 7112 的 image='catalog/product5.jpeg',sort\_order=7

更新product\_image\_id = 7113 的 image='catalog/product6.jpeg',sort\_order=8

UPDATE asc_product_image 
        SET `image` = CASE product_image_id 
            WHEN 7111 THEN 'catalog/product1.jpeg'
            WHEN 7112 THEN 'catalog/product2.jpeg'
            WHEN 7113 THEN 'catalog/product3.jpeg'
        END,
    		`sort_order` = CASE product_image_id 
            WHEN 7111 THEN 6
            WHEN 7112 THEN 7
            WHEN 7113 THEN 8
        END
    WHERE product_image_id IN (7111,7112,7113)

放段php代码，其实就是拼接成上面的sql：

$update = "UPDATE asc_product_image SET image = CASE product_image_id ";
          $where  = '';
            foreach ($info->rows as $k=>$v){
    
                    $product_image_id = $v['product_image_id'];
                    $image   = "catalog/product".mt_rand(1,10).'.jpeg';
                    $update .= "WHEN {$product_image_id} THEN '{$image}' ";
    
                    $where   .= "$product_image_id,";
            }
    
            $update .= " END ";
            $where   = trim($where,',');
            $update .= " where product_image_id IN ($where)";
            $this->db->query($update);

也可以用replace into 这样的方法哦，我不喜欢。

[20200511142141844.png]: /images/20230312/2246b3208d504ebfa103651152de7ce9.png