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

最近，我遇到了一个由数据库创建脚本中的typo造成的问题，数据库中的一个列被创建为varchar(0)而不是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.

我希望我能得到一个0长度的字符串字段的错误，但是我没有。varchar(0)或char(0)的目的是什么，因为我不能在这个列中存储任何数据。

5 个解决方案

#1

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

它不允许在SQL-92标准中使用，但在MySQL中是允许的。从MySQL手册:

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

MySQL允许您创建CHAR类型的列(0)。当您必须遵从依赖于某个列的存在的旧应用程序时，这是很有用的，但实际上并没有使用它的值。CHAR(0)也非常好，当您需要一个只需要两个值的列时:一个被定义为CHAR(0) NULL的列只占用了一个位，并且只需要值NULL和“(空字符串)。

#2

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

刚刚检查过MySQL，它允许零长度CHAR和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.

并不是说它非常有用，但我可以想象当你将一个列截断为0时，当你不再需要它的时候，但你不想破坏写东西的现有代码。您分配给0长度的列的任何内容都将被截断并发出警告，但是警告并不是错误，它们不会破坏任何东西。

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

由于它们是类似的类型，char和varchar，我将冒险猜测varchar(0)的用例与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).

MySQL允许您创建CHAR类型的列(0)。当您必须遵从依赖于某个列的存在的旧应用程序时，这是很有用的，但实际上并没有使用它的值。CHAR(0)也非常好，当您需要一个只需要两个值的列时:一个被定义为CHAR(0) NULL的列只占用了一个位，并且只需要值NULL和“(空字符串)。

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

如果您想在表中标记一个特定的行(例如，因为它是默认的)，那么它可以与惟一的索引结合使用。惟一的索引确保所有其他行必须为空，因此您可以始终通过该列检索行。

#5

You can use it to store boolean values.

您可以使用它来存储布尔值。

Look this code:

看这段代码:

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

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

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

mysql> select 'true' from chartest where a is null;
+------+
| true |
+------+
| true |
+------+
1 row in set (0.00 sec)

mysql> select 'false' from chartest where a is not null;
+-------+
| false |
+-------+
| false |
+-------+
1 row in set (0.00 sec)

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

我们可以使用NULL来表示true和“(空字符串)表示false!”

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

根据MySQL参考手册，只有NULL占用一点。

#1