What are MySQL data types?

MySQL, one of the leading relational database management systems, offers a variety of data types so that developers can store data of different types and formats. Data management is fundamental to any information system, and one of the most important aspects of data management is choosing the appropriate data type for each value stored in the database. Additionally, having a clear understanding of data types can help you write more efficient and scalable code, and avoid common mistakes related to data management.

In this article, we will explore the data types available in MySQL and discuss their specific characteristics and uses. In that vein, let’s start with an overview of numeric data types, followed by an analysis of character, datetime, binary, geometric, full-text, and Unicode data types. By understanding the different types of data available in MySQL, you can make informed decisions about how to store and manage data in your application.

Numeric data types

MySQL numeric data types are used to store numeric values ​​with different sizes and precisions.

1. INT : The INT data type is used to store integer values. See the example:

INSERT INTO my_values ​​(integer_value) VALUES (10);

2. BIGINT : BIGINT data type is similar to INT type but can store larger integer values, suitable for storing large integer values ​​and in this sense, used in tables with many rows and columns. See the example:

INSERT INTO my_values ​​(large_value) VALUES (9223372036854775808);

3. SMALLINT : The SMALLINT data type is used to store smaller integer values ​​and is used in tables with limited values. See the example:

INSERT INTO my_values ​​(small_value) VALUES (-32768);

4. TINYINT : The TINYINT data type is similar to the SMALLINT type, but can store integer values ​​in a range of -128 to 127. See example:

INSERT INTO my_values ​​(very_small_value) VALUES (-128);

5. DECIMAL : DECIMAL data type is used to store numeric values ​​with a specified precision, suitable for storing monetary and financial values. See the example:

INSERT INTO my_values ​​(decimal_value) VALUES (123.45);

6. FLOAT : FLOAT data type is used to store numeric values ​​with a specific precision. Thus, storing decimal number values ​​of up to 6 digits. See the example:

INSERT INTO my_values ​​(float_value) VALUES (3.1415926535897932385);

7. DOUBLE : The DOUBLE data type is similar to the FLOAT type, but it can store numeric values ​​with a higher precision, up to 15-16 digits. This way, it stores decimal values, such as financial and scientific values. See the example:

INSERT INTO my_values ​​(double_value) VALUES (3.14159265358979323846);

Character data types

MySQL’s character data types are used to store text and character data.

1. CHAR : CHAR data type is used to store fixed text data with a specific length. In this sense, it is used when creating tables to store text values ​​with the same size in all columns. See the example that creates a table “my_values” with a column “name” and stores texts of a maximum of 10 characters in length.

CREATE TABLE my_values ​​(
    name CHAR(10)
);

2. VARCHAR : The VARCHAR data type is similar to the CHAR type, but allows text values ​​to be variable, with different lengths in each column. As such, it is used when creating tables to store text values ​​with variable lengths. See the example below that creates a “my_values” table with an “email” column that can store texts of up to 200 characters in length, being flexible in terms of the size of the data stored:

CREATE TABLE my_values ​​(
    email VARCHAR(200)
);

3. TEXT : The TEXT data type is used to store long and complex text data, with lengths ranging from 1 to 4 GB. Thus, it stores content-heavy text values, such as blog articles and documents. See the example:

INSERT INTO my_values ​​(description) VALUES ('This is an example of a long text value');

4. BLOB : The BLOB data type is used to store binary data, such as images, videos and document files. In this sense, it stores large values ​​and can store up to 65,535 bytes of data. Thus, see the example:

INSERT INTO my_values ​​(image) VALUES (FILE('/path/to/image.jpg'));

Full-text data types

FULLTEXT :

The FULLTEXT data type is used to store text data and perform full-text queries. Thus, it supports full-text queries across multiple fields and allows the use of collision and proximity operations to find more accurate results.

CREATE TABLE books (
  id INT PRIMARY KEY,
  TEXT title,
  author TEXT,
  TEXT content,
  KEY(content)(FULLTEXT)
);

INSERT INTO books (id, title, author, content) VALUES (1, 'Book 1', 'Author 1', 'Content 1, Content 2, Content 3');

In this example, the “content” column is of type TEXT and is indexed with FULLTEXT. Thus, this allows MySQL to perform full-text queries on all columns indexed with FULLTEXT.

MySQL Date and Time Data Types

In MySQL date and time data types are used to store date and time values.

1. DATE : The DATE data type is used to store only the date without the time. Therefore, it is made up of 8 bytes and can store date values ​​in the range of 1000 to 9999 for the year and 0 to 65535 for the day of the month. See the example:

INSERT INTO my_values ​​(date) VALUES ('2022-03-14');

2. TIME : The TIME data type is used to store only the time without the date. Thus, see the example:

INSERT INTO my_values ​​(time) VALUES ('13:30:00');

3. DATETIME : The DATETIME data type is used to store date and time values ​​together. See the example:

INSERT INTO my_values ​​(date_time) VALUES ('2022-03-14 13:30:00');

4. TIMESTAMP : The TIMESTAMP data type is similar to the DATETIME type, but includes precision to the second. See the example:

INSERT INTO my_values ​​(date_time_seconds) VALUES ('2022-03-14 13:30:00');

MySQL binary data types

Supports MySQL several binary data types, each of which has its own characteristics and uses. Here they are:

1. BIT : The BIT data type is a simple binary type that can have a single value of 0 or 1. Thus, see example:

CREATE TABLE people (
  id INT PRIMARY KEY,
  BIT sex);

INSERT INTO people (id, gender) VALUES (1, 1);

2. BITMAP : The BITMAP data type is a more complex binary type that allows you to store multiple bit values ​​in a single column. See the example:

CREATE TABLE people (
  id INT PRIMARY KEY,
  hobbies BITMAP);

INSERT INTO people (id, hobbies) VALUES (1, 0b00100000);

3. SET : The SET data type is a binary type that allows you to store multiple bit values ​​in a single column. In this sense, SET is similar to BITMAP, but easier to use and offers better performance. See the example:

CREATE TABLE people (
  id INT PRIMARY KEY,
  hobbies SET);

INSERT INTO people (id, hobbies) VALUES (1, 'reading, cinema');

4. ENUM : The ENUM data type is a binary type that allows you to store a value from a predefined list of values. See the example:

CREATE TABLE colors (
  id INT PRIMARY KEY,
  color ENUM('green', 'blue', 'purple'));

INSERT INTO colors (id, color) VALUES (1, 'green');

Range data types

1. YEAR : We use the YEAR data type to store a year number, it stores values ​​from 1 to 9999. Therefore, it is often used to store the date of birth or the start date of a contract, for example:

CREATE TABLE pessoas (
  id INT PRIMARY KEY,
  nascimento YEAR);

INSERT INTO pessoas (id, nascimento) VALUES (1, 1990);

2. MONTH : We use the MONTH data type to store a month number. Thus, it stores values ​​from 1 to 12, for example:

CREATE TABLE pessoas (
  id INT PRIMARY KEY,
  nascimento MONTH);

INSERT INTO pessoas (id, nascimento) VALUES (1, 1);

3. DAY : We use the DAY data type to store a day of the week number. In this sense, it stores values ​​from 0 to 6, where 0 represents Sunday and 6 represents Saturday, for example:

CREATE TABLE pessoas (
  id INT PRIMARY KEY,
  nascimento DAY);

INSERT INTO pessoas (id, nascimento) VALUES (1, 0);

MySQL geometric data types

MySQL geometric data types are used to store spatial geometry data such as points, lines, and polygons.

1. POINT : The POINT data type is used to store a point on a surface. In this sense, represented by a tuple of values ​​(x, y), where x and y are the latitude and longitude values ​​of the point.

CREATE TABLE locations (
  id INT PRIMARY KEY,
  POINT position);

INSERT INTO locations (id, position) VALUES (1, (10.5, -69.5));

In this example, the “position” column is of type POINT and stores the location of a point on a surface. The value “(10.5, -69.5)” in this case represents the latitude and longitude of the point.

2. LINE : We use the LINE data type to store a line on a surface. Thus, represented by a sequence of points (x1, y1, x2, y2, …), where each point represents a point on the line. See the example:

CREATE TABLE borders (
  id INT PRIMARY KEY
  LINE border);

INSERT INTO borders (id, border) VALUES (1, ('0.0' ,'10.0', '10.10', '0.10', '0.0'));

Thus, in this example, the “border” column is of type LINE and stores the location of a line on a surface.

3. POLYGON : We use the POLYGON data type to store a polygon on a surface. Thus, In this sense, represented by a sequence of lines (x1, y1, x2, y2, …, xn, yn), where each line represents a point on the line.

CREATE TABLE polygons (
  id INT PRIMARY KEY,
  polygon POLYGON);

INSERT INTO polygons (id, polygon) VALUES (1, POLYGON ('LINESTRING(-10.5 -69.5, 0 -69.5, 0 -10.5, -10.5 -69.5)'));

In this example, the “polygon” column is of type POLYGON and stores the location of a polygon on a surface.

4. GEOMETRY : The GEOMETRY data type is a data type that supports several spatial geometry types, including POINT, LINE, and POLYGON. In this sense, we use it to store more complex spatial geometry data, such as curves and irregular polygons.

Unicode data types

1. UTF-8 : We use UTF-8 data type to store data in Unicode format in byte.

CREATE TABLE example_table (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (id)
);

In this example, we define the “name” column as type VARCHAR and uses the utf8mb4 character set, which handles blank characters up to 3 bytes, which includes most languages ​​and scripts.

2. UTF-16 : We use UTF-16 data type to store data in Unicode format in 16 bits. Thus, In this sense, we use it a lot in applications that require more floating point precision.

CREATE TABLE example_table (
  id INT NOT NULL AUTO_INCREMENT,
  name VARBINARY(65535) CHARACTER SET utf16 COLLATE utf16_general_ci,
  PRIMARY KEY (id)
);

In this example, we define the “name” column as VARBINARY type and uses the utf16 character set, which handles blank characters of up to 2 bytes.