为 oracle 数据库表定义列的字符集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9956433/
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
Defining a Character Set for a column For oracle database tables
提问by Ashwin N Bhanushali
I am running following query in SQL*Plus
我在 SQL*Plus 中运行以下查询
CREATE TABLE tbl_audit_trail (
id NUMBER(11) NOT NULL,
old_value varchar2(255) NOT NULL,
new_value varchar2(255) NOT NULL,
action varchar2(20) CHARACTER SET latin1 NOT NULL,
model varchar2(255) CHARACTER SET latin1 NOT NULL,
field varchar2(64) CHARACTER SET latin1 NOT NULL,
stamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
user_id NUMBER(11) NOT NULL,
model_id varchar2(65) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (id),
KEY idx_action (action)
);
I am getting following error:
我收到以下错误：
action varchar2(20) CHARACTER SET latin1 NOT NULL,
*
ERROR at line 5:
ORA-00907: missing right parenthesis
Can you suggest what am I missing?
你能建议我错过什么吗？
回答by Ben
The simple answer is that, unlike MySQL, character sets can't be defined at column (or table) level. Latin1is not a valid Oracle character set either.
简单的答案是，与 MySQL 不同，字符集不能在列（或表）级别定义。Latin1也不是有效的 Oracle 字符集。
Character sets are consistent across the database and will have been specified when you created the database. You can find your character by querying NLS_DATABASE_PARAMETERS,
字符集在整个数据库中是一致的，并且将在您创建数据库时指定。您可以通过查询找到您的角色NLS_DATABASE_PARAMETERS，
select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET'
The full list of possible character sets is available for 11g r2and for 9ior you can query V$NLS_VALID_VALUES.
11g r2和9i的可能字符集的完整列表可用，或者您可以查询V$NLS_VALID_VALUES.
It is possible to use the ALTER SESSIONstatementto set the NLS_LANGUAGEor the NLS_TERRITORY, but unfortunately you can't do this for the character set. I believe this is because altering the language changes how Oracle would displaythe stored data whereas changing the character set would change how Oracle storesthe data.
可以使用该ALTER SESSION语句来设置NLS_LANGUAGE或NLS_TERRITORY，但不幸的是，您不能对字符集执行此操作。我相信这是因为改变语言会改变 Oracle显示存储数据的方式，而改变字符集会改变 Oracle存储数据的方式。
When displaying the data, you can of course specify the required character set in whichever client you're using.
显示数据时，您当然可以在您使用的任何客户端中指定所需的字符集。
Character set migrationis not a trivial task and should not be done lightly.
字符集迁移不是一项微不足道的任务，不应掉以轻心。
On a slight side note why are you trying to use Latin 1? It would be more normal to set up a new database in something like UTF-8 (otherwise known as AL32UTF8- don't use UTF8) or UTF-16 so that you can store multi-byte data effectively. Even if you don't need it now it's wise to attempt - no guarantees in life - to future proof your database with no need to migrate in the future.
稍微说明一下，您为什么要尝试使用拉丁语 1？以 UTF-8（也称为AL32UTF8- 不要使用UTF8）或 UTF-16 之类的格式设置新数据库会更正常，以便您可以有效地存储多字节数据。即使您现在不需要它，明智的做法是尝试 - 生活中没有任何保证 - 将来证明您的数据库而无需将来迁移。
If you're looking to specify differing character sets for different columns in a database then the better option would be to determine if this requirement is really necessary and to try to remove it. If it is definitely necessary1then your best bet might be to use a character set that is a superset of all potential character sets. Then, have some sort of check constraint that limits the column to specific hex values. I would not recommend doing this at all, the potential for mistakes to creep in is massive and it's extremely complex. Furthermore, different character sets render different hex values differently. This, in turn, means that you need to enforce that a column is rendered in a specific character, which is impossible as it falls outside the scope of the database.
如果您希望为数据库中的不同列指定不同的字符集，那么更好的选择是确定此要求是否真的有必要并尝试将其删除。如果绝对有必要1那么你最好的选择可能是使用一个字符集，它是所有潜在字符集的超集。然后，使用某种检查约束将列限制为特定的十六进制值。我根本不建议这样做，错误蔓延的可能性很大，而且非常复杂。此外，不同的字符集以不同的方式呈现不同的十六进制值。反过来，这意味着您需要强制以特定字符呈现列，这是不可能的，因为它超出了数据库的范围。
1. I'd be interested to know the situation
1.我有兴趣了解情况
回答by Yuri Levinsky
According to provided DDL statement it's some need to use 2 character sets. The implementation of this functionality in Oracle is different from MySQL and done with n* data types like nvarchar2, nchar... Latin1 is similar to some Western European character set that might be default. So you able to define for example "Latin1" (WE**) and some Unicode (UTF8..).
根据提供的 DDL 声明，有些需要使用 2 个字符集。此功能在 Oracle 中的实现与 MySQL 不同，它使用 n* 数据类型（如 nvarchar2、nchar...Latin1）完成，类似于一些可能是默认的西欧字符集。所以你可以定义例如“Latin1”（WE**）和一些Unicode（UTF8..）。
The NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database (which uses VARCHAR2). The NVARCHAR2 is a Unicode-only datatype. The reason you want to use NVARCHAR2 might be that your DB uses a non-Unicode character and you still want to be able to store Unicode data for some columns. Columns in your example would be able to store the same data, however the byte storage will be different.
NVARCHAR2 数据类型由 Oracle 引入，用于希望对某些列使用 Unicode 同时为数据库的其余部分（使用 VARCHAR2）保留另一个字符集的数据库。NVARCHAR2 是仅限 Unicode 的数据类型。您想使用 NVARCHAR2 的原因可能是您的数据库使用了非 Unicode 字符，并且您仍然希望能够为某些列存储 Unicode 数据。您示例中的列将能够存储相同的数据，但是字节存储会有所不同。
