MySQL Cheat Sheet

Updated 2026-04-27

Next Topic: Neo4j and Cypher Query Language Cheat Sheet

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and querying data. As one of the world's most popular database systems, MySQL powers everything from small web applications to large-scale enterprise systems, offering ACID-compliant transactions, replication, and high availability features. MySQL 8.4 is the current Long-Term Support (LTS) release, bringing improvements to JSON handling, window functions, LATERAL derived tables, and role-based access control while removing legacy master/slave replication terminology. A critical distinction to understand: while MySQL supports multiple storage engines, InnoDB (the default since MySQL 5.5) provides row-level locking and foreign key constraints, whereas the legacy MyISAM uses table-level locking—choosing the right engine and understanding isolation levels can make the difference between a responsive system and one plagued by deadlocks.

What This Cheat Sheet Covers

This topic spans 38 focused tables and 306 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Data TypesTable 2: DDL StatementsTable 3: DML StatementsTable 4: Query ClausesTable 5: JOIN TypesTable 6: Aggregate FunctionsTable 7: String FunctionsTable 8: Regular Expression FunctionsTable 9: Date and Time FunctionsTable 10: Numeric FunctionsTable 11: Type ConversionTable 12: Conditional ExpressionsTable 13: ConstraintsTable 14: Generated ColumnsTable 15: Index TypesTable 16: Full-Text SearchTable 17: Window FunctionsTable 18: SubqueriesTable 19: Common Table Expressions (CTE)Table 20: Set OperationsTable 21: Transaction ControlTable 22: Isolation LevelsTable 23: User and Privilege ManagementTable 24: RolesTable 25: Stored Procedures and FunctionsTable 26: Prepared StatementsTable 27: TriggersTable 28: Event SchedulerTable 29: JSON FunctionsTable 30: Performance and OptimizationTable 31: Administrative CommandsTable 32: Storage EnginesTable 33: Character Sets and CollationsTable 34: PartitioningTable 35: ReplicationTable 36: Backup and RecoveryTable 37: Locking MechanismsTable 38: Operators

Table 1: Data Types

Type	Example	Description
INT	`id INT PRIMARY KEY`	• Integer range -2,147,483,648 to 2,147,483,647 (signed) • 4 bytes.
VARCHAR	`name VARCHAR(100)`	• Variable-length string • only uses space needed plus 1–2 bytes for length • max 65,535 chars.
TEXT	`description TEXT`	• Variable-length string for large text blocks • max 65,535 chars • cannot have a default value or be a primary key.
DECIMAL	`price DECIMAL(10,2)`	• Fixed-point number with exact precision • first param = total digits, second = decimal places • ideal for currency.
DATE	`birth_date DATE`	• Stores date only in `YYYY-MM-DD` format • range `1000-01-01` to `9999-12-31`.
DATETIME	`created_at DATETIME`	• Stores date and time in `YYYY-MM-DD HH:MM:SS` • range up to `9999-12-31 23:59:59` • not timezone-aware.
TIMESTAMP	`updated_at TIMESTAMP` `DEFAULT CURRENT_TIMESTAMP`	• Stores datetime with automatic timezone conversion • range `1970-01-01` to `2038-01-19 UTC` • auto-updates on modification.
BOOLEAN	`is_active BOOLEAN`	• Synonym for TINYINT(1) • stores 0 (false) or 1 (true) • MySQL converts TRUE/FALSE to 1/0.
BIGINT	`user_id BIGINT`	• Large integer • 8 bytes • range ±9.2×10¹⁸ • used for large auto-increment IDs and big counts.
JSON	`metadata JSON`	• Native JSON type • validates JSON syntax • supports path expressions and indexing via generated columns.
ENUM	`status ENUM('pending',` `'active', 'closed')`	• String with predefined allowed values • stored internally as integers • max 65,535 distinct values.

Table 1: Data Types

Type	Example	Description
INT	`id INT PRIMARY KEY`	• Integer range -2,147,483,648 to 2,147,483,647 (signed) • 4 bytes.
VARCHAR	`name VARCHAR(100)`	• Variable-length string • only uses space needed plus 1–2 bytes for length • max 65,535 chars.
TEXT	`description TEXT`	• Variable-length string for large text blocks • max 65,535 chars • cannot have a default value or be a primary key.
DECIMAL	`price DECIMAL(10,2)`	• Fixed-point number with exact precision • first param = total digits, second = decimal places • ideal for currency.
DATE	`birth_date DATE`	• Stores date only in `YYYY-MM-DD` format • range `1000-01-01` to `9999-12-31`.
DATETIME	`created_at DATETIME`	• Stores date and time in `YYYY-MM-DD HH:MM:SS` • range up to `9999-12-31 23:59:59` • not timezone-aware.
TIMESTAMP	`updated_at TIMESTAMP` `DEFAULT CURRENT_TIMESTAMP`	• Stores datetime with automatic timezone conversion • range `1970-01-01` to `2038-01-19 UTC` • auto-updates on modification.
BOOLEAN	`is_active BOOLEAN`	• Synonym for TINYINT(1) • stores 0 (false) or 1 (true) • MySQL converts TRUE/FALSE to 1/0.
BIGINT	`user_id BIGINT`	• Large integer • 8 bytes • range ±9.2×10¹⁸ • used for large auto-increment IDs and big counts.
JSON	`metadata JSON`	• Native JSON type • validates JSON syntax • supports path expressions and indexing via generated columns.
ENUM	`status ENUM('pending',` `'active', 'closed')`	• String with predefined allowed values • stored internally as integers • max 65,535 distinct values.