An Overview of MySQL Data Types

What data types are available in a MySQL database? In this article, you’ll learn about numeric data types, text data types, and much more!

You may have seen our article about data types in SQL. Here, we focus on the MySQL data types. We will discuss the most commonly used ones: numeric, text, and date and time.

If you are interested in the topic, be sure to check out our Data Types in SQL course. It is part of the Creating Database Structure track. Learn more about this track in this article on our blog.

Data types describe what kind of data to expect in each column. It may be numbers, text, dates, or many others. Based on this information, the database engine decides how to handle data in a given column; for example, it sorts texts alphabetically but numbers by increasing or decreasing numerical value.

Let’s get started!

Numeric Data Types in MySQL

The numeric data types are for storing numbers. This means we can perform arithmetic operations on them. For example, we use numeric data types to store the number of items purchased by a customer in an order, the price of an item, or the temperature values.

A numeric data type may be exact or approximate.

Exact Numeric Data Types in MySQL

INTEGER (INT)

The INTEGER (or INT; used interchangeably) data type in MySQL stores whole numbers. These numbers can be positive or negative, but they cannot have a fractional part. We use INTEGER when we know the data in the column must always be a whole number. For example, it can be used to store the number of students in a class or the number of T-shirts sold by a store.

The INTEGER data type is also a good candidate for the primary key. A primary key is a column or a set of columns that is different for each entry in the table and therefore can be used to identify a specific row. If we use INTEGER to store the primary key, then each row is assigned a different number. INTEGER in MySQL also has a handy feature, the AUTO_INCREMENT attribute. Look at the following SQL code:

CREATE TABLE book (
id INT AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255)
);

This creates a table book with two columns: id and title. The id column is a primary key. Its data type is INT, and it uses the AUTO_INCREMENT attribute.

Now, let’s run this code:

INSERT INTO
    book(title)
VALUES
    (“A Tale of Two Cities”);

Wait, we didn’t provide the id! That’s where AUTO_INCREMENT does its magic. When you insert NULL into the INT AUTO_INCREMENT column or omit its value completely (as we did in the example above), it is set automatically. AUTO_INCREMENT is a sequence that starts at 1 by default and is increased by 1 with each inserted record.

INTEGER values in MySQL can be signed or unsigned. An unsigned INT does not accept negative values. If you want unsigned integers, simply add the UNSIGNED keyword:

CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255)
);

A single INTEGER value needs 4 bytes of storage. Thus, the maximum value an UNSIGNED INT can store is 4294967295.

SMALLINT

SMALLINT in MySQL is very similar to INT. The main difference is that SMALLINT takes up less space – it only needs 2 bytes. However, this also means the range of numbers it can store is smaller. Its maximum unsigned value is 65535.

DECIMAL (NUMERIC)

The DECIMAL type in MySQL stores the exact numeric data values. However, unlike INTEGER and SMALLINT, it can have fractional parts. In MySQL, NUMERIC is treated as a synonym for DECIMAL.

Use DECIMAL when working with financial data, as it offers decimal precision. Monetary calculations must be precise; it is not acceptable to use approximations (read more about it on Vertabelo blog).

When defining a DECIMAL column, you can provide two arguments: precision and scale. The precision is the total number of significant digits stored. The scale is the maximum number of digits stored after the decimal point.

Let’s create a table book with a price column:

CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255),
price DECIMAL(4,2)
);

In the above example, the minimum value for column price is -99.99, and the maximum is 99.99.

The scale argument may be omitted. Its default value is 0, which means fractions are not stored. That is, price DECIMAL(4) is equivalent to price DECIMAL(4,0).

You can also use DECIMAL with no arguments. The default value for precision in MySQL is 10. That is, price DECIMAL is equivalent to price DECIMAL(10,0).

The amount of space needed by DECIMAL values depends on the number of digits stored and is calculated separately for integer and fractional parts. The digits are divided into sets of 9, and each set requires 4 bytes. The storage requirements for the remaining digits are given in the following table:

In our example above, for DECIMAL(4,2), we have 2 digits for the integer part and 2 digits for the fractional part, requiring 1 + 1 = 2 bytes according to the table.

Approximate Numeric Data Types in MySQL

FLOAT

FLOAT is another MySQL data type used for storing numbers. However, it differs from DECIMAL in that it is not exact. FLOAT provides only approximate values because it stores numbers in the binary format (it uses base 2 instead of base 10), and many decimal fractions cannot be expressed precisely using binary fractions.

Although this saves storage, it comes at the cost of precision. Keep in mind that FLOAT cannot be used to store monetary data! Instead, it is good for storing scientific data, such as measurement results:

CREATE TABLE measurement (
distance FLOAT
);

FLOAT needs 4 bytes of storage. Its ranges are 3.402823466E+38 to 1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. Note that in scientific notation, E+n means 10 to the power of n; so, 3.402823466E+38 is the same as 3.402823466E × 10³⁸.

You can also use the syntax FLOAT(n), where n specifies the precision in bits. n can take on any value from 0 to 53 and is used to determine whether to use FLOAT or DOUBLE for the resulting data type. If it is 24 or less, the data type used is FLOAT; otherwise, it is DOUBLE.

DOUBLE PRECISION (DOUBLE, REAL)

In MySQL, DOUBLE and REAL are synonyms for DOUBLE PRECISION. DOUBLE requires 8 bytes of storage, with values ranging from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and from 2.2250738585072014E-308 to 1.7976931348623157E+308. Since E+n means 10 to the power of n in scientific notation, -1.7976931348623157E+308 is the same as –1.7976931348623157 × 10³⁰⁸.

As you can see, DOUBLE is more precise than FLOAT. If you want greater precision, use DOUBLE instead of FLOAT:

CREATE TABLE measurement (
distance DOUBLE
);

However, keep in mind it is still not completely precise and can only provide an approximate value.

Read more about numeric data types in SQL on our blog!

Text Data Types in MySQL

Text data types are used to store sequences of characters including letters, numbers, and symbols, which are commonly called “strings.” For example, we can use strings to store a user email, a book title, or the contents of a blog post.

CHAR

A CHAR is a text data type with a fixed size. That is, the storage occupied by a CHAR column is always as specified in the column definition, even if some of the rows store fewer characters in that column. If the value of a string is shorter than the specified size of the column, it is padded with blank spaces until it meets that size; it does not save any space.

Each character in MySQL needs 1 byte of data. This means the table size can be reduced significantly by decreasing the declared CHAR length. Use the CHAR data type when all the values have the same length. Otherwise, it is better to choose VARCHAR.

For example, you may use CHAR for storing ISBN book numbers (ISBN stands for International Standard Book Number and always consists of 13 characters):

CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255),
isbn CHAR(13)
);

VARCHAR

A VARCHAR column has a variable size. This saves space when entries differ in length (for example, book titles).

Each character in MySQL needs 1 byte. However, it requires additional space to store the string length: 1 byte if the string stored is shorter than 255 characters or 2 bytes if it’s longer than 255 characters.

MySQL version 5.0.3 or later stores 65,535 characters in a VARCHAR column. In earlier versions, VARCHAR stored only up to 255 characters.

Let’s see VARCHAR in action:

CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255)
);

The additional prefix with string size needs to be read first when the column data is processed. This increases processing time. For this reason, VARCHAR indexing is slower than CHAR indexing.

TEXT

The TEXT data type in MySQL is used to store large amounts of text. They can be blog posts, articles, or even an entire book.

It is important to note that the TEXT column stores only a pointer. The actual text data is stored outside of the table as a blob. This means data is retrieved from outside, and as a consequence, the access time is longer. VARCHAR is stored directly in the table, so it is much faster.

In MySQL versions prior to 5.0.3, TEXT stored more characters than VARCHAR (65,535 characters compared to 255). In MySQL version 5.0.3 or later, the maximum size for both is 65,535.

Does it mean they can now store the same number of characters? Not necessarily.

MySQL does not allow the row size to exceed 65,535. This means the VARCHAR size cannot reach its theoretical maximum size if the table has other columns. With TEXT, the data is stored outside and does not count towards this limit.

We can use TEXT to store a longer book summary:

CREATE TABLE book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255),
summary TEXT
);

TEXT stores up to 64 kilobytes of data (which is equivalent to 65535 characters). It also requires additional 2 bytes to store the pointer to the actual data.

Need to store even more characters? You can use the MEDIUMTEXT or even the LONGTEXT data type!

Date and Time Data Types in MySQL

These MySQL data types are used for handling date and time values: days, months, years, hours, minutes, seconds, and fractions of seconds. They can be used to store the user registration date, the race finish time of an athlete, or the start time of a university lecture. MySQL offers many date functions to work with these values.

DATE

The DATE type is used to store dates (without the time part). It displays dates in the ‘YYYY-MM-DD’ format and supports values ranging from '1000-01-01' to '9999-12-31'. A DATE column needs 3 bytes of storage.

For example, we can use it to store user birthdates:

CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
	birthdate DATE
);

DATETIME

The DATETIME type stores values containing both date and time parts. It uses the format ‘YYYY-MM-DD hh:mm:ss’ and supports values ranging from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

As an example, if we need to store birthdates with exact hours (for example, in a hospital database), DATETIME is a good option:

CREATE TABLE patient (
id INT AUTO_INCREMENT PRIMARY KEY,
	birthdate DATETIME
);

DATETIME needs 5 bytes of storage plus additional bytes depending on the precision of the fractional seconds (see table below). However, note that it is 8 bytes in MySQL versions prior to 5.6.4.

The precision of the fractional seconds is defined using the syntax DATETIME(n), where n takes on values from 0 to 6 and represents the number of digits in the fractional part. The default value is 0, which means there are no fractional seconds.

TIMESTAMP

The TIMESTAMP type stores values containing both date and time parts. Its values can range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

See the UTC part? This is the main difference between DATETIME and TIMESTAMP. TIMESTAMP adjusts the data by converting it from the current time zone to UTC when data is saved to the database and doing the reverse when it is retrieved. TIMESTAMP lets you pinpoint the exact point in time regardless of the time zone. It can be handy if the application operates in multiple time zones.

TIMESTAMP requires 4 bytes of storage (MySQL version 5.6.4 and later needs additional bytes depending on the precision of the fractional seconds – see the table above in the DATETIME section). It requires less space than DATETIME but has a smaller supported range.

TIMESTAMP may not work for a column with dates before 1970 or after 2038. However, you may choose it for logging user actions or application events, such as the timestamp of the most recent user login:

CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
	last_login TIMESTAMP(3)
);

Note we use TIMESTAMP(n) syntax to store fractional seconds here.

TIME

The TIME data type is used to store time values. It can also represent elapsed time or time intervals; for that reason, the values can be negative, and the hour part can be larger than 24.

TIME displays in the format ‘hh:mm:ss’ or ‘hhh:mm:ss’. It supports values ranging from '-838:59:59' to '838:59:59' and needs 3 bytes (MySQL version 5.6.4 and later needs additional bytes depending on the precision of the fractional seconds – see the table in the DATETIME section).

We may use TIME to store a university lecture schedule:

CREATE TABLE lecture (
id INT AUTO_INCREMENT PRIMARY KEY,
	start_time TIME
end_time TIME
);

Other MySQL Data Types

There are many other data types in MySQL. You can read more about them in the MySQL documentation. You may be interested in numeric data types such as TINYINT, MEDIUMINT, and BIGINT, as well as in the string data types such as BINARY, VARBINARY, BLOB, ENUM, and SET.

In addition, there are other data type categories in MySQL: spatial and JSON. Spatial data types can store geospatial data such as coordinates. The JSON data type can store objects in JavaScript Object Notation format.

Use Your New Knowledge About MySQL Data Types

That’s all for today! You are now armed with a better understanding of the MySQL data types. If you want to learn even more about MySQL, you definitely do not want to miss our new SQL from A to Z in MySQLcourse!

Don’t forget about our Data Types in SQL course, which is a part of the Creating Database Structure track. Read more about this track in this article on our blog.

And in case you want to take a look at data types in other database engines, check out our articles on MS SQL Server Data Types and PostgreSQL Data Types.

Good luck!