MySQL Data Types | What Are Different Data Types In MySQL

Learn about different MySQL Data Types i.e. Numeric, String, Date data type, JSON, Boolean, etc. with examples:

In this tutorial, we will learn about different data types that are supported by MySQL. Data types are specified against each column when a table is created and also while updating/altering the table to add a new column to the existing table

We will learn about the data types in three different categories i.e. Numeric data type, String data types, and Datetime data types.

=> Click here for the complete MySQL tutorial series

MySQL Data Types

MySQL Data Types


We will be learning all data types with working examples in MySQL. Hence it’s recommended to have MySQL installed to execute the queries along with details for a better understanding of the concept.

Also, you can create a sample database named sql_data_types which would contain all the tables that we would use for examples.

create database sql_data_types;
use sql_data_types;

Numeric Data Types

Numeric Data types can be divided into 3 types:

  • Integer Data Types
  • Fixed Point Types
  • Floating Point Types

Let’s discuss each of these:

Integer Data Types

These data types support whole numbers without any decimal representation. There are various subtypes like – INT, TINYINT, MEDIUMINT, SMALLINT, BIGINT





Most commonly used numeric type. Stores whole numbers

DEFAULT - Unsigned values
–2,147,483,648 to 2,147,483,6474 bytesIf used with UNSIGNED option - Range changes to
0 to 4,294,967,295

Width option can be used with ZEROFILL to pad the entries with zero for values less than the width


We will create a table with 1 column having INT data type and different options.

CREATE TABLE numbers_int(int_col INT)
INSERT into numbers_int values(100),(-324),(2456),(-100000);
select * from numbers_int

Output of the SELECT command:

INT subtype

CREATE TABLE numbers_int_options(int_col_with_options INT(4) ZEROFILL );
INSERT into numbers_int_options values(1),(123),(1234),(12345);
select * from numbers_int_options;

Output of the above command:


Other variations of INT:

There are multiple options available for INT depending on the specific requirements. These are typically used when memory or space is a concern, but for all practical purposes, INT is the most widely used.

The different variations of the INT data type that are available are listed below:

DataTypeRangeExampleMemory / Bytes used
TINYINT-128 - 127CREATE TABLE numbers_tinyint(tinyint_col TINYINT);1
SMALLINT-32768 - 32767CREATE TABLE numbers_smallint(smallint_col SMALLINT);2
MEDIUMINT-8388608 - 8388607CREATE TABLE numbers_mediumint(mediumint_col MEDIUMINT);3
BIGINT-2^63 - (2^63-1)CREATE TABLE numbers_bigint(bigint_col BIGINT);8

Floating Point Types

Floating Point Types are approximate value types and this depends on the no. of decimal point precision specified during the column type declaration.

There are 2 types of floating-point data types: FLOAT and DOUBLE which support different ranges and consume memory/storage.


As per the new recommended syntax – both FLOAT and DOUBLE precision can be specified using just FLOAT data types.



Here, p -> precision


FLOAT/DOUBLE represents floating point numbers with approximate values
I.e. when MySQL stores these values these values are approximated to nearest precision as per the type declared.
Precision between 0-23 represents FLOAT type whereas 24 to 53 would generate a double type consuming 8 bytes
-Precision - 0-23 => 4 bytes
-Precision -24-53 => 8 bytes
-Floating point numbers can be SIGNED as well as UNSIGNED
-FLOAT is generally accurate up to 7 decimal places while DOUBLE is accurate up to 14 decimal points
-There is also another non standard way of declaring FLOAT and DOUBLE with the specification of precision points
FLOAT(n,d) - where n is total digits and d is no of decimal points
-Create table with a column DOUBLE data type
CREATE TABLE numbers_double(double_col FLOAT(30));

-Create table with a column DOUBLE data type and precision digits as 5
CREATE TABLE numbers_double(double_col DOUBLE(20,5));

Let’s see some examples of retrieving the values of double types:

CREATE TABLE numbers_double(double_col DOUBLE(20,5));
insert into numbers_double VALUES(123.45),(-145),(12.3456788);
select * from numbers_double;

Output of the SELECT statement:

float and double datatype

We can see here that the values are stored up to 5 decimal points as specified during the data type declaration.

Fixed Point Types

These data types are used to store an exact value with determined precision. These data types are usually used when exact precision storage is required. For example, bank accounts it’s a requirement to maintain balances with 2 decimal points, we need data to be stored with exact precision.



DECIMAL[(width[,decimals])] [UNSIGNED] [ZEROFILL]


DECIMAL is a numeric data type in MySQL and stores exact values with the precision specified.
The default scale or width for the DECIMAL data type is 10 and 0 precision.

Please note that the types of DECIMAL and NUMERIC can be used interchangeably.
Depends on the range specified
For example DECIMAL(5,2) would have a range of -999.99 to 999.99
MySQL users binary format to store DECIMAL data type - It requires four bytes for every 9 digits - So for example, if we have DECIMAL(14,2) - would require a total of - 9(4) + 2(1) => 7 bytes-Max value of width can be 265
-The number of decimals is optional and the default value is 0


CREATE TABLE numbers_decimal(dec_col DECIMAL(4,2))
INSERT INTO numbers_decimal values(-99),(50.50);

DateTime Data Types

DateTime data types in MySQL, as the name suggests, are used to store date and time values in the MySQL database.

There are 2 temporal types supported by MySQL – DATETIME and TIMESTAMP

Let’s discuss both of these in the below sections.




Here, n -> precision/fractional part of seconds part (Max precision supported is 6).


Used to store date and time in MySQL column
When queried, the column data display datetime in the format below:
1000-01-01 00:00:00 - 9999-12-31 23:59:595 bytes
When a fractional part is included then for each 2 fractional digits an extra byte is consumed.
CREATE table datetime_example(date_col DATETIME);
INSERT INTO datetime_example values(NOW());
SELECT * from datetime_example;

date_col 2020-08-08 22:22:53




Here, n -> precision/fractional part of seconds part (Max precision supported is 6)


Another temporal data type used to store datetime.
This stores date time as UTC also with a limited range between 1970 and 2038
1970-01-01 00:00:01 UTC - 2038-01-19 03:14:07 UTC4 bytesCREATE table timestamp_example(ts_col TIMESTAMP);
INSERT INTO timestamp_example values(NOW());
SELECT * from timestamp_example;

2020-08-08 22:19:11

String Data Types

String data types, as the name suggests, are used to store strings/texts or blobs of textual information in the database. Depending upon the use case there are different such data types available -CHAR, VARCHAR, BINARY, VARBINARY, TEXT, ENUM, SET & BLOB

Let’s understand each of these different data types with examples.


Both these types are used to store String values in the columns in MySQL, but they differ in how values are stored and retrieved.

CHAR & VARCHAR are declared with length, which indicates the maximum length of the String that you want to store in the column.



Here, n -> max no. of characters to be stored by the column


Syntax - CHAR(n)
CHAR can store ‘n’ length String as defined during the declaration.
If the String is less than n character, then it's padded with spaces.
Length of CHAR data type can vary from 0 - 255
Depending on length, memory consumption would range from 0 - 255 bytes.
CREATE TABLE string_example(char_col CHAR(50));
Syntax - VARCHAR(n)
VARCHAR allows you to store variable length Strings and consume memory against the actual size of String stored rather than the max value that’s specified during the column definition.Length of VARCHAR data type can vary from 0 - 65535
Depending on length, memory consumption would range from 0 - 65535 bytes.
CREATE TABLE string_example(varchar_col VARCHAR(50));


Similar to CHAR and VARCHAR – these data types store Strings but in Binary format.

The length of BINARY and VARBINARY data types is measured in bytes as opposed to number of characters in CHAR and VARCHAR data types.



Here, n -> max no. of bytes to be stored by the column.


Syntax - BINARY(n)
BINARY can store ‘n’ binary bytes.
For values less than n they are right padded with 0 byte and stored
Length of BINARY data type can vary from 0 - 255
Depending on length, memory consumption would range from 0 - 255 bytes.
CREATE TABLE binary_string(binary_col BINARY(50));
Syntax - VARBINARY(n)
VARBINARY allows you to store variable length binary Strings upto ‘n length (as specified in column definition)Length of VARBINARY data type can vary from 0 - 65535
Depending on length, memory consumption would range from 0 - 65535 bytes.
CREATE TABLE variable_binary_string(varbinary_col VARBINARY(50));

Let’s understand the BINARY data type in more detail. We will create a table with a column each of binary and varbinary data type and inspect the contents.

CREATE TABLE binary_strings(binary_col BINARY(50), varbinary_col VARBINARY(50));
INSERT into binary_strings values("hello", "hello");
SELECT * from binary_strings;

binary and varbinary

Now we have seen the output as BLOB which means – Binary Large OBject – and these are nothing but Binary/Hex representation of the String values that we have inserted in the columns.

Now we will inspect the values of these binary strings and see how they are stored.

First, let’s see the value present in ‘binary_col’ which is of BINARY data type.


Let’s understand what are the values that are getting stored – If you notice the values against first 5 entries – i.e. first row and first 5 columns (0-4)

first row and first 5 columns 5

These are just the HEX representations for the characters of the String ‘hello’ that we have stored.

Now since it’s a BINARY data type of length 50, we can see that rest of the bytes are padded with ‘00’ values which is nothing but byte representation for digit ‘0’.

See the value present in ‘varbinary_col’ which is of VARBINARY data type.


Here, for VARBINARY, you can see that the values are just populated for a length of 5 which is equivalent to the String length of we stored. There is no Zero padding even though we declared a VARBINARY column of length 50.


BLOB and TEXT data types are similar to BINARY and CHAR data types with the distinction that they can support greater sizes as against their basic counterparts.

It’s important to note that BLOB stores data as Binary Strings while TEXT data type stores as non-binary Strings. Also, all these data types do not require specifying the length while mentioning the data type. They are inherently Variable length in nature and consume memory against only that is stored as the actual column value.

BLOB data types are generally used to store files such as images, pdf documents, etc as binary strings in a secure and efficient way.

Details about different variants of BLOB and TEXT data types are enlisted below:

TINYTEXT0-255 (255 Bytes)Range is the same as VARCHAR - Used for storing small info like titles, author names etc.CREATE TABLE tinytext_example(tinytext_col TINYTEXT);
TEXT0-65535 (64KB)This data type is sufficient to store text for a small to medium sized article.CREATE TABLE text_example(text_col TEXT);
MEDIUMTEXT0-16,777,215 (16MB)This data type can be sufficient to store text for an entire textbookCREATE TABLE mediumtext_example(mediumtext_col MEDIUMTEXT);
LONGTEXT0 - 4,294,967,295 (4GB)LONGTEXT is rarely used specifically in cases where MEDIUMTEXT does not suffice.CREATE TABLE longtext_example(longtext_col LONGTEXT);
TINYBLOB0-255 (255 Bytes)All BLOB data types are used to store Binary Strings and are generally preferred for storing files like images, pdf documents or small applications itself.
Depending on the size requirements, the different BLOB data types can be chosen and used.
CREATE TABLE tinyblob_example(tinyblob_col TINYBLOB);
BLOB0-65535 (64KB)CREATE TABLE blob_example(blob_col BLOB);
MEDIUMBLOB0-16,777,215 (16MB)CREATE TABLE mediumblob_example(mediumblob_col MEDIUMBLOB);
LONGBLOB0 - 4,294,967,295 (4GB)CREATE TABLE longblob_example(longblob_col LONGBLOB);


ENUM is a String data type that pre-defines the permitted values that a column can have. It’s similar to ENUM data types that exist in different programming languages like JAVA, C#, etc.

ENUM values, when stored are converted into numbers against each column value which results in a considerable memory saving for tables having a large no of records i.e. suppose we have an ENUM having values – value1, value2, and value3 then actual memory storage would be numeric indexes 1,2,3 behind the scenes.


ENUM({comma separated enum values})

Example Queries:

Create a table with 2 columns for storing mobile models as String and company names as ENUM data types having values – APPLE, SAMSUNG and NOKIA. Let’s also see queries to fetch data against a particular ENUM value.

CREATE TABLE mobile_details(model VARCHAR(100), brand ENUM('SAMSUNG', 'APPLE', 'NOKIA'));
INSERT INTO mobile_details values ("IPHONE X", 'APPLE'),("GALAXY M1", 'SAMSUNG'), ("GALAXY M2", 1) ;

In the above query, you can see that we have used Enum names as well as numeric indexes both during inserting.

Let’s try querying all the mobile devices with the brand name ‘SAMSUNG’ as well as querying against the numeric index of SAMSUNG which is ‘1’.

SELECT * FROM mobile_details where brand='SAMSUNG'
SELECT * FROM mobile_details where brand=1

Output of both the above queries will be the same as mentioned below:

model and brand 6


MySQL SET datatype is a String object which can have one or more than one value from a permitted range as described during column definition. It’s similar to ENUM but it allows multiple values from the defined list to be associated as column values.

Also, SET data types are stored as Number Indexes starting 2^0 – i.e. 1,2,4,8 etc.


SET({comma separated list of allowed values})


Let’s try understanding the SET data type with examples.We will create a table named mobile_details with a column model of String type and a column named supported OS which is a set data type and contains a list of supported operating system versions.

CREATE TABLE mobile_details(model VARCHAR(100), supported_os SET('ios9', 'ios10', 'android8', 'android9','android10'));

We can see that we have listed operating system values as part of a column having SET data type. The corresponding DECIMAL values that are assigned to these are enlisted below:


Now, if we want to assign SET value, for example, ‘android8, android9’ to a row we can simply assign the addition of the assigned decimal values i.e. 12 to have the same effect.

Please refer to the INSERT query above for assignment to row with model name ‘GALAXYM1’

INSERT INTO mobile_details values ("IPHONE X", ('ios9,ios10')),("GALAXY 
 M1", (12)), ("GALAXY M2", ('android9,android10')) ;

SELECT * from mobile_details;

Output of the above command:

supported os

We can also query the SET column against their decimal representation – suppose we want to query for ‘ios9, ios10’ – the sum of decimal representation is 3, so we can query as:

SELECT * from mobile_details where supported_os=3


mobile detail

Special Data Types


MySQL Boolean data type stores a column value as TRUE or FALSE. This data type is generally suited for storing flag values in the MySQL tables. For example – A Bank Account table having a column named is_savings_account can store either true or false.

The BOOLEAN value is stored in MySQL as 1 or 0 for TRUE and FALSE, respectively.


columnName BOOLEAN


CREATE TABLE account_details(accNo BIGINT, is_savings BOOLEAN);
INSERT into account_details values (123456,true), (110002, false), (234567, 1);

While inserting or retrieving values for BOOLEAN data type we can either use TRUE or FALSE or their numeric representations – i.e. 1 or 0.

 select * from account_details where is_savings=1; 

Output of above command:



MySQL supports native JSON as a data type for objects in JSON notation. It makes storage, querying and retrieval easy for JSON type documents rather than storing as Text strings or binary Blobs.


columnName JSON


JSON data type is used to store documents that are in JSON format.

Any column defined as JSON would throw an error if an invalid JSON is stored by an INSERT query.
The Range or size of JSON data type is similar to LONGTEXT or LONGBLOBCREATE TABLE json_example(json_col JSON);


MySQL provides different functions for querying JSON data. Let’s try inserting below JSON and then we will see MySQL functions to query.

  "data": {
    "id": 1,
    "email": "",
    "first_name": "Steve",
    "last_name": "Wilson",

  "data": {
    "id": 2,
    "email": "",
    "first_name": "Alex",
    "last_name": "Johson",

Let’s see commands to create table and insert the JSON data.

create table json_example(json_col JSON);
insert into json_example values(
  '{"data": {
    "id": 1,
    "email": "",
    "first_name": "Steve",
    "last_name": "Wilson"
insert into json_example values(
  '{"data": {
    "id": 2,
    "email": "",
    "first_name": "Alex",
    "last_name": "Johnson"

Query the table and print the first name and email fields from the JSON data that’s stored in the table rows.

 select json_col->'$.data.first_name' as firstname, json_col->'$' as email from json_example 



Frequently Asked Questions

Q #1) How can we change the data type for a MySQL column once assigned?

Answer: The data type of the column can be changed using ALTER TABLE Command.
Consider a table student_info having 2 columns name and age of types VARCHAR and INT respectively. This can be achieved using the following CREATE TABLE command:

CREATE TABLE student_info(name CHAR(20), age INT);

There are couple of variations in this command

  • If we want to change just the data type of the column – We can use MODIFY command along with ALTER
    In the above table suppose we want to change the datatype of age column we can use the following command
  • If we want to change the column name as well as data type of the column – We can use CHANGE command along with ALTER
    In the above table suppose we want to change the name of column from ‘name’ to ‘sname’ and data type from CHAR to VARCHAR(50), we can use the following command:
ALTER TABLE student_info CHANGE name sname VARCHAR(50)

Q #2) What is the data type for an image in mysql?

Answer: For storing any of the file types in MySQL like images, pdf etc BLOB or binary data type is the most suited type. There are different variants of BLOB data type available depending on the size of the target file that we want to store. We can use variants of BLOB like – TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

Q #3) Which datatype is more suitable for storing documents in mysql?

Answer: Similar to BLOB for images or pdf files, for storing text documents/articles, TEXT data type can be used. This is an extension to CHAR data type with support for extra character storage.

The different variants of TEXT data type that are available are – TEXT, TINYTEXT, MEDIUMTEXT & LONGTEXT

Q #4) What is the data type for currency in MySQL?

Answer: For storing data for currency values, the most suited data type is DECIMAL. It’s used to store data types with exact precision. For example, a column name with type DECIMAL(4,2), would store values in range of -99.99 to 99.99 and return the value with the same precision on retrieval unlike. approximation for Floating point numbers.

Q #5) What happens if someone tries to insert negative numbers for UNSIGNED integer columns?

Answer: MySQL generates an error for such insert statements
Let’s try creating a table with an INT column having UNSIGNED option.

CREATE TABLE int_unsigned(age INT UNSIGNED);
INSERT into int_unsigned values(-350);
Error Code: 1264. Out of range value for column 'int_col_with_options' at row 1

Q #6) How to get current date data in mysql?

Answer: MySQL provides 2 in built date time functions for fetching current values

CURDATE() function returns the current date




NOW() function returns the current date with timestamp.



2020-08-10 00:42:54

Q #7) Out of CHAR and VARCHAR – which one is more suitable?

Answer: VARCHAR stands for variable character and has an important advantage of consuming less memory as against CHAR of same length. For example, A column with VARCHAR(50) if stores strings of length 20 would result in consuming just 20 bytes as opposed to a data type declared with CHAR(50)

Q #8) How to update ENUM value in MySQL?

Answer: In order to update an ENUM column in MySQL, you will need to modify mentioning the existing column values so that the existing entries remain intact.

Let’s try to understand this with an example.

Suppose we have a table named mobile_details having a model field as String (VARCHAR) and brand column as ENUM having initial values as ‘APPLE’, SAMSUNG’ and ‘NOKIA’

Now, suppose we want to add another brand name ‘MOTOROLA’ to the existing enum. Let’s see the queries that we would need to execute.

CREATE TABLE mobile_details(model VARCHAR(100), brand ENUM('SAMSUNG', 'APPLE', 'NOKIA'));

Once created, you can get the details of original ENUM values using DESCRIBE command

DESCRIBE mobile_details;

Now let’s run the command to update the ENUM

ALTER TABLE mobile_details MODIFY column brand ENUM('SAMSUNG', 'APPLE', 'NOKIA', 'MOTOROLA');

We can again run the DESCRIBE command to see if the updates are successfully applied.

DESCRIBE mobile_details

Here’s the output of above command:

Default and Extra


In this tutorial we learned about the different data types that are supported by MySQL.

We learned about Numeric, Floating, String and Date time data types along with syntax and different examples.

MySQL data types are the foundation building blocks for getting started and one of the most crucial aspects during the MySQL Schema Design.