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

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

这是一个创建于 2769 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

2017-04-27 12:01:10 +08:00 via Android

> 怕在查询和 update 的间隔被插入

呃，事务不是用来干这种事情的吗？

msg7086

2017-04-27 13:25:57 +08:00

MySQL 的唯一索引支持 NULL 值啊……

abcdefghi

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

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

2017-04-27 18:02:45 +08:00 via iPhone

我的妈……你们没有用过 UNIQUE constraint 吗？

reus

2017-04-27 18:48:28 +08:00

@geelaw 看清楚需求

geelaw

2017-04-27 18:53:27 +08:00

@reus 这里只有一个表啊，而且你上面的那个 unique index 已经是解决方案了，为什么要再建立一个表？

fzleee

2017-04-27 19:24:38 +08:00

@azh7138m
即使读取和写入这两个任务在一个事务里面，也不能解决这种问题的吧。
关键是要添加约束

reus

2017-04-27 20:17:26 +08:00

@geelaw “但是在表上面这个字段又不能做唯一限制因为这个值初始插入是可以为空的”

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

2017-04-27 20:29:58 +08:00

另外即使你使用的数据库引擎在 UNIQUE constraint 里面要求只有最多一个 NULL，鉴于手机号一般也是字符串存储（你不会想用数字，这是一个很恶心的事情），假设你用 char(50)，你可以用 '@' + CONVERT(CHAR(40), NEWID()) 表示 NULL，然后你可以用一个 CHECK constraint 确保手机号具有正确的格式（要么是 11 位以 1 开头的数，要么是 '@' 开头）。

ryd994

2017-04-28 08:10:24 +08:00 via Android

说真的，这个其实没必要掐死
相信你这个也是用户输入的
两个用户同时操作，输入同一个手机号，而且间隔够小能出竞态，那明显是自己作
只能说活该丫的
加一个索引还影响性能

看你本身需求吧，如果手机号就有索引那加个 unique 几乎免费

woshixiaohao1982

2017-04-28 08:36:20 +08:00

主键或者唯一约束 unique index 都是可以的，实际上没有必要上事务，事务通常是针对有价值的数据，而且事务会产生锁，很烦人的，通常针对注册这种业务，unique index 就搞定了