Oracle 中的布尔字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Boolean Field in Oracle
提问by Eli Courtwright
Yesterday I wanted to add a boolean field to an Oracle table. However, there isn't actually a boolean data type in Oracle. Does anyone here know the best way to simulate a boolean? Googling the subject discovered several approaches
昨天我想向 Oracle 表添加一个布尔字段。但是，Oracle 中实际上没有布尔数据类型。这里有人知道模拟布尔值的最佳方法吗？谷歌搜索这个主题发现了几种方法
Use an integer and just don't bother assigning anything other than 0 or 1 to it.
Use a char field with 'Y' or 'N' as the only two values.
Use an enum with the CHECK constraint.
使用一个整数，不要费心为其分配 0 或 1 以外的任何值。
使用带有“Y”或“N”的字符字段作为仅有的两个值。
使用带有 CHECK 约束的枚举。
Do experienced Oracle developers know which approach is preferred/canonical?
有经验的 Oracle 开发人员知道哪种方法是首选/规范的吗？
采纳答案by ColinYounger
I found thislink useful.
我发现这个链接很有用。
Here is the paragraph highlighting some of the pros/cons of each approach.
以下段落重点介绍了每种方法的一些优缺点。
The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.
最常见的设计是模仿 Oracle 数据字典视图使用的许多类似布尔值的标志，选择“Y”表示真，“N”表示假。但是，要与主机环境（例如 JDBC、OCCI 和其他编程环境）正确交互，最好为 false 选择 0，为 true 选择 1，以便它可以与 getBoolean 和 setBoolean 函数一起正常工作。
Basically they advocate method number 2, for efficiency's sake, using
基本上他们提倡方法2，为了效率，使用
- valuesof 0/1 (because of interoperability with JDBC's
getBoolean()etc.) with a check constraint - a typeof CHAR (because it uses less space than NUMBER).
getBoolean()带有检查约束的 0/1值（因为与 JDBC 的互操作性等）- 一个类型CHAR的（因为它使用更少的空间比NUMBER）。
Their example:
他们的例子：
create table tbool (bool char check (bool in (0,1)); insert into tbool values(0); insert into tbool values(1);`
create table tbool (bool char check (bool in (0,1)); insert into tbool values(0); insert into tbool values(1);`
回答by Leigh Riffel
Oracle itself uses Y/N for Boolean values. For completeness it should be noted that pl/sql has a boolean type, it is only tables that do not.
Oracle 本身对布尔值使用 Y/N。为了完整起见，应该注意 pl/sql 有一个布尔类型，只有表没有。
If you are using the field to indicate whether the record needs to be processed or not you might consider using Y and NULL as the values. This makes for a very small (read fast) index that takes very little space.
如果您使用该字段来指示是否需要处理记录，您可以考虑使用 Y 和 NULL 作为值。这使得索引非常小（读取速度快），占用的空间非常小。
回答by Bill the Lizard
To use the least amount of space you should use a CHAR field constrained to 'Y' or 'N'. Oracle doesn't support BOOLEAN, BIT, or TINYINT data types, so CHAR's one byte is as small as you can get.
要使用最少的空间，您应该使用限制为“Y”或“N”的 CHAR 字段。Oracle 不支持 BOOLEAN、BIT 或 TINYINT 数据类型，因此 CHAR 的一字节尽可能小。
回答by Andrew Spencer
The best option is 0 and 1 (as numbers - another answer suggests 0 and 1 as CHARfor space-efficiency but that's a bit too twisted for me), using NOT NULL and a check constraint to limit contents to those values. (If you need the column to be nullable, then it's not a boolean you're dealing with but an enumeration with three values...)
最好的选择是 0 和 1（作为数字 - 另一个答案建议将 0 和 1 作为CHAR以提高空间效率，但这对我来说有点太扭曲了），使用 NOT NULL 和检查约束将内容限制为这些值。（如果您需要该列可以为空，那么它不是您要处理的布尔值，而是具有三个值的枚举......）
Advantages of 0/1:
0/1的优点：
- Language independent. 'Y' and 'N' would be fine if everyone used it. But they don't. In France they use 'O' and 'N' (I have seen this with my own eyes). I haven't programmed in Finland to see whether they use 'E' and 'K' there - no doubt they're smarter than that, but you can't be sure.
- Congruent with practice in widely-used programming languages (C, C++, Perl, Javascript)
- Plays better with the application layer e.g. Hibernate
- Leads to more succinct SQL, for example, to find out how many bananas are ready to eat
select sum(is_ripe) from bananasinstead ofselect count(*) from bananas where is_ripe = 'Y'or even (yuk)select sum(case is_ripe when 'Y' then 1 else 0) from bananas
- 语言独立。如果每个人都使用它，'Y' 和 'N' 就可以了。但他们没有。在法国，他们使用“O”和“N”（我亲眼所见）。我没有在芬兰编程，看他们在那里是否使用“E”和“K”——毫无疑问，他们比那更聪明，但你不能确定。
- 与广泛使用的编程语言（C、C++、Perl、Javascript）的实践一致
- 与应用层（例如 Hibernate）一起玩得更好
- 导致更简洁的 SQL，例如，找出准备吃的香蕉数量，
select sum(is_ripe) from bananas而不是select count(*) from bananas where is_ripe = 'Y'（yuk）select sum(case is_ripe when 'Y' then 1 else 0) from bananas
Advantages of 'Y'/'N':
'Y'/'N' 的优点：
- Takes up less space than 0/1
- It's what Oracle suggests, so might be what some people are more used to
- 占用空间小于 0/1
- 这是 Oracle 建议的，因此可能是某些人更习惯的
Another poster suggested 'Y'/null for performance gains. If you've proventhat you need the performance, then fair enough, but otherwise avoid since it makes querying less natural (some_column is nullinstead of some_column = 0) and in a left join you'll conflate falseness with nonexistent records.
另一张海报建议使用 'Y'/null 以提高性能。如果您已经证明您需要性能，那么就足够公平了，但要避免，因为它会使查询不那么自然（some_column is null而不是some_column = 0），并且在左连接中，您会将错误与不存在的记录混为一谈。
回答by Matthew Watson
Either 1/0 or Y/N with a check constraint on it. ether way is fine. I personally prefer 1/0 as I do alot of work in perl, and it makes it really easy to do perl Boolean operations on database fields.
带有检查约束的 1/0 或 Y/N。以太方式很好。我个人更喜欢 1/0，因为我在 perl 中做了很多工作，这使得在数据库字段上执行 perl 布尔运算变得非常容易。
If you want a really in depth discussion of this question with one of Oracles head honchos, check out what Tom Kyte has to say about this Here
如果你想这个问题与甲骨文头大佬之一的真正深入的讨论，查了一下汤姆凯特有说这个在这里
回答by Erick B
The database I did most of my work on used 'Y' / 'N' as booleans. With that implementation, you can pull off some tricks like:
我完成大部分工作的数据库使用“Y”/“N”作为布尔值。通过该实现，您可以使用一些技巧，例如：
Count rows that are true:
SELECT SUM(CASE WHEN BOOLEAN_FLAG = 'Y' THEN 1 ELSE 0) FROM XWhen grouping rows, enforce "If one row is true, then all are true" logic:
SELECT MAX(BOOLEAN_FLAG) FROM Y
Conversely, use MIN to force the grouping false if one row is false.
计算正确的行数：
SELECT SUM(CASE WHEN BOOLEAN_FLAG = 'Y' THEN 1 ELSE 0) FROM X对行进行分组时，强制执行“如果一行为真，则全部为真”逻辑：
SELECT MAX(BOOLEAN_FLAG) FROM Y
相反，如果一行为假，则使用 MIN 强制分组为假。
回答by Ben.12
A working example to implement the accepted answer by adding a "Boolean" column to an existing table in an oracle database (using numbertype):
通过向 oracle 数据库中的现有表添加“布尔”列来实现已接受答案的工作示例（使用number类型）：
ALTER TABLE my_table_name ADD (
my_new_boolean_column number(1) DEFAULT 0 NOT NULL
CONSTRAINT my_new_boolean_column CHECK (my_new_boolean_column in (1,0))
);
This creates a new column in my_table_namecalled my_new_boolean_columnwith default values of 0. The column will not accept NULLvalues and restricts the accepted values to either 0or 1.
这将创建一个新列 inmy_table_name调用my_new_boolean_column，默认值为 0。该列将不接受NULL值并将接受的值限制为0或1。
回答by Ryan Ahearn
In our databases we use an enum that ensures we pass it either TRUE or FALSE. If you do it either of the first two ways it is too easy to either start adding new meaning to the integer without going through a proper design, or ending up with that char field having Y, y, N, n, T, t, F, f values and having to remember which section of code uses which table and which version of true it is using.
在我们的数据库中，我们使用一个枚举来确保我们将它传递给 TRUE 或 FALSE。如果您使用前两种方法中的任何一种，则很容易在没有经过适当设计的情况下开始为整数添加新含义，或者以具有 Y、y、N、n、T、t 的字符字段结束， F、f 值，并且必须记住代码的哪一部分使用哪个表以及它使用的是哪个版本的 true。
