What are the main data types used in MySQL? — MySQL Interview Question #4

In MySQL, there are several data types that can be used to store different types of data. These data types can be classified into the following categories:

1. Numeric data types: These data types are used to store numeric values. Some examples of numeric data types are:

- TINYINT: 1-byte integer, ranged from -128 to 127 or from 0 to 255 (unsigned)

- SMALLINT: 2-byte integer, ranged from -32,768 to 32,767 or from 0 to 65,535 (unsigned)

- MEDIUMINT: 3-byte integer, ranged from -8,388,608 to 8,388,607 or from 0 to 16,777,215 (unsigned)

- INT: 4-byte integer, ranged from -2,147,483,648 to 2,147,483,647 or from 0 to 4,294,967,295 (unsigned)

- BIGINT: 8-byte integer, ranged from -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807 or from 0 to 18,446,744,073,709,551,615 (unsigned)

- DECIMAL: Fixed-point decimal, with configurable precision and scale

2. Date and time data types: These data types are used to store date and time related values. Some examples of date and time data types are:

- DATE: Date value, with format as "YYYY-MM-DD"

- TIME: Time value, with format as "HH:MM:SS"

- DATETIME: Date and time value, with format as "YYYY-MM-DD HH:MM:SS"

- TIMESTAMP: Timestamp value, with format as "YYYY-MM-DD HH:MM:SS"

3. String data types: These data types are used to store string values. Some examples of string data types are:

- CHAR: Fixed-length string, with configurable length

- VARCHAR: Variable-length string, with configurable maximum length

- TEXT: Variable-length string, with maximum length of 65,535 characters

- BLOB: Binary large object, with maximum length of 65,535 bytes

4. Other data types: There are some other data types that can be used to store specific types of data. Some examples of other data types are:

- ENUM: Enumeration, with a defined set of possible values
- SET: Set of values, each of which can be either present or absent
- BOOLEAN, BOOL: Boolean value, with possible values of TRUE or FALSE

Here’s an example of how to create a table with different data types:

CREATE TABLE my_table (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age TINYINT UNSIGNED,
    birth_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    PRIMARY KEY (id)
);

In this example, we have used different data types to define the columns of the "my_table" table. There is an id column which is of type INT and has been made as the primary key. The first_name and last_name columns are of type VARCHAR and can hold string values of up to 50 characters. The age column is of type TINYINT UNSIGNED and can hold integer values ranging from 0 to 255. The birth_date column is of type DATE and can hold date values. The created_at column is of type TIMESTAMP and has been assigned a default value of the current timestamp. The is_active column is of type BOOLEAN and has been assigned a default value of TRUE.