数据库存储手机号格式思考：

问题：

今天在设计数据库字段时，有一个字段是手机号，如果仅仅是从工作的角度来说的话，肯定是无脑使用VARCHAR，因为系统中其他表都是VARCHAR存放，如果使用bigint会带来业务代码转换的麻烦；但是假设从全新设计一套系统来看，手机号是纯数字的，bigint也是一种选择，究竟该如何选择呢？

思考：

手机号或者座机号+区号都是11位，再考虑充分点，地区编码最多4位，也全都是数字，那么bigint完全有能力对其进行储存；退一步来讲，就算不使用bigint，那CHAR也是可以考虑的，地区编码+手机号完全可以设计为固定长度，有利于提高操作效率；

bigint的优势有：

更小的空间占用；
更高的操作效率（待验证）；
支持更多的操作，比如between and操作；

VARCHAR的优势：

灵活；设计时候可以指定最大长度，存储时，存多少占用多少；

CHAR的优势：

效率比varchar更高；

假设我们将字段内容设计为地区编码（4位）+手机号或者区号座机号（11位）的格式，那么该字段内容固定位15位长度：

空间：

bigint：8字节
VARCHAR：15字节
CHAR:15字节

bigint > varchar = char

效率猜测：

bigint > char > varchar

扩展性：

bigint：最多19位，实际使用15位，还有至少3位扩展空间（最高位如果拿来用时，其他位是有可能溢出的）
varchar：扩展性根据创建时候指定
char：没有扩展性

varchar > bigint > char

推断：

个人认为目前手机号扩展的可能性并不大，11位已经足够满足要求，人口不太可能大规模增长，4位的地区编码能够支撑9999个国家和地区，目前还不到3000个，完全够用，所以扩展性这块我觉得不必考虑太多；如果操作效率猜测得不错的话，那么bigint就是完胜，各方面都很好，3位~4位之间的的预留也足够应付可能性不大的增长带来的位数增加，至于char和varchar，如果在意扩展，用varchar，在意效率用char，个人猜测char和varchar的操作效率应该差距不大，毕竟varchar使用如此之广泛，数据库没理由不对其进行优化；

使用jmeter测试效率：

软硬件环境：

固态硬盘
cpu为intel i7 3740，但限制了主频为1.4GHz；
内存为16G DDR3 1600Mhz；
系统为win10；
docker安装在windows环境下，设置为linux容器；
mysql为docker环境下8.0.20-1debian10；
mysql均为默认参数，没有调优；

apache jmeter jdbc测试方法链接：

https://jmeter.apache.org/usermanual/build-db-test-plan.html

测试步骤：

准备jmeter：

解压jmeter，使用管理员权限运行jmeter.bat；
将mysql-connector-java-5.1.49-bin.jar赋值到jmeter的lib目录下；
创建一个线程组，包含一个线程，循环执行五万次；
线程组中创建一个JDBC链接；
创建JDBC request；
创建一个随机变量范围为 13788880000 ~ 13789889999；
创建一个summary report（listener）观测结果；

准备数据表：

表1字段：

id主键(int)
user_name(varchar(30))
phone_number(bigint)

表2字段：

id主键(int)
user_name(varchar(30))
phone_number(char(11))

表3字段：

id主键(int)
user_name(varchar(30))
phone_number(varchar(15))

测试结果数据（mysql8.0 InnoDB）：

insert（只插入phone_number（id自增长））：

                        响应时间（ms）
            平均          最小          最大
bigint      22          12          228
char        22          12          182
varchar     22          12          175

select（索引覆盖，只select phone_number一列）：

                        响应时间（ms）
            平均          最小          最大
bigint      5           2           383
char        3           2           161
varchar     3           2           153

结论：

从以上的测试数据来看：

varchar与char的表现相差无几，最大响应时间varchar 略优于char，这个我认为可以算是测试误差吧，毕竟其已经远超平均水平了，这个时间最重要的影响因素肯定不是操作本身了；
varchar与bigint对比，不看最大响应时间，在写响应上一样，读响应上，char与varchar居然响应优于bigint，且差距还不小；

测试结果反转了我对操作效率的认知，实际为：

varchar >= char > bigint;

这样一来，char唯一可能的优势（效率）就不复存在了，bigint的优势也只剩下：

空间占用；
支持between and操作；

对于bigint的优势，对其进行分析：

空间占用：对于一个存放4位区号（不太可能，这里取4位为了计算最大的空间占用差距）和11号码的bigint和varchar，一个号码bigint 8Byte，varchar 15Byte，差距7Byte，假设中国是精确的十三亿人口，每人存放一条手机号码信息：差距就是：9100000000Byte，换算一下不到8.5GB，数据冗余备份共3个副本的话，就是25.5GB；节省的空间相当有限，因为其本身占用空间就不大，相信这个空间节省很难打动一个拥有13亿数据量的应用，将手机号存储切换至bigint；

这还是考虑到4位区号的情况下，如果不考虑区号，不计算副本，空间节省将变成不到3.7GB；实在难以让人心动；
支持between and操作这个优势，varchar也能使用正则表达式来实现相同的功能；或许在这一点上，bigint效率会高于varchar？这也许又是另一个话题了，但无论如何，bigint始终没有达到我对插入、查询方面的效率改进的预期；

综上，bigint没有足够多的优势打动我去使用bigint存储手机号，虽然很不甘心，当初次萌生这个想法的时候是相当兴奋的，觉得好像发现了新大陆，但是不得不将使用bigint存放手机号这个想法给打消；或许下次我应该试试使用int来存储手机号；

不存储区号；如果需要区号，分库或者分表的要求是非常合理的；甚至服务器本身都不会在同一个地区；
省略手机号开头的1（国内还没有看到1以外的数字开头的手机号，国外情况未知），这样就只剩下10位了，依然不够，第二位从0到9分库分表，还剩下9位，这对于int来说，能够存储了；

我一直认为对于数字的处理，交给数字类型比较好，所有才会有这样的想法，int对比varchar总该有性能提升吧；当然这样做，也有局限性：

实现起来变麻烦了，不过对于13亿数据量来说本身就应该分表了，一张表200w数据后就可能操作起来显著变慢；可是涉及到所有有手机号的字段都需要建立0～9 10个分表，这实在是代价有点大；也不能varchar和int混用，否则int那点可能存在的效率上的优势会被转换消化掉；
没有扩展性，如果将来手机号演变为2开头，那就麻烦啦；当然按照人口发展规律，可能性不大；但是另一个恐怖的念头展现在我脑海中了，那就是物联网，如果要给每个接入物联网的设备一个唯一号码呢？那int实现的扩展性将是致命的；

分析下来，int或许有些许性能优势，但其带来的复杂度和扩展上的劣势让我根本都不必动手，就打消了念头了；唉，也许这就是为什么大多数公司选择varchar存储手机号的原因吧；

2. 数据库手机号存储格式的思考