设计数据库时，存储多个真/假值的首选方式是什么？

Question

设计数据库时，存储多个真/假值的首选方式是什么？

sql-serveroracledatabase-designquery-optimization

13

如标题所述，在设计数据库时，处理只存储 true/false 值的多个列的首选方法是什么（例如，“Y/N”或“0/1”）？同样，不同数据库之间可能会出现一些问题（例如 Oracle 和 SQL Server），这可能会影响如何处理这些列吗？

- rjzii

10个回答

5

根据我的经验，我更喜欢使用char(1)来表示'Y'或'N'。使用0和1可能会有些混淆，这取决于我已经喝了多少啤酒以及C++ main()函数成功返回0。ENUM和BIT类型带来的麻烦远比它们的价值大。

有趣的是，MySQL的information_schema使用VARCHAR(3)来表示'YES'或'NO'。

示例：

information_schema.USER_PRIVILEGES (
  ...
  IS_GRANTABLE VARCHAR(3) NOT NULL DEFAULT ''
)

- Yada

1

唯一的缺点是使用CHAR类型允许包括'Y'和'N'，以及'1'和'0'在内的可能值。在您的代码中，您（或另一个开发人员）可能会编写if (myTrueFalseFieldFromDatabase.equals("1")) { ... }，而实际上您想要比较的是"Y"。使用INT或BIT类型的好处是只允许使用1和0，因此您不会犯这个错误。但只要您小心，这并不是什么大问题。 - James Cronen

5

坦率地说，我不建议使用ENUM('Y','N')，因为在其他编程语言中它们都会转换为1/true。如果您使用0和1，即使只是ENUM(1,0)而不是BIT/BOOLEAN，那么在像Perl，PHP等“软”语言中，0将被转换为false。 0和1是表示布尔值的经典方式，并且大多数编程语言都能准确解释它们。 - Teekin

1

@Tenner：一个INT通常可以存储不止0或1个值。如果在列上放置一个检查约束（假设MySQL支持它们），那么允许的值可以只是'Y'和'N'（或'1'和'0'）。我会选择Y/N，但使用1/0代替也没有太大问题 - 在CHAR（1）字段中。 - Jonathan Leffler

1

如果你必须自己创建布尔字段，我建议创建一个类来将语言（C++、Java等）的布尔值转换为所需的表示形式，并始终使用它。这样可以避免出现某个模块中有人不小心使用 T/F 而不是 Y/N，或者使用小写字母而不是大写字母等问题。 - Jay

好像网站上的人没有听说过约束条件。Jonathan 指出，对于正确值的混淆担忧是没有根据的。一个检查约束将解决 T/F 或 Upper/Lower 问题，而且完全没有歧义。 - Stephanie Page

@Stephanie：没错。我想这让使用“翻译器”类的优势变得微不足道了。真正的优点在于它可以防止冗余代码并鼓励一致性。请参见下面我回答的编辑。 - Jay

4

与其使用布尔数据类型，你可能需要考虑另一种数据模型来存储布尔值，尤其适用于以下情况：

当你将拥有许多yes / no列时。
当你可能需要在未来添加更多的yes / no列时。
当yes / no值不会非常频繁地更改时。

定义用户权限可能是上述情况的典型示例。请考虑以下表格：

Table "Users":             (user_id, name, surname, country)

Table "Permissions":       (permission_id, permission_text)

Table "Users_Permissions": (user_id, permission_id)

在“权限”表中，您需要定义所有可能适用于用户的权限。对于每个yes/no属性，您需要向“权限”表添加一行。正如您可能已经注意到的那样，这使得将来很容易添加新的权限而无需修改数据库架构。

有了上述模型，您可以通过在“用户_权限”表中为“user_id”分配“permission_id”来指示TRUE值。否则，默认情况下为FALSE。

例如：

Table "Permissions"

permission_id   text
-----------------------------------
1               "Read Questions"
2               "Answer Questions"
3               "Edit Questions"
4               "Close Questions"


Table "Users_Permissions"

user_id         permission_id
-----------------------------------
1               1
1               2
1               3
2               1
2               3

优点

索引：您可以轻松地使用索引来查询特定的事实。
节省空间：当您有许多假值时，默认约定可以节省空间。
规范化：事实在它们自己的表中定义（在Permissions和Users_Permissions表中）。您可以轻松地在每个事实上存储更多信息。

缺点

查询：简单的查询需要使用JOIN操作。
将值设置为False：要将值设置为false，您必须删除行（从Users_Permissions表中）。否则，您可以在Users_Permissions表中使用“已删除”标志，这也允许您存储审核跟踪信息，例如何时以及由谁修改了权限。如果删除行，则无法存储此信息。

- Daniel Vassallo

2

如果您的数据库管理系统支持布尔数据类型，比如MySQL，请使用它。如果不支持，比如Oracle，我通常使用char(1)并将其值设为Y或N。在后一种情况下，最好编写几个函数来将您的Java或C ++或其他布尔类型转换为Y / N，以避免编写重复的代码。这是一个非常微不足道的函数，但它必须处理像null或Y或N之外的值等情况，并且您希望始终如一地处理这些情况。

我绝对不会使用位运算将标志打包到单个变量中。是的，这会节省一些磁盘空间，但代价是更大的复杂性和出错机会。如果您的数据库管理系统不支持位运算 - 因为我从未想过做这样的事情，所以我不知道哪些DBMS支持 - 那么您将很难根据此类标志进行选择或排序。当然，您可以检索满足其他条件的所有记录，然后让调用代码筛选出具有正确标志值的记录。但是，如果只有很少一部分的记录具有所需的标志值，并且您有一个连接许多其他记录的查询呢？例如，“select employee.name，sum(pay.amount) from employee join pay using (employee_id) where employee.executive=true and pay.bonus=true”。有了where子句，您可能只检索到非常少量的记录。如果没有它，您将检索整个数据库。

如今磁盘空间很便宜，因此任何磁盘节省都可能不重要。如果您确实有大量标志 - 比如每个记录有数百或数千个标志 - 那么我认为可能有理由将它们打包。但那将是我的设计选择列表中的最后一个。

编辑：让我详细说明编写一个类来将您的“SQL布尔值”转换为“Java布尔值”。同样适用于任何语言，但我将以Java作为示例。

如果您的DBMS具有内置的布尔类型，则可以使用ResultSet.getBoolean()将其直接读入布尔变量中。

但是，如果您必须将其存储为例如字符“Y”或“N”，则必须将其读入字符串中。因此，我认为声明一个像这样的类是有意义的：

class MyBoolean
{
  boolean value;
  final static MyBoolean TRUE=new MyBoolean(true), FALSE=new MyBoolean(false);
  public MyBoolean(boolean b)
  {
    value=b;
  }
  public MyBoolean(String s)
  {
    if (s==null)
      return null;
    else if (s.equals("Y"))
      return MyBoolean.TRUE;
    else
      return MyBoolean.FALSE;
  }
  public static String toString(MyBoolean b)
  {
    if (b==null)
      return null;
    else if (b.value)
      return "Y";
    else
      reutrn "N";
  }
  public String toString()
  {
    return toString(this);
  }
}

然后，您可以使用"MyBoolean flag=new MyBoolean(rs.getString("flag"));"从数据库中轻松获取布尔值，并使用"rs.setString("flag", flag.toString());"将数据写入数据库。

当然，如果您有其他需要处理的布尔值，可以向该类添加任何其他逻辑。如果出于某些目的，您想将布尔值显示为T / F或Yes / No或On / Off或其他内容，则可以添加替代的toString变体--toTFString或toString（value，truetext，falsetext）--而不是一遍又一遍地编写类似的代码。

- Jay

2

使用适合您所使用的特定数据库引擎的内容。需要处理它的是与数据库交互的接口。如果代码端对数据库的接口足够模块化，那么只需进行简单的一行更改即可处理底层数据库中不同的布尔类型。

- Aaron

虽然这是真的，但我认为问题不在于此。我相信，像许多情况一样，提问者隐藏了他查询的真正原因，即他正在编写一个支持多个数据库供应商的应用程序...因此，他需要选择最少问题的数据类型。 - Stephanie Page

2

我认为“是/否”值比“1/0”更有意义。在使用Oracle时，我会按照以下方式尽可能使数据经过数据库引擎验证：

将列定义为char(1)
添加一个检查约束条件，将可能的值限制为“in ('Y', 'N')”
如果符合业务规则，则将它们设置为非空——这可以避免在SQL中隐含地假定任何不为“Y”的值具有“N”的值时出现问题

- dpbradley

1

不要添加列，我建议你创建另一个表。听我说...

假设你有一个名为Customer的表：

CREATE TABLE Customer
(
  CustomerID NUMBER,
  Name       VARCHAR(100)
)

现在，假设您想指示客户是否允许出现在搜索结果中。一种选择是添加某些列，表示两种可能状态之一：

CREATE TABLE Customer
(
  CustomerID NUMBER,
  Name       VARCHAR(100),
  Searchable BOOLEAN /* or CHAR(1) or BIT... */
)

你的搜索查询将类似于这样：

SELECT CustomerID, Name
  FROM Customer
 WHERE Name LIKE '%TheCustomerNameIAmLookingFor%'
   AND Searchable = TRUE /* or 'Y' or 0... */

这很好而且简单。许多人在这个帖子上都提供了很好的建议，以便选择哪种数据类型应该用于此列，以使语法在各种数据库中运行得很好。

替代方案：创建一个单独的表

与其向Customer添加另一列，我将创建一个单独的表，其中存储每个可搜索客户的CustomerID。

CREATE TABLE Customer
(
  CustomerID NUMBER,
  Name       VARCHAR(100)
)

CREATE TABLE SearchableCustomer
(
  CustomerID NUMBER
)

在这种情况下，如果客户的 CustomerID 存在于 SearchableCustomer 表中，则认为该客户是可搜索的。现在搜索客户的查询变成了：

SELECT CustomerID, Name
  FROM Customer
 WHERE Name LIKE '%TheCustomerNameIAmLookingFor%'
   AND CustomerID IN (SELECT CustomerID FROM SearchableCustomer)

您会发现，这种策略在关系型数据库管理系统中非常通用：

使用IN子句或JOIN查找可搜索的客户
使用INSERT语句使客户可搜索
使用DELETE语句使客户不可搜索

一个惊喜的好处

如果您将SearchableCustomer定义为视图而不是表，则可以自由地将可搜索客户的定义变得更加复杂：

CREATE VIEW SearchableCustomer AS
SELECT CustomerID
  FROM Customer
 WHERE Name LIKE 'S%' /* For some reason, management only cares about customers whose name starts with 'S' */

你的搜索查询根本不会改变！ :D 根据我的经验，这导致了巨大的灵活性。

- Adam Paynter

2

正是因为像这样的东西，优化器才需要如此努力工作:CustomerID IN (SELECT CustomerID FROM SearchableCustomer) - Stephanie Page

1

嗯，我不太清楚为什么“where customerid in (select customerid from searchable)”比“where searchable=true”更好。如果我有3个这样的字段，那么我就不是一个表而是4个（“主”表加上3个“标志表”）。如果你担心布尔值的跨数据库可移植性，可以使用char(1)代替声明布尔值。 - Jay

@Stephanie：我喜欢现代优化器！:D 我可以用集合符号编写查询！ - Adam Paynter

@Adam，你可以编写各种无意义的查询，优化器可以处理...但是，是否已经为它们提供了完全优化所需的所有结构是另一回事。如果在SearchableCustomer中对CustomerID设置了唯一约束，则编写应该是JOIN而不是IN的查询也无关紧要。 - Stephanie Page

@Stephanie：哦，我开始明白你的意思了。我同意，添加一个唯一约束可以让我在使用JOIN时不必担心重复值。虽然我的想法有点倒错，但我选择使用IN子句是因为它更能表达我的思路。:) - Adam Paynter

显示剩余3条评论

0

位列通常用于表示SQL Server中的T/F或Y/N类型值。尽管数据库纯粹主义者可能会告诉你，位列在数据库中没有位置，因为它们“太接近硬件”- Joe Celko。

- Randy Minder

你有“太靠近硬件”这一点的引用吗？ - James A Mohler

0

"SELECT * FROM mytable WHERE col1

这在SQL Server和Oracle中是不可能的（你需要有某种谓词）。"

这只能说明Oracle和SQL Server是多么荒谬可笑的畸形产物。

如果col1被声明为BOOLEAN类型，则表达式“col1”就是一个谓词。

如果WHERE子句的语义要求其表达式仅评估为真值，并且某些列被声明为“真值”类型，则“WHERE that-column”应该被允许和支持。毫无疑问，任何不支持此功能的系统都会暴露其作者的无能和平庸。

- Erwin Smout

哇，荒谬和可恶，因为你必须在列名后面键入整整4个额外字符才能添加谓词？好吧... - Jeffrey Kemp

我觉得某人有私心。首先，在Oracle中无法定义布尔列。所以，当然没有 WHERE boolcol。它不存在。Erwin，告诉我们你对关系数据库管理系统的选择，我会挑出100个Oracle拥有但你最喜欢的没有的特性。 - Stephanie Page

-1

通常我不会使用BIT/BOOLEAN值。相反，我会使用三个表。假设我们有一个项目管理系统，其中有项目，这些项目有许多属性。

然后我们有以下表：

Project
 - Project_ID (INT),
 - Name (VARCHAR)
Attribute
 - Attribute_ID (INT),
 - Name (VARCHAR)
ProjectAttribute_Rel
 - Project_ID (INT),
 - Attribute_ID (INT)

项目的属性是true还是false取决于ProjectAttribute_Rel中是否有对应的行。

通常，在代码中你会处理Attribute_IDs，所以当你读取项目的属性（在那里你可能有Project_ID），你只需要执行以下操作（此处以PHP为例）：

$arrAttributes = array();
$oQuery = mysql_query('
    SELECT Attribute_ID
    FROM ProjectAttribute_Rel
    WHERE Project_ID = '.addslashes($iProjectId).'
');
while ($rowAttribute = mysql_fetch_assoc($oQuery)) {
    $arrAttributes[] = $rowAttribute['Attribute_ID'];
}

此时，您可以通过检查 $arrAttributes 中是否存在项目属性来检查其是否为 true。在 PHP 中，代码如下：

if (in_array($arrAttributes, $iAttributeId)) {
    // Project attribute is true!
}

这种方法还允许您执行各种花招，以避免在更新时列出大量属性，再次选择（因为代码中的SELECT *很糟糕），插入等操作。这是因为您始终可以通过循环遍历表属性来查找可用属性，因此如果您添加一个属性并且按照这种方式进行操作，则添加/编辑/删除属性非常简单。很可能您的SQL甚至不需要更改，因为属性本身是在数据库中定义的，而不是在代码中定义的。

希望这有所帮助。

- Teekin

2

天啊，越来越多的人推荐使用EAV结构。现在就让我死了吧。 - Stephanie Page

但是，我们不能仅仅从结果集中提取记录字段，而是必须循环遍历结果以查找我们想要的字段。拼写错误的字段名很难与空值字段区分开来。架构不再是定义给定表适用哪些属性的明确列表，因为没有任何明确的列表。（有人可能会在纸上写下它，但谁能保证它是最新的呢？）我认为这是解决一类非常专业化问题的方案，而不是您希望通常使用的东西。 - Jay

如果我的话听起来有点粗鲁，我很抱歉，但在我个人看来，拼写错误的字段名很容易被发现。我认为这不是一个真正的问题，就像“缺少分号”问题一样。当然，硬编码属性也有一些好处，但最后一次我检查时，硬编码这些东西被反对，因为它们是铭刻在石头上的。要获取属性列表，“SELECT Name FROM Attributes;” - 我看不出有什么问题。请记住，极客之间的争论总是比它们看起来更具敌意。 :) - Teekin

2

Helgi，你的建议太过荒谬，它甚至有自己的名字，叫做EAV模型。你说“这样的事情是不被鼓励的”。在编程世界里，你是对的...但在数据库世界里并非如此。如果你按照你所描述的设计一个EAV，那么每个传统的RDBMS都会失败。只需访问http://stackoverflow.com/questions/1940327/can-an-attribute-designate-one-table-over-another/1942193#1942193并从那里阅读链接即可。 - Stephanie Page

我想我们只能各自坚持自己的观点了。也许我是世界上最伟大的程序员，但我从来没有遇到过这种做事方式的任何问题。我看不出拼写错误的字段名和缺少分号之间的区别，对这两个问题都毫不尊重。我认为要求程序员和数据库设计师能够阅读他们面前的代码并不过分。如果我的话听起来很苛刻或不体贴，那我很抱歉，但这就是我的想法。 - Teekin

Helgi，你有跟进这个链接吗？这与阅读代码无关，与拼写错误的字段名称无关，与性能有关。阅读链接。那位程序员也是天才。但问题在于RDBMS并不能支持你。我应该说传统的RDBMS。 - Stephanie Page

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

在SQL Server中，有一个BIT数据类型。您可以在那里存储0或1，比较值，但不能运行MIN或MAX。

在Oracle中，您只需使用NUMBER或CHAR(1)。

在MySQL和PostgreSQL中，任何数据类型都可以隐式转换为BOOLEAN。

这两个系统都支持BOOLEAN数据类型，您可以直接在WHERE或ON子句中使用它，而不需要使用操作符：

SELECT  *
FROM    mytable
WHERE   col1

在SQL Server和Oracle中不可能实现（需要在那里添加某种谓词）。

在MySQL中，BOOLEAN是TINYINT(1)的同义词。

在PostgreSQL中也是如此（就存储而言），但在逻辑上，它不能隐式转换为任何其他类型。