MySQL - Data Types

MySQL Data Types: A Modern Guide

Choosing the right data type for your table columns is a fundamental aspect of database design. It’s not just about what kind of data you can store; it’s a critical decision that impacts storage efficiency, query performance, and data integrity. In modern MySQL, the range of data types has expanded to support new kinds of applications, from simple blogs to complex geo-spatial and JSON-based services.

Best Practice: Always choose the smallest and most restrictive data type that can reliably contain your data. This saves space, improves performance, and prevents invalid data from being stored.

Core Data Type Categories

MySQL’s data types are broadly classified into several categories:

• Numeric Types: For numbers, both whole (integers) and fractional (decimals).
• Date and Time Types: For temporal information like dates, times, and timestamps.
• String Types: For text data, from single characters to long articles.
• Spatial Types: For geographic data.
• The JSON Type: For storing JSON documents.

Numeric Data Types

Numeric types are for storing numbers. A key attribute for integer types is UNSIGNED, which disallows negative values and doubles the maximum positive range.

Type	Storage (Bytes)	Description & Common Use Case
TINYINT	1	A very small integer. Use for flags, booleans (BOOLEAN is a synonym for TINYINT(1)), or small counters. Range: -128 to 127 (signed) or 0 to 255 (unsigned).
SMALLINT	2	A small integer. Use for things like the age of a person or the number of pages in a small book. Range: -32,768 to 32,767 (signed) or 0 to 65,535 (unsigned).
MEDIUMINT	3	A medium-sized integer. Less common, but useful for saving a byte over INT when you know the range fits. Range: -8,388,608 to 8,388,607 (signed) or 0 to 16,777,215 (unsigned).
INT	4	The standard integer type. Common for primary keys (AUTO_INCREMENT). Range: -2.1 billion to 2.1 billion (signed) or 0 to 4.2 billion (unsigned).
BIGINT	8	A large integer. Use when INT is not large enough, such as for transaction IDs in a high-volume system or when interfacing with 64-bit systems. Range is very large.
DECIMAL(M, D)	Varies	A fixed-point number. Essential for financial and monetary calculations where exact precision is required. M is the total number of digits, D is the number of decimal places. Example: DECIMAL(10, 2).
FLOAT	4	An approximate-value, single-precision floating-point number. Subject to rounding errors. Suitable for scientific measurements where absolute precision is not critical. The (M,D) syntax is non-standard and its use is discouraged.
DOUBLE	8	An approximate-value, double-precision floating-point number. More precise than FLOAT but still subject to rounding errors. REAL is a synonym for DOUBLE.

Pro Tip: DECIMAL vs. FLOAT/DOUBLE

Never use FLOAT or DOUBLE to store monetary values. Their approximate nature can lead to rounding errors that accumulate over time. Always use DECIMAL for financial data.

Date and Time Data Types

Storing temporal data is a common requirement. MySQL 8+ has improved these types, notably with support for fractional seconds.

• DATE: Stores a date in ‘YYYY-MM-DD’ format. Range: ‘1000-01-01’ to ‘9999-12-31’. Perfect for storing a birth date.
• TIME: Stores a time in ‘HH:MM:SS.fractional’ format. Can also be used to store a time interval. Range is large enough for most applications.
• DATETIME(fsp): A combination of date and time, ‘YYYY-MM-DD HH:MM:SS.fractional’. The fsp (fractional seconds precision) can be up to 6 digits (microseconds). It is timezone-unaware.
• TIMESTAMP(fsp): Similar to DATETIME, but with a crucial difference: it is timezone-aware. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. Its range is up to ‘2038-01-19 03:14:07.999999’ UTC. It also has special auto-updating capabilities, making it ideal for created_at or updated_at columns.
• YEAR: Stores a year in 4-digit format. Range: 1901 to 2155. Note: The 2-digit YEAR(2) format is deprecated and should not be used in new designs.

String Data Types

String types store text. A critical concept for strings is the character set and collation. The best practice for modern applications is to use the utf8mb4 character set to support the full range of Unicode characters, including emojis.

• CHAR(M): A fixed-length string. If a stored string is shorter than M, it’s padded with spaces. Use for data with a consistent length, like country codes (‘US’, ‘DE’) or MD5 hashes.
• VARCHAR(M): A variable-length string. Uses only the space required for the actual string plus 1 or 2 bytes for length. Ideal for most text data like names, usernames, and titles. M is the maximum length.
• TEXT Types: For long-form text. They come in four sizes: TINYTEXT (255 bytes), TEXT (64 KB), MEDIUMTEXT (16 MB), and LONGTEXT (4 GB). Use for blog posts, articles, or product descriptions.
• BLOB Types: Binary Large Objects. Used for storing binary data like images or files directly in the database. They correspond in size to the TEXT types (TINYBLOB, BLOB, etc.). Note: Storing large files in the database can negatively impact performance; often, it’s better to store a file path or URL instead.
• ENUM: An enumeration. A string object whose value must be chosen from a predefined list of allowed values, e.g., ENUM('pending', 'processing', 'shipped'). Useful for status columns, but can be inflexible if the list needs to change often.
• SET: A string object that can have zero or more values, each chosen from a predefined list. E.g., SET('read', 'write', 'execute') for permissions.

Modern Specialized Data Types

Modern MySQL includes powerful data types for new application architectures.

The JSON Data Type

Since MySQL 5.7, you can natively store JSON documents. MySQL automatically validates the JSON and provides an efficient binary storage format. You can then query and manipulate the data within the JSON document using a suite of built-in functions.

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"ram_gb": 16, "storage_gb": 512, "color": "silver"}');

-- Querying inside the JSON document
SELECT name, attributes->'$.color' AS color
FROM products
WHERE attributes->>'$.ram_gb' > 8;

Spatial Data Types

MySQL supports spatial data types like GEOMETRY, POINT, LINESTRING, and POLYGON to store geographical information. This allows you to perform location-based queries, such as finding all stores within a 5-kilometer radius of a user’s location. This is a specialized field, but powerful for GIS and location-aware applications.