MySQL "NULL" questions

I have a table with several columns.

Sometimes some of these column fields may be empty (ie. I won't use them in some cases).

My questions:

1. Would it be smart to set them to NULL in phpmyadmin?

2. What does the "NULL" property actually do?

3. Would I gain anything at all by setting them to NULL?

4. Is it possible to use a NULL field the same way even though it开发者_运维技巧 is set to null?

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same as an empty string '', or a value of zero.

This is not the case. Conceptually, NULL means "a missing unknown value" and it is treated somewhat differently from other values. For example, to test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>.

If you have columns that may contain "a missing unknown value", you have to set them to accept NULLs.

On the other hand, a table with many NULL columns may be indicating that this table needs to be refactored into smaller tables that better describe the entities they represent.

I recommend you read Problems with NULL Values.

1- Would it be smart to set them to NULL in phpmyadmin?

All fields are null by default unless you specify a default value for them or insert some value for them. No need to do this...

2 -What does the "NULL" property actually do?

Null means that you have not assigned any value to it.

3- Would I gain anything at all by setting them to NULL?

As said before, all fields are null by default unless you specify a default value for them or insert some value for them. I don't think you are going to gain anything.

4- Is it possible to use a NULL field the same way even though it is set to null?

What would you gain out of a field having value of NULL? No need for this too.

Going to try to answer your questions all at once here.

NULL represents something along the lines of "Unknown"/"No value" or "Not applicable". So yes, if there are columns that are unused in certain circumstances, it would be appropriate to set them to NULL when not used (as no other value is appropriate).

It is possible to constrain a column to NOT NULL, meaning that the column must have a value for each row. An example would the "name" column of a "person" record. It doesn't make sense for a name to be NULL, as everybody has a name.

You can "use" a NULL column, just keep in mind you have to be careful when doing comparisons. A NULL field is never equal to another field. Check for "IS NULL" or "IS NOT NULL".

Brief answers to your questions:

1. Yes, NULL means that the field contains nothing at all. If that's the true state of affairs, that's what the data should say. An example would be the shipped_date for an order which has not yet shipped. In this case, NULL would accurately represent the value until the order ships out, since until it does there isn't a valid time at which it did (and in this case, checking for the NULL value might be quite a valuable tool in determining which orders do still need to be shipped).

2. NULL means that the field contains nothing. "Nothing" is different from, say, the value 0 or the string "", as these are values. NULL means roughly the same thing as "N/A" or "I decline to answer". What exactly it would mean is context dependent on the column. Of course, some columns should never be NULL, and you can enforce that with your table design.

3. If most of the fields in a column are NULL, you should rethink exactly how you're using that column. Generally speaking, a large number of NULL values indicates you could design your tables better. As to defaulting, you can always set a nullable value to default to NULL.

4. The same way as what? NULL is a unique value. It's not equivalent to 0, or "", or anything else like that. In a query, you must check for IS NULL or IS NOT NULL, and if a null is pulled in to a dataset, you must check for it specifically there too. Asking if a column set to NULL is equal to 0, or "", or what have you, will return false.

Now sometimes some of these column fields may be empty (ie. I wont use them in some cases).

1. Would it be smart to set them to NULL in phpmyadmin?

Yes, that's what it's for.

2. What does the "NULL" property actually do?

It makes the database allow NULL as a value stored in the column. "NOT NULL" means a column must have a value that is not NULL.

3. Would I gain anything at all by setting them to NULL?

No. If your logic requires that a column never contains NULL as a value, it's better to set it to "NOT NULL". Think of it as an assertion: it is safe to assume the column value will never be NULL, so you don't have to test for it. That database takes care of that assertion.

4. Is it possible to use a NULL field the same way even though it is set to null?

I'm not sure what you mean by that... Anyway, NULL and NOT NULL columns are identical in every way, except that NULL columns can contain NULL.

And NULL is a strange value. val = NULL is never true, even if val is NULL. For that you have to test with "IsNull()", "IS NULL" or "IS NOT NULL". See Reference Manual: Comparison Functions and Operators.