CODEWITHSUNDEEP
mysqlsqldatabasedrupalphpmyadmin
Saahir Foux
Saahir Foux

Reputation: 674

INT Column value scrambled beyond 10 characters

I'm creating a table with column of INT(20). Whenever I submit a value greater than 10 characters, the value is scrambled. Anything 10 or less characters as the value is stored properly, and I'm not sure why. I've set the length to 20, yet it seems to be ignoring this.

[Char length +10]
Ex: ( 1234567890123 ---becomes---> 2147483647 )

[Char length <= 10]
Ex: ( 1234567890 ---remains---> 1234567890 )

I'm thinking I may need to changed the structure of the table somehow, but so far nothing has worked. I'd appreciate any helpful words of wisdom to get my numeric values greater than 10 stored properly in my table.

Thanks.

Note: I'm using MySQL, PhpMyAdmin, Drupal

Upvotes: 2

Views: 361

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Actually, you should be using DECIMAL rather than INT or BIGINT. This is the data type in SQL designed to hold numbers with specified precision, which is what you seem to have.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838156

It's overflowing.

The maximum value of an INT is 2147483647. You should use a BIGINT instead of INT.

                 size    min                         max
INT              4       -2147483648                 2147483647
UNSIGNED INT     4       0                           4294967295
BIGINT           8       -9223372036854775808        9223372036854775807
UNSIGNED BIGINT  8       0                           18446744073709551615

See the documentation:

Upvotes: 4

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324630

INT(20) just means that 20 numbers may be displayed. This acts as a cut-off if the number is bigger, or a pad size when ZEROFILL is specified.

Since the biggest integer that can be stored is 232-1 (assuming UNSIGNED), and you are exceeding that value, you get "scrambled" numbers.

To solve this issue, use BIGINT, which allows you to go up to 264-1, or FLOAT to get the potential for humongous numbers at the cost of some precision.

Upvotes: 1

Related Questions