TINYINT
-128 to 127 0-255SMALLINT (^) -32768 to 32767 0-65535
MEDIUMINT
-8388608 to 8388607 0-16777215
INT
-2147483648 to
2147483647
0-4294967295
BIGINT
-
9223372036854775808
to
92233720368547758070-
18446744073709550
615
FLOAT(M,D) (^) Varies depending on
values
DOUBLE(M,D) (^) Varies depending on
values
DECIMAL(M,D) (^) Varies depending on
values
If the column is numeric and declared UNSIGNED, the range doubles for the given type. For example, if
you declare a column that is an UNSIGNED TINYINT, the range of this column is from 0 to 255. By
declaring a column as UNSIGNED, you cause that column to have only positive values. The size of the
type you are using, (TINYINT, BIGINT) does not change, only the range of values it can hold.
FLOATs, DOUBLEs, and DECIMALs are numeric types that can hold fractions. The other types cannot.
MySQL gives you the ability to limit the number of digits to the right of the decimal point. For example,
suppose you had a value that was 5.6876. You are going to store it in a column whose type is
FLOAT(4,2). The number would be stored as 5.69. MySQL rounds the decimal to the number of digits
right of the decimal point that is declared in the FLOAT. This same number, declared as a FLOAT(4,3),
would be stored and displayed as 5.688. These numbers are the display widths. The first number is the
number of digits for the whole number and the second is the number of digits for the fraction. The
display values are not required for any numeric data types.
Remember that as a general rule, MySQL processes numeric data faster than other data types. So if
you want quicker queries, use a numeric data type with which to search. Also, numeric indexes are
generally quicker than character-based indexes.
Nothing is more important than choosing the correct type for the right job. When you define your
columns, be sure you take into account the largest possible value you will need. If you don't do this, you
could have some serious problems later, especially if your database is used for critical business
applications. The following are some general rules for choosing the right numeric types:
Use numeric types for primary keys. It makes being unique easy, as well as providing
a faster way of retrieving data,
Use DECIMAL for really large numbers. DECIMALs are stored differently and have not
limits.
Use DECIMAL for currency to retain accuracy.
Use the right data type. Using a number that is larger than needed may lead to
inefficiencies later.
When you are transferring data from one database to another, make sure you pick the right types to
represent the data. A wrong type could spell disaster. MySQL replaces values that are outside the
range with the maximum number for that data type. For example, suppose you had an unsigned
TINYINT, whose max range is 255. Suppose you tried to insert a value that was 1000. MySQL would
store only the value 255. As you can see, this would be devastating on a mission-critical business
application.
The AUTO_INCREMENT, UNSIGNED, and ZEROFILL modifiers can only be used with numeric data
types. They perform operations that can only be done with numbers. You have already learned about
the UNSIGNED modifier (it makes columns positive—no negative numbers), read on to learn about the
other modifiers.
AUTO_INCREMENT
The AUTO_INCREMENT column modifier automatically increases the value of a column by adding 1 to the
current maximum value. It provides a counter that is useful for creating unique values. The value of a newly