What is the difference between an empty string and NULL in SQL?

Navigation
6

What's the difference if you store a string as NULL or empty in SQL?

How can these two behave when I make a SELECT , or INSERT with value '' in that column that is of type varchar ?

If I leave the default value as None , I can not do INSERT if I do not assign any value to this column . Why does this happen?

Example of the table in question: 

CREATE TABLE 'logs' (
  'id' int(20) NOT NULL AUTO_INCREMENT,
  'host_name' varchar(50) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
    
asked by anonymous 13.01.2017 / 15:58

2 answers

7

The empty string is a text that has zero characters , but it's a text.

The null is value indetermination . Not a text has it there. Some find it to be lack of value, but a null is a value. And so it's nothing too. You know when you have a search where you have the options "yes", "no" and "do not want to respond". The null is another "I do not want to respond". Although often this is a third option that maybe should be part of the roll of accepted values *. Null is a value, but not a text, which is usually what you expect there.

Your case

The declaration of the column host_name indicates that it can not be null, so it must have some text placed, anything serves, even an empty text.

This is a case that seems appropriate because it would be strange to have nothing registered there. Of course it's still strange to allow empty text, or something that does not look like a hostname . So I wonder what this restriction is worth if you continue to allow invalid data. On the other hand, validating hostname is not easy because it accepts a lot. If you were to validate only domains, there is RFC about it . And if the application will validate this, why not let everything be validated there?

Null or not null, that's the question

There is a chain that says that the relational model should not allow nulls and that it is always possible to avoid them with proper modeling. True, but pragmatically this is not always ideal. It can complicate the model just to follow this rule. Of course it is good to think if you can avoid nulos without complications, just do not make it an obligation.

The null is usually not part of the valid data and is not usually entered into a selection, unless it explicitly states that this should occur. I just can not remember if this happens by default in MySQL, or if it depends on some configuration (or who knows collate , which I doubt).

Space occupied

If you are concerned about the size of the container, it is complicated and depends on the storage mechanism used. I'll simplify some things.

In both the space occupied if it is null or not, or if it is empty or it is not the same.

MyISAM

Each nullable column will occupy 1 bit in the header of the line. Obviously there will always be a padding to reach 1 byte if you have a non-divisible number by 8 (byte size), noting that there is still 1 bit to indicate if the line is deleted and goes into that account, so for up to 7 nullable columns, the cost is zero. Note that NOT NULL columns do not occupy this space ever, and the fact that it is null or not does not change the size.

A varchar column always takes 2 bytes to indicate its size, so an empty string would have a value of 0 and would not occupy anything else. If it is null it would be the same, but the database would not consider the value.

Documentation .

InnoDB

All columns need an entry in the header of the 1 or 2 byte line (fixed to the entire row) to indicate its size and whether it is null or not. It does not matter if the column is nullable or not. Each column will occupy this byte (two if it exceeds 127).

There is no extra cost for a varchar .

Documentation .

Performance

A column having null value may have a slight gain in search performance under some circumstances.

Related

13.01.2017 / 16:32
4

Differences:

Size that occupies the bank: NULL is literally nothing and an empty string has at least the information to be a string.

Difference in everyday use with SQL: The comparison with an empty string in SQL uses a =, since the comparison with NULL uses the terms IS NULL or IS NOT NULL.

Philosophy: A field whose padding is required when the table is created will be created as NOT NULL. Previously, if nothing was entered in a field that was set to NOT NULL in the creation of the table then the bank tried to insert a value corresponding to empty according to the field type, strings were "", numbers 0 or 0.0, boolean was false , but new versions of the bank are currently brewing these inserts.

Difference in programming code: The empty string comparison occurs with ==, the comparison with NULL uses a language function. In the case of php the function is is_null ($ variable)

    
13.01.2017 / 16:43
How to get session cookie via javascript? How do you know if a dot (x, y) is within the filled percentage of a pie chart?