我用不小心用 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

Sorry LZ, this answer is just as Sunyanzi pointed out, MySQL does not eat the high-order bytes but converts them to the maximum value of Int. Considering that the answer originally submitted took some effort to write, I will still leave it here. It may be helpful to other high-order byte overflow problems.

---

This is a bit interesting. The problem is that int has only 4 bytes, and the mobile phone number is an 11-digit decimal value composed of 5 bytes, so the highest 5th byte after converting to int is "eaten" ”, and then it fell apart.

Solution: Get the lost byte back. According to the current mobile phone number range of 130 0000 0000 to 189 9999 9999, the missing high-order byte may be 0x03 or 0x04. Therefore, adding 0x03 or 0x04, the restored value (Long type) conforms to the mobile phone number range/format, and the original value can be obtained. Remaining issues: It is possible that adding 0x03 and 0x04 both conforms to the mobile phone number range/format, and the result of adding 0x04 is taken (there is no way around it)

Okay, on to the code (Java) code:

/**
 * 按照当前手机号码范围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

There is no other way. The range of int is -2147483648~2147483647, and unsigned int, which is an unsigned integer, is twice the range of 0~4294967295.

If your data exceeds the limit, the computer will automatically calculate it based on maxinteger. To give a simple analogy - the USB flash drive is full and can no longer be inserted, but you didn't notice it at the time and it was useless when you found it later.

Unless you have external records or LOG such as the MYSQL script that saved the mobile phone number at that time, it is impossible to recover from yourself.

Answer 3

Have you opened bin-log?

Answer 4

According to the processing logic in the MySQL source code, if a number is greater than the maximum value (or less than the minimum value) of the field, the maximum value (or minimum value) will be returned during parsing, so it is impossible to analyze it from the number itself restored. But if your MySQL service has LOG enabled, recovery is possible.

The following is MySQL's integer parsing code Field_num::get_int(), from 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

It probably can’t be found, and even if it can be found, there’s no guarantee it’s correct

Answer 6

If the binlog is opened by default, mysql defaults to statement-based binlog, which means that the complete statement is saved and can be restored through binlog

Answer 7

Although Master Po is very tragic, this is a good question and leads to two good answers.