MySQL supports various data types that allow us to store different types of data in our tables. Each column in a database table is required to have a name and a data type.

Overview

When creating a table in SQL, the SQL developer needs to choose the kind of information that will be kept in each column. The data type is like a set of instructions for SQL, telling it what kind of information to expect in each column and how to handle that information. It helps SQL work with and understand the data stored in the columns.

Data types might have different names in different database. Also the size and other details may be different. We are going through version 8.

MySQL Data Types

In MySQL there are three main data types: string, numeric, and date and time.

(A) String Data Types

1: CHAR(size)

• A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters – can be from 0 to 255. Default is 1
• Example:

-- Creating a table with a CHAR column
CREATE TABLE ExampleTable (
    id INT,
    name CHAR(10),
    age INT
);

2: VARCHAR(size)

• A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters – can be from 0 to 65535. Unlike CHAR, VARCHAR only uses as much storage as needed for the actual data, so it is more space-efficient for variable-length strings.
• Example:

-- Creating a table with a VARCHAR column
CREATE TABLE ExampleTable (
    id INT,
    name VARCHAR(50),
    age INT
);

3: BINARY(size)

• Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1.
• This data type is useful when you want to store binary data, such as raw binary or hexadecimal representations. Keep in mind that the specified size is the fixed length, and the actual length of the stored binary data may vary.
• Example:

-- Creating a table with a BINARY column
CREATE TABLE ExampleBinaryTable (
    id INT,
    binary_data BINARY(8),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleBinaryTable (id, binary_data, description) VALUES
(1, 0x48656C6C6F, 'Hello'),  -- Hexadecimal representation of 'Hello'
(2, 0x576F726C64, 'World');  -- Hexadecimal representation of 'World'

4: VARBINARY(size)

• Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. This data type is useful when you want to store variable-length binary data, and the specified size is the maximum length the column can hold. The actual length of the stored binary data may vary.
• Example:

-- Creating a table with a VARBINARY column
CREATE TABLE ExampleVarBinaryTable (
    id INT,
    varbinary_data VARBINARY(10),
    description VARCHAR(50)
);

5: TINYBLOB

• For BLOBs (Binary Large Objects). Max length: 255 bytes.
• It’s important to note that TINYBLOB is suitable for very small binary data, and if you need to store larger binary data, you might consider using other BLOB types like BLOB, MEDIUMBLOB, or LONGBLOB.
• Example:

-- Creating a table with a TINYBLOB column
CREATE TABLE ExampleTinyBlobTable (
    id INT,
    tinyblob_data TINYBLOB,
    description VARCHAR(50)
);

6: TINYTEXT

• Holds a string with a maximum length of 255 characters.
• TINYTEXT is suitable for small text strings, and if you need to store larger text data, you might consider using other text types like TEXT, MEDIUMTEXT, or LONGTEXT.
• Example:

-- Creating a table with a TINYTEXT column
CREATE TABLE ExampleTinyTextTable (
    id INT,
    tinytext_data TINYTEXT,
    description VARCHAR(50)
);

7: TEXT(size)

• Holds a string with a maximum length of 65,535 bytes.
• In MySQL, the TEXT data type is used to store variable-length text strings. Unlike VARCHAR, TEXT does not require a size specification, as it can hold larger amounts of text data.
• Example:

-- Creating a table with a TEXT column
CREATE TABLE ExampleTextTable (
    id INT,
    text_data TEXT,
    description VARCHAR(50)
);

8: BLOB(size)

• For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data.
• In MySQL, the BLOB(size) data type is used to store binary large objects (BLOBs) with a specified maximum size.
• Example:

-- Creating a table with a BLOB column
CREATE TABLE ExampleBlobTable (
    id INT,
    blob_data BLOB(100),
    description VARCHAR(50)
);

9: MEDIUMTEXT

• Holds a string with a maximum length of 16,777,215 characters.
• The MEDIUMTEXT data type is used to store variable-length text strings with a larger capacity compared to TEXT.
• Example:

-- Creating a table with a MEDIUMTEXT column
CREATE TABLE ExampleMediumTextTable (
    id INT,
    mediumtext_data MEDIUMTEXT,
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleMediumTextTable (id, mediumtext_data, description) VALUES
(1, 'This is a short text.', 'Short Text'),
(2, 'Lorem ipsum dolor sit amet, at scelerisque sem fringilla.', 'Long Text');

10: MEDIUMBLOB

• For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data.
• The MEDIUMBLOB data type is used to store variable-length binary large objects (BLOBs) with a larger capacity compared to BLOB.

11: LONGTEXT

• Holds a string with a maximum length of 4,294,967,295 characters.
• The LONGTEXT data type is used to store variable-length text strings with a very large capacity.

12: LONGBLOB

• For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data.

13: ENUM (value1, value2, value3, …)

• A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them.
• Example:

-- Creating a table with an ENUM column
CREATE TABLE ExampleEnumTable (
    id INT,
    status ENUM('Active', 'Inactive', 'Pending'),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleEnumTable (id, status, description) VALUES
(1, 'Active', 'Product 1'),
(2, 'Inactive', 'Product 2'),
(3, 'Pending', 'Product 3');

14: SET (value1, value2, value3, …)

• A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list.
• SET data type is used to store a set of values chosen from a predefined list. Each column declared with the SET data type can have zero or more of the listed values.
• Example:

-- Creating a table with a SET column
CREATE TABLE ExampleSetTable (
    id INT,
    options SET('Option1', 'Option2', 'Option3', 'Option4'),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleSetTable (id, options, description) VALUES
(1, 'Option1,Option2', 'Product 1'),  -- Selecting Option1 and Option2
(2, 'Option3', 'Product 2'),           -- Selecting only Option3
(3, 'Option2,Option4', 'Product 3');   -- Selecting Option2 and Option4

(B) Numeric Data Types

1: BIT(size)

• A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1.
• Note that the b prefix is used to indicate a binary literal in MySQL. The size of the BIT column determines the number of bits that can be stored. In this example, a BIT(4) column allows storing up to four bits.
• Example:

-- Creating a table with a BIT column
CREATE TABLE ExampleBitTable (
    id INT,
    flags BIT(4),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleBitTable (id, flags, description) VALUES
(1, b'1010', 'Record 1'),   -- Binary representation of 1010
(2, b'1101', 'Record 2'),   -- Binary representation of 1101
(3, b'0011', 'Record 3');   -- Binary representation of 0011

2: TINYINT(size)

• A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255).
• The TINYINT data type allows you to save storage space when dealing with small integer values. If you need a larger range, you can consider other integer types like SMALLINT, MEDIUMINT, or INT, depending on your specific requirements.
• Example:

-- Creating a table with a TINYINT column
CREATE TABLE ExampleTinyIntTable (
    id INT,
    quantity TINYINT,
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleTinyIntTable (id, quantity, description) VALUES
(1, 5, 'Product 1'),   -- Storing a small integer value (5)
(2, 3, 'Product 2'),   -- Storing another small integer value (3)
(3, 7, 'Product 3');   -- Storing another small integer value (7)

3: BOOL

• Zero is considered as false, nonzero values are considered as true.
• Keep in mind that MySQL doesn’t have a dedicated BOOL type, and BOOLEAN is used as a synonym for TINYINT(1) for compatibility reasons. You can use TINYINT(1) if you prefer. The key is to use a small integer type with a width of 1 if you want to represent Boolean values.
• Example:

-- Creating a table with a BOOLEAN column
CREATE TABLE ExampleBooleanTable (
    id INT,
    is_active BOOLEAN,
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleBooleanTable (id, is_active, description) VALUES
(1, 1, 'Product 1'),   -- Storing a boolean value (1 for true)
(2, 0, 'Product 2'),   -- Storing a boolean value (0 for false)
(3, 1, 'Product 3');   -- Storing a boolean value (1 for true)

4: BOOLEAN

• It equal to BOOL

5: SMALLINT(size)

• A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255).
• The SMALLINT data type is suitable for storing small integer values that may require a larger range than TINYINT. If you need an even larger range, you can consider other integer types like MEDIUMINT or INT, depending on your specific requirements.
• Example:

-- Creating a table with a SMALLINT column
CREATE TABLE ExampleSmallIntTable (
    id INT,
    quantity SMALLINT,
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleSmallIntTable (id, quantity, description) VALUES
(1, 300, 'Product 1'),   -- Storing a small integer value (300)
(2, 150, 'Product 2'),   -- Storing another small integer value (150)
(3, 500, 'Product 3');   -- Storing another small integer value (500)

6: MEDIUMINT(size)

• A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255).
• The MEDIUMINT data type is used to store medium-sized integer values.
• Example:

-- Creating a table with a MEDIUMINT column
CREATE TABLE ExampleMediumIntTable (
    id INT,
    quantity MEDIUMINT,
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleMediumIntTable (id, quantity, description) VALUES
(1, 50000, 'Product 1'),   -- Storing a medium-sized integer value (50,000)
(2, 30000, 'Product 2'),   -- Storing another medium-sized integer value (30,000)
(3, 70000, 'Product 3');   -- Storing another medium-sized integer value (70,000)

7: INT(size)

• A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255).
• The INT data type is used to store integer values. The optional size parameter can be specified to indicate the display width of the field.
• Example:

-- Creating a table with an INT column
CREATE TABLE ExampleIntTable (
    id INT,
    quantity INT(10),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleIntTable (id, quantity, description) VALUES
(1, 2000000000, 'Product 1'),   -- Storing an integer value (2,000,000,000)
(2, 1000000000, 'Product 2'),   -- Storing another integer value (1,000,000,000)
(3, 3000000000, 'Product 3');   -- Storing another integer value (3,000,000,000)

8: INTEGER(size)

• It equal to INT(size).
• In MySQL, there is no specific INTEGER data type with an optional size parameter as there is with INT. The INT data type is commonly used to represent integer values, and its size is determined by the system architecture (32-bit or 64-bit).

9: BIGINT(size)

• A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255).
• In MySQL, the BIGINT data type is used to store large integer values. The optional size parameter can be specified to indicate the display width of the field, but it doesn’t affect the range or storage size of the BIGINT data type.
• Example:

-- Creating a table with a BIGINT column
CREATE TABLE ExampleBigIntTable (
    id INT,
    quantity BIGINT(20),
    description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleBigIntTable (id, quantity, description) VALUES
(1, 2000000000000000000, 'Product 1'),   -- Storing a large integer value (2 quintillion)
(2, 1000000000000000000, 'Product 2'),   -- Storing another large integer value (1 quintillion)
(3, 3000000000000000000, 'Product 3');   -- Storing another large integer value (3 quintillion)

10: FLOAT[(M,D)]

• Where M represents the total number of digits, and D represents the number of digits after the decimal point. If D is omitted, it defaults to 0.
• FLOAT data type is suitable for storing numbers that require decimal precision, such as temperature, weight, price, etc. It can provide higher precision and a wider range compared to integer types, which can only store integer values.
• Example:

-- Creating a table
CREATE TABLE products (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price FLOAT(8,2) NOT NULL,
    PRIMARY KEY (id)
);

-- Inserting data into the table
INSERT INTO products (name, price) VALUES
    ('Product A', 12.99),
    ('Product B', 29.95),
    ('Product C', 8.75);

11: DOUBLE(size, d)

• A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter.
• The MySQL DOUBLE data type is used to store double-precision floating-point numbers, which are 64-bit (8-byte) IEEE floating-point numbers. The DOUBLE data type allows for a wider range of values to be stored compared to the FLOAT data type.
• Example:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DOUBLE(10,2)
);

-- Inserting data into the table
INSERT INTO products (id, name, price) VALUES
(1, 'Product A', 19.99),
(2, 'Product B', 12.49);

12: DECIMAL(size, d)

• An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
• For example, DECIMAL(10,2) indicates that there can be a maximum of 10 digits, with 2 digits after the decimal point. Therefore, the maximum value is 99999999.99, and the minimum value is -99999999.99.
• Example:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  total_amount DECIMAL(10,2)
);

-- Inserting data into the table
INSERT INTO orders VALUES (1, 99.99);
INSERT INTO orders VALUES (2, 499.50);
INSERT INTO orders VALUES (3, 1099.00);

13: DEC(size, d)

• It equal to DECIMAL(size, d).

(C) Date and Time Data Types

1: DATE

• A date. Format: YYYY-MM-DD. The supported range is from ‘1000-01-01’ to ‘9999-12-31’.
• The DATE data type is used to store date values without a time component.
• Example:

-- Creating a table with a DATE column
CREATE TABLE ExampleDateTable (
    id INT,
    event_date DATE,
    event_description VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO ExampleDateTable (id, event_date, event_description) VALUES
(1, '2023-08-15', 'Event 1'),   -- Storing a date value
(2, '2023-08-20', 'Event 2'),   -- Storing another date value
(3, '2023-08-25', 'Event 3');   -- Storing another date value

2: DATETIME (fsp)

• A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time.
• The DATETIME data type is commonly used for storing data with date and time information. <fsp> represents fractional seconds precision, which can be any value from 0 to 6. If fractional seconds precision is not specified, it defaults to 0.
• Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME
);

-- Inserting data into the table
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1, 101, '2022-01-01 10:30:00');

3: TIMESTAMP (fsp)

• A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition.
• fractional_seconds is optional and represents the number of digits for fractional seconds. If fractional_seconds is not specified, it defaults to 0.
• Example:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  order_status VARCHAR(20) NOT NULL
);

-- Inserting data into the table
INSERT INTO orders (customer_name, order_status)
VALUES ('John Smith', 'Pending'),
       ('Jane Doe', 'Complete'),
       ('Bob Johnson', 'Pending');

4: TIME (fsp)

• A time. Format: hh:mm:ss. The supported range is from ‘-838:59:59’ to ‘838:59:59’.
• The TIME data type is commonly used to store time durations or the time when an event occurs.
• Example:

CREATE TABLE employee (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  work_time TIME,
  PRIMARY KEY (id)
);

-- Inserting data into the table
INSERT INTO employee (name, work_time) VALUES ('Akki', '07:20:00');

5: YEAR

• A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
  MySQL 8.0 does not support year in two-digit format.
• Example:

CREATE TABLE users (
  id INT PRIMARY KEY,
  birth_year YEAR(4)
);

-- Inserting data into the table
INSERT INTO users (id, birth_year)
VALUES
  (1, 1890),
  (2, 1885),
  (3, 2000);

As we can see MySQL supports multiple data types, and this page summarizes the common data types in MySQL.