MySQL，最好插入NULL还是空字符串?

2021-11-20 00:00:00 sql mysql sql-null

我在一个网站上有一个表格，它有很多不同的领域.有些字段是可选的，而有些是强制性的.在我的数据库中，我有一个包含所有这些值的表，将 NULL 值或空字符串插入到用户未放置任何数据的数据库列中是更好的做法吗?

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 using NULL you can distinguish between "put no data" and "put empty data".

更多不同点:

  • 一个NULL的LENGTH是NULL，一个空字符串的LENGTH是0.

  • A LENGTH of NULL is NULL, a LENGTH of an empty string is 0.

NULL 排在空字符串之前.

COUNT(message) 将计算空字符串但不计算 NULLs

COUNT(message) will count empty strings but not NULLs

您可以使用绑定变量搜索空字符串，但不能搜索NULL.此查询:

You can search for an empty string using a bound variable but not for a NULL. This query:

SELECT  *
FROM    mytable 
WHERE   mytext = ?

永远不会匹配 mytext 中的 NULL，无论您从客户端传递什么值.要匹配 NULLs，您必须使用其他查询:

will never match a NULL in mytext, whatever value you pass from the client. To match NULLs, you'll have to use other query:

SELECT  *
FROM    mytable 
WHERE   mytext IS NULL

相关文章