Programming MySQL MySQL Data Types – a complete list

MySQL Data Types – a complete list

MySQL Data Types - Techbriefers

MySQL supports a number of SQL data types. These data types can be divided in several categories: numeric types, date and time types, string (character and byte) types, spatial types, and the JSON data type.

Contents show

In this article I have provided an overview of all MySQL data types.

A more detailed description of these data types in each category will be soon provided in each article separately and I will include all the links here.

What is MySQL Data Type?

A database table comprises multiple columns with specific data type like numeric data type or string data type. MySQL provides a lot more data types than just numeric types and string types. Each data type in MySQL can be identified by:

  • The type of values it represents.
  • The space occupied and the property of the values to be a fixed-length or variable length.
  • Whether the values of a specific data type can be indexed or not.
  • How comparison of the values of a specific data type is done by MySQL.

I am going to explain the following data types of MySQL briefly in this article:

  • Numeric types
  • Date and Time types
  • String Types

MySQL Numeric Data Types

MySQL provide support for all the standard ANSI SQL numeric data types, so if you are new to MySQL and coming to MySQL from a different database system, these definitions will be quite familiar to you. These types include the exact numeric data types and approximate numeric data types.

Exact numeric data types include INTEGER, INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT. Approximate numeric data types include FLOAT, REAL, and DOUBLE PRECISION.

To avoid any confusion please remember that:

  • INT and INTEGER are same data types, hence INT is a synonym for INTEGER.
  • DEC, FIXED & DECIMAL are same data types, hence DEC and FIXED are synonyms for DECIMAL

Under Numeric Data Types we have:

  • Integer Types of exact Values – INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
  • Fixed-Point Types of Exact Values – DECIMAL, NUMERIC
  • Floating-Point Types of Approximate Values – FLOAT, DOUBLE

Exact numeric data types:

Integer Types – INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL supports the SQL standard integer types INTEGER (INT same as INTEGER) and SMALLINT. It also supports other integer types TINYINT, MEDIUMINT, and BIGINT as an extension to the standard.

Below is a table that shows the required storage and range for each integer type.

Type Storage (Bytes) Min Value Signed Min Value Unsigned Max Value Signed Max Value Unsigned
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -263 0 263-1 264-1

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when we have to preserve exact precision (no. of values after decimal point), for example in financial data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

In phpMyAdmin I could find only DECIMAL type option.

Reference : MySQL documentation and web sources.

Share this article

Leave a Reply