在mysql DB中存储大量数字的问题

本文介绍了在mysql DB中存储大量数字的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用 mysql db 来保存 ID.它工作正常.

I am using mysql db to save id's. It was working fine.

但是现在像这样的 id 10000000754987.存储为 1.0000000754987E+14.我该如何解决.

But now an id like this 10000000754987. Is stored as 1.0000000754987E+14. How I can I fix it.

该字段设置为 varchar 255 个字符限制.

The field is set to varchar 255 characters limit.

感谢您的帮助.

谢谢.

推荐答案

您显然是在使用 PHP 来生成该 ID.

You're apparently using PHP to generate that ID.

由于您没有提到具体发生了什么，我只能假设您的数据库设计的某些原因.

Since you didn't mention what's exactly happening, I can only assume certain reasons for your database design.

首先，您可以将该数字存储为 bigint 而不是 varchar.这是您要保存的整数，我认为您没有理由使用 varchar 并且本质上浪费了比需要更多的空间.bigint 使用 8 个字节来存储一个数字.这意味着每个存储在 varchar 字段中的数字超过 8 位的数字将比可以存储高达 2^64 的数字的 bigint 字段使用更多的空间.

First off, you could store that number as bigint and not varchar. It's an integer you're saving, I see no reason why you'd use varchar and inherently waste more space than needed. bigint uses 8 bytes to store a number. That means that every number with more than 8 digits stored in varchar field would use more space than a bigint field that can store numbers up to 2^64.

其次，在将计算操作的结果发送到数据库之前，请确保您没有使用任何数字格式.我复制/粘贴了您发布的整数 (10000000754987) 并且 php 不会自动将其转换为科学记数法，所以我的猜测是您在该数字生成的背景中还有其他事情 - 如果可能，将其关闭并存储适当字段类型 (bigint) 中的数字.如果您发布有关您的应用及其功能的其他信息，这也会很有用，因为错误并不总是人们所关注的.

Second, make sure you don't use any number formatting before sending the result of your calculation operation to the db. I copy/pasted the integer you posted (10000000754987) and php doesn't automatically convert it to scientific notation so my guess is that you have something else going on there in the background with that number generation - turn it off if possible and store the number in appropriate field type (bigint). It'd also be useful if you posted additional info about your app and what it does, because error isn't always where people thing it is.