Data Types
Name: Jesse Jimenez
Term: Winter 2012
Data Types
Tables can hold sorts of data, from numbers, to text, to dates and times, to binary image data. As a result, it is a good idea to specify data types to reflect the content each field of a table reflects. By declaring data types, the MySQL tables will run more efficiently and will help you maintain the integrity of the data.
Type |
Size |
Description |
CHAR [Length] |
Length bytes |
A fixed-length field from 0 to 255 characters long. This means it fills in the rest of the characters if none are provided. |
VARCHAR(Length) |
String length + 1 bytes |
A variable-length field from 0 to 255 characters long. |
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. |
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 |
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 stored as a string, allowing for a fixed decimal point. |
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 inthe year 2037. |
TIME |
3 bytes |
In the format of HH:MM:SS |
ENUM |
1 or 2 bytes |
Short for enumeration, which means that each column can haveone of several possible values. |
SET |
1, 2, 3, 4, or 8 bytes |
Like ENUM except that each column can have more than one ofseveral possible values. |
Data Size
the data size can be specified in some instances to increase performance. If you know that the field only needs 20 charachters, (i.e. a password) than using all 0-255 of the varchar would not be wise. By setting the size to 20 characters, the table can be optimized to take up less space, and therefore improve queries.