是好的存储十进制值作为varchar在mysql？ [英] is it good to store decimal value as varchar in mysql?

问题描述

我必须存储用户信用，可以是十进制格式，如（10.99，0.99，等）。现在我把列字段作为varchar（100）。

I have to store user credits which can be in decimal format like (10.99, 0.99, etc.). For now i am taking the column field as varchar(100).

如果我使用decimal而不是varchar。将会改善性能。

what if i use decimal instead of varchar. will it be improving the performance??

推荐答案

简单的答案是：不会，会伤害性能。

The short answer is: No, it will hurt performance.

更长的答案：VARCHAR字段是可变长度，意味着数据库块的格式化不能预先考虑填充在那里的数据的大小。当然，有一个字段的最大长度，但除此之外，你更糟。

The longer answer: VARCHAR fields are variable length, meaning, that the formatting of the database blocks cannot pre-account for the size of the data filling in there. Sure, there's a maximum length of the field, but other than that, you're off worse.

另一方面，读取一个VARCHAR，然后解释它适合进入浮点类型会花费你。它引入了新的问题可能性。尝试获取占11.99的所有小数。希望你带来正确的小数位数，因为在VARCHAR11.99！=11.990。

On the other hand, reading a VARCHAR and then interpreting it to fit into a floating point type will cost you. And it introduces new problem possibilities. Try to get all decimals that account for "11.99". Hope you bring the right number of decimal places, because in VARCHAR "11.99" != "11.990".

然后有索引。通常情况下，没有索引一个十进制字段，但是如果不这样做，VARCHAR字段上的索引将需要更多的空间。

And then there's indexing. Usually, nobody indexed a decimal field, but on the off chance that you do, an index on a VARCHAR field will require more space.

总而言之，它不是好主意保存在非特定字段类型的东西。在数据库结构中，通常认为最好的做法是尽可能具体。

All in all, it's not a good idea to save something in an unspecific field type. In the DB structure, it's usually considered best practice to be as specific as possible.

这篇关于是好的存储十进制值作为varchar在mysql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！