CODEWITHSUNDEEP
mysqldatabase
user1578460
user1578460

Reputation: 315

What is the meaning of NULL and NOT NULL in MySQL database

whenever we create our table in mysql database then a option of null checkbox is present there. and if click it then also it takes the null value and if it remains unchecked then also it takes null values. What is the meaning of that?

Upvotes: 2

Views: 20909

Answers (4)

Kermit
Kermit

Reputation: 34055

According to the manual:

The NULL value means "no data." NULL can be written in any lettercase. A synonym is \N (case sensitive).

NOT NULL would mean the opposite of NULL, or not no data. Since some columns can be NULL, you use WHERE col IS NOT NULL to find values that are not "empty."

Upvotes: 0

tadman
tadman

Reputation: 211560

NULL is used to represent "no value" and allow that to be distinguished between 1 and 0, true or false, or an empty string versus a string with content. It's similar to nil in Ruby, null in JavaScript or NULL in PHP.

If you define a column as NOT NULL then it won't allow an INSERT without a value for that column being specified. If you have a DEFAULT then this will be applied automatically. If you use an ORM it may fill it in for you with a safe, minimal default.

Columns that can be NULL require an almost insignificant amount of additional storage per row, one bit, to hold the NULL or NOT NULL flag.

Remember that NULL in MySQL is unusual in that it is not greater than, less than, or equal to any other value. This is why IS NULL and IS NOT NULL are required for logical comparisons.

Upvotes: 3

John3136
John3136

Reputation: 29266

Null is basically "no value". If you allow nulls, you need to ensure that your code is able to handle that column not containing a value. Another approach is a defined "empty" value for each column (e.g. 0 for an integer). This can sometimes be less effort than handling a null value.

As noted by HLGEM below: I'm not trying to say 0 is equivalent to null. Consider: DB with "StockOnHand" column.

0 means you know there is no stock on hand. Null really means unknown (you man have stock, you may not)

Depending on the application, perhaps you decide to treat "unknown" the same as "no stock" - in this case you could use "no nulls" and just have a 0 value.

Upvotes: 0

user1477388
user1477388

Reputation: 21430

The checkbox probably sets the default value to null. There may also be a box that specifies whether or not the field accepts null values. Null basically means Empty or Nothing (in VB).

Upvotes: 1

Related Questions