MySQL中的十进制VS Int? [英] Decimal VS Int in MySQL?

问题描述

decimal(10,0) unsigned类型和int(10) unsigned类型之间是否存在性能差异?

Are there any performance difference between decimal(10,0) unsigned type and int(10) unsigned type?

推荐答案

它可能取决于您使用的MySQL版本.参见此处.

It may depend on the version of MySQL you are using. See here.

在MySQL 5.0.3之前，DECIMAL类型存储为字符串，通常比较慢. 但是，由于MySQL 5.0.3的DECIMAL类型是以二进制格式存储的，所以与上面DECIMAL的大小一样，性能可能不会有太大差异.

Prior to MySQL 5.0.3, the DECIMAL type was stored as a string and would typically be slower. However, since MySQL 5.0.3 the DECIMAL type is stored in a binary format so with the size of your DECIMAL above, there may not be much difference in performance.

主要的性能问题是不同类型占用的空间量(DECIMAL较慢).在MySQL 5.0.3+中，这似乎没什么问题，但是，如果您要在查询中对值进行数值计算，则可能会有一些性能差异.这可能值得测试，因为我看不到任何说明文件.

The main performance issue would have been the amount of space taken up by the different types (with DECIMAL being slower). With MySQL 5.0.3+ this appears to be less of an issue, however if you will be performing numeric calculations on the values as part of the query, there may be some performance difference. This may be worth testing as there is no indication in the documentation that i can see.

关于int(10) unsigned，我认为这只是一个4字节的int.但是，最大值为4294967295，严格来说，它不能提供与DECIMAL(10,0) unsigned 相同的数字范围.

With regards to the int(10) unsigned, i took this at face value as just being a 4 byte int. However this has a maximum value of 4294967295 which strictly doesn't provide the same range of numbers as a DECIMAL(10,0) unsigned .

正如@Unreason指出的那样，您需要使用bigint来覆盖10位数字的全部范围，将大小推升到8个字节.

As @Unreason pointed out, you would need to use a bigint to cover the full range of 10 digit numbers, pushing the size up to 8 bytes.

一个常见的错误是，在MySQL中指定数字列类型时，人们经常认为括号中的数字会影响他们可以存储的数字的大小.没有.数字范围纯粹基于列类型以及它是带符号的还是无符号的.括号中的数字是结果显示的目的，对列中存储的值没有影响.除非您也在列上指定ZEROFILL选项，否则它也不会影响结果的显示.

A common mistake is that when specifying numeric columns types in MySQL, people often think the number in the brackets has an impact on the size of the number they can store. It doesn't. The number range is purely based on the column type and whether it is signed or unsigned. The number in the brackets is for display purposes in results and has no impact on the values stored in the column. It will also have no impact of the display of the results unless you specify the ZEROFILL option on the column as well.

这篇关于MySQL中的十进制VS Int?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！