为列定义字符集对于oracle数据库表 [英] Defining a Character Set for a column For oracle database tables

问题描述

我在SQL * Plus中运行以下查询

I am running following query in 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)
);

我收到以下错误：

action varchar2(20) CHARACTER SET latin1 NOT NULL,
                      *
ERROR at line 5:
ORA-00907: missing right parenthesis

您可以建议我丢失什么吗？

Can you suggest what am I missing?

推荐答案

简单的答案是，与MySQL不同，字符集不能在列（或表）级。 Latin1 也不是有效的Oracle字符集。

The simple answer is that, unlike MySQL, character sets can't be defined at column (or table) level. Latin1 is not a valid Oracle character set either.

字符集在数据库中是一致的，当您创建数据库时。您可以通过查询 NLS_DATABASE_PARAMETERS ，

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,

select value
  from nls_database_parameters
 where parameter = 'NLS_CHARACTERSET'

可能的字符集的完整列表适用于 11g r2 和 for 9i ，或者您可以查询 V $ NLS_VALID_VALUES 。

The full list of possible character sets is available for 11g r2 and for 9i or you can query V$NLS_VALID_VALUES.

可以使用ALTER SESSION 语句以设置 NLS_LANGUAGE 或 NLS_TERRITORY ，但不幸的是，您无法为字符集执行此操作。我相信这是因为改变语言改变了Oracle如何显示存储的数据，而改变字符集将改变Oracle如何存储数据。

It is possible to use the ALTER SESSION statement to set the NLS_LANGUAGE or 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 display the stored data whereas changing the character set would change how Oracle stores the data.

显示数据时，您可以在您使用的任何客户端中指定所需的字符集。

When displaying the data, you can of course specify the required character set in whichever client you're using.

字符集迁移不是一项简单的任务，不应轻易完成。

Character set migration is not a trivial task and should not be done lightly.

在一个小小的方面，你为什么要使用拉丁语1？在诸如UTF-8（也称为 AL32UTF8 ）之类的东西中设置一个新的数据库更为正常，不要使用 UTF8 ）或UTF-16，以便可以有效地存储多字节数据。即使你不需要它，现在是明智的尝试 - 在生活中没有保证 - 未来证明你的数据库，而不需要在将来迁移。

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.

如果你为了在数据库中为不同的列指定不同的字符集，那么更好的选择是确定这个需求是否真的有必要，并尝试删除它。如果肯定需要 ¹ ，那么你最好的选择是使用一个字符集，它是所有潜在字符集的超集。然后，有某种检查约束将列限制为特定的十六进制值。我不建议这样做，潜在的错误蔓延是巨大的，它是非常复杂。此外，不同的字符集不同地呈现不同的十六进制值。这反过来意味着你需要强制列以特定字符呈现，这是不可能的，因为它不在数据库的范围之内。

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 necessary¹ then 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.

< sub> 1。我有兴趣知道情况

这篇关于为列定义字符集对于oracle数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！