MySQL Data Types
Selecting the proper column type for your tables is key to a successful database. Tables B.7, B.8, and B.9 define the different string, number, and other types you can use, along with how much space they will take up on the server's hard drive. When choosing a type for each column, you should use the most efficient (i.e., the most size-frugal) data type in terms of the largest possible value for the column.
Table B.7. Here are most of the available numeric column types for use with MySQL databases. For FLOAT, DOUBLE, and DECIMAL, the Length argument is the maximum total number of digits, and the Decimals argument dictates the number of that total to be found after the decimal point. (As of MySQL 5.0.3, the size of DECIMAL column is based upon a formula.)
MySQL Numeric Types
Range of 128 to 127 or 0 to 255 unsigned.
Range of 32,768 to 32,767 or 0 to 65535 unsigned.
Range of 8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
Range of 2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
Range of 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.
A small number with a floating decimal point.
A large number with a floating decimal point.
Length + 1 or Length + 2 bytes
A DOUBLE with a fixed decimal point.
Table B.8. Here are the most common column types for storing text in a MySQL database.
MySQL Text Types
A fixed-length field from 0 to 255 characters long.
String length + 1 or 2 bytes
A fixed-length field from 0 to 255 characters long (65,535 characters long as of MySQL 5.0.3).
String length + 1 bytes
A string with a maximum length of 255 characters.
String length + 2 bytes
A string with a maximum length of 65,535 characters.
String length + 3 bytes
A string with a maximum length of 16,777,215 characters.
String length + 4 bytes
A string with a maximum length of 4,294,967,295 characters.
Similar to CHAR but stores binary data.
Data length + 1 bytes
Similar to VARCHAR but stores binary data.
Data length + 1 bytes
Stores binary data with a maximum length of 255 bytes.
Data length + 2 bytes
Stores binary data with a maximum length of 65,535 bytes.
Data length + 3 bytes
Stores binary data with a maximum length of 16,777,215 bytes.
Data length + 4 bytes
Stores binary data with a maximum length of 4,294,967,295 bytes.
1 or 2 bytes
Short for enumeration, which means that each column can have one of several possible values.
1, 2, 3, 4, or 8 bytes
Like ENUM except that each column can have more than one of several possible values.
Table B.9. These are the available date and time column types for MySQL.
MySQL Date and Time Types
In the format of YYYY-MM-DD
In the format of YYYY-MM-DD HH:MM:SS
In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037
In the format of HH:MM:SS
In the format of YYYY, with a range from 1901 to 2155
Further, I should mention that MEDIUMINT, SET, ENUM, as well as the different-sized BLOB and TEXT column types are all MySQL-specific extensions of the SQL defaults. Finally, when it comes to defining columns, remember that any column type can be NULL or NOT NULL, integers can be UNSIGNED, and any number can be ZEROFILL. A column can also be defined as having a DEFAULT value, should a value not otherwise be supplied for it.