我用不小心用 mysql 的int(11) 存了 手机号，数据都有问题，有办法恢复么？

Question

我用不小心用 mysql 的int(11) 存了用户的手机号，结果里面存的数据都是 10 位的，而且也不是单纯的被截断了一位， 比如手机号 [phone] 会被转成 4294967295 有办法恢复么，急。。。。。

问题补充： 1.数据库的存的手机号是类似这样的 511129633 437709550 947221024 1544096837 2770221786 3052396450 985251741 2147791994 1663290693 3067028521 842826454 2382976437 1811997122 2128974539 694514931 1816715878 876431887 737421250 1107794384 847325325

2.我问了下运维，是开了binlog 的，然后我跟他们要了一份。把里面所有的用户填手机号的sql的都找了出来，但是神奇的是sql里的手机号跟数据库里的是一样的，难道binlog里的sql是溢出之后的？

Answer 1

对不起LZ了，这个答案正像Sunyanzi指出的，MySQL不是把高位字节吃掉而是转成了Int的最大值。 考虑到原先提交的答案还是花了点心思写的，就还留在这里了，也许对其他高位字节溢出的问题有所帮助。

---

这个有点意思，问题出在int只有4个字节，而手机号码是11位的十进制值由5个字节组成，所以转成int后最高位的第5个字节被“吃掉了”，然后就杯具了。

解决思路： 把丢失的那个字节找回来。 按照当前手机号码范围130 0000 0000到189 9999 9999经分析，丢失的高位字节可能是0x03或者0x04。 因此加上0x03或者0x04恢复后的值（Long长整型）符合手机号码范围/格式，就可以得到原始值了。 遗留问题： 有可能出现加0x03和0x04都符合手机号码范围/格式的情况，取加0x04的结果（没法子的事情）

好了，上代码（Java）代码：

/**
 * 按照当前手机号码范围130 0000 0000到189 9999 9999经分析，丢失的高位字节可能是0x03或者0x04。
 * 因此加上0x03或者0x04恢复后的值（Long长整型）符合手机号码范围/格式，就可以得到原始值了。
 * 有可能出现加0x03和0x04都符合手机号码范围/格式的情况，取加0x04的结果（没法子的事情）
 * 
 * @param original 溢出前的原始11位手机号码
 * @return 转int之后，再重新恢复得到的11位手机号码
 */
public static long recover(long original) {
    Pattern p = Pattern.compile("1[3,4,5,8]\\d{9}");
    // 更精确的手机号段，但可能不是最新的，这里先不使用。参考： http://wenku.baidu.com/view/9d088df30242a8956bece435.html
    // Pattern.compile("(133|153|180|181|189|134|135|136|137|138|139|150|151|152|157|158|159|182|183|187|188|130|131|132|155|156|185|186|145|147)\\d{8}");
    int errorInt = (int) original;
    System.out.println("溢出前的long值:" + original);
    System.out.println("溢出后的int值:" + errorInt);
    System.out.println("溢出前的16进制值:" + Long.toHexString(original));

    String hexA = "000000000000" + Long.toHexString(errorInt);
    hexA = hexA.substring(hexA.length() - 8);
    System.out.println("溢出后的16进制值（左补0）:" + Long.toHexString(errorInt));

    String hex1 = "4" + hexA;

    System.out.println("补全后的16进制值1：" + hex1);
    BigInteger bi1 = new BigInteger(hex1, 16);
    long rt1 = bi1.longValue();
    System.out.println("补全后的Long值:" + rt1);

    String hex2 = "3" + hexA;
    System.out.println("补全后的16进制值2：" + hex2);
    BigInteger bi2 = new BigInteger(hex2, 16);
    long rt2 = bi2.longValue();
    System.out.println("补全后的Long值2:" + rt2);

    final boolean m1 = p.matcher(String.valueOf(rt1)).matches();
    final boolean m2 = p.matcher(String.valueOf(rt2)).matches();

    long rt = 0;
    if (m1 && m2) {
        // 加3加4都符合手机号码格式
        System.err.println("加3加4都符合手机号码格式的溢出后int值:" + errorInt + ". 2个可能的恢复值为： " + rt1 + ", " + rt2);

        //有可能出现加0x03和0x04都符合手机号码范围/格式的情况，取加0x04的结果（没法子的事情）
        rt = rt1;
    } else {
        if (m1) {
            rt = rt1;
        }
        if (m2) {
            rt = rt2;
        }
    }
    System.out.println("恢复后的符合手机号码格式的值:" + rt + "\n\n");
    return rt;
}

Answer 2

沒辦法了，int的範圍是-2147483648~2147483647，而unsigned int也就是無符號整形的就是其兩倍0~4294967295。

你的數據超出了，計算機就自動按maxinteger來算了。舉個簡單點的比喻——U盤塞滿了，再塞不進了，但是你當時沒發現，到後來發現的時候已經沒用了。

除非你有當時存手機號的MYSQL腳本這類外界的記錄或者是LOG，想從本身恢復是不可能了。

Answer 3

有开bin-log吗？

Answer 4

根据MySQL源码里的处理逻辑，如果某个数字大于该字段的最大值（或小于最小值），则解析的时候返回的是最大值（或最小值），所以仅从数字本身是无法恢复的。但是如果你的MySQL服务开启了LOG，那么就有可能恢复。

以下是MySQL的整数解析代码Field_num::get_int()，来自 sql/field.cc +1130

/*
  Conver a string to an integer then check bounds.

  SYNOPSIS
    Field_num::get_int
    cs            Character set
    from          String to convert
    len           Length of the string
    rnd           OUT longlong value
    unsigned_max  max unsigned value
    signed_min    min signed value
    signed_max    max signed value

  DESCRIPTION
    The function calls strntoull10rnd() to get an integer value then
    check bounds and errors returned. In case of any error a warning
    is raised.

  RETURN
    0   ok
    1   error
*/

bool Field_num::get_int(CHARSET_INFO *cs, const char *from, uint len,
                        longlong *rnd, ulonglong unsigned_max, 
                        longlong signed_min, longlong signed_max)
{
  char *end;
  int error;

  *rnd= (longlong) cs->cset->strntoull10rnd(cs, from, len,
                                            unsigned_flag, &end,
                                            &error);
  if (unsigned_flag)
  {

    if (((ulonglong) *rnd > unsigned_max) && (*rnd= (longlong) unsigned_max) ||
        error == MY_ERRNO_ERANGE)
    {
      goto out_of_range;
    }
  }
  else
  {
    if (*rnd < signed_min)
    {
      *rnd= signed_min;
      goto out_of_range;
    }
    else if (*rnd > signed_max)
    {
      *rnd= signed_max;
      goto out_of_range;
    }
  }
  if (table->in_use->count_cuted_fields &&
      check_int(cs, from, len, end, error))
    return 1;
  return 0;

out_of_range:
  set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, 1);
  return 1;
}

Answer 5

应该找不回来，就算能找回也不能保证是正确的

Answer 6

如果是默认开的binlog,mysql默认是基于语句的binlog 也就是保存了完整的语句,可以通过binlog恢复

Answer 7

虽然 Po 主很悲剧，但是这是一个好问题，也引出了两个好答案。