考虑到Postgres数据库的性能和空间:varchar(1)或smallint,我将存储从0到7的状态,并且我想知道哪个是最好的类型字段。
I'll store a status from 0 to 7 and I want to know which is the better type field to store, considering performance and space on Postgres database: varchar(1) or smallint.
顺便说一句,设置字段varchar(1)或varchar(100)并保持性能和空间有何不同吗?
By the way, is there any difference to set a field varchar(1) or varchar(100), still talking about performance and space?
推荐答案虽然我同意鲍勃·贾维斯(Bob Jarvis)的观点,这确实是过早的优化,但我将尝试着重于所问的问题。
While I agree with Bob Jarvis that this is really premature optimisation, I'll try to focus on the question as asked.
您忽略了最重要的选择。您的选择包括:
You're neglecting the most important choices. Your choices include:
- smallint
- 枚举
- 字符
- 个字符和个字符变化
- smallint
- enum
- "char"
- character and character varying
您可以使用枚举类型。只要您期望永远不要删除有效值,这实际上是可以的,因为PostgreSQL当前不支持从枚举类型中删除值。
You could use an enumerated type. This is only really OK so long as you expect never to remove valid values, since PostgreSQL currently doesn't support deleting values from enum types.
或者,您可以使用字符 数据类型。是的,引号很重要。它是单个字符,例如C数据类型 char 。在分析时,不带引号的 char 会变成 character(1)。
Alternately, you could use the "char" data type. Yes, the quotes matter. It's a single character, like the C data type char. Without quotes char turns into character(1) at parse time.
varchar 和 character 并不是很理想,因为它们是带有标头的可变宽度类型
varchar and character aren't really ideal for this because they're variable-width types with header overheads etc.
顺便说一句,设置字段 varchar(1)$有什么区别或 varchar(100),还在谈论性能和空间吗?
By the way, is there any difference to set a field varchar(1) or varchar(100), still talking about performance and space?
不。其他问题(很多次)可以回答。
No. This is answered (many times) in other questions.
更多推荐
性能类型varchar(1)或smallint来存储状态Postgres
发布评论