'Can anyone explain what the difference is in these two "empty strings"?

So, I had a table where the default was NULL on an ENUM column. I did some testing, setting some rows to various values, and then updated one of the rows to a value not in the ENUM. I then got this: MariaDB [my_db]> select this_value, count(*) FROM my_table GROUP by this_value;

+--------------------+----------+
| this_value         | count(*) |
+--------------------+----------+
| NULL               |      242 |
|                    |        1 |
| AA                 |        1 |
| AB                 |        1 |
| AC                 |        1 |
| BA                 |        1 |
| BB                 |        1 |
| BC                 |        1 |
+--------------------+----------+
8 rows in set (0.001 sec)

Then, I did a ALTER TABLE my_table MODIFY COLUMN this_value, adding the '' to the list of value options and making the default ''.

Now, this is what you get:

MariaDB [my_db]> select this_value, count(*) FROM my_table GROUP by this_value;
+--------------------+----------+
| this_value         | count(*) |
+--------------------+----------+
|                    |        1 |
|                    |      242 |
| AA                 |        1 |
| AB                 |        1 |
| AC                 |        1 |
| BA                 |        1 |
| BB                 |        1 |
| BC                 |        1 |
+--------------------+----------+
8 rows in set (0.001 sec)

So, what is the difference between the two "empty strings". The whole goal of this was because with a query like this_value NOT LIKE 'A%', we were not getting the NULL values (and we wanted those, since they were "not starting with A" values), and this does resolve to that ("SELECT count(*) FROM my_table WHERE this_value NOT LIKE "A%" returns 246), but, I am just interested in how Mysql is differentiating those two values... doing a CHAR_LENGTH on it shows they are both 0, but obviously they still aren't exactly "equivalent" to mysql.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source