CODEWITHSUNDEEP
mysql
michael
michael

Reputation: 41

mysql insert/update converts a number

I have two mysql columns both int unsigned zerofill. The first 5 in length, second 11 in length. First value takes any 5 digit number no problem. The second, no matter what converts any 11 digit number into 04294967295. Any clue on what I can do to solve this puzzle?

Upvotes: 1

Views: 119

Answers (3)

itz2k13
itz2k13

Reputation: 159

Range of UNSIGNED INT is 0 to 4294967295. so Any value above 4294967295, it will insert that max value, as that is the maximum value possible for unsigned INT.

Upvotes: 0

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

The length you give to an integer field is just for displaying. If you pass a length 11, a select commando on that column will display a number of 11 digits (which is the length of the number you got). The actual size of the field is determined by the type you chose for the column (int). int-fields are capable of storing 232 different values. For an unsigned field having 0 as its first value, this results in a maximum value of 232 - 1, which matches the number in your output. Since any 11 digit number you input will be bigger than this number, the value saved will be 232 - 1.

You can overcome this limitation by using the BIGINT-type, which allows you to store any number up to 264.

Upvotes: 0

ceejayoz
ceejayoz

Reputation: 180004

Your number is larger than the integer field can handle - 232 - 1.

Change the column to an unsigned BIGINT and you'll be good up to 18,446,744,073,709,551,615.

Upvotes: 2

Related Questions