What kind of integer should I use in MySQL?

I'm creating the database schema for a system and I started to wonder something about the Integer datatypes in MySQL. I've seen that, at least in Moodle, the datatypes are sometimes TINYINT (for stuff like flags), INT (for id numbers) or BIGINT (for almost-infinite AI values, like user id's).

开发者_如何学JAVAI was wondering: how does that affect the actual database? If I use INT for something like a flag (e.g 1 = pending, 2 = in process, 3 = revewing, 4 = processed) instead of TINYINT or BIGINT, does it has repercussions? What about not setting up constraints? (Like, again, with a flag, using TINYINT(1) or TINYINT without an specific number)

The size that you provide will not affect how data is stored.

So INT(11) is stored on disk the same way as INT(3).

With your example of 1 = pending, 2 = in process, 3 = revewing, 4 = processed

I would use an ENUM('pending', 'inprocess', 'revewing', 'processed') instead. That keeps it readable in your code and in the data, while it provides the same speed as using an integer.

What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

You should read about the different numeric datatypes and make your decision based on that.