Monday, October 19, 2009

MySQL Data Types

The data types used in MySQL for storing text strings are:

  • CHAR: used for columns with values of an exact length

    Ex: CHAR(2) for state abbreviations like "PA" and "NY"
  • VARCHAR: used for columns with values of variable lengths up to 255

    Ex: VARCHAR(25) for names
  • TEXT: used for free-form text such as comments of up to ~65000 characters
  • MEDIUMTEXT: free-form text of up to ~1.6 million characters
  • LONGTEXT: free-form text of up to ~4 trillion characters

The data types for storing integers are shown in the table below.  As the table shows, columns of these types can be signed or unsigned.  Unsigned columns can only hold positive values.  Signed columns can hold both positive and negative values.

Type Signed Range Unsigned Range
TINYINT -128 to 128 0 to 255
SMALLINT -32768 to 32767 0 to 65535
MEDIUMINT -8.3M to 8.3M 0 to 16M
INT -2.1B to 2.1B 0 to 4.2B
BIGINT > ±2.1B > 4.2B

 For numbers that have digits after the decimal point, there are two main data types:

  • FLOAT: used for numbers with up to 7 significant digits
  • DOUBLE: used for numbers with up to 16 significant digits

The data type limits above are approximate in many cases.