mysql , bigint 或 decimal 用于存储 >32 位值但小于 64 位 [英] mysql , bigint or decimal for storing > 32 bit values but less than 64 bits

问题描述

我们需要存储最多 2^38 的整数值.有什么理由使用 decimal(12,0) 还是应该使用 bigint ?

We're needing to store integer values of up to 2^38 . Are there any reasons to use decimal(12,0) or should we use bigint ?

推荐答案

在我看来， bigint 会更好.它存储为一个整数，MySQL 将在不需要任何转换的情况下本机理解该整数，因此(我想)在操作上会更快.因此，如果您使用 bigint，您应该期望 MySQL 的效率稍微提高一些.

In my view, bigint would be better. It's stored as an integer that MySQL will understand natively without any conversion required, and will therefore (I imagine) be faster at manipulating. You should therefore expect MySQL to be marginally more efficient if you use bigint.

根据本手册页，您号码的前 9 位数字将存储在一个四字节块中，其余数字(您最多需要 12 个)将存储在一个两字节块中.这意味着您的列每行占用 6 个字节，而 bigint 占用 8 个字节.我建议除非 a) 您将要存储真正令人讨厌的行数，因此占用的空间是一个严重的问题，并且 b) 您将需要很少查询有问题的数据，否则您应该使用 bigint.

According to this manual page, the first 9 digits of your number will be stored in a four-byte block and the remaining digits (you require up to 12) will be stored in a two-byte block. That means your column takes up 6 bytes per row, as opposed to 8 bytes for bigint. I would suggest that unless a) you are going to be storing a truly obscene number of rows, such that the space taken up is a serious concern, and b) you are going to need to query the data in question very little, you should go with bigint.

这篇关于mysql , bigint 或 decimal 用于存储 >32 位值但小于 64 位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！