MySQL Corrupted Insert

Question

I try to insert an entry into my db, but i get different result on select query.

INSERT INTO  `mobilpay_sms_log` (
        `id` ,
        `messageid` ,
        `user` ,
        `sms_key` ,
        `sender` ,
        `final_value` ,
        `brute_value` ,
        `message` ,
        `code` ,
        `status` ,
        `time`
        )
        VALUES (
        NULL ,  '20130814195225:0721254789',  '0',  '10752000000',  '0721547896',  '10.00',  '10.00',  'LICENTA 111',  '8A5SSVPQ',  'FINISHED', 
        '20130814195225'
        );

After Select i get strange value from "sms_key" field, "2147483647". sms_key structure is int(20)

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

This is because int cannot store that big numbers. You will need to store it in long int or just varchar. Here is more info about integer sizes in MySQL:

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

The number in your column specification (20) restricts only count of characters that can be stored in your column.

Additionally, I would be very careful with storing phone numbers as integers. More explanation why:

What's the right way to represent phone numbers?

I believe you could have such problems in your sender column.

edited May 23 '17 at 11:56

Community

1
1

answered Aug 17 '13 at 19:57

Witold Sosnowski

506
2
9

1

One of my biggest peeves when I started using MySql that, my personal favourite was BigInt(2), I saw in someone's schema. – Tony Hopkinson Aug 17 '13 at 20:07
I don't think it's a phone number, but the point stands. Unless you do arithmetics with the number you can just use varchar. – JJJ Aug 17 '13 at 20:13

MySQL Corrupted Insert

1 Answers1