值作为2147483647插入到数据库中 [英] Value being inserted as 2147483647 into database

问题描述

我尝试输入数据库的所有电话号码都插入 2147483647 。

All phone numbers I am trying to enter into my database are being inserted as 2147483647.

数据库field是整数（11）。

在插入电话号码之前，它会通过以下代码进入要删除所有空格，短划线和括号：

Before the phone number is inserted, it goes through the following code in order to remove all spaces, dashes, and brackets:

if (!empty($hphone)) $phone = $hphone;
else if (!empty($HomePhone)) $phone = $HomePhone;
else if (!empty($Phone1)) $phone = $Phone1;
$phone = preg_replace("/[^0-9]/", "", $phone);

为什么插入电话号码 2147483647 每次，无论电话号码是多少？

Why is it inserting the phone number as 2147483647 every time, no matter what the phone number is?

推荐答案

如果可以，请将电话号码转换为VARCHAR字段，不要将它存储为有符号（或无符号）数值（int，bigint，double等等）。

If you can, convert the phone number to a VARCHAR field, don't store it as a signed (or unsigned) numeric value (int, bigint, double, etc...).

在这种情况下，MySQL的2147483647中签名INT的限制是造成问题的原因。插入此字段的任何较大的内容都将限制在该最大值。

In this case, the limit for signed INT in MySQL of 2147483647 is what is causing your issue. Anything larger inserted into this field will be capped at that maximum value.

例如电话号码555-555-5555如果大于该限制（5555555555> 2147483647），因此存储它将导致最大值2147483647.。

For example the phone number 555-555-5555 if bigger than that limit (5555555555 >2147483647), as such storing it would result in the max value 2147483647.

我还建议不要将其存储为BIG INT或任何其他数字类型。你将如何处理扩展名或特殊编码字符，如：

I also recommend not storing it as a BIG INT or any other numeric type. How will you handle extension or special encoded characters like:

   +02 112020 10020  
   1-333-232-9393 x203

BTW：不知道第一个是真正的非美国数字格式，但你得到这个想法

BTW: don't know if the first is real non-US number format, but you get the idea

此外，相关领先0的电话号码也会丢失一些，无论数量有多大：

Also, phone numbers with relevant leading 0's would be have some of it lost no mater how large the number:

 021-392-9293

将是编号213929293

Would be the number 213929293

这篇关于值作为2147483647插入到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！