防止在MySQL中插入空字符串的约束 [英] A constraint to prevent the insert of an empty string in 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 <> '')

不幸的是，MySQL对约束的支持有限.从 MySQL参考手册:

Unfortunately MySQL has limited support for constraints. From MySQL Reference Manual:

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

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

这就是为什么必须使用触发的原因正如人们指出的那样.

That's why you have to use triggers as a workaround, as people have pointed out.

将来，您可能想看看PostgreSQL ，它被认为具有通过其他内容中) https://stackoverflow.com/questions/27435/mysql-vs-postgresql-for-web-applications>很多人.

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.

这篇关于防止在MySQL中插入空字符串的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！