Table of contents
1.
Introduction
2.
Numeric Data Type
3.
Date and Time Data Type
4.
String Data Type 
5.
Binary Large Object Data Types(BLOB)
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Data Types

Author Divyansh Jain
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

A Data Type defines a specific type of data, such as integers, floating points, Booleans, and so on. It also identifies the possible values for that type, the operations that can be performed on that type, and the manner in which that type's values are stored. Each database table in MySQL has many columns and specific data types for each column.

The proper definition of a table's fields is critical to the overall optimization of your database which means that you should only utilize the type and size of the field that you truly require. 

For example, if you know you'll only use two characters, don't make a field 10 characters large. 

Now let’s deep dive into the data types present in MySQL: 

MySQL data types are as follows: 

  • Numeric Data type
  • Date and Time Data type
  • String Data Type
  • Binary Large Object Types(BLOB)

 

Let’s understand them one by one: 

Numeric Data Type

MySQL comes with all of the SQL numeric data types you'll need. These data types can be exact numeric data types (for example, integer, decimal, numeric, and so on) or approximate numeric data types (for example, integer, decimal, numeric, and so on). It also has a BIT data type for storing bit values. Numeric data types in MySQL are divided into two categories: signed and unsigned, with the exception of bit data types.

Data Type  Description
BIT(size) This is a bit-value type. The size specifies the number of bits per value. The size option can be set to any number between 1 and 64. Size is set to 1 by default.
TINYINT(size) An extremely tiny number. From -128 to 127 is the signed range. From 0 to 255, the unsigned range is used. The maximum display width is specified by the size option (which is 255).
SMALLINT(size) An insignificant number. From -32768 to 32767 is the signed range. 0 to 65535 is the unsigned range. The maximum display width is specified by the size option (which is 255). 
MEDIUMINT(size) A medium-sized number. -8388608 to 8388607 is the signed range. 0 to 16777215 is the unsigned range. The maximum display width is specified by the size option (which is 255). 
INT(size) A medium-sized number. -2147483648 to 2147483647 is the signed range. 0 to 4294967295 is the unsigned range. The maximum display width is specified by the size option (which is 255). 
INTEGER(size) Same as INT(size)
BIGINT(size) A significant number. -9223372036854775808 to 9223372036854775807 is the signed range. 0 to 18446744073709551615 is the unsigned range. The maximum display width is specified by the size option (which is 255). 
FLOAT(size,p) A number that can be expressed as a floating-point number. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point. In MySQL 8.0.17, this syntax was deprecated, and it will be eliminated in future MySQL versions.
FLOAT(p) A number that can be expressed as a floating-point number. The p-value is used by MySQL to determine whether the final data type should be FLOAT or DOUBLE. If p is between 0 and 24, the data type is FLOAT (). If p is between 25 and 53, the data type is DOUBLE ().
DOUBLE(size,d) A floating-point number of typical size. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point.
DOUBLE PRECISION(size,d) A double precision floating point number where size is the total digits and d is the number of digits after decimal.
DECIMAL(size,d) A fixed-point number that is exact. The size of the total number of digits is specified. The d option specifies the number of digits after the decimal point. Size has a maximum limit of 65. The highest value for d is thirty. Size is set to 10 by default. The value of d is set to 0 by default.
DEC(size,d) Same as DECIMAL(size,d)
BOOL Nonzero values are deemed true, while zero values are considered false.
BOOLEAN Same as BOOL

 

Date and Time Data Type

This data type can represent temporal values such as date, time, DateTime, timestamp, and year. Each temporal category has a value, including zero. When we insert an invalid value, MySQL is unable to represent it and defaults to a value of zero.

Data Type Syntax Maximum Size  Explanation
YEAR[(2|4)] 2 digits or4 digits The default value is four digits. For storing, 1 byte is required.
DATE  Form ‘1000-01-01’ to ‘9999-12-31’ The format is 'yyyy-mm-dd'. For storing, 3 bytes are required.
TIME ‘-838:59:59’ to ‘838:59:59’ The format is 'HH:MM:SS'. Storage takes 3 bytes + fractional seconds.
DATETIME ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ The format is 'yyyy-mm-dd hh:mm:ss'. Storage takes 5 bytes + fractional seconds.
TIMESTAMP(m) ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ TC 'YYYY-MM-DD HH:MM:SS' is the format used. Storage takes 4 bytes plus fractional seconds.

String Data Type 

The string data type is used to store binary data and plain text, such as pictures, files, and other types of data. Using pattern matching techniques such as the LIKE operator and Regular Expressions, MySQL can search for and compare string values.

DATATYPE DESCRIPTION
CHAR(size) A STRING WITH A SET LENGTH (can contain letters, numbers, and special characters). The size argument determines the character length of the column, which can range from 0 to 255. 1 is the default value.
VARCHAR(size) A STRAIN OF VARIABLE LENGTH (can contain letters, numbers, and special characters). The size option sets the maximum length of a column in characters, which can range from 0 to 65535.
BINARY (size) CHAR() is the same as CHAR(), but it saves binary byte strings. The column length in bytes is specified by the size argument. 1 is the default value.
VARBINARY (size) VARCHAR() is similar to VARCHAR(), but it holds binary byte strings. The maximum column length in bytes is specified by the size argument.
TINYBLOB In the case of BLOBs (Binary Large Objects). 255 bytes is the maximum length.
TINYTEXT A string with a maximum length of 255 characters is stored in this variable.
TEXT(size) Max length of 65,535 bytes
BLOB(size) In the case of BLOBs (Binary Large Objects). Up to 65,535 bytes of data can be stored.
MEDIUMTEXT Holds a string of up to 16,777,215 characters in length.
MEDIUMBLOB In the case of BLOBs (Binary Large Objects). Up to 16,777,215 bytes of data can be stored.
LONGTEXT Holds a string of up to 4,294,967,295 characters in length.
LONGBLOB In the case of BLOBs (Binary Large Objects). Up to 4,294,967,295 bytes of data can be stored.
ENUM(val 1,val 2,val 3) A string object with only one possible value is picked from a list of options. An ENUM list can contain up to 65535 values. A blank value will be inserted if a value is entered that is not in the list. The values are sorted in the order in which they were entered.
SRT (val 1,val 2,val 3) From a list of possible values, a string object with 0 or more values is picked. A SET list can include up to 64 values.

 

Binary Large Object Data Types(BLOB)

BLOB is a data type in MySQL that can carry a large quantity of data. They are divided into four types based on the maximum length of data that they can carry.

Data Type Syntax Maximum Size
TINYBLOB 225 bytes
BLOB 65,535 bytes
MEDIUMBLOB 16,777,215 bytes
LONGBLOB 4 Gb or 4,294,967,295 bytes

FAQs

  1. What is the maximum length of the varchar column?
    The maximum length of the varchar column is up to 65,535 bytes. 
     
  2. Which “text-type” has the maximum number of bytes?
    The long text has the maximum number of bytes and that is 4Gb.
     
  3. What is the maximum length of the char columns?
    The maximum length of the char column is 255 bytes.
     
  4. Which numeric datatype has the largest range?
    The datatype “Int” has the largest range.
     
  5. Which data type is more suitable for storing “documents” in Mysql?
    The “long” datatype is considered to be suitable for storing “documents” in Mysql.

Key Takeaways

In the above article, we have learned about maximum size, properties, and types of MySQL data types.

To study more about data types, refer to Abstract Data Types in C++.

Recommended Readings:

Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Learning Ninja :) 

Live masterclass