🌐

MariaDB Miscellaneous & Network Functions

MariaDB

Complete reference for MariaDB miscellaneous and network functions covering UUID(), SLEEP(), INET_ATON(), INET_NTOA(), GET_LOCK(), IS_IPV4(), IS_IPV6(), FORMAT_BYTES(), and replication helper functions. Updated for MariaDB 11.x.

31 functions

What are MariaDB Miscellaneous & Network Functions?

MariaDB miscellaneous functions cover a range of utility operations that do not fit into other categories. UUID() generates a UUID v1 value (time-based, unlike PostgreSQL's gen_random_uuid() which is v4). INET_ATON() and INET_NTOA() convert between IPv4 address strings and their integer representation. GET_LOCK() and RELEASE_LOCK() implement named application-level locks similar to PostgreSQL's advisory locks. SLEEP() introduces a delay in seconds and is useful for testing lock timeouts.

UUID

MariaDB 5.5+→ VARCHAR(36)

Generates a universally unique identifier (UUID) based on RFC 4122 version 1 (time-based).

Signature

UUID()

Examples

Basic UUID generation

sql

SELECT UUID();

→-- '6ccd780c-baba-1026-9564-5b8c656024db'

Each call returns a unique value

sql

SELECT UUID(), UUID();

→-- Two different UUIDs

As primary key

sql

INSERT INTO orders (id, item) VALUES (UUID(), 'Widget');

→-- 1 row inserted

Always 36 characters

sql

SELECT LENGTH(UUID());

→-- 36

Never returns NULL

sql

SELECT UUID() IS NULL;

→-- 0

⚠Anti-Pattern— Using UUID as a clustered key without consideration

Using UUID() as a VARCHAR clustered primary key causes severe index fragmentation due to random ordering.

✓ Instead: Store as BINARY(16) with UNHEX(REPLACE(UUID(),'-','')) or use UUID_SHORT() for monotonic integer keys.

example

INSERT INTO sessions (id, user_id) VALUES (UUID(), 42);

→'6ccd780c-baba-1026-9564-5b8c6560eff4'

◆MariaDB Note

UUID() generates a v1 UUID derived from timestamp and MAC address; output is non-random but globally unique.

⇄ vs PostgreSQL: PostgreSQL uses gen_random_uuid() (pgcrypto or built-in in 13+) for UUID v4 (random); uuid-ossp extension provides uuid_generate_v1() for v1.

UUID_SHORT

MariaDB 5.5+→ BIGINT UNSIGNED

Returns a 64-bit monotonically increasing integer unique across servers when server_id and server startup time differ.

Signature

UUID_SHORT()

Examples

Basic call

sql

SELECT UUID_SHORT();

→-- 92395783831158784

As BIGINT primary key

sql

INSERT INTO events (id, name) VALUES (UUID_SHORT(), 'login');

→-- 1 row inserted

Always positive

sql

SELECT UUID_SHORT() > 0;

→-- 1

Each call increments

sql

SELECT UUID_SHORT(), UUID_SHORT();

→-- Two incrementing values

Never NULL

sql

SELECT UUID_SHORT() IS NULL;

→-- 0

⚠Anti-Pattern— Assuming global uniqueness across all servers

UUID_SHORT() uniqueness relies on server_id (0-255) being unique across all replication nodes; duplicate server_ids cause collisions.

✓ Instead: Ensure each MariaDB instance has a unique server_id set in my.cnf before using UUID_SHORT() in a multi-master setup.

example

SELECT UUID_SHORT() AS short_id;

→99467439786967040

◆MariaDB Note

UUID_SHORT() value encodes server_id (8 bits), startup time (32 bits), and an auto-incrementing counter (24 bits).

⇄ vs PostgreSQL: PostgreSQL has no direct equivalent; use BIGSERIAL for auto-incrementing or gen_random_uuid() for UUID-style uniqueness.

SLEEP

MariaDB 5.5+→ INT

Pauses execution for the specified number of seconds (can be fractional) and returns 0 on normal completion or 1 if interrupted.

Signature

SLEEP(seconds)

Parameters

Parameter	Type	Description
seconds	DECIMAL	Number of seconds to sleep; supports fractional values like 0.5

Examples

Sleep 1 second, returns 0

sql

SELECT SLEEP(1);

→-- 0

Sleep half a second

sql

SELECT SLEEP(0.5);

→-- 0

No-op sleep

sql

SELECT SLEEP(0);

→-- 0

Check return value

sql

SELECT IF(SLEEP(2) = 0, 'Done', 'Interrupted');

→-- 'Done'

NULL input returns NULL

sql

SELECT SLEEP(NULL);

→-- NULL

⚠Anti-Pattern— Using SLEEP() in production to throttle queries

SLEEP() holds connections and locks for its duration, degrading concurrency and potentially causing deadlocks under load.

✓ Instead: Use application-level rate limiting or MariaDB's built-in query governor features for throttling.

example

SELECT SLEEP(0.5), 'done';

→0 | done — after 500ms delay

◆MariaDB Note

Returns 1 (not an error) when the sleep is interrupted by KILL QUERY; the interrupted session resumes immediately.

⇄ vs PostgreSQL: PostgreSQL uses pg_sleep(seconds) which returns void; pg_sleep_for(interval) and pg_sleep_until(timestamp) are also available.

INET_ATON

MariaDB 5.5+→ BIGINT UNSIGNED

Converts a dotted-decimal IPv4 address string to its numeric representation as a BIGINT UNSIGNED.

Signature

INET_ATON(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	Dotted-decimal IPv4 address string (e.g., '192.168.1.1')

Examples

Standard IPv4

sql

SELECT INET_ATON('192.168.1.1');

→-- 3232235777

Private range

sql

SELECT INET_ATON('10.0.0.1');

→-- 167772161

Max IPv4

sql

SELECT INET_ATON('255.255.255.255');

→-- 4294967295

Min IPv4

sql

SELECT INET_ATON('0.0.0.0');

→-- 0

NULL handling

sql

SELECT INET_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Using INET_ATON for IPv6 addresses

INET_ATON only handles IPv4; passing an IPv6 address returns NULL silently.

✓ Instead: Use INET6_ATON() which handles both IPv4 and IPv6 and returns a VARBINARY(16).

example

SELECT INET_ATON('192.168.1.1') AS numeric_ip;

→3232235777

◆MariaDB Note

Returns NULL for invalid IPv4 strings; does not support abbreviated forms like '192.168'.

⇄ vs PostgreSQL: PostgreSQL uses the inet data type and operators directly; no exact equivalent function, but host(inet) and inet_client_addr() exist.

INET_NTOA

MariaDB 5.5+→ VARCHAR(15)

Converts a numeric IPv4 address (BIGINT UNSIGNED) to its dotted-decimal string representation.

Signature

INET_NTOA(expr)

Parameters

Parameter	Type	Description
expr	BIGINT UNSIGNED	Numeric IPv4 address as produced by INET_ATON()

Examples

Reverse of INET_ATON

sql

SELECT INET_NTOA(3232235777);

→-- '192.168.1.1'

Private range

sql

SELECT INET_NTOA(167772161);

→-- '10.0.0.1'

Round-trip

sql

SELECT INET_NTOA(INET_ATON('10.20.30.40'));

→-- '10.20.30.40'

Zero address

sql

SELECT INET_NTOA(0);

→-- '0.0.0.0'

NULL handling

sql

SELECT INET_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Passing an IPv6 numeric value to INET_NTOA

INET_NTOA only works with 32-bit IPv4 numeric values; larger values produce incorrect or NULL output.

✓ Instead: Use INET6_NTOA() for IPv6 numeric VARBINARY values.

example

SELECT INET_NTOA(3232235777) AS dotted_ip;

→'192.168.1.1'

◆MariaDB Note

Returns NULL for out-of-range values (greater than 4294967295).

⇄ vs PostgreSQL: PostgreSQL uses host(inet_value)::text or the inet type's text representation directly.

INET6_ATON

MariaDB 5.6+→ VARBINARY(16)

Converts an IPv4 or IPv6 address string to its binary representation (4 bytes for IPv4, 16 bytes for IPv6).

Signature

INET6_ATON(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	IPv4 dotted-decimal or IPv6 colon-separated address string

Examples

IPv4 as 4-byte binary

sql

SELECT HEX(INET6_ATON('192.168.1.1'));

→-- 'C0A80101'

IPv6 loopback

sql

SELECT HEX(INET6_ATON('::1'));

→-- '00000000000000000000000000000001'

IPv6 address

sql

SELECT HEX(INET6_ATON('2001:db8::1'));

→-- '20010DB8000000000000000000000001'

IPv6 is always 16 bytes

sql

SELECT LENGTH(INET6_ATON('::1'));

→-- 16

NULL handling

sql

SELECT INET6_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Storing INET6_ATON result as VARCHAR

The VARBINARY result of INET6_ATON contains raw binary bytes which will be corrupted or truncated if stored in a VARCHAR column.

✓ Instead: Always use VARBINARY(16) for the storage column when using INET6_ATON/INET6_NTOA.

example

SELECT HEX(INET6_ATON('::1')) AS hex;

→'00000000000000000000000000000001'

◆MariaDB Note

Returns NULL for invalid address strings; supports both compact (::1) and full IPv6 notation.

⇄ vs PostgreSQL: PostgreSQL uses the inet or cidr data type natively; no binary conversion function is needed for storage.

INET6_NTOA

MariaDB 5.6+→ VARCHAR(39)

Converts a binary IPv4 (4 bytes) or IPv6 (16 bytes) address to its human-readable string representation.

Signature

INET6_NTOA(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY	Binary IP address as produced by INET6_ATON() — 4 bytes for IPv4, 16 bytes for IPv6

Examples

IPv4 round-trip

sql

SELECT INET6_NTOA(INET6_ATON('192.168.1.1'));

→-- '192.168.1.1'

IPv6 loopback round-trip

sql

SELECT INET6_NTOA(INET6_ATON('::1'));

→-- '::1'

IPv6 compressed form

sql

SELECT INET6_NTOA(INET6_ATON('2001:db8::1'));

→-- '2001:db8::1'

From column

sql

SELECT INET6_NTOA(ip_bin) FROM connections;

→-- human-readable IPs

NULL handling

sql

SELECT INET6_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Comparing INET6_NTOA output as strings for range checks

String comparison of IP addresses does not produce correct numeric ordering.

✓ Instead: Compare the raw VARBINARY values from INET6_ATON() directly for range queries.

example

SELECT INET6_NTOA(INET6_ATON('::ffff:192.168.1.1'));

→'::ffff:192.168.1.1'

◆MariaDB Note

Returns NULL if the input is not exactly 4 or 16 bytes.

⇄ vs PostgreSQL: PostgreSQL uses host(inet_value) or the inet type's implicit text cast.

IS_IPV4

MariaDB 5.6+→ INT (0 or 1)

Returns 1 if the argument is a valid dotted-decimal IPv4 address string, otherwise 0.

Signature

IS_IPV4(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	String to test as an IPv4 address

Examples

Valid IPv4

sql

SELECT IS_IPV4('192.168.1.1');

→-- 1

Out-of-range octet

sql

SELECT IS_IPV4('256.0.0.1');

→-- 0

IPv6 is not IPv4

sql

SELECT IS_IPV4('::1');

→-- 0

Invalid string

sql

SELECT IS_IPV4('not-an-ip');

→-- 0

NULL handling

sql

SELECT IS_IPV4(NULL);

→-- NULL

⚠Anti-Pattern— Using IS_IPV4 to validate IPv6-mapped IPv4 addresses

IS_IPV4 returns 0 for IPv6-mapped IPv4 addresses like '::ffff:192.168.1.1'.

✓ Instead: Use IS_IPV4_MAPPED() to detect IPv6-mapped IPv4 addresses stored in IPv6 format.

example

SELECT addr, IS_IPV4(addr) AS v4, IS_IPV6(addr) AS v6 FROM ip_log LIMIT 5;

→(addresses with version flags)

⇄ vs PostgreSQL: PostgreSQL uses family(inet_value) = 4 or a regex; the inet type enforces validity at storage time.

IS_IPV4_COMPAT

MariaDB 5.6+→ INT (0 or 1)

Returns 1 if the given VARBINARY IPv6 address is an IPv4-compatible address (::w.x.y.z form, deprecated format), otherwise 0.

Signature

IS_IPV4_COMPAT(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY(16)	Binary IPv6 address as returned by INET6_ATON()

Examples

IPv4-compatible IPv6

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::192.168.1.1'));

→-- 1

IPv4-mapped is not IPv4-compat

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::ffff:192.168.1.1'));

→-- 0

Regular IPv6

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('2001:db8::1'));

→-- 0

Loopback is IPv4-compat

sql

SELECT IS_IPV4_COMPAT(INET6_ATON('::1'));

→-- 1

NULL handling

sql

SELECT IS_IPV4_COMPAT(NULL);

→-- NULL

⚠Anti-Pattern— Confusing IS_IPV4_COMPAT with IS_IPV4_MAPPED

::ffff:192.168.1.1 (IPv4-mapped) and ::192.168.1.1 (IPv4-compatible) are different formats; the functions test different bit patterns.

✓ Instead: Check both IS_IPV4_COMPAT() and IS_IPV4_MAPPED() when filtering for any IPv4-derived IPv6 address.

example

SELECT IS_IPV4_COMPAT('::192.168.1.1');

→1

◆MariaDB Note

The argument must be the binary result of INET6_ATON(), not a string.

⇄ vs PostgreSQL: No direct PostgreSQL equivalent; use family() and masklen() checks on the inet type.

IS_IPV4_MAPPED

MariaDB 5.6+→ INT (0 or 1)

Returns 1 if the given VARBINARY IPv6 address is an IPv4-mapped address (::ffff:w.x.y.z), otherwise 0.

Signature

IS_IPV4_MAPPED(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY(16)	Binary IPv6 address as returned by INET6_ATON()

Examples

IPv4-mapped address

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:192.168.1.1'));

→-- 1

IPv4-compat, not mapped

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::192.168.1.1'));

→-- 0

Pure IPv6

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('2001:db8::1'));

→-- 0

Private IPv4-mapped

sql

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.0.1'));

→-- 1

NULL handling

sql

SELECT IS_IPV4_MAPPED(NULL);

→-- NULL

⚠Anti-Pattern— Storing IPv4-mapped addresses and not normalizing

The same client may appear as '192.168.1.1' (IPv4) and '::ffff:192.168.1.1' (IPv4-mapped IPv6) depending on connection path.

✓ Instead: Normalize on insert: if IS_IPV4_MAPPED(ip), strip the mapping prefix before storage.

example

SELECT IS_IPV4_MAPPED('::ffff:192.168.1.1');

→1

◆MariaDB Note

The argument must be the VARBINARY result of INET6_ATON(), not a plain string.

⇄ vs PostgreSQL: PostgreSQL: use (family(addr) = 6 AND addr << '::ffff:0.0.0.0/96'::inet) to detect IPv4-mapped.

IS_IPV6

MariaDB 5.6+→ INT (0 or 1)

Returns 1 if the argument is a valid IPv6 address string (colon-separated), otherwise 0.

Signature

IS_IPV6(expr)

Parameters

Parameter	Type	Description
expr	VARCHAR	String to test as an IPv6 address

Examples

IPv6 loopback

sql

SELECT IS_IPV6('::1');

→-- 1

Valid IPv6

sql

SELECT IS_IPV6('2001:db8::1');

→-- 1

IPv4 is not IPv6

sql

SELECT IS_IPV6('192.168.1.1');

→-- 0

Invalid hex group

sql

SELECT IS_IPV6('gggg::1');

→-- 0

NULL handling

sql

SELECT IS_IPV6(NULL);

→-- NULL

⚠Anti-Pattern— Using IS_IPV6 on binary data from INET6_ATON

IS_IPV6 expects a string argument; passing binary VARBINARY bytes returns 0 even for valid IPv6 binary data.

✓ Instead: Apply IS_IPV6() before calling INET6_ATON(), not after.

example

SELECT IS_IPV6('2001:db8::1'), IS_IPV6('192.168.1.1');

→1 | 0

⇄ vs PostgreSQL: PostgreSQL validates at INSERT time using the inet type; family(addr) = 6 tests for IPv6.

FORMAT_BYTES

Available from MariaDB 10.7.0.→ VARCHAR

Converts a raw byte count to a human-readable string with an appropriate binary unit suffix (e.g., '1.00 MiB').

Signature

FORMAT_BYTES(count)

Parameters

Parameter	Type	Description
count	BIGINT UNSIGNED	Number of bytes to format

Examples

Kilobytes

sql

SELECT FORMAT_BYTES(1024);

→-- '1.00 KiB'

Megabytes

sql

SELECT FORMAT_BYTES(1048576);

→-- '1.00 MiB'

Gigabytes

sql

SELECT FORMAT_BYTES(1073741824);

→-- '1.00 GiB'

Under 1 KiB

sql

SELECT FORMAT_BYTES(512);

→-- '512 bytes'

NULL handling

sql

SELECT FORMAT_BYTES(NULL);

→-- NULL

⚠Anti-Pattern— Using FORMAT_BYTES on older MariaDB versions

FORMAT_BYTES was introduced in MariaDB 10.7; calling it on earlier versions causes an error.

✓ Instead: Check the version with SELECT VERSION() or use a CASE expression with manual calculation for compatibility.

example

SELECT FORMAT_BYTES(1073741824);

→'1.00 GiB'

◆MariaDB Note

Uses binary prefixes (KiB, MiB, GiB, TiB, PiB, EiB) per IEC 80000-13 standard.

⇄ vs PostgreSQL: PostgreSQL uses pg_size_pretty(bigint) for the same purpose.

SYS_GUID

Available from MariaDB 10.3 with Oracle mode.→ VARCHAR(32)

Oracle-compatibility alias for UUID() that returns a 32-character UUID without hyphens when used in Oracle SQL_MODE.

Signature

SYS_GUID()

Examples

32-char uppercase, no hyphens

sql

SELECT SYS_GUID();

→-- '6CCD780CBABA102695645B8C656024DB'

In Oracle mode

sql

SET sql_mode='ORACLE'; SELECT SYS_GUID();

→-- '6CCD780CBABA102695645B8C656024DB'

Always 32 characters

sql

SELECT LENGTH(SYS_GUID());

→-- 32

Each call is unique

sql

SELECT SYS_GUID() = SYS_GUID();

→-- 0

Never NULL

sql

SELECT SYS_GUID() IS NULL;

→-- 0

⚠Anti-Pattern— Relying on SYS_GUID outside Oracle compatibility mode

SYS_GUID() availability may depend on the active sql_mode in some MariaDB versions.

✓ Instead: Use UUID() for standard SQL and REPLACE(UUID(),'-','') if you need the hyphen-free format portably.

example

SELECT SYS_GUID();

→'6CCD780CBABA102695645B8C6560EFF4'

◆MariaDB Note

Available when MariaDB is run with Oracle SQL compatibility mode (sql_mode=ORACLE).

⇄ vs MySQL: MySQL does not have SYS_GUID(); this is a MariaDB Oracle-compatibility feature.

⇄ vs PostgreSQL: PostgreSQL has no SYS_GUID; use gen_random_uuid() or uuid_generate_v1() from uuid-ossp.

MASTER_POS_WAIT

MariaDB 5.5+→ BIGINT

Blocks until the replica has applied all binary log events up to the specified file and position, or until the timeout expires.

Signatures

MASTER_POS_WAIT(log_name, log_pos)

MASTER_POS_WAIT(log_name, log_pos, timeout)

MASTER_POS_WAIT(log_name, log_pos, timeout, channel_name)

Parameters

Parameter	Type	Description
log_name	VARCHAR	Binary log file name on the primary
log_pos	BIGINT	Binary log position to wait for
timeout	INT	Maximum seconds to wait; 0 or omitted means wait indefinitely
channel_name	VARCHAR	Replication channel name for multi-source replication

Examples

Wait indefinitely

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500);

→-- 0 (events applied)

Timeout after 10 seconds

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500, 10);

→-- -1 (timeout)

Explicit infinite wait

sql

SELECT MASTER_POS_WAIT('mariadb-bin.000001', 500, 0);

→-- 0

Conditional check

sql

SELECT IF(MASTER_POS_WAIT('mariadb-bin.000001',1000,5)>=0,'Synced','Lagging');

→-- 'Synced'

NULL handling

sql

SELECT MASTER_POS_WAIT(NULL, NULL);

→-- NULL

⚠Anti-Pattern— Calling MASTER_POS_WAIT on the primary server

MASTER_POS_WAIT is a replica-side function; calling it on a primary returns NULL immediately.

✓ Instead: Call this function only on replica servers; use SHOW MASTER STATUS to obtain the coordinates from the primary.

example

SELECT MASTER_POS_WAIT('binlog.000001', 154, 10);

→0 (replica caught up within 10s)

◆MariaDB Note

Returns NULL if not a replica or replication is not running; returns -1 on timeout; returns >= 0 (events skipped count) on success.

⇄ vs PostgreSQL: PostgreSQL uses pg_wal_replay_wait() (v16+) or application-level polling of pg_last_wal_replay_lsn().

MASTER_GTID_WAIT

MariaDB 10.0+→ INT

Blocks until the replica has applied all transactions up to the specified GTID position, returning 0 on success or -1 on timeout.

Signatures

MASTER_GTID_WAIT(gtid_pos)

MASTER_GTID_WAIT(gtid_pos, timeout)

Parameters

Parameter	Type	Description
gtid_pos	VARCHAR	GTID position string (e.g., '0-1-100') to wait for
timeout	DECIMAL	Maximum seconds to wait; negative or omitted means wait indefinitely

Examples

Wait for GTID position

sql

SELECT MASTER_GTID_WAIT('0-1-100');

→-- 0

With 10-second timeout

sql

SELECT MASTER_GTID_WAIT('0-1-100', 10);

→-- 0 or -1

Multiple GTID domains

sql

SELECT MASTER_GTID_WAIT('0-1-100,0-2-50', 5);

→-- 0 or -1

Conditional check

sql

SELECT IF(MASTER_GTID_WAIT('0-1-200',3)=0,'OK','Timeout');

→-- 'OK'

NULL handling

sql

SELECT MASTER_GTID_WAIT(NULL);

→-- NULL

⚠Anti-Pattern— Using MASTER_GTID_WAIT without GTID replication enabled

If gtid_mode is not enabled, MASTER_GTID_WAIT returns immediately with 0 without actually waiting for any events.

✓ Instead: Verify GTID replication is active with SHOW VARIABLES LIKE 'gtid_mode' before relying on this function.

example

SELECT MASTER_GTID_WAIT('0-1-100', 10);

→0 (GTID position reached within 10s)

◆MariaDB Note

MariaDB GTID format is domain_id-server_id-sequence_no, different from MySQL's UUID-based GTIDs.

⇄ vs PostgreSQL: PostgreSQL has no GTID concept; pg_wal_replay_wait() (v16+) serves a similar purpose using LSN positions.

SOURCE_POS_WAIT

Available from MariaDB 10.6.0.→ BIGINT

Alias for MASTER_POS_WAIT() introduced for naming consistency; blocks until the replica has applied events up to the specified binlog file and position.

Signatures

SOURCE_POS_WAIT(log_name, log_pos)

SOURCE_POS_WAIT(log_name, log_pos, timeout)

SOURCE_POS_WAIT(log_name, log_pos, timeout, channel_name)

Parameters

Parameter	Type	Description
log_name	VARCHAR	Binary log file name on the source/primary
log_pos	BIGINT	Binary log position to wait for
timeout	INT	Maximum seconds to wait; 0 or omitted waits indefinitely
channel_name	VARCHAR	Replication channel name for multi-source replication

Examples

Wait for position

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000001', 1024);

→-- 0

30-second timeout

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000001', 1024, 30);

→-- 0 or -1

Timeout example

sql

SELECT SOURCE_POS_WAIT('mariadb-bin.000002', 256, 5);

→-- -1 (timed out)

With conditional

sql

SELECT IF(SOURCE_POS_WAIT('mariadb-bin.000001',2048,10)>=0,'Caught up','Lagging');

→-- 'Caught up'

NULL handling

sql

SELECT SOURCE_POS_WAIT(NULL, NULL);

→-- NULL

⚠Anti-Pattern— Mixing SOURCE_POS_WAIT and MASTER_POS_WAIT in the same codebase

They are equivalent but mixing names causes confusion during code review and maintenance.

✓ Instead: Standardize on SOURCE_POS_WAIT() for new code while MASTER_POS_WAIT() remains available for backward compatibility.

example

SELECT SOURCE_POS_WAIT('binlog.000001', 154, 10);

→0 (replica caught up)

◆MariaDB Note

Functionally identical to MASTER_POS_WAIT(); introduced as a terminology update.

⇄ vs MySQL: MySQL also added SOURCE_POS_WAIT as an alias in MySQL 8.0.26.

⇄ vs PostgreSQL: PostgreSQL uses pg_wal_replay_wait() (v16+) or pg_last_wal_replay_lsn() polling.

GTID_SUBSET

MariaDB 10.0+→ INT (0 or 1)

Returns 1 if all GTIDs in the first GTID set are also contained in the second GTID set (subset check), otherwise 0.

Signature

GTID_SUBSET(subset, set)

Parameters

Parameter	Type	Description
subset	VARCHAR	GTID set to test as the potential subset
set	VARCHAR	GTID set to test against (the superset candidate)

Examples

10 is within 100

sql

SELECT GTID_SUBSET('0-1-10', '0-1-100');

→-- 1

100 is not within 10

sql

SELECT GTID_SUBSET('0-1-100', '0-1-10');

→-- 0

Multi-domain subset

sql

SELECT GTID_SUBSET('0-1-5,0-2-3', '0-1-10,0-2-10');

→-- 1

Empty set is subset of anything

sql

SELECT GTID_SUBSET('', '0-1-100');

→-- 1

NULL handling

sql

SELECT GTID_SUBSET(NULL, '0-1-100');

→-- NULL

⚠Anti-Pattern— Using GTID_SUBSET with MySQL-format GTIDs

MariaDB GTID format (domain-server-seq) is incompatible with MySQL UUID-based GTIDs; mixing formats returns NULL or incorrect results.

✓ Instead: Ensure all servers in the topology use MariaDB-native GTID format.

example

SELECT GTID_SUBSET('0-1-10', '0-1-20');

→1 (first GTID set is subset of second)

◆MariaDB Note

Operates on MariaDB's domain_id-server_id-sequence GTID format.

⇄ vs MySQL: MySQL also has GTID_SUBSET() but uses UUID-based GTID sets; syntax differs.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL; LSN comparison uses pg_lsn operators like <=.

GTID_SUBTRACT

MariaDB 10.0+→ VARCHAR

Returns the set of GTIDs in the first set that are not present in the second set (set difference).

Signature

GTID_SUBTRACT(set1, set2)

Parameters

Parameter	Type	Description
set1	VARCHAR	GTID set to subtract from
set2	VARCHAR	GTID set to subtract (remove from set1)

Examples

GTIDs in set1 not in set2

sql

SELECT GTID_SUBTRACT('0-1-10', '0-1-5');

→-- GTIDs in set1 not in set2

Identical sets give empty result

sql

SELECT GTID_SUBTRACT('0-1-10', '0-1-10');

→-- ''

Multi-domain subtraction

sql

SELECT GTID_SUBTRACT('0-1-10,0-2-5', '0-1-10');

→-- '0-2-5'

Subtract empty = original

sql

SELECT GTID_SUBTRACT('0-1-10', '');

→-- '0-1-10'

NULL handling

sql

SELECT GTID_SUBTRACT(NULL, '0-1-5');

→-- NULL

⚠Anti-Pattern— Assuming subtraction order doesn't matter

GTID_SUBTRACT(A, B) and GTID_SUBTRACT(B, A) return completely different results; the function is not commutative.

✓ Instead: Always place the larger/primary set first and the subset to remove second.

example

SELECT GTID_SUBTRACT('0-1-20', '0-1-10');

→'0-1-11:12:13:14:15:16:17:18:19:20'

◆MariaDB Note

Returns an empty string '' (not NULL) when the result is an empty set.

⇄ vs MySQL: MySQL also has GTID_SUBTRACT() but uses UUID-based GTID format.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL.

WSREP_LAST_SEEN_GTID

MariaDB 10.1+ (Galera)→ VARCHAR

Returns the GTID of the last write transaction seen (received from any node) by the current Galera cluster node.

Signature

WSREP_LAST_SEEN_GTID()

Examples

Last seen GTID on this node

sql

SELECT WSREP_LAST_SEEN_GTID();

→-- '1-1-12345'

Check Galera is active

sql

SELECT WSREP_LAST_SEEN_GTID() IS NOT NULL;

→-- 1 (on Galera node)

Capture and sync

sql

SET @gtid = WSREP_LAST_SEEN_GTID(); SELECT WSREP_SYNC_WAIT_UPTO_GTID(@gtid);

→-- 1

Compare seen vs written

sql

SELECT WSREP_LAST_SEEN_GTID(), WSREP_LAST_WRITTEN_GTID();

→-- Two GTID values

Non-Galera returns NULL

sql

SELECT WSREP_LAST_SEEN_GTID() IS NULL;

→-- 1 (non-Galera) or 0

⚠Anti-Pattern— Using WSREP functions on non-Galera MariaDB

WSREP_* functions return NULL or error on standard MariaDB replication setups without Galera.

✓ Instead: Check the wsrep_on variable or test for NULL before using WSREP_* functions in portable code.

example

SELECT WSREP_LAST_SEEN_GTID();

→'4b6dd1cd-1234-11ec-b98d-a6c1b1f3a7a0:1-100'

◆MariaDB Note

Only meaningful on nodes running Galera Cluster (wsrep_on=ON).

⇄ vs MySQL: MySQL does not have WSREP functions; PXC (Percona XtraDB Cluster) has similar functions.

⇄ vs PostgreSQL: PostgreSQL Patroni/BDR have different consistency mechanisms; no direct equivalent function.

WSREP_LAST_WRITTEN_GTID

MariaDB 10.1+ (Galera)→ VARCHAR

Returns the GTID of the last write transaction written (originated) by the current Galera cluster node.

Signature

WSREP_LAST_WRITTEN_GTID()

Examples

GTID of last local write

sql

SELECT WSREP_LAST_WRITTEN_GTID();

→-- '1-2-9876'

After a write transaction

sql

BEGIN; UPDATE accounts SET balance=100 WHERE id=1; COMMIT; SELECT WSREP_LAST_WRITTEN_GTID();

→-- GTID after commit

Check if node is writer

sql

SELECT WSREP_LAST_WRITTEN_GTID() = WSREP_LAST_SEEN_GTID();

→-- 0 or 1

No writes on this node

sql

SELECT WSREP_LAST_WRITTEN_GTID() IS NULL;

→-- 1 (no writes yet)

Returns NULL outside Galera

sql

SELECT WSREP_LAST_WRITTEN_GTID();

→-- NULL (non-Galera)

⚠Anti-Pattern— Confusing WSREP_LAST_WRITTEN_GTID with WSREP_LAST_SEEN_GTID

WRITTEN tracks what this node wrote; SEEN tracks the latest transaction applied from any node — they differ in multi-writer topologies.

✓ Instead: For read-after-write on the same node's writes, use WSREP_LAST_WRITTEN_GTID(); for cluster-wide sync, use WSREP_LAST_SEEN_GTID().

example

SELECT WSREP_LAST_WRITTEN_GTID();

→'4b6dd1cd-1234-11ec-b98d-a6c1b1f3a7a0:1-101'

◆MariaDB Note

Returns NULL if no write transactions have been executed on this node or if Galera is not active.

⇄ vs MySQL: MySQL does not have WSREP functions.

⇄ vs PostgreSQL: No direct equivalent in PostgreSQL.

WSREP_SYNC_WAIT_UPTO_GTID

MariaDB 10.1+ (Galera)→ INT

Blocks until the Galera node has applied all transactions up to the specified GTID, returning 1 on success or raising an error on timeout.

Signatures

WSREP_SYNC_WAIT_UPTO_GTID(gtid)

WSREP_SYNC_WAIT_UPTO_GTID(gtid, timeout)

Parameters

Parameter	Type	Description
gtid	VARCHAR	GTID position to wait for (as returned by WSREP_LAST_WRITTEN_GTID or WSREP_LAST_SEEN_GTID)
timeout	INT	Maximum seconds to wait; if omitted, waits indefinitely

Examples

Wait for GTID, indefinitely

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID('1-1-100');

→-- 1

Wait up to 10 seconds

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID('1-1-100', 10);

→-- 1 or error

Causal read pattern

sql

SET @g = WSREP_LAST_WRITTEN_GTID(); SELECT WSREP_SYNC_WAIT_UPTO_GTID(@g, 5);

→-- 1

Sync to latest seen

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID(WSREP_LAST_SEEN_GTID(), 30);

→-- 1

NULL handling

sql

SELECT WSREP_SYNC_WAIT_UPTO_GTID(NULL);

→-- NULL

⚠Anti-Pattern— Calling WSREP_SYNC_WAIT_UPTO_GTID without a timeout in production

Without a timeout, a lagging or partitioned node will block the calling thread indefinitely.

✓ Instead: Always specify a reasonable timeout and handle the error case in application code.

example

SELECT WSREP_SYNC_WAIT_UPTO_GTID('uuid:seqno', 5);

→0 (cluster synced to GTID within 5s)

◆MariaDB Note

Raises ER_LOCK_WAIT_TIMEOUT error (not returns -1) when timeout is exceeded, unlike MASTER_GTID_WAIT.

⇄ vs MySQL: MySQL/PXC uses a wsrep_sync_wait session variable approach rather than a function.

⇄ vs PostgreSQL: No direct equivalent; BDR extension has similar functionality.

NAME_CONST

MariaDB 5.5+→ Same as value type

Returns the given value while assigning a specific column name to it in the result set; primarily used internally by stored procedures for replication.

Signature

NAME_CONST(name, value)

Parameters

Parameter	Type	Description
name	VARCHAR	Column name to assign in the result set
value	any literal	Constant literal value to return (must be a literal, not an expression or column reference)

Examples

Integer with custom name

sql

SELECT NAME_CONST('my_col', 42);

→-- Column named 'my_col' with value 42

String value

sql

SELECT NAME_CONST('greeting', 'Hello');

→-- Column 'greeting' = 'Hello'

Decimal value

sql

SELECT NAME_CONST('pi_approx', 3.14159);

→-- Column 'pi_approx' = 3.14159

Boolean value

sql

SELECT NAME_CONST('flag', TRUE);

→-- Column 'flag' = 1

NULL value

sql

SELECT NAME_CONST('empty', NULL);

→-- NULL

⚠Anti-Pattern— Using NAME_CONST with non-literal expressions

NAME_CONST requires its second argument to be a constant literal; passing a function call or column reference causes an error.

✓ Instead: Use a subquery with AS alias syntax: SELECT expr AS col_name FROM ...

example

SELECT NAME_CONST('greeting', 'Hello');

→'Hello' — alias as 'greeting' in result column

◆MariaDB Note

Mainly used in CREATE PROCEDURE binlog events to preserve parameter names for replication; not intended for general query use.

⇄ vs PostgreSQL: No equivalent in PostgreSQL; use AS alias in SELECT.

CONVERT

MariaDB 5.5+→ Varies (depends on target type)

Converts a value to a specified data type, or converts a string's character set using the USING clause.

Signatures

CONVERT(expr, type)

CONVERT(expr USING charset_name)

Parameters

Parameter	Type	Description
expr	any	Value or expression to convert
type	type keyword	Target data type (e.g., CHAR, SIGNED, UNSIGNED, DECIMAL, DATE, DATETIME, BINARY)
charset_name	VARCHAR	Target character set name when using USING clause (e.g., utf8mb4, latin1)

Examples

String to integer

sql

SELECT CONVERT('42', SIGNED);

→-- 42

Float to unsigned int (rounds)

sql

SELECT CONVERT(3.7, UNSIGNED);

→-- 4

String to DATE

sql

SELECT CONVERT('2024-01-15', DATE);

→-- 2024-01-15

Character set conversion

sql

SELECT CONVERT('hello' USING utf8mb4);

→-- 'hello' in utf8mb4

NULL handling

sql

SELECT CONVERT(NULL, CHAR);

→-- NULL

⚠Anti-Pattern— Using CONVERT for locale-sensitive formatting

CONVERT does not apply locale-specific number or date formatting; it performs type casting only.

✓ Instead: Use FORMAT() for locale-aware number formatting or DATE_FORMAT() for date string formatting.

example

SELECT CONVERT('café' USING utf8mb4);

→'café' in utf8mb4 encoding

◆MariaDB Note

CONVERT(expr, type) and CAST(expr AS type) are equivalent for type conversion; CONVERT USING is unique to CONVERT.

⇄ vs PostgreSQL: PostgreSQL uses expr::type syntax or CAST(expr AS type); character set conversion uses convert(bytea, name, name) or client_encoding settings.

CAST

CAST to FLOAT/DOUBLE available from MariaDB 10.7.→ Varies (depends on target type)

Converts a value to the specified SQL data type using standard SQL syntax.

Signature

CAST(expr AS type)

Parameters

Parameter	Type	Description
expr	any	Value or expression to cast
type	type keyword	Target type: CHAR[(n)], BINARY[(n)], DATE, DATETIME, TIME, DECIMAL[(m[,d])], SIGNED [INTEGER], UNSIGNED [INTEGER], JSON, FLOAT, DOUBLE, INTERVAL

Examples

String to signed integer

sql

SELECT CAST('123' AS SIGNED);

→-- 123

Float to decimal with precision

sql

SELECT CAST(9.99 AS DECIMAL(5,1));

→-- 10.0

String to DATE

sql

SELECT CAST('2024-06-01' AS DATE);

→-- 2024-06-01

Integer to string

sql

SELECT CAST(255 AS CHAR);

→-- '255'

NULL handling

sql

SELECT CAST(NULL AS UNSIGNED);

→-- NULL

⚠Anti-Pattern— Casting to FLOAT expecting lossless precision

CAST(decimal_value AS FLOAT) can introduce floating-point rounding errors for monetary or exact decimal values.

✓ Instead: Use CAST(expr AS DECIMAL(m,d)) to preserve exact decimal precision.

example

SELECT CAST('2026-03-16' AS DATE), CAST(3.14 AS DECIMAL(10,2));

→'2026-03-16' | 3.14

◆MariaDB Note

MariaDB 10.4+ added CAST(expr AS INTERVAL); MariaDB 10.7+ added CAST(expr AS FLOAT) and CAST(expr AS DOUBLE).

⇄ vs PostgreSQL: PostgreSQL supports CAST(expr AS type) and the shorthand expr::type; supports more types like ARRAY, JSON, custom types.

VALUES

MariaDB 5.5+→ Same as the referenced column type

In an INSERT ... ON DUPLICATE KEY UPDATE statement, returns the value that would have been inserted into the specified column (the new value, not the existing one).

Signature

VALUES(col_name)

Parameters

Parameter	Type	Description
col_name	column reference	Name of the column whose prospective INSERT value you want to reference in the ON DUPLICATE KEY UPDATE clause

Examples

Increment on duplicate

sql

INSERT INTO stats (id, hits) VALUES (1, 10) ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits);

→-- hits incremented by 10

Multi-column upsert

sql

INSERT INTO cache (k, v, updated_at) VALUES ('key1','val',NOW()) ON DUPLICATE KEY UPDATE v=VALUES(v), updated_at=VALUES(updated_at);

→-- Upsert pattern

Single column update

sql

INSERT INTO t (id, a, b) VALUES (1,10,20) ON DUPLICATE KEY UPDATE a=VALUES(a);

→-- a updated to 10

Counter pattern

sql

INSERT INTO counters (name, cnt) VALUES ('views', 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);

→-- counter incremented

NULL handling

sql

INSERT INTO t (id, val) VALUES (1, NULL) ON DUPLICATE KEY UPDATE val = VALUES(val);

→-- val set to NULL

⚠Anti-Pattern— Using VALUES() outside of ON DUPLICATE KEY UPDATE

VALUES() is only meaningful inside the ON DUPLICATE KEY UPDATE clause; using it elsewhere returns NULL or causes an error.

✓ Instead: Use VALUES() exclusively within ON DUPLICATE KEY UPDATE, or consider INSERT ... SELECT with explicit conflict handling.

example

INSERT INTO t(a,b) VALUES (1,2),(3,4) ON DUPLICATE KEY UPDATE b = VALUES(b);

→(2 rows inserted/updated)

◆MariaDB Note

The VALUES() function approach works in all MariaDB versions supporting ON DUPLICATE KEY UPDATE; the new_row alias (MariaDB 10.3.3+) is preferred for readability.

⇄ vs PostgreSQL: PostgreSQL uses INSERT ... ON CONFLICT DO UPDATE SET col = EXCLUDED.col (EXCLUDED pseudo-table alias, not VALUES()).

VEC_ADD

MariaDB 11.7+→ VECTOR

Adds two vectors element-wise. Both vectors must have the same number of dimensions. Returns a new vector where each element is the sum of the corresponding elements of the input vectors. Core building block for AI/ML vector arithmetic in the database.

Signature

VEC_ADD(vec1, vec2) → VECTOR

Parameters

Parameter	Type	Description
vec1	VECTOR / JSON array string	First input vector; must match vec2 in dimension count
vec2	VECTOR / JSON array string	Second input vector; must match vec1 in dimension count

Examples

Basic element-wise addition

sql

SELECT VEC_ADD('[1,2,3]', '[4,5,6]');

→-- [5,7,9]

Floating-point vectors

sql

SELECT VEC_ADD('[0.1,0.2]', '[0.9,0.8]');

→-- [1.0,1.0]

Orthogonal unit vectors

sql

SELECT VEC_ADD('[1,0,0]', '[0,1,0]');

→-- [1,1,0]

Column-to-column arithmetic

sql

SELECT VEC_ADD(embedding_a, embedding_b) FROM vector_pairs;

→-- element-wise sums per row

Step in centroid calculation

sql

SELECT VEC_ADD('[1,2,3]', '[4,5,6]') AS centroid_component;

→-- [5,7,9]

⚠Anti-Pattern— Adding vectors of mismatched dimensions

VEC_ADD requires both operands to have the same number of dimensions. Passing vectors of different lengths raises an error at runtime and there is no automatic padding.

✓ Instead: Always validate that vectors stored in the same column share a consistent dimension count — use a CHECK constraint or application-layer validation when inserting embeddings.

To compute the centroid of a cluster of vectors, accumulate VEC_ADD across all rows and then divide each element by the count. This lets you keep centroid arithmetic inside SQL without exporting data to Python.

example

SELECT VEC_ADD(v1, VEC_ADD(v2, v3)) FROM (SELECT '[1,0]' v1, '[0,1]' v2, '[1,1]' v3) t;

→-- [2,2]

◆MariaDB Note

Part of the native VECTOR data type system introduced in MariaDB 11.7. Vectors are stored as fixed-length binary blobs of 32-bit floats internally.

⇄ vs MySQL: MySQL has no native vector arithmetic functions; this is a MariaDB 11.7+ feature.

⇄ vs PostgreSQL: PostgreSQL (with pgvector extension) supports the + operator for vector addition: embedding1 + embedding2.

VEC_COSINE_DISTANCE

MariaDB 11.7+→ DOUBLE

Computes the cosine distance between two vectors. Cosine distance = 1 − cosine similarity. Range is [0, 2]: 0 means identical direction (most similar), 1 means orthogonal (unrelated), 2 means exactly opposite direction. Widely used in semantic search, recommendation systems, and NLP embedding comparisons.

Signature

VEC_COSINE_DISTANCE(vec1, vec2) → DOUBLE

Parameters

Parameter	Type	Description
vec1	VECTOR / JSON array string	First input vector
vec2	VECTOR / JSON array string	Second input vector; must match vec1 in dimension count

Examples

Identical vectors → distance 0

sql

SELECT VEC_COSINE_DISTANCE('[1,0,0]', '[1,0,0]');

→-- 0

Orthogonal vectors → distance 1

sql

SELECT VEC_COSINE_DISTANCE('[1,0,0]', '[0,1,0]');

→-- 1

Opposite vectors → distance 2

sql

SELECT VEC_COSINE_DISTANCE('[1,0,0]', '[-1,0,0]');

→-- 2

Nearest-neighbor search

sql

SELECT id, VEC_COSINE_DISTANCE(embedding, '[0.1,0.9,0.3]') AS dist FROM docs ORDER BY dist LIMIT 5;

→-- top 5 most semantically similar docs

Parallel vectors (same direction, different magnitude) → distance 0

sql

SELECT VEC_COSINE_DISTANCE('[1,2,3]', '[2,4,6]');

→-- 0

⚠Anti-Pattern— Using cosine distance on zero vectors

A zero vector [0,0,...,0] has no direction; cosine distance is mathematically undefined (division by zero in the denominator). MariaDB may return NULL or raise an error.

✓ Instead: Filter out zero vectors before computing cosine distance, or use VEC_EUCLIDEAN_DISTANCE as a fallback for degenerate cases.

When all stored vectors are pre-normalized to unit length (via VEC_NORMALIZE at insert time), cosine distance reduces to 1 − dot product. This means VEC_DOT_PRODUCT on unit vectors is equivalent and may use SIMD optimizations more effectively.

example

INSERT INTO docs (embedding) VALUES (VEC_NORMALIZE('[3,4,0]'));

→-- stores [0.6,0.8,0] instead

◆MariaDB Note

Available as part of the native VECTOR type system. For large tables, create a VECTOR INDEX to accelerate approximate nearest-neighbor searches.

⇄ vs MySQL: MySQL 9.0+ added vector support with DISTANCE(v1, v2, 'COSINE'); MariaDB uses the VEC_* function family instead.

⇄ vs PostgreSQL: pgvector uses the <=> operator for cosine distance: embedding1 <=> embedding2.

VEC_DOT_PRODUCT

MariaDB 11.7+→ DOUBLE

Computes the dot product (inner product) of two vectors. The dot product is the sum of the element-wise products: Σ(vec1[i] × vec2[i]). A higher positive value indicates vectors pointing in the same direction with large magnitudes; 0 indicates orthogonality. When both vectors are unit-normalized, the dot product equals cosine similarity.

Signature

VEC_DOT_PRODUCT(vec1, vec2) → DOUBLE

Parameters

Parameter	Type	Description
vec1	VECTOR / JSON array string	First input vector
vec2	VECTOR / JSON array string	Second input vector; must match vec1 in dimension count

Examples

1×4 + 2×5 + 3×6 = 4+10+18 = 32

sql

SELECT VEC_DOT_PRODUCT('[1,2,3]', '[4,5,6]');

→-- 32

Orthogonal unit vectors → 0

sql

SELECT VEC_DOT_PRODUCT('[1,0,0]', '[0,1,0]');

→-- 0

Unit vector dot itself = 1 (its squared magnitude)

sql

SELECT VEC_DOT_PRODUCT('[0.6,0.8]', '[0.6,0.8]');

→-- 1.0

Ranking search results

sql

SELECT id, VEC_DOT_PRODUCT(embedding, query_vec) AS score FROM items ORDER BY score DESC LIMIT 10;

→-- top 10 by similarity score

Perpendicular 2D vectors

sql

SELECT VEC_DOT_PRODUCT('[2,3]', '[-3,2]');

→-- 0

⚠Anti-Pattern— Using dot product without normalization for similarity ranking

Raw dot product favors high-magnitude vectors regardless of direction. A vector [100,100] will outscore [1,1] even if both point in the same direction relative to the query.

✓ Instead: Normalize vectors with VEC_NORMALIZE before storing, or use VEC_COSINE_DISTANCE which accounts for magnitude internally.

If you normalize all embeddings to unit length when inserting (using VEC_NORMALIZE), then VEC_DOT_PRODUCT gives you cosine similarity directly — without the overhead of computing magnitudes at query time. This is the standard trick used by vector databases for fast similarity ranking.

example

SELECT VEC_DOT_PRODUCT(VEC_NORMALIZE('[3,4]'), VEC_NORMALIZE('[6,8]'));

→-- 1.0 (same direction)

◆MariaDB Note

Operates on the native VECTOR type. Can also accept JSON-style array strings like '[1,2,3]' which MariaDB automatically converts.

⇄ vs MySQL: MySQL 9.0+ has no direct dot product function; use DISTANCE() with 'DOT' mode or compute manually.

⇄ vs PostgreSQL: pgvector uses the <#> operator for negative inner product (for max-inner-product search): -(embedding <#> query).

VEC_EUCLIDEAN_DISTANCE

MariaDB 11.7+→ DOUBLE

Computes the Euclidean (L2) distance between two vectors. This is the straight-line distance in high-dimensional space: √(Σ(vec1[i] − vec2[i])²). Lower values indicate higher similarity. Standard metric for image similarity, clustering (k-means), and anomaly detection.

Signature

VEC_EUCLIDEAN_DISTANCE(vec1, vec2) → DOUBLE

Parameters

Parameter	Type	Description
vec1	VECTOR / JSON array string	First input vector
vec2	VECTOR / JSON array string	Second input vector; must match vec1 in dimension count

Examples

Classic 3-4-5 right triangle

sql

SELECT VEC_EUCLIDEAN_DISTANCE('[0,0]', '[3,4]');

→-- 5

Identical vectors → distance 0

sql

SELECT VEC_EUCLIDEAN_DISTANCE('[1,1,1]', '[1,1,1]');

→-- 0

√3 ≈ 1.732

sql

SELECT VEC_EUCLIDEAN_DISTANCE('[0,0,0]', '[1,1,1]');

→-- 1.7320508...

k-NN search

sql

SELECT id, VEC_EUCLIDEAN_DISTANCE(embedding, '[0.2,0.5,0.1]') AS dist FROM items ORDER BY dist LIMIT 5;

→-- 5 nearest neighbors by L2 distance

Radius / ball query

sql

SELECT VEC_EUCLIDEAN_DISTANCE(centroid, embedding) FROM clusters JOIN items USING(cluster_id) HAVING dist < 0.5;

→-- items within radius 0.5 of centroid

⚠Anti-Pattern— Using Euclidean distance for text embeddings from language models

Language model embeddings (BERT, OpenAI, etc.) are designed for cosine similarity, not Euclidean distance. High-dimensional text embeddings have nearly equal L2 norms, but the direction (angle) carries the semantic meaning.

✓ Instead: Use VEC_COSINE_DISTANCE for text/NLP embeddings; reserve VEC_EUCLIDEAN_DISTANCE for image features, tabular numeric embeddings, or models explicitly trained with L2 distance.

Euclidean distance ranking is equivalent to squared-distance ranking because √ is monotone. If your DB optimizer does not inline the SQRT, computing (a-b)² per element and summing (done outside MariaDB) avoids the square root entirely. For MariaDB's VEC_EUCLIDEAN_DISTANCE the computation is already vectorized internally, so just use the function directly.

example

SELECT id FROM items ORDER BY VEC_EUCLIDEAN_DISTANCE(embedding, query_vec) LIMIT 10;

→-- exact 10-NN

◆MariaDB Note

A VECTOR INDEX on the embedding column will use approximate nearest-neighbor (ANN) algorithms to accelerate ORDER BY VEC_EUCLIDEAN_DISTANCE(...) LIMIT N queries.

⇄ vs MySQL: MySQL 9.0+ uses DISTANCE(v1, v2, 'EUCLIDEAN'); MariaDB uses VEC_EUCLIDEAN_DISTANCE().

⇄ vs PostgreSQL: pgvector uses the <-> operator for L2 distance: embedding1 <-> embedding2.

VEC_NORMALIZE

MariaDB 11.7+→ VECTOR

Normalizes a vector to unit length (L2 norm = 1) by dividing each element by the vector's magnitude. The result lies on the unit hypersphere. Useful as a preprocessing step before cosine similarity comparisons, since normalized dot products equal cosine similarity.

Signature

VEC_NORMALIZE(vec) → VECTOR

Parameters

Parameter	Type	Description
vec	VECTOR / JSON array string	Input vector to normalize; must be non-zero

Examples

3-4 vector: magnitude=5, so 3/5=0.6, 4/5=0.8

sql

SELECT VEC_NORMALIZE('[3,4]');

→-- [0.6,0.8]

Already a unit vector

sql

SELECT VEC_NORMALIZE('[1,0,0]');

→-- [1,0,0]

1/√2 ≈ 0.7071

sql

SELECT VEC_NORMALIZE('[1,1]');

→-- [0.7071...,0.7071...]

Normalize at insert time

sql

INSERT INTO docs (id, embedding) VALUES (1, VEC_NORMALIZE('[3,1,4,1,5]'));

→-- stores unit vector

Parallel vectors → cosine similarity = 1

sql

SELECT VEC_DOT_PRODUCT(VEC_NORMALIZE('[3,4]'), VEC_NORMALIZE('[6,8]'));

→-- 1.0

⚠Anti-Pattern— Normalizing a zero vector

A zero vector [0,0,...,0] has magnitude 0; dividing by zero is undefined. VEC_NORMALIZE will return NULL or raise an error for a zero vector.

✓ Instead: Guard against zero vectors: WHERE VEC_EUCLIDEAN_DISTANCE(embedding, '[0,0,0]') > 0, or handle the NULL result downstream with COALESCE.

If you normalize embeddings when inserting rows, every query-time similarity computation becomes a cheap dot product on pre-normalized vectors. Normalizing at query time means repeating the same division for every row scanned — normalize once, query many times.

example

UPDATE docs SET embedding = VEC_NORMALIZE(embedding) WHERE embedding IS NOT NULL;

→-- back-fills existing rows with unit vectors

◆MariaDB Note

The result is a VECTOR of the same dimension with L2 norm = 1. Useful as a DEFAULT expression or GENERATED COLUMN: embedding_norm VECTOR(768) AS (VEC_NORMALIZE(embedding)) STORED.

⇄ vs MySQL: MySQL 9.0+ has no VEC_NORMALIZE equivalent; normalize externally before inserting.

⇄ vs PostgreSQL: pgvector does not have a normalize function; normalize in application code (e.g., numpy) before inserting, or use the l2_normalize() function added in pgvector 0.5.0.

MATCH ... AGAINST

MariaDB 5.5+ (InnoDB FULLTEXT: MariaDB 10.0+)→ DOUBLE (relevance score)

Full-text search operator. MATCH(col1, col2, ...) specifies the columns to search (which must have a FULLTEXT index); AGAINST(expr [mode]) specifies the search expression and optional search mode. Returns a floating-point relevance score — higher means a stronger match. Three modes are supported: NATURAL LANGUAGE MODE (default, rank by relevance), BOOLEAN MODE (supports +, -, *, " operators), and WITH QUERY EXPANSION (two-pass: find matches, expand with related terms, re-rank).

Signatures

MATCH(col1 [, col2, ...]) AGAINST (expr) → DOUBLE

MATCH(col1 [, col2, ...]) AGAINST (expr IN NATURAL LANGUAGE MODE) → DOUBLE

MATCH(col1 [, col2, ...]) AGAINST (expr IN BOOLEAN MODE) → DOUBLE

MATCH(col1 [, col2, ...]) AGAINST (expr WITH QUERY EXPANSION) → DOUBLE

Parameters

Parameter	Type	Description
col1, col2, ...	TEXT / VARCHAR columns	Columns to search; must be covered by a FULLTEXT index, listed in the same order as in the index definition
expr	VARCHAR / string literal	The search string or Boolean expression
mode	keyword	Optional: IN NATURAL LANGUAGE MODE (default), IN BOOLEAN MODE, or WITH QUERY EXPANSION

Examples

Natural language search with relevance ordering

sql

SELECT id, title, MATCH(title, body) AGAINST('database performance') AS score FROM articles WHERE MATCH(title, body) AGAINST('database performance') ORDER BY score DESC;

→-- rows ranked by relevance

Boolean mode: + required, - excluded

sql

SELECT * FROM articles WHERE MATCH(body) AGAINST('+MariaDB -MySQL' IN BOOLEAN MODE);

→-- rows containing MariaDB but not MySQL

Phrase search with double quotes

sql

SELECT * FROM products WHERE MATCH(name, description) AGAINST('"full text"' IN BOOLEAN MODE);

→-- rows with exact phrase 'full text'

Wildcard/prefix search in Boolean mode

sql

SELECT * FROM articles WHERE MATCH(body) AGAINST('optim*' IN BOOLEAN MODE);

→-- matches optimize, optimization, optimal …

Query expansion for broader recall

sql

SELECT *, MATCH(title,body) AGAINST('indexing' WITH QUERY EXPANSION) AS score FROM docs WHERE MATCH(title,body) AGAINST('indexing' WITH QUERY EXPANSION) ORDER BY score DESC LIMIT 10;

→-- expanded result set including related terms

⚠Anti-Pattern— Running MATCH...AGAINST without a FULLTEXT index

Without a FULLTEXT index on the searched columns, MariaDB falls back to a full table scan and may even return an error in strict mode. Performance degrades catastrophically on large tables.

✓ Instead: Always create a FULLTEXT index first: ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, body); — and list the columns in MATCH() in the same order as in the index.

When you use an identical MATCH...AGAINST expression in both the WHERE clause and the SELECT list, MariaDB's optimizer evaluates the full-text index only once and reuses the score — there is no double computation penalty. Always put the filtering condition in WHERE to activate the FULLTEXT index.

example

SELECT title, MATCH(title,body) AGAINST('performance tuning') AS score FROM docs WHERE MATCH(title,body) AGAINST('performance tuning') ORDER BY score DESC LIMIT 20;

→-- efficient: single full-text index scan

◆MariaDB Note

Works with MyISAM and InnoDB FULLTEXT indexes. MariaDB also supports the Mroonga storage engine for Japanese/CJK full-text search. The minimum word length is controlled by ft_min_word_len (MyISAM) or innodb_ft_min_token_size (InnoDB), defaulting to 4.

⇄ vs MySQL: Identical syntax and behavior in MySQL 5.6+. MySQL's InnoDB FULLTEXT index behavior matches MariaDB's in most respects; MariaDB adds Mroonga storage engine as an alternative full-text backend.

⇄ vs PostgreSQL: PostgreSQL uses tsvector / tsquery types with the @@ operator: to_tsvector('english', body) @@ plainto_tsquery('english', 'search term'). The ts_rank() function provides relevance scoring. No MATCH...AGAINST syntax.

Related MariaDB Categories

System & Info →Encryption & Security →

MariaDB Miscellaneous & Network Functions

Jump to function

What are MariaDB Miscellaneous & Network Functions?

UUID

Signature

Examples

UUID_SHORT

Signature

Examples

SLEEP

Signature

Parameters

Examples

INET_ATON

Signature

Parameters

Examples

INET_NTOA

Signature

Parameters

Examples

INET6_ATON

Signature

Parameters

Examples

INET6_NTOA

Signature

Parameters

Examples

IS_IPV4

Signature

Parameters

Examples

IS_IPV4_COMPAT

Signature

Parameters

Examples

IS_IPV4_MAPPED

Signature

Parameters

Examples

IS_IPV6

Signature

Parameters

Examples

FORMAT_BYTES

Signature

Parameters

Examples

SYS_GUID

Signature

Examples

MASTER_POS_WAIT

Signatures

Parameters

Examples

MASTER_GTID_WAIT

Signatures

Parameters

Examples

SOURCE_POS_WAIT

Signatures

Parameters

Examples

GTID_SUBSET

Signature

Parameters

Examples

GTID_SUBTRACT

Signature

Parameters

Examples

WSREP_LAST_SEEN_GTID

Signature

Examples

WSREP_LAST_WRITTEN_GTID

Signature

Examples

WSREP_SYNC_WAIT_UPTO_GTID

Signatures