What's The Purpose Of Varchar(0)

I recently encountered a problem caused by a typo in the database creation script, whereby a column in the database was created as varchar(0) instead of varchar(20). I expected tha

Solution 1:

It's not allowed per the SQL-92 standard, but permitted in MySQL. From the MySQL manual:

MySQL permits you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

Solution 2:

Just checked MySQL, it's true that it allows zero-length CHAR and VARCHAR.

Not that it can be extremely useful but I can think of a situation when you truncate a column to 0 length when you no longer need it but you don't want to break existing code that writes something there. Anything you assign to a 0-length column will be truncated and a warning issued, but warnings are not errors, they don't break anything.

Solution 3:

As they're similar types, char and varchar, I'm going to venture to guess that the use-case of varchar(0) is the same as char(0).

From the documentation of String Types:

MySQL permits you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

Solution 4:

It's useful in combination with a unique index for if you want to mark one specific row in your table (for instance, because it serves as a default). The unique index ensures that all other rows have to be null, so you can always retrieve the row by that column.

Solution 5:

You can use it to store boolean values.

Look this code:

mysql>createtable chartest(a char(0));
Query OK, 0rows affected (0.26 sec)

mysql>insertinto chartest value(NULL);
Query OK, 1row affected (0.01 sec)

mysql>insertinto chartest value('');
Query OK, 1row affected (0.00 sec)

mysql>select'true'from chartest where a isnull;
+------+|true|+------+|true|+------+1rowinset (0.00 sec)

mysql>select'false'from chartest where a isnotnull;
+-------+|false|+-------+|false|+-------+1rowinset (0.00 sec)

We can use NULL to represent true and '' (empty string) to represent false!

According to MySQL reference manual, only NULL occupies one bit.