MySQL INT 类型可以是非零 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9154244/
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
Can MySQL INT type be non-zero NULL
提问by dgeare
I'm creating a MySQL DB which is composed of weekly data sets, going back for the past decade. There are some data points which exist for recent records, but which were not tracked in some of the older data sets. The fields in question all contain integer values, and '0' is a perfectly valid (and frequent) value for the records which did track the data points. I need to be able to distinguish between a value of zero and non-existent data. Therefore, I need to find out if it is possible to store a NULL which is not represented as a '0' (read: BLANK CELL) for an INT type. However, NULL values passed to the DB are represented as '0' (at least, they are in phpMyAdmin), is there any way to change this?
我正在创建一个 MySQL 数据库，它由每周数据集组成，可以追溯到过去十年。有一些数据点存在于最近的记录中，但在一些较旧的数据集中没有被跟踪。有问题的字段都包含整数值，对于跟踪数据点的记录，“0”是一个完全有效（且频繁）的值。我需要能够区分零值和不存在的数据。因此，我需要找出是否可以为 INT 类型存储未表示为“0”（读取：BLANK CELL）的 NULL。但是，传递给 DB 的 NULL 值表示为“0”（至少，它们在 phpMyAdmin 中），有什么办法可以改变这一点？
Thanks for your help.
谢谢你的帮助。
回答by Bruno Silva
You can set the value to NULL. INSERT INTO table (INT_COLUMN) VALUES (NULL)is valid SQL (with INT_COLUMN being a nullable int column).
您可以将该值设置为 NULL。INSERT INTO table (INT_COLUMN) VALUES (NULL)是有效的 SQL（INT_COLUMN 是一个可为空的 int 列）。
回答by Bohemian
The answer is "yes" - you can have nullable int columns. But should you?
答案是“是”——您可以拥有可为空的 int 列。但你应该吗？
Too often, nullis misused and misunderstood. In SQL databases, nullmeans "unknown". In your situation, using nullis perfect, so "yes - you should"
太多时候，null被误用和误解。在 SQL 数据库中，null表示“未知”。在你的情况下，使用null是完美的，所以“是的 - 你应该”
There are cases when you want to use a specific value for "null", for example -1for a quantity. This approach is valid and is used to simplify the application code and SQL, because dealing with nulls in queries is a pain in the ass:
在某些情况下，您希望对“空”使用特定值，例如-1数量。这种方法是有效的，用于简化应用程序代码和 SQL，因为在查询中处理空值是一件很痛苦的事情：
- You have to use the special syntax "IS NULL"
- Any list containing a null will never match anything, eg
WHERE NAME IN (SELECT COL1 FROM SOMETABLE)will never work if any of the selected values is null - Null doesn't equal anything else, even another null
- In strict (eg java) app code, you have a check that a value is null or you'll get a NullPointerException. Using an actual value avoids this
- 您必须使用特殊语法“IS NULL”
- 任何包含空值的列表永远不会匹配任何内容，例如，
WHERE NAME IN (SELECT COL1 FROM SOMETABLE)如果任何选定的值为空值，则永远不会工作 - Null 不等于其他任何东西，甚至是另一个 null
- 在严格的（例如java）应用程序代码中，您需要检查一个值是否为空，否则您将得到一个NullPointerException。使用实际值可以避免这种情况
回答by StudyOfCrying
Yes, be sure your table is designed to allow NULL values in the column in question. See the very basic example below:
是的，请确保您的表设计为允许相关列中的 NULL 值。请参阅下面的非常基本的示例：
DROP TABLE IF EXISTS testTable;
CREATE TABLE `testTable` (
`id` int(11) unsigned NOT NULL auto_increment,
`data` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;
INSERT INTO testTable VALUES
(NULL, 100),
(NULL, 200),
(NULL, NULL),
(NULL, 400);
SELECT * FROM testTable;
The select will have the following result:
选择将有以下结果：
+----+------+
| id | data |
+----+------+
| 1 | 100 |
| 2 | 200 |
| 3 | NULL |
| 4 | 400 |
+----+------+
