SnapShooter Learning Center

MySQL Data Types

Learn about the MySQL data types and how to use them best

Simon Bennett]
Simon Bennett
Last Updated: Mar 23, 2021
Table of Contents

MySQL is a database management system used worldwide. It is based on SQL (Structured Query Language). The most common purpose of MySQL is a web database. Well-known companies like Uber, Netflix, Amazon, and Twitter have reportedly used MySQL in their tech stacks.

Many website operators use MySQL, but the ones who are new to this application are often confused while trying to operate it. One of the reasons for their confusion is the different types of data. They do not understand what type of data is supposed to go in which category of MySQL, leading to a disaster and headache in the end. Here, we shall try to understand what types of data MySQL supports.


# How to determine the data type in MySQL?

Before entering the data into MySQL, the website operator should categorise the data in hand according to the data types of MySQL. The data type can be categorised using the following tips

  • The values to be added in the website are fixed or variable
  • The length of the values to be added in the website is fixed or variable
  • Whether the values can be indexed or not
  • How MySQL will compare the values of the data type

After categorising the given data using the above points, the website operator is now ready to understand the six types of data MySQL supports.


# Numeric Data Type

MySQL accepts various types of numbers and numeric values like integer, decimal, fractional, float, real, double precision, BIT and boolean. All the numeric data falls into this category. Numeric data types are divided into two further categories, signed and unsigned, except for bit type.


# TINYINT

  • It is used for very small sized integer
  • For signed, the range allowed is from -128 to 127
  • For unsigned, the range allowed is from 0 to 255
  • A width of up to 4 digits can be specified and takes 1 byte for storage

# SMALLINT

  • It is used for small sized integer
  • For signed, the range allowed is from -32768 to 32767
  • For unsigned, the range allowed is from 0 to 65535
  • A width of up to 5 digits can be specified and takes 2 byte for storage

# MEDIUMINT

  • It is used for medium sized integer
  • For signed, the range allowed is from -8388608 to 8388607
  • For unsigned, the range allowed is from 0 to 16777215
  • A width of up to 9 digits can be specified and takes 3 bytes for storage

# INT

  • It is used for normal sized integer
  • For signed, the range allowed is from -2147483648 to 2147483647
  • For unsigned, the range allowed is from 0 to 4294967295
  • A width of up to 11 digits can be specified and takes 4 bytes for storage

# BIGINT

  • It is used for Large sized integer
  • For signed, the range allowed is from -9223372036854775808 to 9223372036854775807
  • For unsigned, the range allowed is from 0 to 18446744073709551615
  • A width of up to 20 digits can be specified and takes 8 bytes for storage

# FLOAT (m, d)

  • It is used for floating-point numbers. This cannot be unsigned
  • m is display length and d is number of decimals
  • Default is 10, 2
  • Decimal precision can go to 24 places and takes 2 bytes for storage

# DOUBLE (m, d)/REAL (m, d)

  • Double-precision floating-point number. This cannot be unsigned
  • m is display length and d is the number of decimals
  • Default is 16, 4
  • Decimal precision can go to 53 places and takes 8 bytes for storage

# DECIMAL (m, d)/ NUMERIC (m, d)

  • Unpacked floating-point number. This cannot be unsigned.
  • m is display length and d is the number of decimals.
  • Each decimal takes one byte for storage

# BIT (m)

  • To store bit values in table columns
  • m is the number of bits per value. Range is 1 to 64

# BOOL

  • True and false condition
  • 1 is true and 0 is false

# BOOLEAN

  • Similar to BOOL

# Date and Time Data Type

Date, time, datetime, time of beginning and year are categorised under time type. Information like hiring dates/times, date/time of births and date/time of deaths fall in this category.


# YEAR [(2|4)]

  • Year value as 2 or 4 digits
  • Default is 4 digits
  • 1 byte for storage

# DATE

  • Range is 1000-01-01 to 9999-12-31
  • Displayed as YYYY-MM-DD
  • 3 bytes for storage

# TIME

  • Range is -838:59:59 to 838:59:59
  • Displayed as HH:MM:SS
  • 3 bytes plus fractional seconds for storage

# DATETIME

  • Range is 1001-01-01 00:00:00 to 9999-12-31 23:59:59
  • Displayed as YYYY-MM-DD HH:MM:SS
  • 5 bytes plus fractional seconds for storage

# TIMESTAMP (m)

  • Range is 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
  • Displayed as YYYY-MM-DD HH:MM:SS
  • 4 bytes plus fractional seconds for storage

# String Data Types

This type contains plain text and binary data. Data which comes in this category are files, images, audio files, names, and addresses.


# CHAR (size)

  • Maximum size - 255 characters
  • (size) is number of characters to store
  • Fixed-length strings
  • Space padded on the right

# VARCHAR (size)

  • Maximum size - 255 characters
  • (size) is number of characters to store
  • Variable-length string

# TINYTEXT (size)

  • Maximum size - 255 characters
  • (size) is number of characters to store

# TEXT (size)

  • Maximum size - 65535 characters
  • (size) is number of characters to store

# MEDIUMTEXT (size)

  • Maximum size - 16777215 characters
  • (size) is number of characters to store

# LONGTEXT (size)

  • Maximum size - 4 GB or 4294967295 characters
  • (size) is number of characters to store

# BINARY (size)

  • Maximum size - 255 characters
  • (size) is number of characters to store
  • Fixed-length strings
  • Space padded on the right

# VARBINARY (size)

  • Maximum size - 255 characters
  • (size) is number of characters to store
  • Variable-length string

# ENUM

  • Maximum size - 65535 characters
  • Short for enumeration
  • Uses numeric indexes to represent values
  • Storage of 1 or 2 bytes

# SET

  • Can hold any number of string values
  • Must be chosen from a predefined list of values in table created before
  • Storage of 1, 2, 3, 4 or 8 bytes

# Binary Large Object Data Types (BLOB)

BLOB can hold a variable data amount. It has four subtypes.

Syntax Maximum size
TINYBLOB Maximum size - 255 bytes
BLOB (size) Maximum size - 65535 bytes
MEDIUMBLOB Maximum size - 16777215 bytes
LONGBLOB Maximum size - 4294967295 bytes

# Spatial Data Types

This data type contains geographical and geometrical values. This type can be used to draw and describe figures.

Syntax Description
GEOMETRY Collection of points to hold all types of spatial values with location
POINT Stores values of (x, y) coordinates
POLYGON A planar surface representing multi-sided geometry. It can be defined by any interior boundary and only one exterior boundary
LINESTRING A curve having one or more point values
GEOMETRYCOLLECTION Collection of zero or more geometry values
MULTILINESTRING A collection of linestring values in the form of a multi-curve geometry
MULTIPOINT Collection of multiple point elements which cannot be connected or ordered
MULTIPLYGON A 2D geometry representing a collection of multiple polygon elements

# JSON Data Type

MySQL has the JSON data type from the version v5.7.8. Website operators use this data type of store and access JSON documents quickly and efficiently.

JSON data type is better than string columns for storing JSON-format strings because

  1. JSON data type provides validation of JSON documents automatically. It will show errors if the documents stored in JSON columns is invalid.
  2. JSON data type provides a storage format optimal for JSON documents.

Did you find this article helpful?