防止在 MySQL 中插入空字符串的约束

2021-11-20 00:00:00 constraints mysql

在这个问题中，我学习了如何防止插入 NULL 值.但是，不幸的是，无论如何都会插入一个空字符串.除了在 PHP 端防止这种情况外，我还想使用诸如数据库约束之类的东西来防止这种情况.当然需要在应用端进行检查，但我希望两边都检查.

In this question I learned how to prevent the insert of a NULL value. But, unfortunately, an empty string is being inserted anyway. Apart from preventing this on the PHP side, I'd like to use something like a database constraint to prevent this. Of course a check on the application side is necessary, but I'd like it to be on both sides.

我被教导无论应用程序如何与您的数据库通信，它都不应该能够在其中插入基本上错误的数据.所以...

I am taught that whatever application is talking to your database, it should not be able to insert basically wrong data in it. So...

CREATE TABLE IF NOT EXISTS tblFoo (
  foo_id int(11) NOT NULL AUTO_INCREMENT,
  foo_test varchar(50) NOT NULL,
  PRIMARY KEY (foo_id)
);

仍然允许我做这个插入:

Would still allow me to do this insert:

INSERT INTO tblFoo (foo_test) VALUES ('');

我想阻止.

推荐答案

通常你会用 CHECK 约束来做到这一点:

Normally you would do that with CHECK constraint:

foo_test VARCHAR(50) NOT NULL CHECK (foo_test <> '')

在 8.0 版之前，MySQL 对约束的支持有限.来自 MySQL 参考手册:

Prior to Version 8.0 MySQL had limited support for constraints. From MySQL Reference Manual:

CHECK 子句被解析但被所有存储引擎忽略.

The CHECK clause is parsed but ignored by all storage engines.

如果您必须坚持使用旧版本，请使用触发器 作为一种解决方法，正如人们所指出的那样.

If you must stick to an old version use triggers as a workaround, as people have pointed out.

以后，你可能想看看PostgreSQL，这被认为是更好地支持数据完整性(包括其他内容)很多人.

In future, you may want to take a look at PostgreSQL, which is considered to have better support for data integrity (among other things) by many people.

相关文章