MySQL Column Types

Teaches you about the different kinds of column types and how to use them.

Introduction

When you create a table to hold information you will need to figure out what information, you need to store and what format you need to store it in. In general, there are 3 types of data: text (sometimes called "strings"), numbers, and dates. Each type has different subtypes and you'll have to choose the subtype that gives you the best performance. Some fields will have a length attribute which tells you how many characters can be stored in the column. The length attribute will be set inside of [brackets] after the column type name. If you have a limit set on the field length and try to enter or import more than the limit then the extra will just get cut off.

Column Types - Text

Type	Description
CHAR[Length]	A fixed-length field from 0 to 255 characters long.
VARCHAR[Length]	A variable-length field from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65,535 characters.
MEDIUMETXT	A string with a maximum length of 16,677,215 characters.
LONGTEXT	A string with a maximum length of 4,294,967,295 characters.

When choosing a field to store text you will probably look at VARCHAR, and TEXT. Most of time you will choose a VARCHAR field with a set length. The maximum field length for this type of field is 255. This means it will store a text string that has at most 255 characters. If you are wondering why you would choose a VARCHAR column type indtead of a CHAR type it is because VARCHAR columns take up less space than a CHAR field of the same length. This is because a CHAR column stores any unused space by inserting blank spaces after any stored information. For example, if you have a CHAR[200] field and insert the word "hello" then it will add 195 blank spaces after the word "yes" which will take up space in the database. A VARCHAR field only stores the word. The only time you should use a CHAR field instead of VARCHAR is if all of the text you insert will be of the same length - like on a state abbreviation which will always be 2 letters. When determining the length of a VARCHAR column type just figure out what the most amount of information that will be needed to be inserted into that field and set the length to that amount or set it to 255 if you aren't sure. If you need a field to store text that you know will be more than 255 then just choose the TEXT column type.

Column Types - Numbers

TINYINT[Length]	Integers in the range of -128 to 127 or 0 to 255 (unsigned).
SMALLINT[Length]	Integers in the range of -32,768 to 32,767 or 0 to 65,535 (unsigned).
MEDIUMINT[Length]	Integers in the range of -8,388,608 to 8,388,607 or 0 to 16,777,215 (unsigned).
INT[Length]	Integers in the range of -2,147,683,648 to 2,147,683,647 or 0 to 4,294,967,295 (unsigned).
BIGINT[Length]	Integers in the range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808 or 0 to 18,446,744,073,709,551,615 (unsigned).
FLOAT	A small number with a floating decimal point.
DOUBLE	A large number with a floating decimal point.
DECIMAL	A floating point number represented as a string.

Column Types - Date & Time

DATE	In the format of YYYY-MM-DD
DATETIME	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	In the format of YYYYMMDDHHMMSS
TIME	In the format of HH:MM:SS

If you want a column that will store a time setting and will automatically update the time whenever a record is inserted or updated then use TIMESTAMP because that is what that type is specifically set up to do. It should be noted that if you have multiple TIMESTAMP columns then only the first one will automatically update.