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

Pre-Requisites

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

INT:

Syntax:

INT[(width)] [UNSIGNED] [ZEROFILL]

Facts:

Examples:

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:

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:

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.

FLOAT & DOUBLE

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

Syntax:

FLOAT(p)

Here, p -> precision

Facts:

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:

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/NUMERIC

Syntax:

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

Facts:

Example:

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.

DATETIME

Syntax:

DATETIME(n)

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

Facts:

TIMESTAMP

Syntax:

TIMESTAMP(n)

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

Facts:

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.

CHAR And VARCHAR

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.

Syntax:

CHAR(n)
VARCHAR(n)

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

Facts:

BINARY And VARBINARY

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.

Syntax:

BINARY(n)
VARBINARY(n)

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

Facts:

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;

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)

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

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:

ENUM

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.

Syntax:

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:

SET

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.

Syntax:

SET({comma separated list of allowed values})

Example:

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:

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

Output:

Special Data Types

BOOLEAN

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.

Syntax:

columnName BOOLEAN

Example:

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:

JSON

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.

Syntax:

columnName JSON

Facts:

Example:

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": "test@abc.com",
    "first_name": "Steve",
    "last_name": "Wilson",
  }
}

{
  "data": {
    "id": 2,
    "email": "test@123.com",
    "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": "test@abc.com",
    "first_name": "Steve",
    "last_name": "Wilson"
  }}');
insert into json_example values(
  '{"data": {
    "id": 2,
    "email": "test@123.com",
    "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->'$.data.email' as email from json_example 

Output:

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

ALTER TABLE student_info MODIFY age TINYINT

• 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

SELECT CURDATE();

Output

2020-08-10

NOW() function returns the current date with timestamp.

SELECT NOW();

Output

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:

Conclusion

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.