MySQL 将数字存储为 varchar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3008371/
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
storing numbers as varchar
提问by Yeti
Is it ok to store numbers as varchar?
可以将数字存储为 varchar 吗？
What's the difference between
有什么区别
int 123456789012and varchar 123456789012?
int 123456789012和varchar 123456789012？
回答by RedFilter
No, it's almost always a bad idea.
不，这几乎总是一个坏主意。
- will use more space
- indexes will not perform as well
- you can't do arithmetic
- the data is not self-validating because of type
- auto-model generators will give you string type instead of numeric
- aggregates like SUM will no longer work
- the output may sort incorrectly
- you will need to CAST to use it as a number, causing performance hit
- etc.
- 将使用更多空间
- 索引不会表现得那么好
- 你不会算术
- 由于类型，数据不能自我验证
- 自动模型生成器将为您提供字符串类型而不是数字
- 像 SUM 这样的聚合将不再起作用
- 输出可能排序不正确
- 您将需要 CAST 将其用作数字，从而导致性能下降
- 等等。
回答by Juha Syrj?l?
You can store leading zeroes to a varchar that you can't do with integer columns (eg. it is possible to have difference between 123and 0000123). For example zip codes in some countries. However, if you need to do that, then you are really dealing with textual information that should have varchar column. For example, phone numbers or zip codes should definitely go to a varchar column.
您可以将前导零存储到您无法使用整数列执行的 varchar（例如，123和之间可能存在差异0000123）。例如一些国家的邮政编码。但是，如果您需要这样做，那么您实际上是在处理应该具有 varchar 列的文本信息。例如，电话号码或邮政编码绝对应该放在 varchar 列中。
Otherwise if you are using your data like numbers (adding them together, comparing them, etc.) then you should put it into integer column. They consume far less space and are faster to use.
否则，如果您使用数字之类的数据（将它们加在一起，比较它们等），那么您应该将其放入整数列中。它们占用的空间要少得多，使用起来也更快。
回答by theDmi
You will not be able to do calculations with columns declared as varchar, so numeric types should be used. And since your SQL query is a string anyway, MySQL does all the conversion for you (that doesn't mean that you don't need to validate user provided values, of course).
您将无法使用声明为 varchar 的列进行计算，因此应使用数字类型。并且由于您的 SQL 查询无论如何都是字符串，因此 MySQL 会为您完成所有转换（当然，这并不意味着您不需要验证用户提供的值）。
回答by robertokl
I think is ok to store numbers as varchar, as long as you don't want to make calcs with it.
我认为可以将数字存储为 varchar，只要您不想用它进行计算。
For example, a phone number or zip codes would be better to store in varchar fields because you could format them.
例如，电话号码或邮政编码最好存储在 varchar 字段中，因为您可以对其进行格式化。
