防止在 MySQL 中插入空字符串的约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2514178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A constraint to prevent the insert of an empty string in MySQL
提问by Whakkee
In this questionI 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.
在这个问题中，我学习了如何防止插入 NULL 值。但是，不幸的是，无论如何都会插入一个空字符串。除了在 PHP 端防止这种情况外，我还想使用诸如数据库约束之类的东西来防止这种情况。当然，在应用程序方面进行检查是必要的，但我希望双方都进行检查。
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.
我想防止。
采纳答案by jholster
Normally you would do that with CHECK constraint:
通常你会用 CHECK 约束来做到这一点：
foo_test VARCHAR(50) NOT NULL CHECK (foo_test <> '')
Unfortunately MySQL has limited support for constraints. From MySQL Reference Manual:
不幸的是，MySQL 对约束的支持有限。来自MySQL 参考手册：
The CHECK clause is parsed but ignored by all storage engines.
CHECK 子句被解析但被所有存储引擎忽略。
That's why you have to use triggersas 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.
以后你可能想看看PostgreSQL，它被很多人认为对数据完整性（除其他外）有更好的支持。
回答by kpower
You could use triggers to prevent insertion of blankstring.
您可以使用触发器来防止插入空白字符串。
It's not fast, not very concise and not pretty, but...
它不是很快，不是很简洁也不是很漂亮，但是......
Example:
例子：
Create your table:
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)Create your 'before insert' trigger to check for blankstring and disallow.
mysql> delimiter $ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> signal sqlstate '45000'; -> end if; -> end;$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ;Try to insert null and blankstring into your column:
mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition mysql> insert into yar values(NULL); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
创建你的表：
mysql> create table yar (val VARCHAR(25) not null); Query OK, 0 rows affected (0.02 sec)创建您的“插入前”触发器以检查空白字符串并禁止。
mysql> delimiter $ mysql> create trigger foo before insert on yar -> for each row -> begin -> if new.val = '' then -> signal sqlstate '45000'; -> end if; -> end;$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter ;尝试在您的列中插入 null 和 blankstring：
mysql> insert into yar values(""); ERROR 1644 (45000): Unhandled user-defined exception condition mysql> insert into yar values(NULL); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into yar values ("abc"); Query OK, 1 row affected (0.01 sec) mysql> select * from yar; +-----+ | val | +-----+ | abc | +-----+ 1 row in set (0.00 sec)
