Data Types in SQL (MySQL)

This document details the main data types in SQL, organized by category, with examples and recommendations for use based on storage efficiency and purpose.

---

1. Numeric Types

Type	Bytes	Description	Recommendations for use
TINYINT	1	Very small integer (-128 to 127)	Boolean flags, simple states
SMALLINT	2	Small integer	Small codes, low-range counters
MEDIUMINT	3	Medium integer	Intermediate IDs, small balances
INT or INTEGER	4	Standard integer	Primary keys, general counters
BIGINT	8	Large integer	Running totals, transactions, timestamps
DECIMAL(M,D)	Variable	Fixed decimal number	Financial data where exact precision is required
FLOAT	4	Single precision floating point	Approximate metrics, quick calculations
DOUBLE	8	Double precision floating point	Scientific calculations, complex statistics
BIT(n)	N	Bitfield of length n	Compact storage of binary flags

---

2. String Types

Type	Bytes (approx.)	Description	Recommendations for use
CHAR(n)	n	Fixed length string	Uniform codes, fixed keys
VARCHAR(n)	n + 1	Variable length string	Names, addresses, editable fields
TEXT	Variable	Long text up to 65,535 characters	Descriptions, free content
TINYTEXT	≤ 255	Small text	Brief comments, tags
MEDIUMTEXT	≤ 16 MB	Medium text	Articles, blog posts
LONGTEXT	≤ 4 GB	Very long text	Extensive documents, detailed logs
ENUM	Variable	Defined list of values	Limited states, closed categories
SET	Variable	Multiple set of values	Multiple selections among known values
BINARY(n)	n	Fixed binary data	Hashes, encoded keys
VARBINARY(n)	n + 1	Variable binary data	Files, small images

---

3. Date and Time Types

Type	Bytes	Description	Recommendations for use
DATE	3	Date (YYYY-MM-DD)	Date of birth, events
DATETIME	8	Full date and time	Human timestamps, event logs
TIMESTAMP	4	UNIX format Date/time (TZ auto-adjust)	Audit logs, external synchronization
TIME	3	Time of day	Schedules, specific duration
YEAR	1	Year (YYYY)	Annual record, fiscal periods

---

4. Special Types

Type	Bytes	Description	Recommendations for use
JSON	Variable	Structured JSON object	Semi-structured data, dynamic configuration
GEOMETRY	Variable	Spatial coordinates	GIS systems, maps
POINT, LINESTRING, POLYGON	Variable	Geometrical variants	Geolocation, spatial plotting
BOOL / BOOLEAN	1 (alias of TINYINT)	Logical true/false	Flags, binary states

---

5. General Recommendations

• Efficiency: Use TINYINT, SMALLINT or ENUM when the data set is limited.
• Financial precision: Prefer DECIMAL over FLOAT/DOUBLE to avoid rounding errors.
• Long text: TEXT and its variants are not directly indexable, consider VARCHAR if you need searches.
• Timestamps: Use TIMESTAMP if you need timezone compatibility; DATETIME if you want to avoid adjustment.
• Identifiers: For primary keys use INT UNSIGNED AUTO_INCREMENT unless cardinality demands it (BIGINT).
• Flexibility: JSON is useful in dynamic models but more costly in operations; avoid overusing in normalized databases.

---