I have a database table, let's call it Countries. Countries all have names, short names and continents. Let's assume that the longest name of any country in the world is 54 characters long. Should I set the maximum size to 54 or should I set it to e.g. 64 or something else? Will my choice affect queries or storage in any way?
我有一个数据库表,我们称之为国家。各国都有名字,短名和大陆。我们假设世界上任何一个国家的最长名称都是54个字符。我应该将最大尺寸设置为54还是应该将其设置为例如64还是其他什么?我的选择会以任何方式影响查询或存储吗?
I know this might seem like pre-optimizing, but I find myself often choosing 32, 64, 128, etc. and I'd like to know if that matters.
我知道这可能看起来像预优化,但我发现自己经常选择32,64,128等,我想知道这是否重要。
Thanks
4 个解决方案
#1
A solid DBMS should know what's best for it. If you need "54 chars" but the database can do better search-optimizations with 64 chars, it will expand the data-field internally automatically. If it doesn't, you will have saved 12 chars per row.
一个坚实的DBMS应该知道什么是最好的。如果您需要“54个字符”但数据库可以使用64个字符进行更好的搜索优化,它将自动在内部扩展数据字段。如果没有,您将每行保存12个字符。
I don't think that's something you should worry about, just write down what you need.
我不认为这是你应该担心的事情,只需记下你需要的东西。
#2
Answers will be RDBMS specific:
答案将特定于RDBMS:
If you are using SQL Server (2000 onwards), a varchar only uses space for the actual characters stored in it (plus a small overhead), up to the maximum size as declared.
如果您使用的是SQL Server(2000以后版本),则varchar仅为存储在其中的实际字符使用空间(加上一小部分开销),最大为声明的最大大小。
As a general rule of thumb, don't optimise until you have a problem.
作为一般经验法则,在遇到问题之前不要进行优化。
#3
Depends on the data type. For instance in ORACLE - a varchar2 data type would be a good choice for variable-length strings. You need only to define the maximum length of the string - varchar2(maxsize). This would mean that any string with a length of up to 10 would fit. The length of the column would depend on the actual string stored in it.
取决于数据类型。例如在ORACLE中 - varchar2数据类型对于可变长度字符串是一个不错的选择。您只需要定义字符串的最大长度 - varchar2(maxsize)。这意味着任何长度最多为10的字符串都适合。列的长度取决于存储在其中的实际字符串。
See. link text
看到。链接文字
In your case, if your absolutely sure that there is no country name longer than 54 characters, I would use variable-size data type of size 54.
在您的情况下,如果您绝对确定没有超过54个字符的国家/地区名称,我将使用大小为54的可变大小数据类型。
#4
If you're using SQL Server, I would strongly recommend NOT using VARCHAR(8000) or VARCHAR(MAX) for all fields..... you need to let common sense prevail here - make the field as big as you think you'll probably need it, add a little extra for unexpected length, and you're done :-)
如果你正在使用SQL Server,我强烈建议你不要在所有领域使用VARCHAR(8000)或VARCHAR(MAX)......你需要让常识占上风 - 让你认为你的领域一样大'可能需要它,为意外长度添加一点额外的,你完成了:-)
Also, I think it's a good idea to keep it to a few lengths that you use over and over again. E.g. I use VARCHAR(255) for most fields like street address or e-mail etc. that could be a bit longer, VARCHAR(50) for things like phone numbers etc., and VARCHAR(20) for short strings like zip code and such.
另外,我认为将它保持在你一遍又一遍使用的几个长度是个好主意。例如。我对大多数字段使用VARCHAR(255),如街道地址或电子邮件等可能会更长一些,VARCHAR(50)用于电话号码等,而VARCHAR(20)用于短字符串,如邮政编码等。
Be pragmatic about it - find a style that works for you. Don't over-optimize, but don't go nuts the other way, either.
务实 - 找到适合你的风格。不要过度优化,但也不要反其道而行之。
Marc
#1
A solid DBMS should know what's best for it. If you need "54 chars" but the database can do better search-optimizations with 64 chars, it will expand the data-field internally automatically. If it doesn't, you will have saved 12 chars per row.
一个坚实的DBMS应该知道什么是最好的。如果您需要“54个字符”但数据库可以使用64个字符进行更好的搜索优化,它将自动在内部扩展数据字段。如果没有,您将每行保存12个字符。
I don't think that's something you should worry about, just write down what you need.
我不认为这是你应该担心的事情,只需记下你需要的东西。
#2
Answers will be RDBMS specific:
答案将特定于RDBMS:
If you are using SQL Server (2000 onwards), a varchar only uses space for the actual characters stored in it (plus a small overhead), up to the maximum size as declared.
如果您使用的是SQL Server(2000以后版本),则varchar仅为存储在其中的实际字符使用空间(加上一小部分开销),最大为声明的最大大小。
As a general rule of thumb, don't optimise until you have a problem.
作为一般经验法则,在遇到问题之前不要进行优化。
#3
Depends on the data type. For instance in ORACLE - a varchar2 data type would be a good choice for variable-length strings. You need only to define the maximum length of the string - varchar2(maxsize). This would mean that any string with a length of up to 10 would fit. The length of the column would depend on the actual string stored in it.
取决于数据类型。例如在ORACLE中 - varchar2数据类型对于可变长度字符串是一个不错的选择。您只需要定义字符串的最大长度 - varchar2(maxsize)。这意味着任何长度最多为10的字符串都适合。列的长度取决于存储在其中的实际字符串。
See. link text
看到。链接文字
In your case, if your absolutely sure that there is no country name longer than 54 characters, I would use variable-size data type of size 54.
在您的情况下,如果您绝对确定没有超过54个字符的国家/地区名称,我将使用大小为54的可变大小数据类型。
#4
If you're using SQL Server, I would strongly recommend NOT using VARCHAR(8000) or VARCHAR(MAX) for all fields..... you need to let common sense prevail here - make the field as big as you think you'll probably need it, add a little extra for unexpected length, and you're done :-)
如果你正在使用SQL Server,我强烈建议你不要在所有领域使用VARCHAR(8000)或VARCHAR(MAX)......你需要让常识占上风 - 让你认为你的领域一样大'可能需要它,为意外长度添加一点额外的,你完成了:-)
Also, I think it's a good idea to keep it to a few lengths that you use over and over again. E.g. I use VARCHAR(255) for most fields like street address or e-mail etc. that could be a bit longer, VARCHAR(50) for things like phone numbers etc., and VARCHAR(20) for short strings like zip code and such.
另外,我认为将它保持在你一遍又一遍使用的几个长度是个好主意。例如。我对大多数字段使用VARCHAR(255),如街道地址或电子邮件等可能会更长一些,VARCHAR(50)用于电话号码等,而VARCHAR(20)用于短字符串,如邮政编码等。
Be pragmatic about it - find a style that works for you. Don't over-optimize, but don't go nuts the other way, either.
务实 - 找到适合你的风格。不要过度优化,但也不要反其道而行之。
Marc