如何在 Oracle 中存储字节数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3255998/
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
How to store an array of bytes in Oracle?
提问by Arseni Mourzenko
I want to store a short array of 64 bytes in Oracle database (a password hash). I thought char(64 byte)is what I need, but it seems like it doesn't work. In Microsoft SQL, I use binaryand varbinarytypes. What type do I need to use in Oracle?
我想在 Oracle 数据库中存储一个 64 字节的短数组（密码哈希）。我认为这char(64 byte)是我需要的，但似乎不起作用。在 Microsoft SQL 中，我使用binary和varbinary类型。我需要在 Oracle 中使用什么类型？
Every example I've found uses blobto store binary data, but I suppose blobis intended only for large objects, not for fixed size short arrays.
我发现的每个示例都blob用于存储二进制数据，但我认为blob仅适用于大型对象，而不适用于固定大小的短数组。
When updating the data, is the code like this appropriate:
更新数据时，这样的代码是否合适：
byte[] passwordHash = GenerateHash();
using (OracleCommand updateHash = new OracleCommand("update A set passwordHash = :hash where EntryId = :id", oracleConnection))
{
updateHash.Parameters.Add(":hash", passwordHash);
updateHash.Parameters.Add(":id", entryId);
if (updateHash.ExecuteNonQuery() != 1)
{
// ...
}
}
or am I missing something and byte array parameters cannot be added like this?
还是我错过了一些东西并且不能像这样添加字节数组参数？
回答by spencer7593
In Oracle, The RAW datatype is appropriate for storing binary values.
在 Oracle 中，RAW 数据类型适用于存储二进制值。
The issue with using character datatypes for storing binary data is that the values are subject to character set translation.
使用字符数据类型存储二进制数据的问题在于，这些值需要进行字符集转换。
If the client character set does not match the database character set, then values are subject to translation. (That is, a binary value in one encoding represents a particular character, but that character can be represented by a different binary value in another characterset.
如果客户端字符集与数据库字符集不匹配，则值需要进行转换。（即，一种编码中的二进制值表示特定字符，但该字符可以由另一种字符集中的不同二进制值表示。
With the character datatype, Oracle preserves the "character" value, not the encoded binary value.
对于字符数据类型，Oracle 保留“字符”值，而不是编码的二进制值。
If you want to use a character (e.g. CHAR or VARCHAR2) datatype to store binary values, then you really need to encode the binary value as plain text, and store and retrieve the encoded value. Two popular binary-to-text encodings are hexadecimal and base64 (uuencode).
如果您想使用字符（例如 CHAR 或 VARCHAR2）数据类型来存储二进制值，那么您确实需要将二进制值编码为纯文本，并存储和检索编码后的值。两种流行的二进制到文本编码是十六进制和 base64 (uuencode)。
Oracle provides builtin functions RAWTOHEX and HEXTORAW for encoding and decoding binary (RAW datatype) as hexadecimal strings (VARCHAR2 datatype).
Oracle 提供了内置函数 RAWTOHEX 和 HEXTORAW，用于将二进制（RAW 数据类型）编码和解码为十六进制字符串（VARCHAR2 数据类型）。
回答by Powerlord
In addition to blob, Oracle also has the RAWdatatype,
除了blob，Oracle 还有RAW数据类型，
RAW is a variable-length datatype like VARCHAR2
RAW 是一种可变长度的数据类型，如 VARCHAR2
-- Oracle 10g Release 2 Datatypes
RAW can be set up to a max size of 2000 bytes, with LONG RAWhaving a max size of 2GB.
RAW 的最大大小可以设置为 2000 字节，LONG RAW最大大小为 2GB。
However:
然而：
Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns.
Oracle 强烈建议您将 LONG RAW 列转换为二进制 LOB (BLOB) 列。LOB 列受到的限制比 LONG 列少得多。
回答by Allan
The best solution is to use the RAW datatype, which is intended to store binary data. However RAW data is inserted and retrieved as hexadecimal values, so some conversion may be required.
最好的解决方案是使用 RAW 数据类型，它用于存储二进制数据。但是，RAW 数据是作为十六进制值插入和检索的，因此可能需要进行一些转换。
You should be able to use CHAR or VARCHAR2 as well, but those datatypes will not constrain the field to just binary data. If you tell us what error you were receiving when you tried to insert into the CHAR column, we may be able to help you work that out...
您也应该能够使用 CHAR 或 VARCHAR2，但这些数据类型不会将该字段限制为二进制数据。如果您告诉我们您在尝试插入 CHAR 列时收到的错误，我们或许可以帮助您解决这个问题...
Keep in mind, if you use the CHAR datatype, that CHAR values are always padded to the length of the field, so you may need to trim smaller values before using them in some cases.
请记住，如果您使用 CHAR 数据类型，则 CHAR 值始终填充到字段的长度，因此在某些情况下您可能需要在使用它们之前修剪较小的值。
