Aleks G Aleks G - 3 months ago 24
MySQL Question

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 that I would have gotten an error for 0-length string field, but I didn't. What is the purpose of
varchar(0)
or
char(0)
as I wouldn't be able to store any data in this column anyway.

Answer

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).