oracle CLOB 与 VARCHAR2，还有其他选择吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5731301/
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
CLOB vs. VARCHAR2 and are there other alternatives?
提问by Extrakun
I am using DevArt's dotConnect and Entity Developer for my application. I've created the tables using the Entity-First feature.
我正在为我的应用程序使用 DevArt 的 dotConnect 和 Entity Developer。我已经使用实体优先功能创建了表。
I notice that many of the column types are set to CLOB. I only have experience with MySQL and Microsoft SQL server, so I am not sure about using CLOB for the application. I did some reading, and found out that CLOB are for large chunk of data.
我注意到许多列类型都设置为 CLOB。我只有 MySQL 和 Microsoft SQL 服务器的经验，所以我不确定是否将 CLOB 用于应用程序。我做了一些阅读，发现 CLOB 用于大块数据。
The questions are:
问题是：
Is using CLOB for most fields, such as the user's gender (which should be a varchar (1) ) or the full name, feasible? The steps for converting a CLOB field to VARCHAR2 requires dropping the column then re-creating it, and is buggy in DevArt's Entity Explorer, so I would like avoid it if possible. Edit: I just found out that if you set a maximum length for a string field it'll automatically be a VARCHAR2.
Are there any equivalents for TINYTEXT in Oracle?
对大多数字段使用 CLOB 是否可行，例如用户的性别（应该是 varchar (1) ）或全名？将 CLOB 字段转换为 VARCHAR2 的步骤需要删除列然后重新创建它，并且在 DevArt 的实体资源管理器中存在问题，因此我希望尽可能避免它。编辑：我刚刚发现，如果为字符串字段设置最大长度，它将自动成为 VARCHAR2。
Oracle 中是否有 TINYTEXT 的等价物？
回答by APC
It is a very bad idea to use a CLOB datatype for a column which ought to be VARCHAR2(1). Apart from the overheads (which are actually minimal, as Oracle will treat inline CLOBs of < 4000 characters as VARCHAR2) we should always strive to use the most accurate representation of our data in the schema: it's just good practice.
对应该是 VARCHAR2(1) 的列使用 CLOB 数据类型是一个非常糟糕的主意。除了开销（实际上很小，因为 Oracle 会将 < 4000 个字符的内联 CLOB 视为 VARCHAR2），我们应该始终努力在模式中使用最准确的数据表示：这只是一种很好的做法。
This really seems like a problem with the DevArt tool, or perhaps your understanding of how to use it (no offence). There ought to be some way for you to specify the datatype of an entity's attribute and/or a way of mapping those specifications to Oracle's physical datatypes. I apologise if this seems a little vague, I'm not familar with the product.
这确实看起来像是 DevArt 工具的问题，或者您对如何使用它的理解（无冒犯）。您应该有某种方式来指定实体属性的数据类型和/或将这些规范映射到 Oracle 的物理数据类型的方式。如果这看起来有点含糊，我很抱歉，我不熟悉该产品。
So, this is the basic problem:
所以，这是基本问题：
SQL> desc t69
Name Null? Type
----------------------------------------- -------- --------
COL1 CLOB
SQL>
SQL> alter table t69 modify col1 varchar2(1)
2 /
alter table t69 modify col1 varchar2(1)
*
ERROR at line 1:
ORA-22859: invalid modification of columns
SQL>
We can fix it by using DDL to alter the table structure. Because the schema has many such columns it is worthwhile automating the process. This function drops the existing column and recreates it as a VARCHAR2. It offers the option to migrate data in the CLOB column to the VARCHAR2 column; you probably don't need this, but it's there for completeness. (This is not production quality code - it needs error handling, managing NOT NULL constraints, etc)
我们可以通过使用 DDL 来改变表结构来修复它。因为模式有很多这样的列，所以自动化这个过程是值得的。此函数删除现有列并将其重新创建为 VARCHAR2。它提供了将 CLOB 列中的数据迁移到 VARCHAR2 列的选项；你可能不需要这个，但它的存在是为了完整性。（这不是生产质量代码 - 它需要错误处理、管理 NOT NULL 约束等）
create or replace procedure clob2vc
( ptab in user_tables.table_name%type
, pcol in user_tab_columns.column_name%type
, pcol_size in number
, migrate_data in boolean := true )
is
begin
if migrate_data
then
execute immediate 'alter table '||ptab
||' add tmp_col varchar2('|| pcol_size|| ')';
execute immediate
'update '||ptab
||' set tmp_col = substr('||pcol||',1,'||pcol_size||')';
end if;
execute immediate 'alter table '||ptab
||' drop column '|| pcol;
if migrate_data
then
execute immediate 'alter table '||ptab
||' rename column tmp_col to '|| pcol;
else
execute immediate 'alter table '||ptab
||' add '||pcol||' varchar2('|| pcol_size|| ')';
end if;
end;
/
So, let's change that column...
因此，让我们更改该列...
SQL> exec clob2vc ('T69', 'COL1', 1)
PL/SQL procedure successfully completed.
SQL> desc t69
Name Null? Type
----------------------------------------- -------- ---------------
COL1 VARCHAR2(1)
SQL>
Calling this procedure can be automated or scripted in the usual ways.
调用此过程可以自动化或以通常的方式编写脚本。
回答by Justin Cave
Using a CLOB for something like a Gendercolumn would, at a minimum, be extremely unusual. If the DDL this tool generates specifies that the LOB data should be stored inline rather than out of line, I wouldn't expect to be any horrible performance issues. But you probably will create problems for other tools accessing the database that don't handle LOBs particularly well.
将 CLOB 用于Gender列之类的东西至少是非常不寻常的。如果此工具生成的 DDL 指定 LOB 数据应内联存储而不是外存储，我不希望出现任何可怕的性能问题。但是您可能会为访问数据库的其他工具创建问题，这些工具不能很好地处理 LOB。
There is no equivalent in Oracle to Tinytextin MySQL. A CLOB is a CLOB.
OracleTinytext中没有与MySQL 中的等价物。CLOB 是 CLOB。
回答by Devart
A simpler solution is to go to Model Explorer -> Model.Store -> Tables/Views, find the necessary column and change the type of this field to VARCHAR2.
Then run the Update Database from Model wizard to persist the changes to the database.
Don't forget to set the MaxLength facet (however, the problem with it is already fixed in the upcoming Beta build).
一个更简单的解决方案是转到 Model Explorer -> Model.Store -> Tables/Views，找到必要的列并将此字段的类型更改为 VARCHAR2。
然后运行“从模型更新数据库”向导以将更改保存到数据库中。
不要忘记设置 MaxLength facet（但是，它的问题已经在即将到来的 Beta 版本中解决了）。
