Understanding MySQL Data Types

MySQL supports a variety of data types that allow you to define the type of data that can be stored in each column of a table. These data types help enforce data integrity, optimize storage, and support efficient data retrieval.

INTEGER

There are several integer data types to choose from MySQL. The available integer data types are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. Each data type has different storage requirements and can accommodate varying ranges of integer values.

Here's a summary of the integer data types and their ranges:

1. TINYINT:

   • Size: 1 byte

   • Range: 0 to 255 (or -128 to 127 if negative numbers are supported)

2. SMALLINT:

   • Size: 2 bytes

   • Range: 0 to 65,535 (if negative numbers aren't supported)

3. MEDIUMINT:

   • Size: 3 bytes

   • Range: 0 to 16,777,215 (if negative numbers aren't supported)

4. INT:

   • Size: 4 bytes

   • Range: 0 to 4,294,967,295 (if negative numbers aren't supported)

5. BIGINT:

   • Size: 8 bytes

   • Range: 0 to 18,446,744,073,709,551,615 (if negative numbers aren't supported)

The phrase negative numbers refers to the sign of the numeric values that can be stored in a column. In MySQL, numeric data types such as INT can store both positive and negative numbers by default. However, there are situations where one might want to restrict a column to only store non-negative (i.e., zero and positive) values.

In the example:

CREATE TABLE books (
    book_id INT(11) PRIMARY KEY,
    title VARCHAR(255),
    number_of_pages SMALLINT UNSIGNED
);

The UNSIGNED attribute is used with the SMALLINT data type for the number_of_pages column. The UNSIGNED attribute means that the column will only store non-negative values (i.e., zero and positive integers). If a negative value is inserted into this column, it will result in an error.

The notation INT(11) in MySQL does not control the range of data that can be stored or the storage size of the INT data type. It is a common misconception that the number within the parentheses specifies the maximum value or range for the INT column. The number 11 in INT(11) is the display width, not the range of the integer. The display width is used to hint to applications about the preferred width for displaying the integer values when the column is part of query results.

DECIMAL

The DECIMAL data type in MySQL is used to store fixed-point numbers, which are numbers with a fixed number of digits both to the left and right of the decimal point. This makes it suitable for representing monetary values or other situations where precision is crucial.

DECIMAL(precision, scale);

Here precision is the total number of digits (both to the left and right of the decimal point) and scale is the number of digits to the right of the decimal point. The storage size of Decimal depends on the specified precision. It can vary, but it is generally more space-consuming than other numeric types due to its fixed-point nature.

FLOAT

In MySQL, the FLOAT data type is used to store approximate numeric values with floating-point precision. It is suitable for scenarios where a wide range of values is needed, and precision is not the primary concern. The storage size for FLOAT is 4 bytes. FLOAT provides a good compromise between range and precision, but it may introduce rounding errors due to its floating-point nature.

DOUBLE

In MySQL, the DOUBLE data type is used to store approximate numeric values with double-precision floating-point precision. It is similar to the FLOAT data type but provides a larger range and higher precision. The storage size for DOUBLE is 8 bytes, which is double the size of FLOAT. DOUBLE provides a larger range and higher precision compared to FLOAT. It is suitable for scenarios where a wide range of values with more significant precision is needed.

CHAR

In MySQL, the CHAR data type is used to store fixed-length character strings. It's suitable for situations where the length of the string is constant.

CHAR(length);

Here the length is the fixed length of the string. The storage size for a CHAR column is fixed and equal to the specified length. It does not depend on the actual length of the stored string. If the stored string is shorter than the specified length, it is padded with spaces to reach the fixed length. Retrieval and storage operations are generally faster for CHAR columns compared to variable-length string types like VARCHAR. This is because the storage size is constant, and there's no need to store length information. CHAR is suitable for scenarios where the length of the string is constant and known in advance.

VARCHAR

In MySQL, the VARCHAR data type is used to store variable-length character strings. Unlike CHAR, which stores fixed-length strings, VARCHAR adapts its storage size based on the actual length of the stored string.

VARCHAR(length);

Here length is the maximum length of the string. The storage size for a VARCHAR column is variable and depends on the actual length of the stored string. It only uses storage space for the characters in the string and a small amount of additional overhead for storing the length of the string. Unlike CHAR, VARCHAR does not pad the stored string with spaces. It only stores the actual characters, making it more storage-efficient when dealing with varying-length strings. Retrieval and storage operations for VARCHAR may be slightly slower than CHAR due to the need to store and retrieve length information. VARCHAR is suitable for scenarios where the length of the string can vary, and you want to optimize storage efficiency.

It's important to choose between CHAR and VARCHAR based on specific requirements. Use CHAR when the length of the string is constant and known in advance, and use VARCHAR when the length can vary and want to optimize for storage efficiency.

If a value is attempted to insert into a VARCHAR/CHAR column in MySQL that exceeds the specified maximum length, then one of the two scenarios can appear depending on the MySQL SQL mode. Here are the common scenarios:

1. Strict SQL Mode:

   • If MySQL is operating in strict SQL mode (which is the default behavior for recent MySQL versions), attempting to insert a value that exceeds the specified length will result in an error, and the insertion will fail.

   • You'll receive a "Data too long for column" error.

2. Non-Strict SQL Mode:

   • If MySQL is not in strict SQL mode (the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL mode is disabled), MySQL will attempt to truncate the value to fit within the specified length.

   • No error will be generated, but the inserted value will be truncated.

BINARY

A binary data type is used to store binary data, meaning sequences of bytes.

CREATE TABLE binary_data (
    id INT,
    hash_value BINARY(16)
);

Here, BINARY(length) means the fixed length of the binary data. The storage size for a BINARY column is fixed and equal to the specified length. If the stored binary data is shorter than the specified length, it is padded with trailing zeros to reach the fixed length. BINARY is suitable for fixed-length binary data, such as checksums or hash values.

VARBINARY

Like BINARY, VARBINARY is also used to store binary data.

CREATE TABLE varbinary_data (
    id INT,
    image_data VARBINARY(255)
);

Here the length specifies the maximum length of the variable-length binary data. The storage size for a VARBINARY column depends on the actual length of the stored data. Unlike BINARY, VARBINARY does not pad the stored data with trailing zeros. VARBINARY is suitable for variable-length binary data, such as images, documents, or other binary content.

TEXT

In MySQL, the TEXT data type is used to store variable-length character strings. It is suitable for storing large amounts of text data. The storage size for a TEXT column depends on the actual length of the stored text. Unlike fixed-length character string types (e.g., CHAR), TEXT does not pad the stored text with spaces.

In MySQL, there are several variations of the TEXT data type, each designed to handle different amounts of text and offer different storage characteristics. Here are the variations:

1. TINYTEXT:

   • Maximum Length: 255 characters.

   • Usage: Suitable for short to moderately short text.

2. TEXT:

   • Maximum Length: 65,535 characters (64KB)

   • Usage: Suitable for larger bodies of text, such as paragraphs or document content.

3. MEDIUMTEXT:

   • Maximum Length: 16,777,215 characters (16 MB)

   • Usage: Suitable for storing very long text, such as lengthy articles or significant amounts of content.

4. LONGTEXT:

   • Maximum Length: 4,294,967,295 characters (4GB)

   • Usage: Suitable for extremely long text, such as large documents or substantial amounts of textual data.

TEXT data is not stored in the memory of the database server. When querying TEXT data, MySQL needs to retrieve it from the disk, resulting in a considerably slower process compared to CHAR and VARCHAR.

BLOB

In MySQL, the BLOB (Binary Large Object) data type is used to store binary data, such as images, audio files, videos, or any other type of raw binary content. The storage size for a BLOB column depends on the actual length of the stored binary data. Similar to VARBINARY, BLOB does not pad the stored data with trailing zeros.

In MySQL, there are several variations of the BLOB (Binary Large Object) data type, each designed to handle different amounts of binary data and offering different storage characteristics.

1. TINYBLOB:

   • Maximum Length: 255 bytes.

   • Usage: Suitable for short to moderately short binary data.

2. BLOB:

   • Maximum Length: 65,535 bytes.

   • Usage: Suitable for larger binary objects, such as images or audio files.

3. MEDIUMBLOB:

   • Maximum Length: 16,777,215 bytes.

   • Usage: Suitable for storing moderate to large binary data.

4. LONGBLOB:

   • Maximum Length: 4,294,967,295 bytes.

   • Usage: Suitable for extremely large binary objects.

ENUMS

In MySQL, the ENUM data type is used to define a column that can have a value chosen from a predefined list of possible values.

CREATE TABLE user (
    id INT,
    gender ENUM('Male', 'Female', 'Other'),
    -- other columns...
);

In this example, the gender column can only have one of the three specified values: 'Male', 'Female', or 'Other' which means these are the possible values of the gender column.

The storage size for an ENUM column is based on the number of different enumeration values. It occupies one or two bytes, depending on the number of enumeration values specified. ENUM values are stored internally as integers representing the position of the value in the enumeration list. In the example the first value specified in the ENUM list which is 'Male' has a position of 1, the second value has a position of 2, and so on.

SELECT gender, gender+0 AS index_number FROM user;

+--------+--------------+
| gender | index_number |
+--------+--------------+
| Male   |            1 |
| Female |            2 |
| Other  |            3 |
+--------+--------------+

In MySQL, when we perform an arithmetic operation like addition (+) on an ENUM column, it implicitly converts the ENUM value to its corresponding numeric index. Here, in the example each gender value is displayed along with its corresponding numeric index in the index_number column.

ENUM is suitable when we have a fixed set of possible values for a column and want to restrict the input to only those values. It provides a convenient way to enforce data integrity by ensuring that only valid values are stored in the column.

When operating in non-strict SQL mode, MySQL handles invalid values inserted into an ENUM column differently. In such cases, MySQL substitutes the invalid value with an empty string (' ') and assigns it the numeric index 0 during insertion. However, enabling SQL strict mode alters this behavior. In strict mode, if we attempt to insert an invalid ENUM value, MySQL will raise an error instead of substituting it with an empty string.

MySQL arranges ENUM values according to their index numbers, meaning the order of members is determined by their integer position value in the enumeration list during definition.

While ENUM provides a convenient way to restrict input values, it's important to note that modifying the list of enumeration values after creating the column can be cumbersome and may involve altering the table structure. ENUM may not be the best choice for columns with a large number of possible values or when frequent modifications to the list of values are expected.

DATES

In MySQL, there are several data types specifically designed for storing dates and times. These include:

DATE:

The DATE data type is used for storing dates in the format YYYY-MM-DD. It stores only the date component without any time information. It is a compact three-byte data type capable of accommodating a broad range of dates spanning from the year 1,000 to 9,999. MySQL utilizes the YYYY-MM-DD format exclusively for storing date values, and this format cannot be altered. Even if we prefer an alternative format like MM-DD-YYYY, MySQL maintains the standard format and offers the DATE_FORMAT function to customize the date display.

MySQL stores the year component of date values using four digits. However, if we input two-digit year values, MySQL applies the following conversions:

• Years within the range 00-69 are interpreted as belonging to the years 2000-2069.

• Years within the range 70-99 are interpreted as belonging to the years 1970-1999.

It's important to note that using two-digit year values can lead to ambiguity, so it's recommended to avoid them.

TIME:

The TIME data type is used for storing time values in the format HH:MM:SS. It stores only the time component without any date information.

The TIME data type in MySQL is designed for storing time values, including hours, minutes, and seconds. It can accommodate durations exceeding 24 hours, making it suitable for storing intervals. While it's capable of representing a range of up to 10 days in terms of hours, minutes, and seconds, it's not frequently utilized in practice.

A TIME value spans from -838:59:59 to 838:59:59, covering a wide range of hours, minutes, and seconds. Moreover, it can also include a fractional seconds component with precision up to microseconds (6 digits).

A TIME value requires 3 bytes for storage. However, if the TIME value includes fractional second precision, it will necessitate additional bytes depending on the number of digits in the fractional second precision.

DATETIME:

The DATETIME data type is used for storing both date and time values in the format YYYY-MM-DD HH:MM:SS. It includes both the date and time components.

By default, DATETIME values span from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL allocates 5 bytes for storing a DATETIME value. Moreover, a DATETIME value can accommodate a trailing fractional second up to microseconds, following the format YYYY-MM-DD HH:MM:SS[.fraction], for example, 2015-12-20 10:01:00.999999 which necessitates 8 bytes of storage where 5 bytes for the date and time portion 2015-12-20 10:01:00 and 3 bytes for the fractional seconds ".999999". Conversely, 2015-12-20 10:01:00.9 only requires 6 bytes, with 1 byte allocated for the fractional second precision.

TIMESTAMP:

The TIMESTAMP data type is used for storing date and time values, similar to DATETIME. However, TIMESTAMP has a narrower range of values and also automatically updates to the current timestamp when a row is inserted or updated.

The TIMESTAMP value spans from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Upon inserting a TIMESTAMP value into a table, MySQL automatically converts it from our connection's time zone to UTC before storing it. When querying a TIMESTAMP value, MySQL reverses the process, converting the stored UTC value back to our connection's time zone. This conversion is specific to TIMESTAMP values and does not apply to other temporal data types like DATETIME. By default, the connection time zone is set to the MySQL Server's time zone. However, we can also choose to connect using a different time zone if needed.

YEAR:

The YEAR data type is used for storing year values in the format YYYY. It stores only the year component without any month or day information. YEAR is the most space-efficient option for storing years ranging from 1901 to 2155. However, it's not widely utilized in practice.

JSON

The JSON data type in MySQL allows to storage JSON (JavaScript Object Notation) documents in a structured format within a column of a table. It provides native support for working with JSON data, enabling efficient storage, retrieval, and manipulation of JSON documents directly within the database.

JSON data is stored in a binary format that allows for efficient storage and retrieval. JSON documents are validated upon insertion to ensure they adhere to the JSON syntax rules. Otherwise, it will throw an error. Also, MySQL provides a rich set of functions and operators for querying JSON data, allowing it to perform tasks such as extracting values, searching, filtering, and aggregating JSON documents.

The JSON data type offers flexibility in schema design, allowing to storage of semi-structured or unstructured data without the need for predefined schemas. This means that with the JSON data type in MySQL, we can store data that doesn't fit neatly into traditional, rigidly defined database schemas. Instead of requiring a fixed structure like traditional relational tables, JSON data allows for semi-structured or unstructured data to be stored directly in the database without the need for predefined schemas. This flexibility is particularly useful when dealing with data that may vary in structure or doesn't conform to a strict schema, such as data from web APIs or applications with dynamic data models.

In MySQL, indexing of JSON columns is not supported. This means that querying JSON documents directly may lead to full table scans, which can be inefficient for large datasets. While JSON columns cannot be indexed directly, we can improve search performance by creating additional columns containing extracted values from the JSON data. These new columns can then be indexed for faster searches.