1

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).

I 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)

AeroCross
  • 3,969
  • 3
  • 23
  • 30

3 Answers3

4

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.

Wouter van Nifterick
  • 23,603
  • 7
  • 78
  • 122
  • Oh, that's an excellent tip! But, for example, if I'm gonna store only data that is based on a 10 digit number, is using INT(10) better than using INT? Does it affect performance / data in any way? – AeroCross Mar 04 '11 at 05:19
  • 1
    `TINYINT(5)` and `INT(5)` are different on disk (1 byte vs 4 bytes), while `INT(1)` and `INT(2)` are the same (both 4 bytes). – Wouter van Nifterick Mar 04 '11 at 18:53
3

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

Community
  • 1
  • 1
Prisoner
  • 27,391
  • 11
  • 73
  • 102
  • what's the difference between tinyint and tinyint unsigned, mediumint and mediumint unsigned... – Jon Black Mar 04 '11 at 05:18
  • In the 2nd answer of this post there's the answer of my other question - basically, it stores the same data and it's accessed the same way (for what I can deduce), but it takes more data (e.g the number 4 stored in TINYINT takes up 1 byte, and the number 4 stored in BIGINT takes up 8 bytes). Thanks! – AeroCross Mar 04 '11 at 05:26
2

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

Andrew Marshall
  • 95,083
  • 20
  • 220
  • 214
  • Important point in the MySQL Reference Manual - I didn't knew until now that the appended number to the datatype is **not** a constraint. – AeroCross Mar 04 '11 at 05:28