MySQL 列设置为 NOT NULL 但仍允许 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18908309/
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
MySQL column set to NOT NULL but still allowing NULL values
提问by doitlikejustin
I have every column set to NOT NULLbut for some reason I am still able to add a NULLvalue in each column. Here is my table info (create syntax):
我将每一列都设置为，NOT NULL但由于某种原因，我仍然能够NULL在每一列中添加一个值。这是我的表信息（创建语法）：
CREATE TABLE `addresses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(100) NOT NULL,
`city` varchar(100) NOT NULL,
`state` varchar(4) NOT NULL,
`zip` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4545 DEFAULT CHARSET=utf8;
Here is a sample INSERTthat works:
这是一个有效的示例INSERT：
INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES ('', '', '', '');
Any ideas as to why this is happening?
关于为什么会发生这种情况的任何想法？
回答by Sudhir Bastakoti
You are inserting empty strings, and empty string are not NULL, to check for NULLerror do:
您正在插入空字符串，而空字符串不是NULL，要检查NULL错误，请执行以下操作：
INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);
and you will see error. The NOT NULLchecks only for values that are not NULL.
你会看到错误。该NOT NULL只对那些不值检查NULL。
To prevent empty string either you have to use triggers, or do the checks on server side programming language to convert empty strings to NULLbefore performing INSERTquery.
An example trigger for INSERTmay be like: (this is just an example)
为了防止空字符串，您必须使用triggers，或者NULL在执行INSERT查询之前对服务器端编程语言进行检查以将空字符串转换为。一个示例触发器INSERT可能是这样的：（这只是一个例子）
CREATE TRIGGER avoid_empty
BEFORE INSERT ON addresses
FOR EACH ROW
BEGIN
IF street = '' THEN SET street = NULL END IF;
END;
回答by Damith
try below
试试下面
INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);
above will not work
以上将不起作用
INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES ('', '', '', '');
above insert empty string
在上面插入空字符串
The empty string specifically means that the value was set to be empty; null means that the value was not set.
空字符串具体表示该值被设置为空；null 表示未设置该值。
to prevent insert empty string check this SO question
防止插入空字符串检查这个SO问题
I'm looking for a constraint to prevent the insert of an empty string in MySQL
回答by Greg
''isn't NULL, NULLis NULL
''不是NULL，NULL是NULL
This will fail:
这将失败：
INSERT INTO `addresses` (`street`, `city`, `state`, `zip`) VALUES (NULL, NULL, NULL, NULL);
回答by peterm
If you have control over all of inserts to the table then you can either check for empty strings in your client code, or if you want MySQL to return an error you can wrap inserting values in NULLIF()function. Assuming that you use php your INSERT statement may look like
如果您可以控制对表的所有插入，那么您可以检查客户端代码中的空字符串，或者如果您希望 MySQL 返回错误，您可以将插入值包装在NULLIF()函数中。假设您使用 php，您的 INSERT 语句可能看起来像
INSERT INTO addresses(city, address, state, zip)
VALUES (NULLIF(:address, ''), NULLIF(:city, ''), NULLIF(:state, ''), NULLIF(:zip, ''));
Another way to prevent empty strings from being inserted is to use BEFORE INSERTtrigger
防止插入空字符串的另一种方法是使用BEFORE INSERT触发器
CREATE TRIGGER tg_bi_addresses
BEFORE INSERT ON addresses
FOR EACH ROW
SET NEW.address =
CASE WHEN
CHAR_LENGTH(NEW.address) = 0 OR
CHAR_LENGTH(NEW.city) = 0 OR
CHAR_LENGTH(NEW.state) = 0 OR
CHAR_LENGTH(NEW.zip) = 0 THEN NULL
ELSE NEW.address
END;
The idea is to detect an empty string and violate NOT NULLconstraint on one of the fields. If you are using MySQL 5.5 or higher you can use SIGNAL.
这个想法是检测一个空字符串并违反NOT NULL其中一个字段的约束。如果您使用的是 MySQL 5.5 或更高版本，则可以使用SIGNAL.
Here is SQLFiddledemo. Try to uncomment last insert statement and the trigger won't allow it to succeed.
这是SQLFiddle演示。尝试取消注释最后一条插入语句，触发器将不允许它成功。
