V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
mebtte
V2EX  ›  MySQL

SQL 中批量更新应该怎么写?

  •  
  •   mebtte ·
    mebtte · 2020-09-13 17:52:03 +08:00 · 2910 次点击
    这是一个创建于 660 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如我有一个表 { id: int, order: int } , 现在想批量更新, 比如 id=1 set order=2, id=2 set order=3 , id 和 order 对应的值是外面传进来的, 一条 update 语句能做到吗?

    15 条回复    2020-09-25 17:24:39 +08:00
    ss098
        1
    ss098  
       2020-09-13 18:45:05 +08:00
    不能。
    codingadog
        2
    codingadog  
       2020-09-13 18:47:48 +08:00 via Android
    就俩字段?一对多多对一多对多根据情况加唯一索引然后 replace into
    mccoymir
        3
    mccoymir  
       2020-09-13 19:33:35 +08:00
    case when ...
    a3613051
        4
    a3613051  
       2020-09-13 19:39:02 +08:00
    要么 case when 要么 jdbcurl 加上 allowMultiQueries=true 一次发多条。replace into 太暴力了 主键全变 关联表不是爆炸了。
    fox0001
        5
    fox0001  
       2020-09-13 19:52:55 +08:00 via Android
    数量在可接受范围的话( 30 万以内吧),可以把对应关系导出到 CVS 或者 excel 文件,然后写个公式生成所有 update 语句,再丢到服务器上更新
    zhangysh1995
        6
    zhangysh1995  
       2020-09-13 20:30:33 +08:00
    ````
    update x set order = (
    case
    when 1
    then 2

    ....
    else
    end
    )
    ````
    vone
        7
    vone  
       2020-09-13 21:07:14 +08:00
    update t
    set t.order=ods.order
    from xxx t
    join (
    select 1 id, 2 order
    union all
    select 2 id, 3 order
    ......
    )ods on ods.id=t.id

    数据量大时 ods 应改为使用临时表导入。
    gavindexu
        8
    gavindexu  
       2020-09-13 22:43:32 +08:00 via iPhone
    先建个表?索引 join 再 update 。
    操作完成后,再把那个临时建的表 drop 掉。
    wzwwzw
        9
    wzwwzw  
       2020-09-13 23:23:18 +08:00
    case when
    buliugu
        10
    buliugu  
       2020-09-13 23:24:51 +08:00
    mysql 可以用 ON DUPLICATE KEY UPDATE
    tomari
        11
    tomari  
       2020-09-14 00:37:46 +08:00
    @gavindexu 这样不会有原子性的问题吗
    xxxyy2y
        12
    xxxyy2y  
       2020-09-14 11:13:24 +08:00
    4 楼已经说了,可以做到啊 allowMultiQueries=true xml 加个 foreach 标签就行了 批量更新 批量插入会打包成一个事务,要考虑表是否频繁更新,如果是频繁更新就容易产生锁表,
    swcat
        13
    swcat  
       2020-09-14 12:41:32 +08:00
    id 是主键吧, 那么可以这样
    ```sql
    insert into tttt(`id`)

    SELECT @row := @row + 1 AS id FROM
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
    (SELECT @row:=0) t

    on duplicate key update `order` = `id`;
    ```
    MaxFang
        14
    MaxFang  
       2020-09-14 21:10:32 +08:00
    看表结构,如果有 uk,看情况可以使用 ON DUPLICATE KEY UPDATE 插入更新。
    另外。。。推荐尽可能保持 SQL 的简单可读,赋值操作放在上层代码中解决。
    THESDZ
        15
    THESDZ  
       2020-09-25 17:24:39 +08:00
    update set value = case when id ='1' then 'v1' ... else value end
    where id in ('1','2','3')
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1954 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 16:47 · PVG 00:47 · LAX 09:47 · JFK 12:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.