Primer – MySQL database Column Types

November 28, 2009
 |  No Comments

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.


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)