The three major types of column types used in MySQL are
- Integer
- Text
- Date
Choosing a column data type is very important in order to achieve speed, effective storage and retrieval. Hence, I’ve dedicated two sessions to this topic. Now, I’ll be touching only the surface; for a thorough explanation refer the resources in What Next? session.
MySQL Numeric Column Types
In addition to int (Integer data type), MySQL also has provision for floating-point and double precision numbers. Each integer type can take also be UNSIGNED and/or AUTO_INCREMENT.
- TINYINT: very small numbers; suitable for ages. Actually, we should have used this data type for employee ages and number of children. Can store numbers between 0 to 255 if UNSIGNED clause is applied, else the range is between -128 to 127.
- SMALLINT: Suitable for numbers between 0 to 65535 (UNSIGNED) or -32768 to 32767.
- MEDIUMINT: 0 to 16777215 with UNSIGNED clause or -8388608 to 8388607.
- INT: UNSIGNED integers fall between 0 to 4294967295 or -2147683648 to 2147683647.
- BIGINT: Huge numbers. (-9223372036854775808 to 9223372036854775807)
- FLOAT: Floating point numbers (single precision)
- DOUBLE: Floating point numbers (double precision)
- DECIMAL:Floating point numbers represented as strings.
Date and time column types
- DATE: YYYY-MM-DD (Four digit year followed by two digit month and date)
- TIME: hh:mm:ss (Hours:Minutes:Seconds)
- DATETIME: YYYY-MM-DD hh:mm:ss (Date and time separated by a space character)
- TIMESTAMP: YYYYMMDDhhmmss
- YEAR: YYYY (4 digit year)
