MySQL 存储在mysql整数字段中时，允许数字以零开头
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2786193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Allow number to start with ZERO when stored in mysql integer field
提问by M.E
I need to store phone numbers starting with 0 but whenever i try to store this in MySql table the starting ZERO is removed because no number start with Zero actually.
我需要存储以 0 开头的电话号码，但是每当我尝试将其存储在 MySql 表中时，起始零都会被删除，因为实际上没有数字以零开头。
How to solve this issue? Do I need to change the field type from Integer to another type?
如何解决这个问题？我是否需要将字段类型从 Integer 更改为另一种类型？
回答by deepcell
change data type to unsigned-zerofillwhatever you are using, float, int, decimal(6,2)... only edit the field to unsigned-zerofill
将数据类型更改为unsigned-zerofill，无论您使用的是什么，float、int、decimal(6,2)... 仅将字段编辑为unsigned-zerofill
回答by Peter Bailey
Phone numbers are not really numbers in the sense that they aren't ordinal. They're just characters - they fact that they are numbers is incidental.
电话号码并不是真正的数字，因为它们不是有序的。它们只是字符——它们是数字的事实是偶然的。
Store them in a varchar and move on :D
将它们存储在 varchar 中并继续：D
回答by Andrew McGregor
Phone numbers can contain other symbols for readability too... a regexp for a phone number looks something like [0-9+-()*#]+. So you need to use a text field for phone numbers, plus some validation.
电话号码也可以包含其他符号以提高可读性......电话号码的正则表达式看起来像[0-9+-()*#]+. 因此，您需要为电话号码使用文本字段，并进行一些验证。
回答by Karthik
You can use data type as varchar to solve this.
您可以使用数据类型作为 varchar 来解决这个问题。
回答by myforwik
Phone numbers aren't integers and you will only end up with problems trying to store them as integers, store them as strings instead.
电话号码不是整数，您只会在尝试将它们存储为整数时遇到问题，而将它们存储为字符串。
回答by DVK
Yes - numeric fields only store the numeric values, not the formatting of those (which paddin with leading zeroes is). You should either
是的 - 数字字段只存储数值，而不是那些的格式（带前导零的填充是）。你应该
change the field type from integer to varchar or char (if # of digits is ALWAYS the same).
Store the number as integer BUT prepend 0 in your presentation layer as needed.
将字段类型从整数更改为 varchar 或 char（如果数字的数量始终相同）。
将数字存储为整数，但根据需要在表示层中添加 0。
回答by brunobliss
You can also wrap the number you want with a lead zero with a function. I made this function to add lead zero if the "string" is smaller than 2 digits (it was used to add lead zeroes to hours and minutes)
您还可以使用函数用前导零包裹所需的数字。如果“字符串”小于 2 位，我做了这个函数来添加前导零（它用于将前导零添加到小时和分钟）
function leadZero($num) {
if (strlen($num) < 2) {
return "0" . $num;
} else {
return $num;
}
}
If you have say, a number 2 that you want to output as 02, you'd do leadZero(2);
如果你说，你想输出为 02 的数字 2，你会做 LeadZero(2);
This will only add a zero IF the number is less than 2 digits long ! For instance leadZero(14); will return 14
如果数字长度小于 2 位，这只会添加一个零！例如leadZero(14); 将返回 14
