CODEWITHSUNDEEP
mysql
Martin Brody
Martin Brody

Reputation: 233

How many characters does an integer (rendered as a string) consume in MySQL?

I have been told that the maximum value for an integer in mysql is approximately 4 billion but this seems to use 10 characters. However when I specify the number of characters for integer I use 11 characters. What happens to the extra character?

Upvotes: 1

Views: 19859

Answers (4)

Andrej
Andrej

Reputation: 7504

It depends on mode which you use in mysql ans transactional/non-transactional tables. By default mysql will truncate number to the closest. If strict mode so you query will fail. See more info http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 81988

It's 11 characters. The extra digit is for the - in negative numbers. Don't believe me, you can count here.

Edit

I wrote that answer before lunch hour, and it has bothered me the whole time that I didn't include anything on most significant bit, because it really is very relevant in this case. So, I'm including Wikipedia's treatment because I don't feel like re-writing the wheel.

Upvotes: 2

Holger Just
Holger Just

Reputation: 55718

An integer is saved as a 32 bit number. That means, it can store 2^32 = 4,294,967,296 different values. Now, if you chose to use a singed integer (which you do by default), you will be able to represent numbers in the interval of [-2147483648, 2147483647] in that 32 bits.

If you now convert the lowest possible number to a string, you will notice it has 11 chars including the minus.

Generally, you should try to work with numbers as numbers instead of strings. Although both might look similar, they is a major difference between a string containing only numeric chars and an actual numeric value.

Upvotes: 2

swordfish
swordfish

Reputation: 4995

It is actually 11 characters. Just in case if you exceed the max limit not just for integer but for any fields the values will be truncated.

Upvotes: 0

Related Questions