blocks|key|3161682|text|在我们的数据库中，我们使用枚举来确保传递的是TRUE或FALSE。如果前两种方法中的任何一种，都很容易在没有经过适当设计的情况下开始向整数添加新的含义，或者以具有Y、y、N、n、T、t、F、f值的char字段结束，并且必须记住代码的哪一部分使用哪个表以及它使用的true版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3161683|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3161712|text|我发现this链接很有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3161713|下面的段落重点介绍了每种方法的一些优缺点。|3161714|3161715|最常见的设计是模仿Oracle的数据字典视图使用的许多类似布尔的标志，选择'Y‘表示true，选择'N’表示false。但是，为了正确地与主机环境交互，比如JDBC、OCCI和其他编程环境，最好选择0表示false，选择1表示true，这样它就可以正确地使用getBoolean和setBoolean函数。|blockquote|3161716|3161717|3161718|基本上，为了提高效率，他们提倡方法2，使用|3161719|的|3161720|值为0/1+(由于与JDBC的getBoolean()等的互操作性)。使用check约束，|unordered-list-item|style|CODE|3161721|是一种CHAR类型(因为它使用的空间比NUMBER少)。|3161722|3161723|他们的例子：|3161724|3161725|创建表tbool+(bool+char+check+(bool+in+(0,1))；insert+into+tbool+values(0)；insert+into+tbool+values(1);`|3161726|3161727|entityMap|0|LINK|mutability|MUTABLE|url|http://www.techrepublic.com/article/oracle-tip-choosing-an-efficient-design-for-boolean-column-values/^0|3|4|0|0|0|0|0|0|0|0|0|F|C|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@$A|1C|B|1D|1|1E]]|C|$]]|$1|D|3|E|5|6|7|1F|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|1G|8|@]|9|@]|C|$]]|$1|G|3|H|5|I|7|1H|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1K|8|@]|9|@]|C|$]]|$1|N|3|O|5|6|7|1L|8|@]|9|@]|C|$]]|$1|P|3|Q|5|R|7|1M|8|@$A|1N|B|1O|S|T]]|9|@]|C|$]]|$1|U|3|V|5|R|7|1P|8|@]|9|@]|C|$]]|$1|W|3|-4|5|6|7|1Q|8|@]|9|@]|C|$]]|$1|X|3|Y|5|6|7|1R|8|@]|9|@]|C|$]]|$1|Z|3|-4|5|6|7|1S|8|@]|9|@]|C|$]]|$1|10|3|11|5|I|7|1T|8|@]|9|@]|C|$]]|$1|12|3|-4|5|6|7|1U|8|@]|9|@]|C|$]]|$1|13|3|-4|5|6|7|1V|8|@]|9|@]|C|$]]]|14|$15|$5|16|17|18|C|$19|1A]]]]

I found <a href="http://www.techrepublic.com/article/oracle-tip-choosing-an-efficient-design-for-boolean-column-values/" rel="noreferrer">this</a> link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

<blockquote>
 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.
</blockquote>

Basically they advocate method number 2, for efficiency's sake, using 

<ul>
<li>values of 0/1 (because of interoperability with JDBC's <code>getBoolean()</code> etc.) with a check constraint</li>
<li>a type of CHAR (because it uses less space than NUMBER). </li>
</ul>

Their example:

<blockquote>
<pre><code>create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`
</code></pre>
</blockquote>

blocks|key|3374548|text|要使用最少的空间，您应该使用约束为'Y‘或'N’的CHAR字段。Oracle不支持BOOLEAN、BIT或TINYINT数据类型，因此CHAR的一个字节是最小的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3374549|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3374581|text|我在数据库上做的大部分工作都使用'Y‘/+'N’作为布尔值。使用该实现，您可以完成一些技巧，如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3374582|3374583|计数为true的行数：|ordered-list-item|3374584|3374585|分组行时从X|3374586|中选择SUM(CASE+WHEN+BOOLEAN_FLAG+=+'Y‘THEN+1+ELSE+0)，执行“如果一行为真，则所有行都为真”的逻辑：|unordered-list-item|3374587|3374588|从Y中选择最大值(BOOLEAN_FLAG)|3374589|相反，如果一行为false，则使用MIN强制分组为false。|3374590|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|U|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|X|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

The database I did most of my work on used 'Y' / 'N' as booleans. With that implementation, you can pull off some tricks like:

<ol>
<li>Count rows that are true: 
SELECT SUM(CASE WHEN BOOLEAN_FLAG = 'Y' THEN 1 ELSE 0) FROM X </li>
<li>When 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.</li>
</ol>

blocks|key|369741|text|带check约束的1/0或Y/N。以太方式很好。我个人更喜欢1/0，因为我在perl中做了很多工作，而且它使得在数据库字段上执行perl布尔操作变得非常容易。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|369742|如果您想要与Oracle+head+honchos中的某个人深入讨论此问题，请查看Tom+Kyte对此Here的看法|offset|length|369743|entityMap|0|LINK|mutability|MUTABLE|url|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595^0|0|1F|4|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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.

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 <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595" rel="noreferrer">Here</a>

blocks|key|369778|text|Oracle本身对布尔值使用Y/N。为了完整性，应该注意到pl/sql有一个boolean类型，只有表没有。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|369779|如果您使用该字段来指示是否需要处理记录，则可以考虑使用Y和NULL作为值。这使得索引非常小(读取速度很快)，并且占用的空间非常小。|369780|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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. 

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.

blocks|key|366427|text|最好的选择是0和1(作为数字-另一个答案建议使用0和1作为CHAR以提高空间效率，但这对我来说有点扭曲)，使用NOT+NULL和check约束将内容限制为这些值。(如果您需要列可以为空，那么您要处理的不是一个布尔值，而是一个具有三个值的枚举……)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|366428|0/1的优势：|366429|独立于|366430|语言。如果每个人都使用它，'Y‘和'N’就可以了。在法国，他们用“O”和“N”(我亲眼所见)。我还没有在芬兰编程来看他们是否使用'E‘和'K’--毫无疑问，他们比这更聪明，但你不能在广泛使用的编程语言(C，sure.|unordered-list-item|366431|Congruent，Perl，Javascript)|366432|Plays+)的实践中掌握更好的应用层，例如，Hibernate|366433|Leads到更简洁的select+sum(case+is_ripe+when+'Y'+then+1+else+0)+from+bananas|offset|length|style|CODE|366434|，来找出有多少香蕉准备吃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|366435|‘Y’/‘N’的优点：|366436|366437|比0/1|366438|占用更少的空间这是Oracle建议的，因此可能是一些人更习惯使用|366439|366440|另一个帖子建议'Y'/null来提高性能。如果你已经证明你需要这样的性能，那就足够公平了，但是避免这样做，因为这会使查询变得不那么自然(用some_column+is+null代替some_column+=+0)，而且在左连接中，你会把错误和不存在的记录混为一谈。|366441|entityMap^0|0|0|0|0|0|0|A|1O|0|C|W|1B|1C|2X|1O|0|0|0|0|0|0|1X|J|2I|F|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$]]|$1|I|3|J|5|H|7|1A|8|@]|9|@]|A|$]]|$1|K|3|L|5|H|7|1B|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1C|8|@$O|1D|P|1E|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1F|8|@$O|1G|P|1H|Q|R]|$O|1I|P|1J|Q|R]|$O|1K|P|1L|Q|R]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1M|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|X|3|Y|5|H|7|1O|8|@]|9|@]|A|$]]|$1|Z|3|10|5|H|7|1P|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1R|8|@$O|1S|P|1T|Q|R]|$O|1U|P|1V|Q|R]]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|15|$]]

The best option is 0 and 1 (as numbers - another answer suggests 0 and 1 as CHAR for 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...)

Advantages of 0/1:

<ul>
<li>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.</li>
<li>Congruent with practice in widely-used programming languages (C, C++, Perl, Javascript)</li>
<li>Plays better with the application layer e.g. Hibernate</li>
<li>Leads to more succinct SQL, for example, to find out how many bananas are ready to eat <code>select sum(is_ripe) from bananas</code> instead of <code>select count(*) from bananas where is_ripe = 'Y'</code> or even (yuk) <code>select sum(case is_ripe when 'Y' then 1 else 0) from bananas</code></li>
</ul>

Advantages of 'Y'/'N':

<ul>
<li>Takes up less space than 0/1</li>
<li>It's what Oracle suggests, so might be what some people are more used to</li>
</ul>

Another poster suggested 'Y'/null for performance gains. If you've proven that you need the performance, then fair enough, but otherwise avoid since it makes querying less natural (<code>some_column is null</code> instead of <code>some_column = 0</code>) and in a left join you'll conflate falseness with nonexistent records.

blocks|key|365005|text|通过向oracle数据库中的现有表添加"Boolean“列(使用number类型)来实现接受答案的工作示例：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|365006|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))
);|code-block|syntax|javascript|365007|这将在my_table_name中创建一个名为my_new_boolean_column的新列，其默认值为0。该列不接受NULL值，并将接受的值限制为0或1。|365008|entityMap^0|W|6|0|0|3|D|N|L|1O|4|23|1|25|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|N|$]]

A working example to implement the accepted answer by adding a "Boolean" column to an existing table in an oracle database (using <code>number</code> type):

<pre><code>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))
);
</code></pre>

This creates a new column in <code>my_table_name</code> called <code>my_new_boolean_column</code> with default values of 0. The column will not accept <code>NULL</code> values and restricts the accepted values to either <code>0</code> or <code>1</code>.

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

<ol>
<li>Use an integer and just don't bother assigning anything other than 0 or 1 to it.</li>
<li>Use a char field with 'Y' or 'N' as the only two values.</li>
<li>Use an enum with the CHECK constraint.</li>
</ol>

Do experienced Oracle developers know which approach is preferred/canonical?

Boolean Field in Oracle

数据库

Perl

昨天我想向Oracle表中添加一个布尔型字段。但是，Oracle中实际上并没有布尔数据类型。这里有没有人知道模拟布尔值的最好方法？在谷歌上搜索这个主题发现了几种方法使用整数，只需将0或1以外的值赋给它即可。使用仅有的两个值为'Y‘或'N’的字符字段。使用带有CHECK约束的枚举。有经验的Oracle开发人员是否知道哪种...

问Oracle中的布尔型字段
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle中的布尔型字段EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle中的布尔型字段
EN