Home » MySQL

MySQL Data Types

MySQL | Data Types: Learn about the MySQL data types and their categories.
Submitted by Apurva Mathur, on September 20, 2022

What are MySQL Data Types?

We know that everything we write in programming language takes someplace in the memory, this is what data types are, data types are the classification in which each determines the types of a value a variable has. It tells the machine how a particular type of variable will use the memory. Without proper knowledge of data types, you cannot perform any task, it is a vital and one of the basic concepts when it comes to any programming language.

Importance of Data Types

Data types are essential for any programming language; a proper knowledge of data types makes your program faster as proper size will be allotted to the variable which will reduce the space complexity.

In MySQL it is important to select an appropriate data type for each column as it shows how professional you are also it improves the performance and data integrity as it makes sure the correct type of data is stored in the column.

MySQL supports many data types, you can store any type of data using these data types, and the data types in MySQL have several categories such as;

Numeric Data Types
String Data Types
Date and Time Data Types
JSON Data Types
Spatial Data Types

MySQL Numeric Data Types

A data type is a classification that determines the types of value a variable has. It tells the machine how a particular type of variable will use the memory.

Let us the two terms which we commonly use while defining any data type.

Signed Number: in simple words, signed numbers are the numbers that contain both positive and negative values. There is no such restriction on a signed number to always be positive or negative. So if any data type is signed which means it can have both the values positive as well as negative
Unsigned Number: Unsigned numbers are always positive by default. It can never be negative.

MYSQL supports many numeric data types

BIT (m): It stores bit values. The default value is 1, m can hold the value from 1 to 64.
TINY INT: It is the smallest integer in MYSQL, it can be both signed and unsigned which means the value stored within this data type can be positive or negative. If the number is signed then the range is from -128 to 127, whereas the unsigned range is from 0 to 255.
SMALL INT: It is a small integer, which can be signed or unsigned. The range for signed small int is -32768 to 32767 whereas the range for the unsigned small int data type is 0 to 65535. It requires 2 bytes of storage.
MEDIUM INT: This type of data type can be also signed or unsigned. The range for signed MEDIUM INT is 8388608 to 8388607whereas the range for unsigned MEDIUM INT data type is 0 to 16777215. It requires 3 bytes of storage.
INT: It is the general which we have used in other programming languages. This type of data type can be also signed or unsigned. The range for signed INT is -2147483648 to 2147483647whereas the range for unsigned INT data type is 0 to 4294967295. It requires 4 bytes of storage.
BIGINT: This data type is bigger than the INT data type. This type of data type can be also signed or unsigned. The range for signed INT is -9223372036854775808 to 9223372036854775807 whereas the range for unsigned INT data type is 0 to 18446744073709551615. It requires 8 bytes of storage.
BOOL: We have used this type of data type many times in other programming languages here also it only has two values true or false, where 1 is considered as true and 0 as false.
BOOLEAN: It works the same as the BOOL data type.
FLOAT (p): A floating data type. MYSQL has this p-value which is used to determine the type of data type (float or double). There is a situation, if p has a range from 0 to 23 then this will be considered a float data type and if the range of p is from 25 to 53 then it becomes double.
DOUBLE (m,d): This data type cannot be unsigned. Here m displays the length and d determines the number of decimals. It requires 8 bytes of storage.
DECIMAL (m,d): This is fixed-point number. Here m is the total number of sized and d determines the number of decimals. The maximum number for m is 65 and d is 30.

MySQL String Data Types

When we store data in our database most of our data is of string type. We know that a string is simply a sequence of characters. MYSQL provides us wide variety of data types to store string-type variables.

TEXT: The range of this data type is from 0-65535, which means it can contain a string within the given range. This is not case sensitive data type.
BLOB: These are commonly known as binary large objects. This data type is really important when you have to store a large set of binary data. As we have seen images have large addresses, so to store them we can use this data type. BLOB is case-sensitive. It can store up to 65535 bytes of data.
CHAR: The range of this data type is from 0 to 255.
VARCHAR: This is a special type of string data type. It can contain a letter, numbers, and special characters. The range of this data type is from 0 to 65535.
TINYTEXT: The maximum length it can store is 255.
MEDIUMTEXT: This data type can store the 16777215 characters.
LONGTEXT: This data type can store 4294967295 characters.
MEDIUMBLOB: These are commonly known as binary large objects. This data type can store the 16777215 characters.
LONGBLOB: These are commonly known as binary large objects. This data type can store 4294967295 characters.
TINYBLOB: The range of this data type is from 0 to 255.
BINARY: It is very similar to CHAR but this data type stores binary strings, the maximum size this data type can hold is 255
VARBINARY: This data type is similar to VARCHAR, but this stores binary strings, the maximum size which this can hold is 255 characters.
ENUM: ENUM is also a special data type as only chosen values can be only stored else the cell will be NULL. Suppose you have a column in which the user has to select some particular option in such case we can use the ENUM data type. This can have 65535 of value.
SET: 64 values can be there in this data type.

MySQL Date and Time Data Types

From varieties of data type MYSQL also provide a data type to store date and time. These data type stores date and time in different formats.

DATE: The format of this data type is YYYY:MM:DD, which means the year would be displayed first then the month, and then the date. This data type follows the range from '1000-01-01' to '9999-12-31'.
DATETIME: The format of this data type is YYYY:MM:DD HH:MM::SS .This data type follows the range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMESTAMP: It is displayed as 'YYYY-MM-DD HH:MM:SS', and has the range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' TC.
TIME: The format of this data type is HH:MM:SS, and has the range from '-838:59:59' to '838:59:59'.
YEAR: The value of this data type is 2 or 4, by default it stores 4 digits.

MySQL Spatial Data Types

This data type is very unique as this data type holds geometric values. Geometry also denotes geographic features. These spatial data types are as follows:

GEOMETRY: It is a spatial data type that can store any geometry value.
POINT: It represents a single location and values are stored in x and y coordinates.
POLYGON: It is represented by a multisided geometry, and stores the points that set of points.
LINESTRING: It stores the points that especially form a curve, if only two points are there then in such case it is represented as a line.
GEOMETRYCOLLECTION: As the name suggests, this data type stores a set of different geometric values.
MULTILINESTRING: This data type stores multiple line string values, it is a multi-curve geometry.
MULTIPOINT: This kind of data type stores multiple points' values.
MULTIPOLYGON: As simple as it sounds, this data type stores multiple polygon values.

MySQL Comments

MySQL CREATE DATABASE

Comments and Discussions!

Load comments ↻