MySQL，最好插入NULL或空字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1267999/
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, better to insert NULL or empty string?
提问by roflwaffle
I have a form on a website which has a lot of different fields. Some of the fields are optional while some are mandatory. In my DB I have a table which holds all these values, is it better practice to insert a NULL value or an empty string into the DB columns where the user didn't put any data?
我在一个网站上有一个表格，它有很多不同的领域。有些字段是可选的，而有些是强制性的。在我的数据库中，我有一个包含所有这些值的表，将 NULL 值或空字符串插入用户未放置任何数据的数据库列中是否更好？
采纳答案by Quassnoi
By using NULLyou can distinguish between "put no data" and "put empty data".
通过使用，NULL您可以区分“不放数据”和“放空数据”。
Some more differences:
还有一些区别：
A
LENGTHofNULLisNULL, aLENGTHof an empty string is0.NULLs are sorted before the empty strings.COUNT(message)will count empty strings but notNULLsYou can search for an empty string using a bound variable but not for a
NULL. This query:SELECT * FROM mytable WHERE mytext = ?will never match a
NULLinmytext, whatever value you pass from the client. To matchNULLs, you'll have to use other query:SELECT * FROM mytable WHERE mytext IS NULL
一个
LENGTH的NULL就是NULL，一个LENGTH空字符串的是0。NULLs 在空字符串之前排序。COUNT(message)将计算空字符串但不计算NULLs您可以使用绑定变量搜索空字符串，但不能搜索
NULL. 这个查询：SELECT * FROM mytable WHERE mytext = ?永远不会匹配 a
NULLinmytext，无论您从客户端传递什么值。要匹配NULLs，您必须使用其他查询：SELECT * FROM mytable WHERE mytext IS NULL
回答by Matt Solnit
One thing to consider, if you everplan on switching databases, is that Oracle does not support empty strings. They are converted to NULL automatically and you can't query for them using clauses like WHERE somefield = ''.
有一点要考虑，如果你曾经在切换数据库计划，是Oracle不支持空字符串。它们会自动转换为 NULL，您不能使用像WHERE somefield = ''.
回答by max
One thing to keep in mind is that NULL might make your codepaths much more difficult. In Python for example most database adapters / ORMs map NULLto None.
要记住的一件事是 NULL 可能会使您的代码路径更加困难。例如，在 Python 中，大多数数据库适配器/ORM 映射NULL到None.
So things like:
所以像：
print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow
might result in "Hello, None Joe Doe!" To avoid it you need something like this code:
可能会导致“你好，没有 Joe Doe！” 为了避免它，你需要这样的代码：
if databaserow.title:
print "Hello, %(title)s %(firstname) %(lastname)!" % databaserow
else:
print "Hello, %(firstname) %(lastname)!" % databaserow
Which can make things much more complex.
这会使事情变得更加复杂。
回答by micaball
Better to Insert NULLfor consistency in your database in MySQL. Foreign keys can be stored as NULLbut NOT as empty strings.
最好插入NULL以在 MySQL 中的数据库中保持一致性。外键可以存储为NULL但不能存储为空字符串。
You will have issues with an empty stringin the constraints. You may have to insert a fake record with a unique empty stringto satisfy a Foreign Key constraint. Bad practice I guess.
您将在约束中遇到空字符串问题。您可能必须插入带有唯一空字符串的假记录以满足外键约束。我猜是不好的做法。
See also: Can a foreign key be NULL and/or duplicate?
另请参阅：外键可以为 NULL 和/或重复吗？
回答by Platinum Azure
I don't know what best practice would be here, but I would generally err in favor of the null unless you want null to mean something different from empty-string, and the user's input matches your empty-string definition.
我不知道这里的最佳实践是什么，但我通常会倾向于使用 null，除非您希望 null 表示与空字符串不同的内容，并且用户的输入与您的空字符串定义相匹配。
Note that I'm saying YOU need to define how you want them to be different. Sometimes it makes sense to have them different, sometimes it doesn't. If not, just pick one and stick with it. Like I said, I tend to favor the NULL most of the time.
请注意，我是说您需要定义您希望它们如何不同。有时让它们不同是有意义的，有时则不然。如果没有，只需选择一个并坚持下去。就像我说的，大多数时候我倾向于使用 NULL。
Oh, and bear in mind that if the column is null, the record is less likely to appear in practically any query that selects (has a where clause, in SQL terms) based off of that column, unless the selection is for a null column of course.
哦，请记住，如果该列为空，则该记录几乎不可能出现在任何基于该列进行选择（在 SQL 术语中具有 where 子句）的查询中，除非该选择是针对空列的当然。
回答by Jo?o Marques
If you are using multiple columns in a unique index and at least one of these columns are mandatory (i.e. a required form field), if you set the other columns in the index to NULL you may end up with duplicated rows. That's because NULL values are ignored in unique columns. In this case, use empty strings in the other columns of the unique index to avoid duplicated rows.
如果您在唯一索引中使用多个列，并且这些列中至少有一个是必需的（即必需的表单字段），如果您将索引中的其他列设置为 NULL，则最终可能会出现重复的行。这是因为在唯一列中忽略了 NULL 值。在这种情况下，在唯一索引的其他列中使用空字符串以避免重复行。
COLUMNS IN A UNIQUE INDEX: (event_type_id, event_title, date, location, url) EXAMPLE 1: (1, 'BBQ', '2018-07-27', null, null) (1, 'BBQ', '2018-07-27', null, null) // allowed and duplicated. EXAMPLE 2: (1, 'BBQ', '2018-07-27', '', '') (1, 'BBQ', '2018-07-27', '', '') // NOT allowed as it's duplicated.
Here are some codes:
下面是一些代码：
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`event_id` int(11) DEFAULT NULL,
`event_title` varchar(50) DEFAULT NULL,
`date` date DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
`url` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `event_id` (`event_id`,`event_title`,`date`,`location`,`url`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Now insert this to see it will allow the duplicated rows:
现在插入它以查看它将允许重复的行：
INSERT INTO `test` (`id`, `event_id`, `event_title`, `date`, `location`,
`url`) VALUES (NULL, '1', 'BBQ', '2018-07-27', NULL, NULL);
INSERT INTO `test` (`id`, `event_id`, `event_title`, `date`, `location`,
`url`) VALUES (NULL, '1', 'BBQ', '2018-07-27', NULL, NULL);
Now insert this and check that it's not allowed:
现在插入并检查它是否不允许：
INSERT INTO `test` (`id`, `event_id`, `event_title`, `date`, `location`,
`url`) VALUES (NULL, '1', 'BBQ', '2018-07-28', '', '');
INSERT INTO `test` (`id`, `event_id`, `event_title`, `date`, `location`,
`url`) VALUES (NULL, '1', 'BBQ', '2018-07-28', '', '');
So, there is no right or wrong here. It's up to you decide what works best with your business rules.
所以，这里没有对与错。由您决定什么最适合您的业务规则。
