Oracle 数据类型:我应该使用 VARCHAR2 还是 CHAR
我应该在 Oracle 中使用 VARCHAR2 还是 CHAR 作为数据类型?
Should I use VARCHAR2 or CHAR as a datatype in Oracle?
有人建议我将 CHAR 用于我需要的这些新表，但我担心这些新表将用于填充使用 VARCHAR2 数据类型的现有表.我担心在 VARCHAR2 字段中放置额外的空格以及比较问题.我知道有一些方法可以通过修剪或转换它们来比较它们，但我担心这会使我的代码变得混乱和错误.
It's been suggested to me to use CHAR for these new tables I need but I'm concerned since these new tables which will be used to populat existing tables that use a VARCHAR2 datatype. I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.
你有什么看法?
推荐答案
我担心在 VARCHAR2 字段中放置额外的空格以及比较问题.我知道有一些方法可以通过修剪或转换它们来比较它们，但我担心这会使我的代码变得混乱和错误.
I'm concerned about extra spaces being placed in the VARCHAR2 fields and with comparison issues. I know there are ways to compare them using by trimming or converting them but I'm afraid it will make my code messy and buggy.
实际上恰恰相反.如果字符串太短，则使用 CHAR 将通过用空格填充它们来强制您的字符串为固定长度.因此，在使用数据的任何应用程序中将 CHAR 与常规字符串进行比较时，该应用程序每次都需要添加修剪.换句话说，VARCHAR2 是自然导致更清晰代码的选择.
It's actually quite the opposite. Using CHAR will force your strings to be a fixed length by padding them with spaces if they're too short. So when comparing CHARs to regular strings in whatever app is using the data, that app would need to add a trim every time. In other words, VARCHAR2 is the choice that naturally leads to cleaner code.
一般来说，您应该始终使用 VARCHAR2，除非您有非常具体的原因为什么需要 CHAR 列.
In general you should always use VARCHAR2, unless you have a very specific reason why you want a CHAR column.
如果您担心字符串的前端或末尾有多余的空格，那么您可以想到以下几个选项:
If you're worried about strings that have extra spaces in the front or end, then there's a few options that come to mind:
- 在插入之前，请确保插入的任何过程都对它们进行了修剪.
- 在列上添加检查约束以确保 string = trim(string).
- 添加一个 before insert 行级触发器，在插入字符串时对其进行修剪.
- 确保在查询表格时对字符串进行修剪
相关文章