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

Question

What’s the difference of storing a string as NULL or empty in SQL?

How these two can behave when I go to make one SELECT, or INSERT worthwhile '' in that column which is of the type varchar?

If I leave the default as None, I can’t do the INSERT if it does not attach any value to that spine. 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;

Answer 1

To string empty is a text that has zero characters, but it’s a text.

The null is the value indetermination. Not a text has there. Some think it’s value-free, but a null is a value. And so it’s nothing either. You know when you have a search where you have the options "yes", "no" and "I don’t want to answer". The null is another "I don’t want to answer". Although often this is still a third option that perhaps should be part of the roll accepted values*. Null is a value, but not a text, which is usually what is expected there.

Your case

The statement in the column host_name indicates that it cannot have null value, therefore it needs to have some text placed, anything serves, even an empty text.

This is a case that seems appropriate because it would be strange not to have something registered there. Of course it remains strange to allow an empty text, or something that doesn’t look like a hostname. So I keep thinking: what is the point of this restriction if it continues to allow invalid data? On the other hand, validate hostname it’s not easy because he accepts a lot. If it were validate only domains, there’s RFC on that. And if the application will validate this, why not let it all be validated there?

Null or not null, that is the question

There is a current 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’s good to think if you can avoid nulls without complications, just don’t make it an obligation.

Null is not usually part of valid data and does not usually enter a selection, unless you explicitly state that this should occur. I just can’t remember if this happens by default in Mysql, or if it depends on some configuration (or who knows collate, which I doubt).

Occupied space

If you are concerned about the busy size, it is complicated and depends on the storage engine used. I will simplify a few things.

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

Myisam

Each voidable column will occupy 1 bit in the row header. Obviously there will always be a fill to reach 1 byte if you have a number not divisible by 8 (byte size), pointing out that there is still 1 bit to indicate if the line is deleted and enters this account, so for up to 7 anullable columns, the cost is zero. Note which columns NOT NULL do not occupy this space, and the fact of being null or not, does not change the size.

A column varchar always takes 2 bytes to indicate its size, so a string empty would have the value 0 and would not occupy another space. If null would occur the same, but the database would not consider the value.

Documentation.

Innodb

All columns need an entry in the 1 or 2 byte row header (fixed for every row) to indicate its size and whether it is null or not. It does not matter if the column is voidable or not. Every column will occupy that byte (two if it passes 127) always.

There is no other extra cost for a varchar.

Documentation.

Performance

A column having a null value may have a slight performance gain in searches in some circumstances, but it is very minimal.

Related

• When should we allow a column of a table of a database to accept NULL?
• What NULL Really Means?
• Why NULL values are not selected?

Answer 2

Differences:

Size it occupies in the bank: The difference is fuzzy, where NULL would take up less space, because NULL is literally nothing and an empty string has at least the information of being a string.

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

Philosophy: A field that is required when creating the table will be created as NOT NULL. In the past if nothing was inserted into a field that was set to NOT NULL in the table creation 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 currently the new bank versions are impoverishing these inserts.

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