The MySQL Supported Types
MySQL has various data types that support different functions. A data type is the type of data a column will
store. There can be many different data types inside a table, but each column will store its own specific type
of information. You can think of a data type as a kind of definition for a column. A column defined as an
integer column will only hold numeric information, whereas a column defined as a CHAR(10) will hold up to
10 alphanumeric characters. These definitions are the key to a quick and efficient database.
There are basically three groups of data formats. The first is obviously numeric. Numeric data is data
that is a positive or negative number such as 4 or -50. Numeric data can also be in hexadecimal format
(2ee250cc), scientific notation (2X10^23), or a decimal. The second type is character or string format.
This format can consist of letters and numbers, whole words, addresses, phone numbers, and generally
anything you have to put quotations around. The final type I like to call miscellaneous. It consists of
everything that doesn't quite fit into either of the other two categories. Some, like dates and times, could
be alphanumeric but are stored like numbers. There are also some more types that fit this description,
and these will be covered in more detail later in this chapter.
As well as data types, MySQL also provides column modifiers. These modifiers further help define a
column's attributes. They are AUTO_INCREMENT, UNSIGNED, PRIMARY KEY, NULL, NOT NULL, and
BINARY. A more detailed discussion of column modifiers takes place following the coverage of the
basic data types.Numeric Types
Numeric types are meant to store numbers only. You cannot put a letter or a string of characters into a
column that is defined as numeric. The numeric type can be broken down further. For example, there are
whole numbers and fractions as well as negative and positive values. Different numeric types take up a
different amount of space in memory. The reason is that each type has a different range.
You may be asking yourself, why all the complexity? A number is a number. Well yes, but you don't
store numbers in memory like a computer does. Suppose you were going shopping for some candy at a
bulk candy store. You know, the kind that stores all of their candy in huge drums. Assume that the
candy costs $1 a pound. It's Halloween time and you want to get about three pounds of candy out of
this drum that probably contains about 50 pounds. You go up to the counter to pay for your candy and
the clerk says that you owe $50 dollars. You're shocked, you only needed 3 pounds which should have
cost $3 dollars, but you're paying for the whole barrel instead. The reason you should only pay for $3
dollars worth of candy is the same reason why there are different ranges of numeric types. You only
have to pay for what you are going to use. If there were no ranges, you would have to use 8 bytes of
storage space every time you used a number because MySQL doesn't know what number you are
going to store. To save on memory (see Table 7.1), there are ranges.
Table 7.1 Numeric Storage
Type Name Memory
Space
TINYINT
1 byte
SMALLINT
2 bytesMEDIUMINT (^) 3 bytes
INT
4 bytes
BIGINT
8 bytes
FLOAT(M,D) (^) 4 bytes
DOUBLE(M,D)
8 bytes
DECIMAL(M,D)
The
value of
M + 2
bytes
The names of the different MySQL types and their subsequent ranges are listed in Table 7.2.
Table 7.2 Numeric Types
Type Name Value Range Unsigned