MySQL Data Types

This chapter provides a structured overview of MySQL data types, including numeric, string, temporal, and other specialized types. Understanding these types is essential for esigning efficient and reliable database schemas.

Numeric Types

Integer Types

MySQL provides several integer types (e.g., TINYINT, INT, BIGINT) to store whole numbers. Historically, these types allowed an optional display width (M).

Key points:

The display width (M) defines the minimum display width, not the storage size.
It is only meaningful when used with:
- UNSIGNED: disallows negative values
- ZEROFILL: pads the number with leading zeros to match width M
If the actual value exceeds M, it is still stored correctly (no truncation).

⚠️ Important (MySQL 8+):

As of MySQL 8.0.17, integer display width is deprecated and ignored.

CREATE TABLE t_int(
    i1 INT,
    i2 INT(2) -- No effect without ZEROFILL
);

DESC t_int;

CREATE TABLE t_int2(
    i1 INT,
    i2 INT(2) UNSIGNED ZEROFILL
);

DESC t_int2;

INSERT INTO t_int2 VALUES(1234,1234);
SELECT * FROM t_int2;

INSERT INTO t_int2 VALUES(1,1);
SELECT * FROM t_int2;

👉 Takeaway: Avoid using (M) in modern MySQL. Focus on choosing the correct integer type and constraints instead.

Floating-Point and Fixed-Point Types

MySQL supports two categories for storing decimal numbers:

Floating-Point Types

FLOAT (single precision)
DOUBLE (double precision)

These types store approximate values and may introduce rounding errors due to binary representation.

Fixed-Point Type

DECIMAL

This type stores exact values and is ideal for financial or high-precision data.

Precision Definition `(M, D)`

M (precision): total number of digits
D (scale): number of digits after the decimal point
Must satisfy: 0 ≤ D ≤ M

Example:

DECIMAL(5,2) → range: -999.99 to 999.99

Behavior:

Extra decimal digits → rounded
Excess integer digits → error (Out of range)

CREATE TABLE t_double(
    d1 DOUBLE,
    d2 DOUBLE(5,2)
);

INSERT INTO t_double VALUES(2.5,2.5);
INSERT INTO t_double VALUES(2.5526,2.5526);
INSERT INTO t_double VALUES(2.5586,2.5586);

-- Error: integer part exceeds limit
INSERT INTO t_double VALUES(12852.5526,12852.5526);

CREATE TABLE t_decimal(
    d1 DECIMAL,      -- default (10,0)
    d2 DECIMAL(5,2)
);

INSERT INTO t_decimal VALUES(2.5,2.5);
SELECT * FROM t_decimal;

-- Decimal assigned to INT → fractional part truncated
INSERT INTO t_int2 VALUES(1.5,1.5);

👉 Best Practice:

Use DECIMAL for money and precise calculations
Use FLOAT/DOUBLE for scientific or approximate data

BIT Type

The BIT type is used to store binary values.

Length range: 1 to 64 bits
Storage is byte-aligned (minimum 1 byte)

Input formats:

Integer (e.g., 5)
Binary literal
Hexadecimal

Important constraints:

The value must fit within the defined number of bits

Display behavior:

Returned as binary by default
Use conversion functions for readability:
- BIN() → binary string
- CONV() → base conversion

CREATE TABLE t_bit(
    b1 BIT,       -- default: 1 bit
    b2 BIT(4)     -- range: 0000–1111
);

INSERT INTO t_bit VALUES(1,1);

SELECT BIN(b1), BIN(b2) FROM t_bit;

-- Error: exceeds bit capacity
INSERT INTO t_bit VALUES(2,2);

INSERT INTO t_bit VALUES(1,8);

SELECT CONV(b1,2,10), CONV(b2,2,10) FROM t_bit;

👉 Use Case: Flags, bitmasks, and compact boolean storage.

String Types

Fixed-Length vs Variable-Length Strings

CHAR(M)

Fixed-length string
Range: 0–255 characters
Right-padded with spaces if shorter than M
Trailing spaces are removed when retrieved

VARCHAR(M)

Variable-length string
Stores only actual content
Requires 1–2 extra bytes for length metadata

Maximum length depends on:

Row size limit (65,535 bytes)
Character set (e.g., utf8mb4 uses up to 4 bytes per character)

CREATE TABLE t_char (
    c1 CHAR,
    c2 CHAR(3)
);

INSERT INTO t_char VALUES('M','mal');
INSERT INTO t_char VALUES('F','fel');

SELECT * FROM t_char;

CREATE TABLE t_char (
    c1 VARCHAR(3)
);

INSERT INTO t_char VALUES('fem');
INSERT INTO t_char VALUES('female'); -- Error

Choosing Between CHAR and VARCHAR

Use CHAR for fixed-length data (e.g., country codes, IDs)
Use VARCHAR for variable-length data (e.g., names, comments)

Storage engine considerations:

MyISAM: CHAR may improve read performance
InnoDB: VARCHAR is usually more space-efficient

ENUM and SET

These types restrict values to predefined lists.

ENUM

Allows only one value
Internally stored as an integer index
Max: 65,535 values

SET

Allows multiple values (combinations)
Internally stored as bitmask values (powers of 2)
Max: 64 members

CREATE TABLE t_enum (
    gender ENUM('Male','Female'),
    hobby SET('Sleep','Game','Sport','Coding')
);

INSERT INTO t_enum VALUES('Male','Sleep,Game');

-- Using numeric representation
INSERT INTO t_enum VALUES(2, 2);
INSERT INTO t_enum VALUES(1, 5);

SELECT * FROM t_enum;

👉 Use Case:

ENUM: status, category
SET: tags, multi-select attributes

TEXT Types

TEXT types are used to store large amounts of text data (e.g., articles, logs).
Variants include:

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

Binary String Types

Binary types store raw byte data rather than character data.

BINARY(M)

Fixed-length binary data
Padded with \0 if shorter than M

VARBINARY(M)

Variable-length binary data
Requires length specification
Includes additional bytes for length storage

👉 Use Case: Images, encrypted data, or non-text binary content.

Date and Time Types

MySQL provides several types for handling temporal data.

YEAR

Format: YYYY
Range: 1901–2155
2-digit format is deprecated

Special cases:

0 → 0000
'0' → 2000

DATE

Format: YYYY-MM-DD

TIME

Format: HH:MM:SS

DATETIME

Stores date and time without timezone conversion

TIMESTAMP

Range: 1970–2038 (UTC)
Automatically converts between time zones

Key Difference

TIMESTAMP: timezone-aware
DATETIME: timezone-independent

CREATE TABLE t_date (
    d1 DATETIME,
    d2 TIMESTAMP
);

INSERT INTO t_date VALUES('2025-6-4 14:45:52','2025-6-4 14:45:52');

SET time_zone = '+2:00';

INSERT INTO t_date VALUES('2025-6-4 14:45:52','2025-6-4 14:45:52');

SELECT * FROM t_date;

CREATE TABLE t_date (
    d YEAR
);

INSERT INTO t_date VALUES(2025),(85),(22),(69),(0),('0');