修改表：只允许唯一或空值。

24 浏览
0 Comments

修改表：只允许唯一或空值。

我有一个现有的mysql表格，我想设置一个字段（varchar），要么是唯一的，要么是空的（默认值）。所以当用户不输入值时，它是空的，否则用户必须输入唯一的值。我应该如何做到这一点？

0

3 答案

0 Comments

alter table: only allow either unique or empty这个问题的出现的原因是在MySQL中，当我们给一个列设置了UNIQUE约束后，该列的值必须是唯一的，不能重复。然而有时候我们希望该列的值可以为空。这就导致了一个矛盾：既要求唯一性，又要求允许为空。

解决这个问题的方法是使用触发器（trigger）。触发器是一种特殊的存储过程，它会在指定的事件发生时自动执行。在这个问题中，我们可以编写一个触发器，在每次更新表时检查该列的值是否符合要求。

下面是一个例子，演示了如何使用触发器解决这个问题：

-- 创建一个表，列a既要求唯一，又要求允许为空
CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  a VARCHAR(50) UNIQUE
);
-- 创建一个触发器，在每次更新表时检查列a的值是否符合要求
DELIMITER //
CREATE TRIGGER check_unique_or_empty
  BEFORE INSERT ON test
  FOR EACH ROW
BEGIN
  IF NEW.a IS NOT NULL AND EXISTS (SELECT * FROM test WHERE a = NEW.a AND id != NEW.id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Column a must be unique or empty';
  END IF;
END //
DELIMITER ;
-- 测试插入数据
INSERT INTO test (a) VALUES ('abc'); -- 正常插入，列a唯一
INSERT INTO test (a) VALUES ('abc'); -- 触发器报错，列a重复
INSERT INTO test (a) VALUES (NULL); -- 正常插入，列a为空

在上面的例子中，我们首先创建了一个表`test`，其中列`a`既要求唯一，又要求允许为空。然后我们创建了一个触发器`check_unique_or_empty`，在每次插入数据之前会检查列`a`的值是否符合要求。如果列`a`的值既不为空，又存在其他记录的`a`值与之相同，则触发器会抛出一个错误，提示列`a`必须是唯一或为空。

通过使用触发器，我们可以在MySQL中实现对列唯一性和允许为空的双重约束。这样就解决了alter table: only allow either unique or empty这个问题。

0
0 Comments

在MySQL中，可以使用约束类型UNIQUE来实现这一目的。如果声明一个列为UNIQUE并允许其中包含NULL值，那么所有的值都将被强制为唯一，除非它们是NULL值。如果你想要防止空字符串('')被存储，你将不得不在代码级别上进行管理，因为MySQL会将其视为另一个字符串并允许它在UNIQUE列中出现一次。如果没有给这样的列传递值，它将自动默认为NULL，但你也可以在代码中以编程方式指定NULL(不带引号)在将值传递给数据库之前。

在接下来的文章中，我将假设表的名称为tablename，列的名称为columnname，varchar字段的大小为255。

如果你想要修改一个已存在的表，而不是创建一个新表：

如果该列尚未支持null值：

ALTER TABLE tablename ALTER COLUMN columnname VARCHAR(255) NULL

添加唯一约束：

ALTER TABLE tablename ADD CONSTRAINT un_tablename_column UNIQUE (columnname)

（我总是给约束命名，这样以后可以具体引用它们）

或者，如果你想在一个新表上进行操作：

CREATE TABLE tablename (
    [...]
    columnname varchar(255) UNIQUE NULL,
    [...]
)

我非常确定这对于MySQL来说并不适用，这可能是你需要注意的特殊引擎设置，如果不是这种情况，就没有理由为什么这个例子在第二次插入时会失败。

对不起，我错了，sqlfiddle不允许我保存失败的例子：sqlfiddle.com/#!2/b3a93你需要在这个例子中去除注释才能看到我在上一个评论中所描述的情况。

它失败是因为你在语句之间忘记了分号。

啊啊啊，非常抱歉，我撤回我之前的说法，这是我的坏习惯，来自SQL Server :)（但仍然是奇怪的行为^^）

事实上，可以将其视为“存在即唯一”。

0
0 Comments

从我所了解的情况来看，根据标准，一个表中的列不应该既允许空值又有唯一约束；如果允许这样的情况出现，那可能是由于特殊的mysql引擎导致的（这样可能会在其他地方产生不可预测或意外的行为）。

你的问题似乎与这个问题相反：

MySQL: UNIQUE, but DEFAULT NULL - allowed by creating of table. More than 1 NULL is allowed to insert. Why?

不过你可以使用触发器来强制实现这一点，你可以在插入/更新时编写一个触发器来检查该值是否已经存在，如果存在则立即将其删除。

到底是什么不可能呢？

0