我正在寻找一个约束，以防止在MySQL中插入一个空字符串 [英] I'm looking for a constraint to prevent the insert of an empty string in MySQL

问题描述

好的，在此问题中，我学习了如何防止插入NULL值。但是，不幸的是，一个空字符串正在插入。除了防止这一点在PHP方面，我想使用像数据库约束来防止这一点。当然，在应用程序端的检查是必要的，但我想它在两边。

我教会说，无论什么应用程序正在与您的数据库，它不应该能够在其中插入基本上错误的数据。所以...

  CREATE TABLE如果不存在tblFoo（
 foo_id int（11）NOT NULL AUTO_INCREMENT，
 foo_test varchar（50）NOT NULL，
 PRIMARY KEY（foo_id）
）; 
  

仍然允许我插入：

  INSERT INTO tblFoo（foo_test）VALUES（''）; 
  

我想防止。

解决方案

通常你会这样做与CHECK约束：

  foo_test VARCHAR不幸的是，MySQL对约束的支持有限，因此，我们可以使用一个简单的例子来说明这一点。从 MySQL参考手册：
 
 
 CHECK子句被解析，但被所有存储引擎忽略。
 
 
因此，您必须使用触发作为解决方法
 
 
 
以后，您可能想要看看PostgreSQL ，这被认为是对数据完整性的更好的支持（其他事项）很多人。
 
Ok, 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 ('');
Which I would like to prevent.
解决方案
Normally you would do that with CHECK constraint:
foo_test VARCHAR(50) NOT NULL CHECK (foo_test <> '')
Unfortunately MySQL has limited support for constraints. From MySQL Reference Manual:

  
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.

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屋！