Oracle 缺少表列的 Bit 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2426145/
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
Oracle's lack of a Bit datatype for table columns
提问by Martin Milan
Oracle does not support a bitdatatype or any other type for true/false scenarios. Does one use the char(1)field instead by using a specific letter to denote yes / true regardless of culture specific issues?
bit对于真/假场景，Oracle 不支持数据类型或任何其他类型。是否char(1)通过使用特定字母来表示是/真的而不考虑文化特定问题来使用该字段？
Instead of charshould it be Number(1,0)for this instead - 0 being considered false / no, anything else being interpreted as true / yes?
而不是char应该Number(1,0)为此 - 0 被认为是假/否，其他任何东西都被解释为真/是？
Is this viable?
这是可行的吗？
Why does Oracle not support a simple boolean type?
为什么 Oracle 不支持简单的布尔类型？
采纳答案by Jens Schauder
I prefer char(1) over number(1), since with some reasonable choice of characters, it is obvious which character has which boolean meaning.
我更喜欢 char(1) 而不是 number(1)，因为通过一些合理的字符选择，很明显哪个字符具有哪个布尔含义。
Of course you should fight all the different varations, choose one and ensure it's use by putting check constraints on the columns.
当然，您应该与所有不同的变体作斗争，选择一个并通过在列上放置检查约束来确保它的使用。
Although it probably is to late in your case, generating the schema from another tool often takes care at least of the consistency issue. I personally prefer hibernate for this purpose, but that is very situation specific.
尽管在您的情况下可能为时已晚，但从另一个工具生成架构通常至少会注意一致性问题。为此，我个人更喜欢休眠，但这取决于具体情况。
And of course that is a glaring obmission. To make it worse, PL/SQL has a boolean, but you can't use it in SQL statements.
当然，这是一个明显的遗漏。更糟糕的是，PL/SQL 有一个布尔值，但你不能在 SQL 语句中使用它。
回答by Samuel
Use a CHAR(1), and a constraint to allow only 1and 0.
使用CHAR(1), 和约束以仅允许1和0。
...
...
col CHAR(1),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('1','0'))
回答by álvaro González
I'm not an English native so I tend to use either 1 and 0 or '1' and '0'. Using 'Y' and 'N' make little sense if you aren't coding in English (yes, native language coding does exist). Using 'SI' and 'NO' or 'S' and 'N' doesn't look professional (just like naming variables with accented letters). Ones and zeroes, on the contrary, are pretty standard if you've coded in C, PHP or JavaScript. In any case, I always add the appropriate constraint to disallow any other character. Apart from subjective issues, I don't think there're noticeable performance gain in choosing CHAR or NUMBER. I like numbers a little more because I don't need to quote them :)
我不是英语母语，所以我倾向于使用 1 和 0 或 '1' 和 '0'。如果您不是用英语编码（是的，母语编码确实存在），则使用“Y”和“N”毫无意义。使用 'SI' 和 'NO' 或 'S' 和 'N' 看起来不专业（就像用重音字母命名变量一样）。相反，如果您使用 C、PHP 或 JavaScript 进行编码，则 1 和 0 是非常标准的。无论如何，我总是添加适当的约束来禁止任何其他字符。除了主观问题，我认为选择 CHAR 或 NUMBER 不会有明显的性能提升。我更喜欢数字，因为我不需要引用它们:)
I agree it's a glaring omission but I've read seriously heated discussions on the subject in some Oracle forums; it's a kind of religious issue. Some claim that booleans belong to application data types and have no place in the database core. Honestly, I believe it's one of those We Have Been So Long Without It That We Had Better Say It Was On Purpose things.
我同意这是一个明显的遗漏，但我在一些 Oracle 论坛上阅读了有关该主题的激烈讨论；这是一种宗教问题。有些人声称布尔值属于应用程序数据类型，在数据库核心中没有位置。老实说，我相信这是我们已经很久没有它的人之一，我们最好说这是有目的的事情。
By the way, MySQL has a BOOLEAN type but it's a synonym for TINYINT(1) so it eventually equals to 1 and 0; which is fine, because it also has the constants TRUE and FALSE that evaluate to 1 and 0.
顺便说一下，MySQL 有一个 BOOLEAN 类型，但它是 TINYINT(1) 的同义词，所以它最终等于 1 和 0；这很好，因为它还具有计算结果为 1 和 0 的常量 TRUE 和 FALSE。
回答by DCookie
Here is an Ask Tomdiscussion on the topic. Gives an Oracle-centric view on the issue.
这是关于该主题的Ask Tom讨论。对这个问题给出了一个以 Oracle 为中心的观点。
As for storage, char(1) is actually a bit (no pun intended) more efficient:
至于存储， char(1) 实际上有点（没有双关语意）更有效：
SQL> CREATE TABLE xx (c CHAR(1), n NUMBER);
Table created
SQL> insert into xx values('T', 1);
1 row inserted
SQL> select dump(c), dump(n) from xx;
DUMP(C) DUMP(N)
------------------- -------------
Typ=96 Len=1: 84 Typ=2 Len=2: 193,2
回答by GilesDMiddleton
According to this Oracle guide - you should use NUMBER(3). Crazy, but true.
根据这个 Oracle 指南 - 您应该使用 NUMBER(3)。疯狂，但真实。
http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm
http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm
回答by Thilo
Number(1) is no better than char(1). Especially if it will be in addition to the existing char(1). That will just add to the confusion.
Number(1) 并不比 char(1) 好。特别是如果它将是现有 char(1) 的补充。这只会增加混乱。
FWIW, Oracle in internal views (such as USER_TAB_COLUMNS) uses varchar2(3) (YES and NO). Not sure if they are 100% consistent here, though.
FWIW，Oracle 在内部视图（例如 USER_TAB_COLUMNS）中使用 varchar2(3)（是和否）。不过，不确定它们在这里是否 100% 一致。
回答by SomeCoder
The question is old but until the latest release of oracle is used it is still a valid question.
这个问题很老，但在使用最新版本的 oracle 之前，它仍然是一个有效的问题。
I would solve the problem this way: Create a table which holds the possible values for true/false plus localized display text f.e. T$KEYWORDS ITEMNO ITEMTEXT ITEMTEXT_DE ITEMTEXT_FE ... 0 False Falsch 1 True Wahr
我会这样解决这个问题：创建一个表，其中包含真/假的可能值加上本地化的显示文本 fe T$KEYWORDS ITEMNO ITEMTEXT ITEMTEXT_DE ITEMTEXT_FE ... 0 False Falsch 1 True Wahr
Instead of True/False this could be also Selected, Not Selected, etc.
也可以选择、未选择等，而不是 True/False。
And then add a foreigh key to your column to this table. That way you have only valid values and they do not change with localization.
然后将您的列的外键添加到此表中。这样你就只有有效的值，它们不会随着本地化而改变。
ANother good solution imho is using a check constraint on your data column. This ofc does not work if your values could be different in the same database/column depending on localization of clients.
恕我直言，另一个好的解决方案是在您的数据列上使用检查约束。如果您的值在同一数据库/列中可能因客户端的本地化而不同，则此 ofc 不起作用。
alter table tblLocations add flag number CONSTRAINT <constraintname> CHECK (flag IN (1,0));
alter table tblLocations add flag number CONSTRAINT <constraintname> CHECK (flag IN (1,0));
回答by Tagar
Oracle internally uses "bits" (not a datatype per se) in different Data Dictionary views.
Oracle 内部在不同的数据字典视图中使用“位”（本身不是数据类型）。
For example, dba_users view has :
例如， dba_users 视图具有：
..
, DECODE (BITAND (u.spare1, 128), 128, 'YES', 'NO')
..
, DECODE (BITAND (u.spare1, 256), 256, 'Y', 'N')
..
which shows a way to workaround this in a way. If you don't have to modify "boolean" bits often, you could employ the same approach that Oracle had since Oracle 6 (at least). Create a table with a NUMBER column, and a VIEW on top of that that hides complexity of BITAND operations.
这显示了一种以某种方式解决此问题的方法。如果您不必经常修改“布尔值”位，您可以采用自 Oracle 6（至少）以来 Oracle 所采用的相同方法。创建一个包含 NUMBER 列的表，并在其上创建一个 VIEW 以隐藏 BITAND 操作的复杂性。
ps. On a side note, Oracle JDBC has a "Bit" datatype https://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/jdbc/OracleTypes.html#BITas well as you already know PL/SQL has Boolean. Although it probably doesn't help you much. See BITAND approach above if it suites your case.
附：附带说明一下，Oracle JDBC 有一个“位”数据类型https://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/jdbc/OracleTypes.html#BIT以及您已经知道的 PL/ SQL 有布尔值。虽然它可能对你没有多大帮助。如果它适合您的情况，请参阅上面的 BITAND 方法。
回答by Artem.Borysov
https://docs.oracle.com/cd/E17952_01/refman-5.5-en/char.html
https://docs.oracle.com/cd/E17952_01/refman-5.5-en/char.html
As DCookie said, char(1) is more efficient. Because VARCHAR2(VARCHAR) empty contains 1 byte, but when we store 1 character then empty 1 byte size + with character 1 byte size --> 2 byte need to store 1 character in varchar
正如 DCookie 所说， char(1) 更有效。因为 VARCHAR2(VARCHAR) 空包含 1 个字节，但是当我们存储 1 个字符时，则空 1 个字节大小 + 字符 1 个字节大小 --> 2 个字节需要在 varchar 中存储 1 个字符
