Can MySQL INT be non-zero NULL

Question

I am creating a MySQL database that consists of weekly datasets returned over the past decade. There are some data points that exist for recent records, but which were not tracked in some older datasets. All the fields in question contain integer values, and "0" is an absolutely valid (and frequent) value for records that tracked data points. I need to be able to distinguish between zero and non-existent data. So I need to find out if it is possible to store NULL, which is not represented as "0" (read: BLANK CELL) for type INT. However, the NULL values ​​passed to the database are represented as "0" (at least they are in phpMyAdmin), is there any way to change this?

Thank you for your help.

Answer 1

You can set the value to NULL. INSERT INTO table (INT_COLUMN) VALUES (NULL) is valid SQL (with INT_COLUMN, which is a NULL column).

Answer 2

The answer is yes - you can have int columns with a null value. But should you?

Too often, null is misused and misunderstood. In SQL databases, null means unknown. In your situation, using null perfect, so "yes - you should"

There are times when you want to use a specific value for "null", for example -1 for a quantity. This approach is valid and is used to simplify application code and SQL, since processing zeros in queries is a pain in the ass:

• You must use the special syntax "IS NULL"
• Any list containing zero will never match anything, for example WHERE NAME IN (SELECT COL1 FROM SOMETABLE) will never work if any of the selected values ​​is null
• Null does not match anything else, even another null
• In a strict (e.g. java) application, you have to check that the value is null or you will get a NullPointerException. Using the actual value avoids this.

Answer 3

Yes, make sure your table is designed to allow NULL values ​​in the corresponding column. See the basic example below:

 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 selection will have the following result:

 +----+------+ | id | data | +----+------+ | 1 | 100 | | 2 | 200 | | 3 | NULL | | 4 | 400 | +----+------+ 

Answer 4

It never ceases to amaze me how people answer questions, even DON'T READ that they are being asked. I see the same thing in phpMyAdmin. Allow null is selected and it does not update the record, "IS NULL" or "or null does not work. The value of the INT column is executed by someone .... By the way, if someone says Javascript, they don’t want a response using" underscore " , JQuery or any other Javascript extension, any such answer should be automatic -10 for the profile !!!