在MySQL数据库中存储一个Yes / No单选按钮

I've seen a Yes/No form radio buttons value be stored/saved in a couple different ways. I wonder which way is better and why? This is for a PHP/MySQL application with a typical Yes/No question as part of a form.

我已经看到一个Yes/No表单单选按钮的值以不同的方式存储/保存。我想知道哪条路更好，为什么更好?这是针对PHP/MySQL应用程序的，作为表单的一部分，典型的是/不是问题。

1.) Store it as 1, 0 or null. 1 being Yes, 0 being No and null being not answered.

1)。将其存储为1、0或null。是的，0是否定的，没有答案。

2.) Store it as Yes, No, null. Assume a language conversion can be made.

2)。存储为Yes, No, null。假设可以进行语言转换。

3.) Use 1, 2 and null so as to better distinct the values.

3)。使用1、2和null来更好地区分值。

Thanks, Jeff

谢谢,杰夫

Edit: I also must mention that most of the issues have been arising due to jQuery/JavaScript and the comparisons and $() bindings.

编辑:我还必须指出，大多数问题是由于jQuery/JavaScript以及比较和$()绑定引起的。

6 个解决方案

#1

Since MySQL has a BOOLEAN type, but it's simply an alias of TINYINT. I recommend against it because the equal sign in PHP == would not distinguish 0 from the lack of value. You'd always need to use triple equal === and it would be easy to make mistakes.

因为MySQL有一个布尔类型，但它只是TINYINT的别名。我建议不要使用它，因为PHP == =中的等号不能区分0和缺少值。你总是需要使用triple === =，这样很容易出错。

As for your options:

至于你的选择:

This seems the natural choice with PHP, but then you've to be careful to distinguish 0 from the lack of value, so I wouldn't recommend it.

这似乎是PHP的自然选择，但是您必须小心地区分0和缺乏值，所以我不推荐它。
I would not recommend this one.

我不推荐这个。
Possible, but the assignment to 1 and 2 is somewhat arbitrary and might be difficult to remember and read in code.

可能，但是赋值1和2是任意的，可能很难在代码中记住和读取。

What I usually do, is use "Y", "N" and NULL if needed, in a CHAR(1) field, it reads well in code and doesn't create problems.

我通常的做法是，如果需要，使用“Y”、“N”和NULL，在CHAR(1)字段中，它在代码中读得很好，不会产生问题。

#2

Use TINYINT(1). Allow NULL if you wish for the "not answered" option. You can also use BOOLEAN as it's just an alias for the aforementioned datatype. This way of storing boolean data is recommended by MySQL.

使用非常小的整数(1)。如果您希望“不回答”选项，则允许NULL。您也可以使用布尔值，因为它只是前面提到的数据类型的别名。这种存储布尔数据的方式是MySQL推荐的。

More details: Which MySQL data type to use for storing boolean values

更多细节:用于存储布尔值的MySQL数据类型

#3

I would go with the 0/1/null for No/Yes/Blank. 0 is always used as false and 1 for true.

我用0/1/null表示No/Yes/Blank。0总是被用为假，1为真。

#4

I don't know if it helps, but in my system I use 1 as yes, 0 as no and just NO value as null - or if I have to specify I set a default value in the structure.

我不知道这是否有用，但在我的系统中，我使用1作为yes, 0作为no，只是没有值作为null——或者如果我必须在结构中指定一个默认值。

I think this system is more flexible, you can always manipulate with this data, for example if you don't want to display 0/1 values you can set something like

我认为这个系统更灵活，你可以对数据进行操作，例如，如果你不想显示0/1的值，你可以设置类似的值

if(table.field == 1)
    echo yes;
else
    echo no;

Also comparing this value to any other database value is easier.

同样，将此值与其他数据库值进行比较也比较容易。

#5

as numbers are processed faster (while searching, sorting,..) by mysql, it takes less space in ur case, and also there are only 3 values (1, 0 nul l) and in binary format ,1 and 0 have just one significant bit (0000000, & 0000001), the speed, in case of any comparisons, while traversing these columns, should remain higher and queries will take less time.

数字加工速度(搜索、排序、. .)通过mysql,它需要更少的空间在你的情况下,也只有3值(1,0 nul l)和二进制格式的,1和0只有一个有效位(0000000,0000001),速度,以防任何比较,当遍历这些列,应该保持高和查询将会节省许多时间。

so i think u can go for first option.

所以我认为你可以选择第一种。

#6

I think that's what ENUM type is for. You can set your storage field type like this:

我想这就是ENUM类型的用途。您可以设置您的存储字段类型如下:

ENUM('no','yes')

and allow NULL as default value. If the answer is 'no' - the field value will actually be 0 (the index of 'no') and 1 if the answer is 'yes'. And you will have a nice representation of the column - 'yes' and 'no' instead of 1 and 0. Though the values will be actially stored as 0 and 1. I think its an advantage of using TINYINT.

并允许NULL作为默认值。如果答案是“否”——字段值实际上是0(“no”的索引)，如果答案是“yes”，则为1。你会得到一个很好的列的表示——“是”和“不是”而不是1和0。虽然这些值将被动态地存储为0和1。我认为使用TINYINT是一种优势。

#1