MariaDB Cheat Sheet

Updated 2026-04-29

🧠Study flashcards on this topic198 cards · spaced repetition→

MariaDB is an open-source relational database management system (RDBMS) created as a fork of MySQL in 2009 by the original MySQL developers. It remains MySQL-compatible while offering enhanced performance, additional storage engines (InnoDB, ColumnStore, Aria, MyRocks), and modern features like temporal tables, native JSON support, VECTOR search for AI workloads, and a commitment to staying fully open-source under GPL/LGPL. As of 2026, MariaDB 11.8 LTS is the current long-term support release, introducing VECTOR data type and HNSW-based vector indexing. MariaDB is widely adopted by enterprises and powers major platforms including Wikipedia, Google, and Red Hat, making it a leading choice for both OLTP and analytical workloads in cloud-native and on-premises environments.

What This Cheat Sheet Covers

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

Table 1: Data Types — NumericTable 2: Data Types — String and TextTable 3: Data Types — Date and TimeTable 4: Data Types — JSON, Spatial, and ExtendedTable 5: Storage EnginesTable 6: IndexesTable 7: ConstraintsTable 8: Generated and Invisible ColumnsTable 9: JoinsTable 10: SubqueriesTable 11: Set OperationsTable 12: Common Table Expressions (CTEs)Table 13: Window FunctionsTable 14: Aggregate FunctionsTable 15: String FunctionsTable 16: Date and Time FunctionsTable 17: JSON FunctionsTable 18: Transactions and IsolationTable 19: ViewsTable 20: Stored Procedures and FunctionsTable 21: Error Handling in Stored RoutinesTable 22: Dynamic SQL / Prepared StatementsTable 23: TriggersTable 24: Event SchedulerTable 25: PartitioningTable 26: Replication and High AvailabilityTable 27: Performance OptimizationTable 28: Backup and RecoveryTable 29: Security and User ManagementTable 30: System Variables and ConfigurationTable 31: Temporal Tables (System-Versioned)Table 32: Full-Text SearchTable 33: Vector Search (MariaDB 11.7+)

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Data Types — Numeric

Choosing the right integer or decimal type is mostly a question of range versus storage—a TINYINT for a boolean flag wastes nothing, while a BIGINT reserves eight bytes for IDs you'll never realistically exhaust. The one rule worth tattooing on your arm: use DECIMAL for money, never FLOAT or DOUBLE, because approximate floating-point silently introduces rounding errors that destroy financial accuracy.

Type	Example	Description
INT	`CREATE TABLE t (id INT)`	• Standard 4-byte signed integer (–2,147,483,648 to 2,147,483,647) • most commonly used integer type for IDs and counters • unsigned variant extends positive range to 4,294,967,295.
BIGINT	`age BIGINT UNSIGNED`	• 8-byte signed integer for very large numbers (±9 quintillion range) • unsigned variant reaches 18.4 quintillion • preferred for large-scale IDs and analytics.
TINYINT	`status TINYINT(1)`	• 1-byte integer (–128 to 127 or 0 to 255 unsigned) • commonly used for boolean-like flags (0/1) and small enumerations • `TINYINT(1)` is a common boolean substitute.
SMALLINT	`port SMALLINT UNSIGNED`	• 2-byte integer (–32,768 to 32,767 or 0 to 65,535 unsigned) • efficient for medium-range values like network ports and small counts.
MEDIUMINT	`counter MEDIUMINT`	• 3-byte integer (±8 million range) • niche use between SMALLINT and INT for specific storage optimization scenarios.
DECIMAL	`price DECIMAL(10,2)`	• Exact fixed-point numeric stored as string internally • DECIMAL(p,s) defines precision (total digits) and scale (digits after decimal) • critical for financial calculations where rounding errors are unacceptable.
FLOAT	`score FLOAT`	• 4-byte approximate floating-point • fast but subject to rounding errors • suitable for scientific data where slight imprecision is acceptable.
DOUBLE	`latitude DOUBLE`	• 8-byte double-precision floating-point • higher precision than FLOAT but still approximate • commonly used for GPS coordinates and scientific measurements.
BIT	`flags BIT(8)`	• Stores bit values (1 to 64 bits) • useful for bitmasking and compact flag storage • less common than TINYINT for boolean representation.

Table 2: Data Types — String and Text

The everyday text types, plus the binary and categorical ones you reach for less often. VARCHAR covers the vast majority of short strings while CHAR is the fixed-width choice for codes that are always the same length; for anything large, the TEXT/BLOB families scale from 255 bytes up to 4 GB. ENUM and SET are worth knowing for tightly constrained categorical data, since they're stored compactly as integers under the hood.

Type	Example	Description
VARCHAR	`name VARCHAR(255)`	• Variable-length string (up to 65,535 bytes depending on row size and character set) • stores only actual string length plus 1–2 bytes overhead • most commonly used for names, emails, and short text fields.

The rest of this section is part of the full cheat sheet

Table 3: Data Types — Date and Time

The choice that matters most here is DATETIME versus TIMESTAMP: the former is timezone-naive and stores exactly what you give it, while the latter is UTC-based and auto-converts to the connection's timezone—plus it can auto-update itself on every row change. MariaDB 11.x also pushed TIMESTAMP past the infamous 2038 overflow, so that old limit no longer constrains your designs.

Type	Example	Description
DATETIME	`created_at DATETIME`	• Stores date and time (YYYY-MM-DD HH:MM:SS), range 1000-01-01 to 9999-12-31 • not timezone-aware; uses 8 bytes • preferred when timezone is irrelevant or stored separately.

The rest of this section is part of the full cheat sheet

Table 4: Data Types — JSON, Spatial, and Extended

This is where MariaDB stretches beyond classic SQL into modern workloads. JSON and native UUID handle semi-structured and identifier-heavy data more efficiently than text columns, the INET4/INET6 types store IP addresses as compact binary, and the spatial family (GEOMETRY, POINT, POLYGON) powers map and geo queries. The headline addition is VECTOR, which stores AI embeddings for approximate-nearest-neighbor search and went GA in 11.8 LTS.

Type	Example	Description
JSON	`metadata JSON`	• Stores JSON documents with validation • internally stored as LONGTEXT with constraint check • supports `JSON_EXTRACT()`, `JSON_SET()`, `JSON_TABLE()` • MySQL-compatible since MariaDB 10.5.7.

The rest of this section is part of the full cheat sheet

Table 1: Data Types — Numeric

Type	Example	Description
INT	`CREATE TABLE t (id INT)`	• Standard 4-byte signed integer (–2,147,483,648 to 2,147,483,647) • most commonly used integer type for IDs and counters • unsigned variant extends positive range to 4,294,967,295.
BIGINT	`age BIGINT UNSIGNED`	• 8-byte signed integer for very large numbers (±9 quintillion range) • unsigned variant reaches 18.4 quintillion • preferred for large-scale IDs and analytics.
TINYINT	`status TINYINT(1)`	• 1-byte integer (–128 to 127 or 0 to 255 unsigned) • commonly used for boolean-like flags (0/1) and small enumerations • `TINYINT(1)` is a common boolean substitute.
SMALLINT	`port SMALLINT UNSIGNED`	• 2-byte integer (–32,768 to 32,767 or 0 to 65,535 unsigned) • efficient for medium-range values like network ports and small counts.
MEDIUMINT	`counter MEDIUMINT`	• 3-byte integer (±8 million range) • niche use between SMALLINT and INT for specific storage optimization scenarios.
DECIMAL	`price DECIMAL(10,2)`	• Exact fixed-point numeric stored as string internally • DECIMAL(p,s) defines precision (total digits) and scale (digits after decimal) • critical for financial calculations where rounding errors are unacceptable.
FLOAT	`score FLOAT`	• 4-byte approximate floating-point • fast but subject to rounding errors • suitable for scientific data where slight imprecision is acceptable.
DOUBLE	`latitude DOUBLE`	• 8-byte double-precision floating-point • higher precision than FLOAT but still approximate • commonly used for GPS coordinates and scientific measurements.
BIT	`flags BIT(8)`	• Stores bit values (1 to 64 bits) • useful for bitmasking and compact flag storage • less common than TINYINT for boolean representation.

Table 2: Data Types — String and Text

Type	Example	Description
VARCHAR	`name VARCHAR(255)`	• Variable-length string (up to 65,535 bytes depending on row size and character set) • stores only actual string length plus 1–2 bytes overhead • most commonly used for names, emails, and short text fields.

Table 3: Data Types — Date and Time

Type	Example	Description
DATETIME	`created_at DATETIME`	• Stores date and time (YYYY-MM-DD HH:MM:SS), range 1000-01-01 to 9999-12-31 • not timezone-aware; uses 8 bytes • preferred when timezone is irrelevant or stored separately.

Table 4: Data Types — JSON, Spatial, and Extended

Type	Example	Description
JSON	`metadata JSON`	• Stores JSON documents with validation • internally stored as LONGTEXT with constraint check • supports `JSON_EXTRACT()`, `JSON_SET()`, `JSON_TABLE()` • MySQL-compatible since MariaDB 10.5.7.