如标题所述,在设计数据库时,处理只存储 true/false 值的多个列的首选方法是什么(例如,“Y/N”或“0/1”)?同样,不同数据库之间可能会出现一些问题(例如 Oracle 和 SQL Server),这可能会影响如何处理这些列吗?
如标题所述,在设计数据库时,处理只存储 true/false 值的多个列的首选方法是什么(例如,“Y/N”或“0/1”)?同样,不同数据库之间可能会出现一些问题(例如 Oracle 和 SQL Server),这可能会影响如何处理这些列吗?
SELECT *
FROM mytable
WHERE col1
在SQL Server
和Oracle
中不可能实现(需要在那里添加某种谓词)。
在MySQL
中,BOOLEAN
是TINYINT(1)
的同义词。
在PostgreSQL
中也是如此(就存储而言),但在逻辑上,它不能隐式转换为任何其他类型。
根据我的经验,我更喜欢使用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 ''
)
CHAR
类型允许包括'Y'和'N',以及'1'和'0'在内的可能值。在您的代码中,您(或另一个开发人员)可能会编写if (myTrueFalseFieldFromDatabase.equals("1")) { ... }
,而实际上您想要比较的是"Y"
。使用INT
或BIT
类型的好处是只允许使用1和0,因此您不会犯这个错误。但只要您小心,这并不是什么大问题。 - James Cronen与其使用布尔数据类型,你可能需要考虑另一种数据模型来存储布尔值,尤其适用于以下情况:
定义用户权限可能是上述情况的典型示例。请考虑以下表格:
Table "Users": (user_id, name, surname, country)
Table "Permissions": (permission_id, permission_text)
Table "Users_Permissions": (user_id, permission_id)
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
表中)。您可以轻松地在每个事实上存储更多信息。缺点
Users_Permissions
表中)。否则,您可以在Users_Permissions
表中使用“已删除”标志,这也允许您存储审核跟踪信息,例如何时以及由谁修改了权限。如果删除行,则无法存储此信息。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)--而不是一遍又一遍地编写类似的代码。
使用适合您所使用的特定数据库引擎的内容。需要处理它的是与数据库交互的接口。如果代码端对数据库的接口足够模块化,那么只需进行简单的一行更改即可处理底层数据库中不同的布尔类型。
我认为“是/否”值比“1/0”更有意义。在使用Oracle时,我会按照以下方式尽可能使数据经过数据库引擎验证:
不要添加列,我建议你创建另一个表。听我说...
假设你有一个名为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 根据我的经验,这导致了巨大的灵活性。
JOIN
时不必担心重复值。虽然我的想法有点倒错,但我选择使用IN
子句是因为它更能表达我的思路。:) - Adam Paynter位列通常用于表示SQL Server中的T/F或Y/N类型值。尽管数据库纯粹主义者可能会告诉你,位列在数据库中没有位置,因为它们“太接近硬件”- Joe Celko。
"SELECT * FROM mytable WHERE col1
这在SQL Server和Oracle中是不可能的(你需要有某种谓词)。"
这只能说明Oracle和SQL Server是多么荒谬可笑的畸形产物。
如果col1被声明为BOOLEAN类型,则表达式“col1”就是一个谓词。
如果WHERE子句的语义要求其表达式仅评估为真值,并且某些列被声明为“真值”类型,则“WHERE that-column”应该被允许和支持。毫无疑问,任何不支持此功能的系统都会暴露其作者的无能和平庸。
通常我不会使用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甚至不需要更改,因为属性本身是在数据库中定义的,而不是在代码中定义的。
希望这有所帮助。
1
和0
不足以让人类阅读来进行调试的开发者。 - Quassnoi