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
Type	Size	Description
TINYINT[Length]	1 byte	Range of 128 to 127 or 0 to 255 unsigned.
SMALLINT[Length]	2 bytes	Range of 32,768 to 32,767 or 0 to 65535 unsigned.
MEDIUMINT[Length]	3 bytes	Range of 8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
INT[Length]	4 bytes	Range of 2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
BIGINT[Length]	8 bytes	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.
FLOAT[Length, Decimals]	4 bytes	A small number with a floating decimal point.
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point.
DECIMAL[Length, Decimals]	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
Type	Size	Description
CHAR[Length]	Length bytes	A fixed-length field from 0 to 255 characters long.
VARCHAR(Length)	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).
TINYTEXT	String length + 1 bytes	A string with a maximum length of 255 characters.
TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters.
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters.
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters.
BINARY[Length]	Length bytes	Similar to CHAR but stores binary data.
VARBINARY[Length]	Data length + 1 bytes	Similar to VARCHAR but stores binary data.
TINYBLOB	Data length + 1 bytes	Stores binary data with a maximum length of 255 bytes.
BLOB	Data length + 2 bytes	Stores binary data with a maximum length of 65,535 bytes.
MEDIUMBLOB	Data length + 3 bytes	Stores binary data with a maximum length of 16,777,215 bytes.
LONGBLOB	Data length + 4 bytes	Stores binary data with a maximum length of 4,294,967,295 bytes.
ENUM	1 or 2 bytes	Short for enumeration, which means that each column can have one of several possible values.
SET	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
Type	Size	Description
DATE	3 bytes	In the format of YYYY-MM-DD
DATETIME	8 bytes	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037
TIME	3 bytes	In the format of HH:MM:SS
YEAR	1 byte	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.