[Resolved] Any Difference Between Integer Fields?

partypete

Hello,

Just a quick MySQL question. Let's say I want to store a number up to 7 digits in length, ie: 9000000 (9 million). I could store this number in several Integer fields and apply limits to the fields to only allow upto 7 digits to be inserted. My question is, when applying limits to the fields so they can only hold so much, is there any advantage/disadvantage to using one field type over another?

For example, is there any difference between:

MedInteger - Limit 7
Integer - Limit 7

The way I see it, Integer can hold much more than MedInteger, but with a Limit applied they would both take up the same amount of space in the database correct?

Right now I'm designing a database with Small, Medium, and regular Integer fields. If there's no effect on the database or wasted space, I'd rather use only "Integer" fields with different Limits.

Thanks for any help or advice!

Peter

Lars_Berg

You have misunderstood this.

int(7) does not mean that you can only insert values with a maximum of seven digits. It rather signifies display length.

An int always uses 4 byte for storage which means that you can store values from -2³² to 2³² - 1. You can not get Mysql to reject values larger than 7 digits, you'll have to check that yourself.

A mediumint uses three bytes -2²⁴ - 2²⁴ - 1

partypete

You have misunderstood this.

int(7) does not mean that you can only insert values with a maximum of seven digits. It rather signifies display length.

I guess I have! Geez😕 . Is this the same for all MySQL column types? If I'm controlling the display of information in my php page itself, then there really is no need to define the "display length" in MySQL correct? As long as I use a column type that can hold the info I want to store, then I don't have to worry about setting the display length? Damn, I've always set it as if it were a limit, wasted quite a bit of time I'd say...:rolleyes:

An int always uses 4 byte for storage which means that you can store values from -2³² to 2³² - 1. You can not get Mysql to reject values larger than 7 digits, you'll have to check that yourself.

Thankfully I've always checked the content for length in the php page, not at the database so I've never ran into trouble.

So an Int always uses 4 bytes? So if one Int field holds only the number "5" and another Int field holds "4294967295", this takes up the exact same amount of space in the database????

Thanks for your help so far. Clearing up this misunderstanding of mine is going to make a big difference.

Peter

partypete

Actually I found the page in the MySQL documentation that talks about storage size for each column type so I understand the differences between column types now.

But thanks again for clearing up that display length misunderstanding! I was soooooo using that wrong.

Thanks