将性别（性别）存储在数据库中 [英] Storing sex (gender) in database

问题描述

我想以尽可能少的（大小/性能）成本将用户的性别存储在数据库中。

I want to store a user's gender in a database with as little (size/performance) cost as possible.

到目前为止，我想到了3种情况

So far, 3 scenarios come to mind

1. Int -与代码中的Enum对齐（1 =男性，2 =女性，3 =。 。）


2. 字符（1）-存储 m ， f 或另一个单个字符标识符


3. 位 （布尔值）-是否为此提供了适当的字段名称选项？

1. Int - aligned with Enum in code (1 = Male, 2 = Female, 3 = ...)
2. char(1) - Store m, f or another single character identifier
3. Bit (boolean) - is there an appropriate field name for this option?

我问的原因是因为答案，其中提到字符比小布尔值。

The reason I ask is because of this answer which mentions that chars are smaller than booleans.

我应该澄清一下，我使用的是MS SQL 2008，实际上确实具有位数据类型。 / p>

I should clarify that I'm using MS SQL 2008, which DOES in fact have the bit datatype.

推荐答案

我将列称为性别。

Data Type   Bytes Taken          Number/Range of Values
------------------------------------------------
TinyINT     1                    255 (zero to 255)
INT         4            -       2,147,483,648 to 2,147,483,647
BIT         1 (2 if 9+ columns)  2 (0 and 1)
CHAR(1)     1                    26 if case insensitive, 52 otherwise

BIT 可以排除数据类型，因为它仅支持两种可能的性别，这是不够的。尽管 INT 支持两个以上的选项，但它占用4个字节-性能使用较小/较窄的数据类型会更好。

The BIT data type can be ruled out because it only supports two possible genders which is inadequate. While INT supports more than two options, it takes 4 bytes -- performance will be better with a smaller/more narrow data type.

CHAR（1）在 TinyINT -两者使用相同数量的字节，但是CHAR提供的数值范围更窄。使用 CHAR（1）会使用 m， f等自然键，而不是使用称为代理/人工键的数字数据。如果需要移植，任何数据库也都支持 CHAR（1）。

CHAR(1) has the edge over TinyINT - both take the same number of bytes, but CHAR provides a more narrow number of values. Using CHAR(1) would make using "m", "f",etc natural keys, vs the use of numeric data which are referred to as surrogate/artificial keys. CHAR(1) is also supported on any database, should there be a need to port.

我会使用选项2：CHAR（1）。

I would use Option 2: CHAR(1).

性别列上的索引可能 没有 会有所帮助，因为低基数列上的索引没有价值。意思是，索引的值没有足够的多样性来提供任何值。

An index on the gender column likely would not help because there's no value in an index on a low cardinality column. Meaning, there's not enough variety in the values for the index to provide any value.

这篇关于将性别（性别）存储在数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！