DigitalOcean
SnapShooter

MySQL Data Types

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.

Was this page helpful?

Thank you for helping us improve!