在数据库中存储性(性别) [英] 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. char(1) - 存储 m、f 或其他单字符标识符
3. Bit (boolean) - 这个选项有合适的字段名称吗?

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，它确实实际上具有 bit 数据类型.

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屋！