If you write SQL and you need to know whether a query, function, or DDL statement will work the same in both mysql and mariadb -- this is the article. Organized by SQL feature area, with working examples and explicit "breaks on X" callouts throughout.

---

1. The Snapshot: How Much Alike Are They?

For basic CRUD -- SELECT, INSERT, UPDATE, DELETE, JOIN, WHERE, GROUP BY, ORDER BY, LIMIT -- they are nearly identical. CTEs, window functions, subqueries, and basic stored procedures are also broadly compatible.

The divergence shows up in:

• JSON functions -- storage model and function set differ
• Regular expressions -- different engine (PCRE vs ICU), different backreference syntax
• Date/time edge cases -- UNIX_TIMESTAMP, EXTRACT, strictness
• DDL -- CREATE OR REPLACE, SEQUENCE, temporal table syntax, invisible columns
• DML -- VALUES() vs VALUE() rename, IGNORE semantics
• Procedural SQL -- PL/SQL mode, %TYPE, SYS_REFCURSOR
• Data types -- INET4, INET6, UUID native types (MariaDB only)

---

2. JSON: The Biggest Practical Trap

2.1 The Storage Model Is Fundamentally Different

	MySQL	MariaDB
Internal format	Binary (compact BSON-like)	Plain text (LONGTEXT)
JSON column type	True native type	Alias: LONGTEXT COLLATE utf8mb4_bin CHECK (JSON_VALID(...))
DESC t shows	json	longtext
JSON value comparison	Semantic (numbers sort numerically)	String comparison
Row-based replication across DBs	Binary incompatible -- breaks	--

This is not a display difference. DESC a JSON column in MariaDB and you will see longtext. The implications cascade:

Comparison semantics break queries silently:

-- MySQL: uses numeric JSON comparison -- works correctly
SELECT * FROM products WHERE specs->'$.version' > '9';
-- Returns rows where version > 9 (numeric)

-- MariaDB: string comparison -- '9' > '10' is TRUE (lexicographic)
-- Same query silently returns wrong results on version numbers

Row-based replication from MySQL to MariaDB fails outright: JSON columns must be converted to TEXT in MySQL first, or switch to statement-based replication (binlog_format=STATEMENT) before migrating.

From MariaDB 10.5.7+, use the mysql_json plugin to auto-convert on import.

---

2.2 Path Operators

Syntax	MySQL	MariaDB	Notes
col->'$.key'	✅	❌	MDEV-13594, still open in 11.7
col->>'$.key'	✅	❌	Unquoted shorthand, also absent
JSON_EXTRACT(col, '$.key')	✅	✅	Portable equivalent
JSON_UNQUOTE(JSON_EXTRACT(...))	✅	✅	Portable unquoted equivalent
JSON_VALUE(col, '$.key')	✅ (8.0.21+)	✅	SQL/JSON standard

Every MySQL codebase that uses -> or ->> -- including many ORMs' JSON column output -- fails on MariaDB. This is the #1 migration breakage in production.

-- MySQL (breaks on MariaDB)
SELECT name, specs->'$.cpu' AS cpu FROM products;

-- Portable
SELECT name, JSON_VALUE(specs, '$.cpu') AS cpu FROM products;
-- or
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(specs, '$.cpu')) AS cpu FROM products;

---

2.3 JSON Functions: What Each Has That The Other Doesn't

MariaDB-only JSON functions:

Function	Purpose
JSON_QUERY(doc, path)	Extracts an object or array -- strict sub-document only, no scalars
JSON_EXISTS(doc, path)	Returns 1/0 if path exists; SQL/JSON standard
JSON_COMPACT(doc)	Removes all whitespace
JSON_DETAILED(doc [, indent])	Pretty-prints with configurable indent level
JSON_LOOSE(doc)	Human-readable with added spaces
JSON_NORMALIZE(doc)	Sorts keys + normalizes numbers for canonical comparison
JSON_KEY_VALUE(doc, path)	Returns key-value pairs as a derived table
JSON_OBJECT_FILTER_KEYS(doc, keys_arr)	Returns object with only specified keys
JSON_OBJECT_TO_ARRAY(doc)	Converts {a:1,b:2} to [[a,1],[b,2]]
JSON_ARRAY_INTERSECT(a, b)	Returns intersection of two JSON arrays

MySQL-only JSON functions:

Function	Purpose
JSON_SCHEMA_VALID(schema, doc)	Validates doc against JSON Schema draft-7
JSON_SCHEMA_VALIDATION_REPORT(s, d)	Detailed validation error report
expr MEMBER OF(json_array)	Tests if value is in a JSON array (operator syntax)
-> / ->>	Path shorthand operators

JSON_TABLE exists in both (MariaDB added it in 10.6), but edge-case behavior and available column types differ. Test queries that use NESTED PATH, ORDINALITY, or non-string RETURNING types.

---

2.4 JSON Migration Rewrites

-- Arrow syntax
-- MySQL:   col->'$.k'        MariaDB: JSON_EXTRACT(col, '$.k')
-- MySQL:   col->>'$.k'       MariaDB: JSON_VALUE(col, '$.k')

-- MEMBER OF
-- MySQL:   'admin' MEMBER OF (roles->'$')
-- MariaDB: JSON_CONTAINS(roles, '"admin"')

-- Schema validation
-- MySQL:   JSON_SCHEMA_VALID('{"type":"object"}', col)
-- MariaDB: no equivalent -- validate in application code

-- Canonical comparison
-- MySQL:   no built-in
-- MariaDB: JSON_NORMALIZE(a) = JSON_NORMALIZE(b)

---

3. Regular Expressions: Different Engine, Different Syntax

MySQL uses ICU (International Components for Unicode).
MariaDB uses PCRE (Perl Compatible Regular Expressions).

Both expose REGEXP_REPLACE, REGEXP_INSTR, and REGEXP_SUBSTR but the feature sets and -- critically -- the backreference syntax differ.

3.1 Backreference Syntax in Replacements

-- Swap first and last name

-- MySQL: $N notation
SELECT REGEXP_REPLACE('John Smith', '(\\w+) (\\w+)', '$2, $1');
-- Result: Smith, John

-- MariaDB: \N notation
SELECT REGEXP_REPLACE('John Smith', '(\\w+) (\\w+)', '\\2, \\1');
-- Result: Smith, John

-- MySQL's $1 on MariaDB: returns '$2, $1' literally -- silent wrong result
-- MariaDB's \1 on MySQL: returns wrong result or literal string

This is a silent failure -- no error, wrong data. Any ETL code using REGEXP_REPLACE with backreferences must be rewritten when crossing between the two.

3.2 PCRE Features Available Only in MariaDB

Feature	MariaDB (PCRE)	MySQL (ICU)
Lookahead (?=...)	✅	✅
Lookbehind (?<=...)	✅ Full	⚠️ Fixed-width only
Atomic groups (?>...)	✅	❌
Possessive quantifiers \d++	✅	❌
Recursive patterns (?R)	✅	❌
Named captures (?P<n>...)	✅	✅
Case override (?i) / (?-i)	✅	❌
Multiline (?m) flag	✅	❌

The (?m) flag matters for multi-line text processing -- MariaDB's ^ and $ can match line boundaries; MySQL's always match the string boundary only.

---

4. Date and Time Functions

Most date functions are compatible. The traps are in the edge cases.

4.1 UNIX_TIMESTAMP Decimal Precision

-- MariaDB: returns 6 decimal places for string input
SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');
-- MariaDB: 1704067200.000000
-- MySQL:   1704067200

-- This breaks partition functions using UNIX_TIMESTAMP(date_string) as the key.
-- Fix: FLOOR(UNIX_TIMESTAMP('2024-01-01 00:00:00'))  -- works in both

4.2 EXTRACT(HOUR FROM ...) Range

-- MySQL: can return > 23 for TIME type (returns elapsed hours)
SELECT EXTRACT(HOUR FROM '100:30:00');
-- MySQL:   100
-- MariaDB: 4   (100 mod 24, SQL standard-compliant since 5.5.35)

This matters for queries that store durations in TIME columns and extract hours.

4.3 Date Strictness

-- UNIX_TIMESTAMP on invalid input
SELECT UNIX_TIMESTAMP('not-a-date');
-- MySQL:   0   (historical permissive behavior)
-- MariaDB: NULL

-- Code that tests = 0 to detect bad dates needs IS NULL for MariaDB

4.4 Compatible Date Functions (Both Work Identically)

DATE_FORMAT, DATE_ADD, DATE_SUB, DATEDIFF, NOW(), CURDATE(), CURTIME(), YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), LAST_DAY(), FROM_UNIXTIME(), DATE(), TIME(), TIMESTAMP(), ADDDATE(), SUBDATE(), PERIOD_ADD(), PERIOD_DIFF(), TO_SECONDS(), TIMESTAMPDIFF(), TIMESTAMPADD().

Neither database supports DATEADD()/DATEDIFF() (SQL Server syntax). Use DATE_ADD(date, INTERVAL n unit) for portability.

---

5. DDL Differences

5.1 CREATE OR REPLACE for Stored Routines

-- MariaDB: atomic replace, no permission loss, no downtime gap
CREATE OR REPLACE PROCEDURE sp_process_orders(IN p_date DATE)
BEGIN
  -- body
END;

-- MySQL: must DROP then CREATE -- two-step with a window of unavailability
DROP PROCEDURE IF EXISTS sp_process_orders;
CREATE PROCEDURE sp_process_orders(IN p_date DATE)
BEGIN
  -- body
END;
-- Any GRANT EXECUTE is destroyed by the DROP and must be re-issued

CREATE OR REPLACE also works for VIEW, FUNCTION, TRIGGER, TABLE, and INDEX in MariaDB. MySQL supports it for VIEW only.

5.2 Sequences

-- MariaDB: native SEQUENCE objects
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXTVAL(order_seq);   -- 1000
SELECT NEXTVAL(order_seq);   -- 1001
SELECT CURRVAL(order_seq);   -- 1001

-- MySQL: no SEQUENCE -- workaround with a counter table + function
CREATE TABLE _sequences (name VARCHAR(50) PRIMARY KEY, val BIGINT NOT NULL);
INSERT INTO _sequences VALUES ('order_seq', 999);

CREATE FUNCTION nextval_order_seq() RETURNS BIGINT DETERMINISTIC
BEGIN
  UPDATE _sequences SET val = val + 1 WHERE name = 'order_seq';
  RETURN (SELECT val FROM _sequences WHERE name = 'order_seq');
END;

5.3 System-Versioned (Temporal) Tables

-- MariaDB: SQL:2011 temporal tables -- no extra triggers, no extra columns needed
CREATE TABLE price_history (
  product_id INT,
  price      DECIMAL(10,2),
  changed_by VARCHAR(100)
) WITH SYSTEM VERSIONING;

-- Point-in-time query
SELECT * FROM price_history
FOR SYSTEM_TIME AS OF '2024-06-01 00:00:00';

-- All historical versions of a row
SELECT *, ROW_START, ROW_END
FROM price_history FOR SYSTEM_TIME ALL
WHERE product_id = 42;

-- MySQL: no equivalent -- requires trigger-maintained audit tables or CDC tooling

5.4 Default Values on TEXT/BLOB Columns

-- MariaDB: literal DEFAULT values allowed on TEXT/BLOB
CREATE TABLE notes (
  id   INT PRIMARY KEY,
  body TEXT DEFAULT 'No notes yet'
);

-- MySQL: literal DEFAULT on TEXT/BLOB is an error
-- (expression defaults via DEFAULT (...) are allowed since 8.0.13 but not literals)

---

6. DML Differences

6.1 VALUES() Rename -- Silent Data Corruption Risk

-- MySQL: VALUES() returns the value proposed for insert
INSERT INTO counters (key_name, hits) VALUES ('pageview', 1)
ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits);

-- MariaDB: renamed to VALUE() -- VALUES is now reserved for table constructors
INSERT INTO counters (key_name, hits) VALUES ('pageview', 1)
ON DUPLICATE KEY UPDATE hits = hits + VALUE(hits);

-- DANGER: MySQL's VALUES() on MariaDB does NOT error -- it returns NULL
-- Result: hits = hits + NULL = NULL -- silent data destruction

-- Portable alternative (MySQL 8.0.19+, MariaDB 10.5+):
INSERT INTO counters (key_name, hits) VALUES ('pageview', 1) AS new_row
ON DUPLICATE KEY UPDATE hits = hits + new_row.hits;

6.2 IGNORE on FK Violations

Both support INSERT IGNORE and UPDATE IGNORE. MariaDB's FK violation handling during IGNORE operations is somewhat stricter at the engine level in edge cases. Test explicitly if your schema uses FK constraints with IGNORE-heavy patterns.

---

7. Stored Procedure and Routine Differences

7.1 Oracle PL/SQL Compatibility Mode (MariaDB Only)

SET sql_mode = 'ORACLE';

CREATE OR REPLACE PROCEDURE get_order(p_id IN NUMBER, p_total OUT NUMBER) AS
  v_rec orders%ROWTYPE;
BEGIN
  SELECT * INTO v_rec FROM orders WHERE id = p_id;
  p_total := v_rec.total;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_total := 0;
END;
/

PL/SQL Feature	MariaDB (ORACLE mode)	MySQL
%TYPE column type reference	✅	❌
%ROWTYPE	✅	❌
SYS_REFCURSOR	✅	❌
EXCEPTION ... WHEN block	✅	❌
RAISE	✅	❌

7.2 JavaScript Stored Programs (MySQL 9.0+ Only)

-- MySQL 9.0+: stored programs via GraalVM MLE
CREATE FUNCTION parse_sku(sku VARCHAR(100))
RETURNS JSON
LANGUAGE JAVASCRIPT AS $$
  const parts = sku.split('-');
  return JSON.stringify({ category: parts[0], product: parts[1] });
$$;

MariaDB has no equivalent. All stored routines must be SQL/PSM or PL/SQL.

---

8. Data Type Differences

8.1 Native Types in MariaDB Only

-- Compact native IP types
CREATE TABLE access_log (
  client_ip INET4,    -- 4 bytes, validates IPv4, supports INET_ATON/INET_NTOA
  server_ip INET6     -- 16 bytes, validates IPv4 and IPv6
);

-- Native UUID type -- 16 bytes, not VARCHAR(36)
CREATE TABLE sessions (
  id UUID DEFAULT UUID() PRIMARY KEY
);

-- MySQL: VARCHAR(45) for IPs, BINARY(16) or VARCHAR(36) for UUIDs
-- No compact storage, no built-in format validation

8.2 VECTOR Type (MySQL 9.0+ Only)

-- For ML embedding workloads
CREATE TABLE embeddings (
  doc_id INT PRIMARY KEY,
  vec    VECTOR(1536)
);

SELECT doc_id,
       DISTANCE(vec, STRING_TO_VECTOR('[0.1, 0.2, ...]'), 'COSINE') AS score
FROM embeddings
ORDER BY score LIMIT 10;

-- MariaDB: no VECTOR type as of 11.7

---

9. Functions: Full Inventory of Differences

9.1 MariaDB-Only Functions

Function	Category	Purpose
JSON_QUERY / JSON_EXISTS	JSON	SQL/JSON standard accessors
JSON_NORMALIZE / COMPACT / DETAILED / LOOSE	JSON	Formatting and normalization
JSON_KEY_VALUE / OBJECT_FILTER_KEYS / OBJECT_TO_ARRAY / ARRAY_INTERSECT	JSON	Extended JSON manipulation
NEXTVAL(seq) / CURRVAL(seq)	Sequence	Sequence object functions
SFORMAT(fmt, ...)	String	Python-style {} string formatting
DECODE_HISTOGRAM(col, val)	Analytics	Read histogram statistics
PROCEDURE ANALYSE()	Introspection	Suggest optimal column types
SHOW EXPLAIN FOR <tid>	Debugging	EXPLAIN plan of running query
BINLOG_GTID_POS()	Replication	MariaDB GTID utilities

9.2 MySQL-Only Functions

Function	Category	Purpose
JSON_SCHEMA_VALID / _VALIDATION_REPORT	JSON	JSON Schema validation
MEMBER OF(arr)	JSON	Array membership test
BIN_TO_UUID / UUID_TO_BIN / IS_UUID	UUID	Binary UUID handling
FORMAT_BYTES / FORMAT_PICO_TIME	System	Human-readable sys metrics
VECTOR_DIM / STRING_TO_VECTOR / VECTOR_TO_STRING / TO_VECTOR / DISTANCE	Vector	ML embedding operations
GTID_SUBSET / GTID_UNION	Replication	MySQL GTID utilities

9.3 Same Name, Different Behavior

Function	MySQL	MariaDB
REGEXP_REPLACE(s, pat, repl)	$1 backrefs	\1 backrefs
UNIX_TIMESTAMP(date_str)	Integer	6 decimal places
EXTRACT(HOUR FROM time)	Can exceed 23	Always 0-23
VALUES(col) in ON DUP KEY	Returns insert value	Returns NULL (renamed)
JSON comparison	Semantic	String
UNIX_TIMESTAMP('invalid')	Returns 0	Returns NULL

---

10. Diagnostic SQL

-- EXPLAIN a live query

-- MySQL
EXPLAIN FOR CONNECTION 42;

-- MariaDB native form (predates MySQL's version)
SHOW EXPLAIN FOR 42;

-- Persist config changes

-- MySQL
SET PERSIST max_connections = 500;  -- survives restart, writes to mysqld-auto.cnf

-- MariaDB: no equivalent -- SET GLOBAL (session only) or edit my.cnf manually

-- Kill any query type

-- MySQL: only kills SELECT
KILL QUERY <tid>;

-- MariaDB: kills any running statement (INSERT, UPDATE, DDL, etc.)
KILL QUERY <tid>;

---

11. Cross-Platform Portability Cheat Sheet

-- JSON extraction (portable)
JSON_VALUE(col, '$.key')                         -- not col->'$.key'

-- JSON unquoted (portable)
JSON_UNQUOTE(JSON_EXTRACT(col, '$.key'))

-- UPSERT value reference (portable, MySQL 8.0.19+ / MariaDB 10.5+)
INSERT INTO t (id, v) VALUES (1, 10) AS src
ON DUPLICATE KEY UPDATE v = src.v;

-- Regex without backreferences (portable)
REGEXP_REPLACE(col, '[^a-zA-Z0-9]', '')

-- Date arithmetic (portable -- not DATEADD)
DATE_ADD(NOW(), INTERVAL 7 DAY)

-- Procedure replacement (portable)
DROP PROCEDURE IF EXISTS sp_name;
CREATE PROCEDURE sp_name() BEGIN ... END;       -- not CREATE OR REPLACE (MySQL)

-- Conditional DDL (portable)
CREATE TABLE IF NOT EXISTS ...
DROP TABLE IF EXISTS ...
CREATE INDEX IF NOT EXISTS ...                   -- MySQL 8.0+, MariaDB 10.1.4+

---

12. Quick Reference: Does This SQL Run on Both?

Feature	MySQL	MariaDB	Notes
Basic DML/DDL	✅	✅	Identical
CTEs, Window functions	✅	✅	Both; minor edge cases
JSON_EXTRACT / JSON_VALUE	✅	✅	Portable
col->'$.key'	✅	❌	MySQL only
JSON_TABLE	✅	✅ (10.6+)	Edge cases differ
JSON_SCHEMA_VALID	✅	❌	MySQL only
JSON_NORMALIZE	❌	✅	MariaDB only
REGEXP_REPLACE (no backrefs)	✅	✅	Portable
REGEXP_REPLACE (\1 backref)	❌	✅	MariaDB syntax
REGEXP_REPLACE ($1 backref)	✅	❌	MySQL syntax
CREATE OR REPLACE PROCEDURE	❌	✅	MariaDB only
CREATE OR REPLACE VIEW	✅	✅	Both
CREATE SEQUENCE	❌	✅	MariaDB only
FOR SYSTEM_TIME AS OF	❌	✅	Temporal tables
INET4 / INET6 / UUID types	❌	✅	MariaDB types
VECTOR type	✅ (9.0+)	❌	MySQL only
VALUES(col) in UPSERT	✅	❌ (returns NULL)	Renamed to VALUE()
VALUE(col) in UPSERT	❌	✅	Not recognized in MySQL
UPSERT alias INSERT ... AS src	✅ (8.0.19+)	✅ (10.5+)	Portable
TEXT/BLOB literal DEFAULT	❌	✅	MariaDB allows it
%TYPE / %ROWTYPE	❌	✅ (Oracle mode)	MariaDB only
JavaScript stored programs	✅ (9.0+)	❌	MySQL only
UNIX_TIMESTAMP returns int	✅	❌ (6 decimals)	MariaDB FLOOR() if needed
EXTRACT(HOUR) > 23	✅ TIME type	❌ (capped at 23)	Standard vs. elapsed
SET PERSIST	✅	❌	MySQL only
SHOW EXPLAIN FOR	❌	✅	MariaDB only
EXPLAIN FOR CONNECTION	✅	✅ (10.9+)

---

Versions covered: MySQL 8.4 LTS / 9.3 Innovation · MariaDB 11.7