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

Question

我用不小心用 mysql 的int(11) 存了用户的手机号，结果里面存的数据都是 10 位的，而且也不是单纯的被截断了一位， 比如手机号 18345231102 会被转成 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 主很悲劇，但是這是一個好問題，也引出了兩個好答案。