When to use the different numeric data types - TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT - MySQL

1
votes

I read the answers given here: What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL? , so I now know how they store the data, but I'm still not sure how to set my database up. For example, if I want a field to be either 0 or 1 (sort of binary, 0 = off, 1 = on), do I use TINYINT with a length of 1?

My main question is, what does the LENGTH setting determine? As each NUMERIC data type already has their own associated data size.

Also, what is the difference between SIGNED and UNSIGNED, and why should I choose one over the other?

3
mysqlnumericunsignedsignedsqldatatypes
Does the length/ size of a field come into play if ZEROFILL has not been used? - Keir Simmons
I've just realised that I have been storing timestamps in INT(99) SIGNED, which can only hold values up to 2147483647, but timestamps are much higher than that - except my timestamps have not been truncated. Why is that? - Keir Simmons

3 Answers

2
votes

Diffrence between SIGNED and UNSIGNED is with UNSIGNED you can store only positive numbers.

For example : about INT (Normal INTEGER) values

The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. If you are using PK auto_increment value then you should use UNSIGNED in this case.

0
votes

For binary fields use BIT.

the length of numerics specifies the precision before and after the comma. See here

0
votes

An integer variable has 32 bits to store the integer value. In signed integer the first bit is reserved to store positive or negative sign. So, a signed integer can use only 31 bits to store a value and hence its range will be −2,147,483,648 to +2,147,483,647. Suppose if your program needs to store only positive integer greater than +2,147,483,647. You need to consider the long integer that will take 8 bits that will cause the wastage of memory. Instead you can go with unsigned integer. In an unsigned integer no bit is reserved for the sign so now you have 32 bits to store the value. The only limitation with an unsigned integer is that you cannot use it to store negative values. The range of an unsigned integer of 32 bits will be 0 to 4,294,967,295. Hope it clears your concept of signed and unsigned integer.