在 MySQL 中使用检查约束来控制字符串长度

Question

我被一个问题困扰了！

我已经使用 MySQL 设置了第一个检查约束，但不幸的是我遇到了问题。当插入应该无法通过测试的行时，无论如何都会插入该行。

结构：

CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uname VARCHAR(10) NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  mail VARCHAR(50) NOT NULL,
  PRIMARY KEY (id),
  CHECK (LENGTH(fname) > 30)
);

insert 语句：

INSERT INTO user VALUES (null, 'user', 'Fname', 'Lname', '[email protected]');

fname 列中的字符串长度应该太短，但无论如何都插入了。

我很确定我在这里缺少一些基本的东西。

Answer 1

MySQL 不在任何引擎上强制执行 CHECK 约束.
这让我想问，为什么要将 fname 列声明为 VARCHAR(50)，但又想强制它的长度只能是 30 个字符？

也就是说，唯一的选择是使用触发器：

CREATE TRIGGER t1 BEFORE INSERT ON user
 FOR EACH ROW BEGIN

   DECLARE numLength INT;
   SET numLength = (SELECT LENGTH(NEW.fname));

   IF (numLength > 30) THEN
     SET NEW.col = 1/0;
   END IF;

END;

Answer 2

从 Mysql 8.0.16 开始，可以强制执行检查约束：

CREATE TABLE socials (ssn varchar(9) NOT NULL, 
UNIQUE KEY ssn (ssn), 
CONSTRAINT socials_chk_1 CHECK ((length(ssn) = 9));

当您尝试插入长度不正确的数据时，它会失败：

insert into socials values('12345678'); -- too short
(3819, "Check constraint 'socials_chk_1' is violated.")

当您尝试插入长度正确的数据时，它会成功：

insert into socials values('123456789'); -- just right
Query OK, 1 row affected

但请注意，当 varchar 长度与约束长度匹配时，约束会出现意外行为，并且输入值比预期长：

insert into socials values('1234567800000'); -- too long
Query OK, 1 row affected
select * from socials;
+-----------+
| ssn       |
|-----------|
| 123456789 |
| 123456780 |
+-----------+

发生了什么？看起来 MySQL 正在将输入字符串截断为 varchar 长度，然后检查约束，这会导致插入“123456780”，这不是所需的行为。作为一种解决方法，使 varchar 字段至少比约束长度长一个字符，并且检查将按预期进行验证：

alter table socials change ssn ssn varchar(10);

然后尝试插入比检查长度长的数据：

insert into socials values('1234567800000'); -- too long
(3819, "Check constraint 'socials_chk_1' is violated.")

Answer 3

如上所述，你必须使用触发器，MySQL不支持check，而且当你的触发器块中有多个语句时，例如声明变量或控制流，你需要以启动它>begin 和 end 并将触发器括在两个分隔符内：

注意：如果您使用 MariaDB，请使用 //< /code> 在第一个分隔符之后和第二个分隔符之前，否则，如果您使用 MySQL，请改用 $$ 。

delimiter //

create trigger `user_insert_trigger` before insert on `user` for each row
begin
    declare maximumFnameLength int unsigned;
    declare fNameLength int unsigned;
    set maximumFnameLength = 30;
    set fNameLength = (select length(new.fNameLength));
    if (fNameLength > maximumFnameLength) then
        signal sqlstate '45000'
            set message_text = 'First name is more than 30 characters long.';
    end if;
end

//

delimiter ;