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
abcdefghi
V2EX  ›  MySQL

update 一条记录的值 WHERE 条件如何做到扩大限制到全表?

  •  
  •   abcdefghi · 2017-04-27 11:25:25 +08:00 · 5003 次点击
    这是一个创建于 2527 天前的主题,其中的信息可能已经有所发展或是发生改变。
    UPDATE `user` SET `mobile`='13888888888' 
    where `id`=1 AND 全 user 表 mobile 字段所有记录没有等于 13888888888
    

    现在的实现方法需要分两步

    1、先查有没有 mobile 记录值等于 13888888888 2、没有才 UPDATE

    繁琐又有风险,怕在查询和 update 的间隔被插入 但是在表上面这个字段又不能做唯一限制 因为这个值初始插入是可以为空的

    这个业务需求能用一条 sql 语句实现吗

    14 条回复    2017-04-28 08:36:20 +08:00
    Nicksxs
        1
    Nicksxs  
       2017-04-27 11:56:30 +08:00
    子查询咯
    ```
    UPDATE `user` SET `mobile`='13888888888'
    where 1 > (select count(*) from (select * from user) a where `mobile` = '13888888888') and `id` = 1;
    ```
    azh7138m
        2
    azh7138m  
       2017-04-27 12:01:10 +08:00 via Android
    > 怕在查询和 update 的间隔被插入

    呃,事务不是用来干这种事情的吗?
    msg7086
        3
    msg7086  
       2017-04-27 13:25:57 +08:00
    MySQL 的唯一索引支持 NULL 值啊……
    abcdefghi
        4
    abcdefghi  
    OP
       2017-04-27 13:53:16 +08:00
    蟹蟹各位

    根据 1 楼的方法又找到了一种方法

    ```
    UPDATE `zc_users` SET `mobile`='13888888888'
    where 1 > (select count(*) from (select * from zc_users) a where `mobile` = '13888888888') and `user_id` = 1;

    ```

    ```
    update zc_users set mobile='13888888888' where user_id=1 And not exists(select user_id from (select * from zc_users) a where mobile='13888888888');
    ```
    reus
        5
    reus  
       2017-04-27 14:57:33 +08:00
    可以把 mobile 另外存一个表,这样就可以用 unique 索引了。

    或者换用 Postgresql,可以建 partial index:CREATE UNIQUE INDEX unique_mobile ON user (mobile) WHERE mobile IS NOT NULL。
    geelaw
        6
    geelaw  
       2017-04-27 18:02:45 +08:00 via iPhone
    我的妈……你们没有用过 UNIQUE constraint 吗?
    reus
        7
    reus  
       2017-04-27 18:48:28 +08:00
    @geelaw 看清楚需求
    geelaw
        8
    geelaw  
       2017-04-27 18:53:27 +08:00
    @reus 这里只有一个表啊,而且你上面的那个 unique index 已经是解决方案了,为什么要再建立一个表?
    fzleee
        9
    fzleee  
       2017-04-27 19:24:38 +08:00
    @azh7138m
    即使读取和写入这两个任务在一个事务里面,也不能解决这种问题的吧。
    关键是要添加约束
    reus
        10
    reus  
       2017-04-27 20:17:26 +08:00
    @geelaw “但是在表上面这个字段又不能做唯一限制 因为这个值初始插入是可以为空的”
    geelaw
        11
    geelaw  
       2017-04-27 20:22:52 +08:00
    @reus SQL Server 的 UNIQUE 可以不检查 NULL,这是 ANSI 标准 SQL: 92, SQL: 1999, SQL: 2003 要求的。此外 MySQL、PostgreSQL、SQLite、Oracle、Firebird 也都允许多重 NULL。

    提问之前先查阅文档和自己试试。
    geelaw
        12
    geelaw  
       2017-04-27 20:29:58 +08:00
    另外即使你使用的数据库引擎在 UNIQUE constraint 里面要求只有最多一个 NULL,鉴于手机号一般也是字符串存储(你不会想用数字,这是一个很恶心的事情),假设你用 char(50),你可以用 '@' + CONVERT(CHAR(40), NEWID()) 表示 NULL,然后你可以用一个 CHECK constraint 确保手机号具有正确的格式(要么是 11 位以 1 开头的数,要么是 '@' 开头)。
    ryd994
        13
    ryd994  
       2017-04-28 08:10:24 +08:00 via Android
    说真的,这个其实没必要掐死
    相信你这个也是用户输入的
    两个用户同时操作,输入同一个手机号,而且间隔够小能出竞态,那明显是自己作
    只能说活该丫的
    加一个索引还影响性能

    看你本身需求吧,如果手机号就有索引那加个 unique 几乎免费
    woshixiaohao1982
        14
    woshixiaohao1982  
       2017-04-28 08:36:20 +08:00
    主键 或者 唯一约束 unique index 都是可以的,实际上没有必要上事务,事务通常是针对有价值的数据,而且事务会产生锁,很烦人的,通常针对注册这种业务,unique index 就搞定了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3647 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 10:44 · PVG 18:44 · LAX 03:44 · JFK 06:44
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.