MySQL 限制 MySQL 字段的值(允许一个特定值和多个其他值.)? [英] MySQL restricting the value of a MySQL field (allow one specific value and multiple others.)?

问题描述

今天偶然发现一个棘手的问题，我想限制某个字段的某些值出现的次数，这是我的表格:

I'm stumbled on a tricky problem today, I want to restrict the number of appearance of some values of a field, here's my table:

CREATE TABLE `test_table` (
  `id` varchar(40) COLLATE utf8_bin NOT NULL,
  `create_time` datetime NOT NULL DEFAULT '2010-01-01 00:00:00',
  `user_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `device_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `is_owner` boolean NOT NULL,
  `user_nickname` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_device` (`device_id`,`is_owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

比如我们有两个用户Bob和Tom，一个设备Echo，user_id之间的关系>、device_id 和 is_owner 是:

For example, we have two users Bob and Tom, and one device Echo, the relationships among user_id, device_id and is_owner are:

1. 一个设备只能有一个所有者，这意味着只允许一对(设备:Echo，is_owner:true).
2. 一个设备可以有多个用户，也就是说允许对(device: Echo, is_owner: false)多次出现.

1. A device ONLY can have one owner, which means only a pair of (device: Echo, is_owner: true) is allowed.
2. A device can have multiple users, this is to say that multiple appearance of the pair(device: Echo, is_owner: false) is allowed.

唯一约束在我的情况下无济于事，请帮忙.

The unique constraint won't help in my case, please help.

推荐答案

如果您使用 null 而不是 false 来表示用户不是所有者，那么您可以使用唯一索引来提供此限制，因为 在 MySQL 中唯一索引允许多个空值.显然，您需要使 is_owner 字段可以为空才能使其工作.

If you use null instead of false to indicate that a user is not an owner, then you can use a unique index to provide this restriction because in MySQL unique indexes allow multiple null values. Obviously, you need to make the is_owner field nullable for this to work.

否则，这个控件最好放在应用层，而不是放在数据库中.在数据库中，您可以使用触发器来检查此条件并防止通过引发 sql 错误设置额外的所有者记录.

Otherwise, this control is better placed in the application layer, than in the database. In the database you can use a trigger to check this condition and prevent extra owner records to be set by raising an sql error.

这篇关于MySQL 限制 MySQL 字段的值(允许一个特定值和多个其他值.)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！