ℹ️

MariaDB System & Info Functions

MariaDB

Complete reference for MariaDB system and info functions covering VERSION(), DATABASE(), USER(), CONNECTION_ID(), LAST_INSERT_ID(), FOUND_ROWS(), and CURRENT_ROLE(). Every function includes syntax, use cases, and notes on session-scoped vs global behaviour. Updated for MariaDB 11.x.

27 functions

What are MariaDB System & Info Functions?

MariaDB system information functions return metadata about the current server session, database, and user. LAST_INSERT_ID() returns the AUTO_INCREMENT value generated by the most recent INSERT, FOUND_ROWS() returns the number of rows that would have been returned by the last SELECT without LIMIT, and ROW_COUNT() returns the number of rows affected by the last INSERT, UPDATE, DELETE, or REPLACE. These are frequently used in stored procedures and application code to handle post-insert logic.

VERSION

MariaDB 5.5+→ VARCHAR

Returns the MariaDB server version string, always including the '-MariaDB' suffix.

Signature

VERSION()

Examples

Get server version

sql

SELECT VERSION();

→-- 10.11.4-MariaDB

System variable alternative

sql

SELECT @@version;

→-- 10.11.4-MariaDB

Confirm running MariaDB

sql

SELECT VERSION() LIKE '%MariaDB%';

→-- 1

Extract major version

sql

SELECT SUBSTRING_INDEX(VERSION(), '.', 1) AS major;

→-- 10

Store in variable

sql

SELECT VERSION() INTO @v; SELECT @v;

→-- 10.11.4-MariaDB

⚠Anti-Pattern— Checking for MySQL with VERSION()

Checking VERSION() LIKE 'MySQL%' will fail on MariaDB. The string contains '-MariaDB' suffix.

✓ Instead: Check VERSION() LIKE '%MariaDB%' or use @@version_comment.

VERSION() always contains '-MariaDB' in MariaDB. Use VERSION() LIKE '%MariaDB%' in compatibility checks or stored procedures that must behave differently on MySQL vs MariaDB.

example

SELECT IF(VERSION() LIKE '%MariaDB%', 'MariaDB', 'MySQL') AS db_flavor;

→db_flavor: 'MariaDB'

◆MariaDB Note

Always includes '-MariaDB' in the version string.

⇄ vs MySQL: MySQL VERSION() returns '8.x.x' without '-MariaDB' suffix.

⇄ vs PostgreSQL: PostgreSQL uses version() or current_setting('server_version').

DATABASE

MariaDB 5.5+→ VARCHAR

Returns the name of the currently selected database, or NULL if no database is selected.

Signature

DATABASE()

Examples

Current database name

sql

SELECT DATABASE();

→-- mydb

Check if database selected

sql

SELECT DATABASE() IS NULL;

→-- 0

After USE statement

sql

USE information_schema; SELECT DATABASE();

→-- information_schema

Cross-reference with catalog

sql

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = DATABASE();

→-- mydb

Save current DB to variable

sql

SET @db = DATABASE(); SELECT @db;

→-- mydb

⚠Anti-Pattern— Hardcoding database name in queries

Using a literal database name in queries makes them non-portable across environments.

✓ Instead: Use DATABASE() to dynamically reference the current database in procedures.

DATABASE() returns NULL outside of any USE statement context, such as in event scheduler procedures or connections where no schema is set. Guard against this with COALESCE(DATABASE(), 'none').

example

SELECT COALESCE(DATABASE(), '(none)') AS current_db;

→current_db: 'myapp'

◆MariaDB Note

Synonym for SCHEMA(). Returns NULL if no database is selected.

⇄ vs MySQL: Identical behavior in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses current_database().

SCHEMA

MariaDB 5.5+→ VARCHAR

Synonym for DATABASE(). Returns the name of the currently selected database.

Signature

SCHEMA()

Examples

Current schema/database name

sql

SELECT SCHEMA();

→-- mydb

SCHEMA and DATABASE are identical

sql

SELECT SCHEMA() = DATABASE();

→-- 1

Fallback if no DB selected

sql

SELECT COALESCE(SCHEMA(), 'none') AS db;

→-- mydb

After switching database

sql

USE test; SELECT SCHEMA();

→-- test

List tables in current DB

sql

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA();

→-- (list of tables)

⚠Anti-Pattern— Mixing SCHEMA() and DATABASE() inconsistently

Using both interchangeably without convention can confuse readers.

✓ Instead: Pick one and use it consistently; SCHEMA() is closer to standard SQL.

SCHEMA() is a non-standard alias for DATABASE(). Most developers recognize DATABASE() immediately; SCHEMA() can be confused with information_schema. Use DATABASE() in new code for wider familiarity.

example

SELECT DATABASE() AS db, SCHEMA() AS sch;

→db: 'myapp', sch: 'myapp'

◆MariaDB Note

Exact synonym for DATABASE().

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses current_schema() or current_database().

USER

MariaDB 5.5+→ VARCHAR

Returns the current MariaDB user and host as 'user@host', representing the connection's authentication credentials.

Signature

USER()

Examples

Current user and host

sql

SELECT USER();

→-- admin@localhost

Extract just the username

sql

SELECT SUBSTRING_INDEX(USER(), '@', 1) AS username;

→-- admin

Extract just the host

sql

SELECT SUBSTRING_INDEX(USER(), '@', -1) AS host;

→-- localhost

Compare to current_user

sql

SELECT USER() = CURRENT_USER();

→-- 1 (usually)

DUAL table form

sql

SELECT USER() FROM DUAL;

→-- admin@localhost

⚠Anti-Pattern— Using USER() for permission checks

USER() returns the login credentials, not the effective privilege account. Security checks based on USER() can be bypassed via proxy users.

✓ Instead: Use CURRENT_USER() which reflects the actual privilege account.

USER() returns 'username@hostname' where hostname is the client's connecting address. This is more specific than CURRENT_USER() which returns the matched auth pattern. Use USER() in audit triggers to capture the actual connecting host.

example

INSERT INTO audit_log (who, action, ts) VALUES (USER(), 'DELETE', NOW());

→who: 'alice@192.168.1.10'

◆MariaDB Note

Returns 'user@host'. May differ from CURRENT_USER() when using definer-context or proxy accounts.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses session_user or current_user.

CURRENT_USER

MariaDB 5.5+→ VARCHAR

Returns the username and hostname combination that was matched in the grant tables for the current session, reflecting the effective privilege account.

Signatures

CURRENT_USER()

CURRENT_USER

Examples

Matched grant account

sql

SELECT CURRENT_USER();

→-- admin@%

Without parentheses (standard SQL)

sql

SELECT CURRENT_USER;

→-- admin@%

May differ with wildcards

sql

SELECT USER(), CURRENT_USER();

→-- admin@localhost | admin@%

Show own grants

sql

SHOW GRANTS FOR CURRENT_USER();

→-- GRANT ... TO 'admin'@'%'

Store in variable

sql

SELECT CURRENT_USER() INTO @cu;

→-- admin@%

⚠Anti-Pattern— Confusing USER() and CURRENT_USER()

USER() is what you logged in as; CURRENT_USER() is the grant row matched. For security decisions, always use CURRENT_USER().

✓ Instead: Use CURRENT_USER() for privilege-related logic.

CURRENT_USER() returns the matched entry from the mysql.user table, such as 'app@%'. This is useful for permission checks because it reflects the privilege row being used, not the specific connecting host.

example

SELECT USER() AS actual, CURRENT_USER() AS auth_pattern;

→actual: 'app@10.0.0.5', auth_pattern: 'app@%'

◆MariaDB Note

Supports both CURRENT_USER and CURRENT_USER() forms per SQL standard.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses current_user (keyword, no parentheses needed).

SESSION_USER

MariaDB 5.5+→ VARCHAR

Synonym for USER(). Returns the current client user and hostname used for authentication.

Signature

SESSION_USER()

Examples

Session user

sql

SELECT SESSION_USER();

→-- admin@localhost

SESSION_USER and USER are identical

sql

SELECT SESSION_USER() = USER();

→-- 1

Extract username part

sql

SELECT SUBSTRING_INDEX(SESSION_USER(), '@', 1);

→-- admin

DUAL table form

sql

SELECT SESSION_USER() FROM DUAL;

→-- admin@localhost

Embed in view

sql

CREATE VIEW session_info AS SELECT SESSION_USER() AS login_user;

→-- View created

⚠Anti-Pattern— Using SESSION_USER instead of CURRENT_USER for access control

SESSION_USER returns the login identity, not the privilege-matched account. Proxy users can log in as one user but match a different grant.

✓ Instead: Use CURRENT_USER() for access control decisions.

SESSION_USER() and USER() return identical values. SESSION_USER() exists for SQL standard compatibility. In MariaDB code, USER() is more commonly seen and immediately recognizable.

example

SELECT SESSION_USER();

→'alice@localhost'

◆MariaDB Note

Exact synonym for USER().

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses session_user.

SYSTEM_USER

MariaDB 5.5+→ VARCHAR

Synonym for USER(). Returns the current authenticated user and host. Provided for SQL standard compatibility.

Signature

SYSTEM_USER()

Examples

System user

sql

SELECT SYSTEM_USER();

→-- admin@localhost

All three user functions agree

sql

SELECT SYSTEM_USER() = SESSION_USER();

→-- 1

Synonym confirmation

sql

SELECT SYSTEM_USER() = USER();

→-- 1

Audit logging

sql

INSERT INTO audit_log (action, who) VALUES ('login', SYSTEM_USER());

→-- 1 row affected

Extract connecting host

sql

SELECT SUBSTRING_INDEX(SYSTEM_USER(), '@', -1) AS client_host;

→-- localhost

⚠Anti-Pattern— Assuming SYSTEM_USER() returns an OS-level user

Despite the name, SYSTEM_USER() in MariaDB returns the database login user, not the operating system user running the server process.

✓ Instead: To get the OS user, query @@global.secure_file_priv or use external tools.

In MariaDB, SYSTEM_USER() is simply another alias for USER(). This differs from SQL Server where SYSTEM_USER refers to the OS-level login. Do not assume OS-level semantics when reading MariaDB code.

example

SELECT SYSTEM_USER();

→'alice@localhost'

◆MariaDB Note

Exact synonym for USER() and SESSION_USER().

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses session_user.

CONNECTION_ID

MariaDB 5.5+→ BIGINT UNSIGNED

Returns the unique thread (connection) ID for the current client session. Useful for debugging, locking, and kill commands.

Signature

CONNECTION_ID()

Examples

Current connection thread ID

sql

SELECT CONNECTION_ID();

→-- 42

Find self in process list

sql

SHOW PROCESSLIST;

→-- (includes row with Id = CONNECTION_ID())

Query own process info

sql

SELECT * FROM information_schema.PROCESSLIST WHERE ID = CONNECTION_ID();

→-- (current session row)

Kill own running query

sql

SELECT CONNECTION_ID() INTO @cid; KILL QUERY @cid;

→-- (kills own query)

Log connection ID

sql

INSERT INTO session_log (conn_id, ts) VALUES (CONNECTION_ID(), NOW());

→-- 1 row affected

⚠Anti-Pattern— Using CONNECTION_ID() as a globally unique row ID

Connection IDs are reused after a connection closes. Using them as a permanent unique identifier in tables can cause collisions.

✓ Instead: Use UUID() or an AUTO_INCREMENT column for permanent unique identifiers.

CONNECTION_ID() is stable for the lifetime of a connection and useful for advisory locking patterns (GET_LOCK), for labeling rows in audit tables, and for matching entries in INFORMATION_SCHEMA.PROCESSLIST.

example

SELECT * FROM information_schema.PROCESSLIST WHERE ID = CONNECTION_ID();

→your own process list entry — shows current query, state, and elapsed time

◆MariaDB Note

Each new client connection gets a unique, incrementing thread ID.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_backend_pid().

LAST_INSERT_ID

MariaDB 5.5+→ BIGINT UNSIGNED

Returns the AUTO_INCREMENT value generated by the most recent INSERT statement in the current session, or sets a custom value for subsequent calls.

Signatures

LAST_INSERT_ID()

LAST_INSERT_ID(expr)

Parameters

Parameter	Type	Description
expr	BIGINT	If supplied, sets the return value for subsequent LAST_INSERT_ID() calls without performing an actual insert.

Examples

ID of just-inserted row

sql

INSERT INTO orders (product) VALUES ('Widget'); SELECT LAST_INSERT_ID();

→-- 101

Bulk insert returns first ID

sql

INSERT INTO t (a) VALUES (1),(2),(3); SELECT LAST_INSERT_ID();

→-- (ID of first inserted row)

Manually set the value

sql

SELECT LAST_INSERT_ID(42); SELECT LAST_INSERT_ID();

→-- 42

Atomic counter pattern

sql

UPDATE counters SET n = LAST_INSERT_ID(n+1) WHERE name='hits'; SELECT LAST_INSERT_ID();

→-- (incremented value)

System variable alternative

sql

SELECT @@last_insert_id;

→-- 101

⚠Anti-Pattern— Assuming LAST_INSERT_ID() works across sessions

LAST_INSERT_ID() is session-specific. Another connection's inserts do not affect your session's value.

✓ Instead: Always call LAST_INSERT_ID() in the same session immediately after the INSERT.

LAST_INSERT_ID() returns the AUTO_INCREMENT value generated by the most recent INSERT on the current connection. Other connections' inserts do not affect it, making it safe for concurrent workloads without locking.

example

INSERT INTO orders (customer_id, total) VALUES (42, 199.99); SELECT LAST_INSERT_ID() AS new_order_id;

→new_order_id: 10047

◆MariaDB Note

The two-argument form LAST_INSERT_ID(expr) is useful for implementing atomic counters in a single UPDATE statement.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses RETURNING id clause or lastval() / currval().

ROW_COUNT

MariaDB 5.5+→ BIGINT

Returns the number of rows affected by the most recent INSERT, UPDATE, DELETE, or REPLACE statement. Returns -1 for SELECT or statements that produce a result set.

Signature

ROW_COUNT()

Examples

Rows deleted

sql

DELETE FROM logs WHERE created_at < '2024-01-01'; SELECT ROW_COUNT();

→-- 1500

Rows updated

sql

UPDATE users SET active=0 WHERE last_login < NOW() - INTERVAL 1 YEAR; SELECT ROW_COUNT();

→-- 23

Rows inserted

sql

INSERT INTO t VALUES (1),(2),(3); SELECT ROW_COUNT();

→-- 3

SELECT always returns -1

sql

SELECT 1; SELECT ROW_COUNT();

→-- -1

After stored procedure

sql

CALL some_proc(); SELECT ROW_COUNT();

→-- (rows from last DML in proc)

⚠Anti-Pattern— Calling ROW_COUNT() after a SELECT

ROW_COUNT() returns -1 after a SELECT statement. It only reflects DML operations.

✓ Instead: Use FOUND_ROWS() or COUNT(*) to count SELECT results.

ROW_COUNT() is reset by every subsequent statement, including a SELECT. Call it in the very next statement after the INSERT/UPDATE/DELETE to capture the correct value, or assign it to a variable immediately.

example

UPDATE inventory SET qty = qty - 1 WHERE product_id = 5 AND qty > 0; SELECT ROW_COUNT() AS rows_updated;

→rows_updated: 1 (or 0 if qty was already 0)

◆MariaDB Note

Returns -1 for SELECT; 0 or positive for DML. The CLIENT_FOUND_ROWS connection flag changes UPDATE counting behavior.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses GET DIAGNOSTICS row_count in PL/pgSQL, or check the command tag.

FOUND_ROWS

MySQL deprecated in 8.0; MariaDB retains full support.→ BIGINT

Returns the number of rows that the preceding SELECT with SQL_CALC_FOUND_ROWS would have returned without the LIMIT clause. Used for pagination to get total result count in one round-trip.

Signature

FOUND_ROWS()

Examples

Total rows ignoring LIMIT

sql

SELECT SQL_CALC_FOUND_ROWS * FROM articles LIMIT 10; SELECT FOUND_ROWS();

→-- 342

Pagination: total count

sql

SELECT SQL_CALC_FOUND_ROWS id FROM products WHERE active=1 LIMIT 20 OFFSET 40; SELECT FOUND_ROWS();

→-- 150

Without SQL_CALC_FOUND_ROWS returns rows in last result

sql

SELECT * FROM t; SELECT FOUND_ROWS();

→-- (same as ROW_COUNT of resultset)

Zero results

sql

SELECT SQL_CALC_FOUND_ROWS * FROM t WHERE 1=0 LIMIT 10; SELECT FOUND_ROWS();

→-- 0

Store total for use in procedure

sql

SELECT FOUND_ROWS() INTO @total;

→-- @total = 342

⚠Anti-Pattern— Using FOUND_ROWS() without SQL_CALC_FOUND_ROWS

Without the SQL_CALC_FOUND_ROWS hint, FOUND_ROWS() just returns the number of rows in the last result set, not the full count without LIMIT.

✓ Instead: Always pair FOUND_ROWS() with SELECT SQL_CALC_FOUND_ROWS ... LIMIT n.

FOUND_ROWS() only works when the preceding SELECT included SQL_CALC_FOUND_ROWS. On MariaDB 10.1+ with optimizations, SQL_CALC_FOUND_ROWS can add overhead. For new pagination code, a separate COUNT(*) query with the same WHERE clause is often cleaner and more index-friendly.

example

SELECT SQL_CALC_FOUND_ROWS id, name FROM products WHERE active=1 LIMIT 20; SELECT FOUND_ROWS() AS total;

→total: 347 — total matching rows ignoring LIMIT

◆MariaDB Note

Not deprecated in MariaDB. Actively maintained and preferred for pagination in many MariaDB applications.

⇄ vs MySQL: MySQL 8.0 deprecated SQL_CALC_FOUND_ROWS and FOUND_ROWS(). MariaDB keeps them active and has no deprecation plan.

⇄ vs PostgreSQL: PostgreSQL has no direct equivalent; use a CTE with COUNT(*) or a window function.

BENCHMARK

MariaDB 5.5+→ INT

Executes an expression a specified number of times and returns 0. Used to measure the performance/speed of scalar expressions.

Signature

BENCHMARK(count, expr)

Parameters

Parameter	Type	Description
count	BIGINT UNSIGNED	Number of times to evaluate the expression.
expr	any scalar	The scalar expression to evaluate repeatedly.

Examples

Benchmark MD5 hashing

sql

SELECT BENCHMARK(1000000, MD5('hello'));

→-- 0 (check elapsed time)

Benchmark encryption

sql

SELECT BENCHMARK(500000, AES_ENCRYPT('text', 'key'));

→-- 0

Baseline arithmetic benchmark

sql

SELECT BENCHMARK(1000000, 1+1);

→-- 0

Compare SHA2 cost

sql

SELECT BENCHMARK(100000, SHA2('password', 256));

→-- 0

Benchmark a subquery (use low count)

sql

SELECT BENCHMARK(1000, (SELECT COUNT(*) FROM large_table));

→-- 0

⚠Anti-Pattern— Using BENCHMARK() with very high counts on expensive queries

Running BENCHMARK(1000000, complex_subquery) can lock up your server. Subqueries are re-executed each iteration.

✓ Instead: Use small counts (100–10000) for subqueries, or benchmark with scalar built-in functions only.

BENCHMARK(n, expr) executes expr n times and returns 0 (the duration is shown in the client's timing). It is useful for comparing two alternative expressions (e.g., SHA1 vs SHA2, REGEXP vs LIKE) without the overhead of table scans.

example

SELECT BENCHMARK(1000000, SHA2('test', 256));

→0 (check client elapsed time for comparison)

◆MariaDB Note

Returns 0, not timing data. Timing is observed via client elapsed time.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL has no built-in BENCHMARK(); use pgbench or EXPLAIN ANALYZE.

CHARSET

MariaDB 5.5+→ VARCHAR

Returns the character set of a string argument. Reflects the character set encoding of the given value.

Signature

CHARSET(str)

Parameters

Parameter	Type	Description
str	VARCHAR/any	The string value whose character set is to be returned.

Examples

Default client charset

sql

SELECT CHARSET('hello');

→-- utf8mb4

After CONVERT

sql

SELECT CHARSET(CONVERT('hello' USING latin1));

→-- latin1

System function result charset

sql

SELECT CHARSET(USER());

→-- utf8mb3

Check column charset

sql

SELECT col, CHARSET(col) FROM t LIMIT 1;

→-- (col value) | utf8mb4

Charset introducer

sql

SELECT CHARSET(_utf8mb4 'test');

→-- utf8mb4

⚠Anti-Pattern— Assuming all strings have the same charset

Mixing charsets (e.g., utf8mb3 and utf8mb4) in JOINs or comparisons can cause implicit conversions and index misses.

✓ Instead: Use CHARSET() to diagnose, then standardize columns to utf8mb4.

CHARSET(expr) reveals the character set of any string expression or column. When a JOIN or comparison produces unexpected results, checking CHARSET on both sides quickly identifies a charset mismatch.

example

SELECT CHARSET(name), CHARSET(CONVERT(name USING utf8mb4)) FROM users LIMIT 1;

→latin1, utf8mb4

◆MariaDB Note

Returns the character set name as a string.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have a CHARSET() function; use pg_catalog functions.

COERCIBILITY

MariaDB 5.5+→ INT

Returns the collation coercibility value of a string argument. The coercibility value determines which collation wins when two strings of different collations are compared.

Signature

COERCIBILITY(str)

Parameters

Parameter	Type	Description
str	VARCHAR/any	The string expression whose coercibility is checked.

Examples

Literal string = coercibility 4

sql

SELECT COERCIBILITY('hello');

→-- 4

System function = coercibility 3

sql

SELECT COERCIBILITY(USER());

→-- 3

Column = coercibility 2

sql

SELECT COERCIBILITY(col) FROM t LIMIT 1;

→-- 2

Explicit COLLATE = coercibility 0

sql

SELECT COERCIBILITY('x' COLLATE utf8mb4_bin);

→-- 0

NULL = coercibility 6 (ignorable)

sql

SELECT COERCIBILITY(NULL);

→-- 6

⚠Anti-Pattern— Ignoring coercibility when diagnosing collation errors

When you get 'Illegal mix of collations', not knowing the coercibility of each side makes the error hard to fix.

✓ Instead: Run SELECT COERCIBILITY(expr) on both sides to understand which should be cast.

When two operands in a comparison have different collations, the one with the lower coercibility value 'wins'. Literals have coercibility 4; columns have 2; explicit COLLATE clauses have 0. Understanding this prevents 'Illegal mix of collations' errors.

example

SELECT COERCIBILITY(name), COERCIBILITY('literal'), COERCIBILITY(name COLLATE utf8mb4_bin) FROM users LIMIT 1;

→2, 4, 0

◆MariaDB Note

Scale: 0 (explicit COLLATE) < 1 (no collation) < 2 (column) < 3 (system func) < 4 (literal) < 5 (numeric/temporal) < 6 (NULL).

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL handles collation differently; no direct COERCIBILITY() equivalent.

COLLATION

MariaDB 5.5+→ VARCHAR

Returns the collation name of the given string argument.

Signature

COLLATION(str)

Parameters

Parameter	Type	Description
str	VARCHAR/any	The string expression whose collation is to be returned.

Examples

Default collation for literal

sql

SELECT COLLATION('hello');

→-- utf8mb4_general_ci

Collation of a table column

sql

SELECT COLLATION(col) FROM t LIMIT 1;

→-- utf8mb4_unicode_ci

After CONVERT

sql

SELECT COLLATION(CONVERT('hi' USING utf8mb4));

→-- utf8mb4_general_ci

Explicit collation

sql

SELECT COLLATION('x' COLLATE utf8mb4_bin);

→-- utf8mb4_bin

System function collation

sql

SELECT COLLATION(USER());

→-- utf8mb3_general_ci

⚠Anti-Pattern— Assuming columns are case-insensitive

A column with _bin or _cs collation will treat 'Hello' and 'hello' as different values.

✓ Instead: Use COLLATION(col) to check, then add a COLLATE clause if you need to override.

COLLATION(expr) shows the collation that will be used when the expression participates in a comparison or sort. In queries mixing columns from tables with different default collations, checking COLLATION() on both sides reveals implicit coercions.

example

SELECT COLLATION(name) FROM users LIMIT 1;

→utf8mb4_unicode_ci

◆MariaDB Note

Returns the full collation name including charset prefix.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have a COLLATION() function; use pg_attribute or information_schema.

CURRENT_ROLE

Available since MariaDB 10.0.5.→ VARCHAR

Returns the currently active role(s) for the session, or NONE if no role is set. MariaDB roles were introduced in 10.0.5.

Signature

CURRENT_ROLE()

Examples

No role currently active

sql

SELECT CURRENT_ROLE();

→-- NONE

After activating a role

sql

SET ROLE analyst; SELECT CURRENT_ROLE();

→-- analyst

Activate all granted roles

sql

SET ROLE ALL; SELECT CURRENT_ROLE();

→-- (all granted roles)

Set default role

sql

SET DEFAULT ROLE analyst FOR 'bob'@'%'; SELECT CURRENT_ROLE();

→-- analyst (on next login)

Store role in variable

sql

SELECT CURRENT_ROLE() INTO @role; SELECT @role;

→-- analyst

⚠Anti-Pattern— Expecting CURRENT_ROLE() to auto-activate roles

Even if a user has been granted a role, CURRENT_ROLE() returns NONE unless SET ROLE or SET DEFAULT ROLE has been applied.

✓ Instead: Use SET DEFAULT ROLE for automatic activation, or SET ROLE at session start.

MariaDB 10.0.5+ supports role-based access control. CURRENT_ROLE() returns the currently active role(s) for the session. Use it in security audit queries or stored procedures that must behave differently based on the caller's role.

example

SELECT CURRENT_ROLE();

→'analyst' (or 'NONE' if no role is set)

◆MariaDB Note

Roles introduced in MariaDB 10.0.5. MySQL has a different role activation model.

⇄ vs MySQL: MySQL 8.0 added roles but uses @@role_xxx variables; behavior differs subtly.

⇄ vs PostgreSQL: PostgreSQL uses current_role (SQL keyword).

DEFAULT

MariaDB 5.5+→ varies

Returns the default value defined for a table column. Used in INSERT/UPDATE statements to explicitly request the column's DEFAULT value.

Signature

DEFAULT(col_name)

Parameters

Parameter	Type	Description
col_name	column reference	The table column whose default value is to be retrieved.

Examples

Show default for a timestamp column

sql

SELECT DEFAULT(created_at) FROM orders LIMIT 1;

→-- CURRENT_TIMESTAMP

Insert using column default

sql

INSERT INTO orders (status) VALUES (DEFAULT(status));

→-- 1 row affected

Reset column to default

sql

UPDATE orders SET priority = DEFAULT(priority) WHERE id = 5;

→-- 1 row affected

Numeric column default

sql

SELECT DEFAULT(stock) FROM products LIMIT 1;

→-- 0

Compare value to default

sql

SELECT col, DEFAULT(col) FROM t LIMIT 3;

→-- (value | default_val)

⚠Anti-Pattern— Using DEFAULT(col) on columns without a defined default

Calling DEFAULT() on a column that has no DEFAULT clause results in an error.

✓ Instead: Check information_schema.COLUMNS for COLUMN_DEFAULT before using DEFAULT().

DEFAULT(col) returns the default value defined for the column in the table's CREATE TABLE statement. Use it in UPDATE statements to restore a column to its original default without hardcoding the value in application code.

example

UPDATE orders SET priority = DEFAULT(priority) WHERE customer_tier = 'standard';

→priority reset to schema default (e.g., 'normal') for all standard customers

◆MariaDB Note

Can be used in both DML and SELECT contexts.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses column_default from information_schema.columns; no DEFAULT() function.

GET_LOCK

MariaDB 5.5+→ INT

Acquires a named advisory lock. Returns 1 if the lock was obtained, 0 if timed out, NULL on error. In MariaDB, multiple named locks per session are supported simultaneously.

Signature

GET_LOCK(str, timeout)

Parameters

Parameter	Type	Description
str	VARCHAR	The name of the advisory lock to acquire.
timeout	INT	Number of seconds to wait for the lock. Use -1 for indefinite wait, 0 for immediate (no wait).

Examples

Acquired within 10 seconds

sql

SELECT GET_LOCK('my_task', 10);

→-- 1

Already locked, no wait

sql

SELECT GET_LOCK('my_task', 0);

→-- 0

Multiple locks in one session

sql

SELECT GET_LOCK('lock_a', 5), GET_LOCK('lock_b', 5);

→-- 1 | 1

Wait forever for lock

sql

DO GET_LOCK('import_job', -1);

→-- (waits indefinitely)

Check before proceeding

sql

SELECT GET_LOCK('task', 30) INTO @got; IF @got = 1 THEN /* do work */ END IF;

→-- conditional logic

⚠Anti-Pattern— Forgetting to release advisory locks

Locks held with GET_LOCK() are automatically released when the session ends, but if you hold them through long operations, other sessions block. Not releasing explicitly causes unnecessary waits.

✓ Instead: Always call RELEASE_LOCK() when the critical section is complete, or use RELEASE_ALL_LOCKS().

GET_LOCK creates a named advisory lock scoped to the connection. The name can be any string up to 64 characters. Use consistent naming conventions (e.g., 'app:resource:id') to avoid accidental conflicts between different lock namespaces.

example

SELECT GET_LOCK('invoice:generate:2024', 5) AS got_lock;

→got_lock: 1 (acquired) or 0 (timeout)

◆MariaDB Note

Multiple simultaneous named locks per session are natively supported.

⇄ vs MySQL: MySQL 5.7 and earlier could only hold one named lock per session; MySQL 8.0+ supports multiple. MariaDB has always (since 10.0) supported multiple.

⇄ vs PostgreSQL: PostgreSQL uses pg_advisory_lock() / pg_advisory_unlock().

RELEASE_LOCK

MariaDB 5.5+→ INT

Releases the named advisory lock acquired with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock was not held by this thread, NULL if the lock does not exist.

Signature

RELEASE_LOCK(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The name of the advisory lock to release.

Examples

Successfully released

sql

SELECT RELEASE_LOCK('my_task');

→-- 1

Lock does not exist

sql

SELECT RELEASE_LOCK('nonexistent_lock');

→-- NULL

Not held by this thread

sql

SELECT RELEASE_LOCK('other_sessions_lock');

→-- 0

Acquire then release pattern

sql

SELECT GET_LOCK('job', 10); /* work */ SELECT RELEASE_LOCK('job');

→-- 1 | 1

Release multiple locks

sql

SELECT RELEASE_LOCK('a'), RELEASE_LOCK('b');

→-- 1 | 1

⚠Anti-Pattern— Releasing a lock from a different session

RELEASE_LOCK() can only release locks held by the current session. Attempting to release another session's lock returns 0 (not an error), which may mislead callers into thinking the resource is free.

✓ Instead: Use IS_USED_LOCK() to check which connection holds the lock before attempting release.

Advisory locks are automatically released when the connection closes, but in long-lived connection pools a lock may be held until the connection is recycled. Always release locks explicitly in a DECLARE ... HANDLER block or application try/finally.

example

SELECT RELEASE_LOCK('invoice:generate:2024') AS released;

→released: 1 (success) or NULL (lock did not exist)

◆MariaDB Note

Supports releasing individual named locks from a multi-lock session.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_advisory_unlock().

IS_FREE_LOCK

MariaDB 5.5+→ INT

Checks whether the named advisory lock is free (not held by any session). Returns 1 if free, 0 if in use, NULL if an error occurred.

Signature

IS_FREE_LOCK(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The name of the advisory lock to check.

Examples

Lock is not held

sql

SELECT IS_FREE_LOCK('my_task');

→-- 1

After acquiring, no longer free

sql

SELECT GET_LOCK('my_task',0); SELECT IS_FREE_LOCK('my_task');

→-- 1 | 0

Check before trying to acquire

sql

SELECT IS_FREE_LOCK('import_job') AS available;

→-- 1

Polling pattern (use with caution)

sql

WHILE IS_FREE_LOCK('resource') = 0 DO SELECT SLEEP(0.1); END WHILE;

→-- (busy wait loop)

Human-readable status

sql

SELECT IF(IS_FREE_LOCK('task'), 'Available', 'Busy') AS status;

→-- Available

⚠Anti-Pattern— TOCTOU race between IS_FREE_LOCK and GET_LOCK

Checking IS_FREE_LOCK() then calling GET_LOCK() is not atomic. Another session can grab the lock between the two calls.

✓ Instead: Call GET_LOCK() with timeout=0 directly; check the return value for 1 (acquired) or 0 (already locked).

IS_FREE_LOCK returns 1 if the lock is not held by any connection, 0 if it is, and NULL on error. Unlike GET_LOCK with timeout=0, IS_FREE_LOCK does not attempt to acquire the lock — it only checks availability.

example

SELECT IS_FREE_LOCK('cron:daily_report') AS free;

→free: 1 (available) or 0 (currently held)

◆MariaDB Note

Returns NULL on error (e.g., invalid lock name).

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_try_advisory_lock() for non-blocking acquisition.

IS_USED_LOCK

MariaDB 5.5+→ BIGINT UNSIGNED

Returns the connection ID of the session that holds the named advisory lock, or NULL if the lock is not held.

Signature

IS_USED_LOCK(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The name of the advisory lock to check.

Examples

Lock is free

sql

SELECT IS_USED_LOCK('my_task');

→-- NULL

Returns holder connection ID

sql

SELECT GET_LOCK('my_task',0); SELECT IS_USED_LOCK('my_task');

→-- 42

Check if current session holds it

sql

SELECT IS_USED_LOCK('import') = CONNECTION_ID() AS i_hold_it;

→-- 1

Identify the blocking session

sql

SELECT * FROM information_schema.PROCESSLIST WHERE ID = IS_USED_LOCK('task');

→-- (holder's process info)

Descriptive status

sql

SELECT IF(IS_USED_LOCK('job') IS NULL, 'Free', CONCAT('Held by conn ', IS_USED_LOCK('job'))) AS status;

→-- Free

⚠Anti-Pattern— KILLing the lock holder without investigation

Seeing IS_USED_LOCK() return a connection ID and immediately KILL-ing it can interrupt important operations.

✓ Instead: Use SHOW PROCESSLIST or query INFORMATION_SCHEMA.PROCESSLIST to understand what the holder is doing before taking action.

IS_USED_LOCK returns the connection_id of the session holding the named lock, or NULL if the lock is free. Cross-reference with INFORMATION_SCHEMA.PROCESSLIST to identify which query is holding a long-running advisory lock.

example

SELECT IS_USED_LOCK('cron:daily_report') AS holder_conn_id;

→holder_conn_id: 47 (or NULL if free)

◆MariaDB Note

Returns NULL when the lock is not held by any session.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_locks view to see advisory lock holders.

RELEASE_ALL_LOCKS

Available since MariaDB 10.0.2.→ INT

Releases all named advisory locks held by the current session at once. Returns the number of locks released.

Signature

RELEASE_ALL_LOCKS()

Examples

Released 3 locks held by this session

sql

SELECT RELEASE_ALL_LOCKS();

→-- 3

Acquire two then release all

sql

SELECT GET_LOCK('a',1); SELECT GET_LOCK('b',1); SELECT RELEASE_ALL_LOCKS();

→-- 1 | 1 | 2

No locks held returns 0

sql

SELECT RELEASE_ALL_LOCKS();

→-- 0

Silent release

sql

DO RELEASE_ALL_LOCKS();

→-- (no result set)

Named result column

sql

SELECT RELEASE_ALL_LOCKS() AS locks_freed;

→-- 1

⚠Anti-Pattern— Relying on session end instead of RELEASE_ALL_LOCKS()

Locks are released when a session closes, but a long-lived connection pool keeps sessions open. This can starve other sessions of advisory locks.

✓ Instead: Call RELEASE_ALL_LOCKS() explicitly in cleanup or FINALLY-equivalent error handler code.

RELEASE_ALL_LOCKS() releases every advisory lock held by the current session in one call. Include it in connection reset procedures or in AFTER SIGNAL handlers to guarantee no orphaned locks remain.

example

SELECT RELEASE_ALL_LOCKS() AS locks_released;

→locks_released: 3 (number of locks released)

◆MariaDB Note

Returns count of released locks. Particularly important in MariaDB since multiple named locks per session are the norm.

⇄ vs MySQL: Available in MySQL 5.7.5+. MariaDB has supported multiple locks per session longer.

⇄ vs PostgreSQL: PostgreSQL uses pg_advisory_unlock_all().

MASTER_GTID_WAIT

Available since MariaDB 10.0.9.→ INT

Waits until the replica has applied all transactions up to the given GTID position, or until the timeout expires. Returns 0 on success, -1 on timeout, 1 on error.

Signatures

MASTER_GTID_WAIT(gtid_pos)

MASTER_GTID_WAIT(gtid_pos, timeout)

Parameters

Parameter	Type	Description
gtid_pos	VARCHAR	The GTID position string to wait for (e.g., '0-1-100').
timeout	DECIMAL	Maximum seconds to wait. Omit or use -1 for indefinite wait.

Examples

Wait until replica reaches GTID

sql

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

→-- 0

Timeout after 5 seconds

sql

SELECT MASTER_GTID_WAIT('0-1-150', 5);

→-- -1

Non-blocking check

sql

SELECT MASTER_GTID_WAIT('0-1-150', 0);

→-- 0 or -1

Read-your-own-writes pattern

sql

SET @gtid = (SELECT @@last_gtid); /* do something on replica */ SELECT MASTER_GTID_WAIT(@gtid, 30);

→-- 0

Status message

sql

SELECT IF(MASTER_GTID_WAIT('0-1-200', 10) = 0, 'Synced', 'Timeout') AS replication_status;

→-- Synced

⚠Anti-Pattern— Using MASTER_GTID_WAIT with an indefinite timeout in production

Omitting the timeout or passing -1 can block an application thread indefinitely if replication lags.

✓ Instead: Always specify a reasonable timeout and handle the -1 (timeout) return value with a fallback or retry.

After a write on the primary, capture @@last_gtid before the response is sent to the client. Pass this value to MASTER_GTID_WAIT() on the replica connection to ensure read-after-write consistency without polling.

example

SELECT MASTER_GTID_WAIT('0-1-452', 5) AS synced;

→synced: 0 (success) or -1 (timeout after 5 seconds)

◆MariaDB Note

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

⇄ vs MySQL: MySQL uses WAIT_FOR_EXECUTED_GTID_SET() with a different GTID format.

⇄ vs PostgreSQL: PostgreSQL uses pg_wal_replay_pause() / pg_wal_replay_resume() and pg_last_wal_replay_lsn() for similar patterns.

SLEEP

MariaDB 5.5+→ INT

Pauses execution for the specified number of seconds (fractional seconds supported). Returns 0 on normal completion, 1 if interrupted.

Signature

SLEEP(seconds)

Parameters

Parameter	Type	Description
seconds	DECIMAL	Duration to sleep in seconds. Fractional values (e.g., 0.5) are accepted.

Examples

Sleep 1 second

sql

SELECT SLEEP(1);

→-- 0

Sleep 250 milliseconds

sql

SELECT SLEEP(0.25);

→-- 0

Returns 1 if interrupted by KILL

sql

SELECT SLEEP(60);

→-- 1 (if killed)

Use with BENCHMARK

sql

SELECT BENCHMARK(1, SLEEP(2));

→-- 0 (after 2 sec)

Zero-sleep status check

sql

SELECT IF(SLEEP(0) = 0, 'OK', 'Interrupted') AS status;

→-- OK

⚠Anti-Pattern— Using SLEEP() in production queries for throttling

SLEEP() holds a connection and any locks for the entire duration. Using it to throttle query rates blocks connection pool slots.

✓ Instead: Handle throttling in application code, outside the database connection context.

SLEEP() is valuable in test scripts to simulate slow queries, lock contention scenarios, or replication delay. In production it holds a connection and its locks for the full duration — never leave SLEEP() calls in production code paths.

example

SELECT SLEEP(0.1);  -- 100ms pause for test pacing

→0

◆MariaDB Note

Returns 1 when the sleep is interrupted via KILL QUERY.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_sleep(seconds).

UUID

MariaDB 5.5+→ VARCHAR(36)

Generates a universally unique identifier (UUID) conforming to RFC 4122 version 1, returned as a 36-character string with hyphens.

Signature

UUID()

Examples

Generate UUID v1

sql

SELECT UUID();

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

UUID without hyphens

sql

SELECT REPLACE(UUID(), '-', '') AS uuid_hex;

→-- '6ccd780cbaba102695645b8c656024db'

Use UUID as row ID

sql

INSERT INTO events (id, name) VALUES (UUID(), 'click');

→-- 1 row affected

Two calls always differ

sql

SELECT UUID() = UUID();

→-- 0

Compact binary storage

sql

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid_binary;

→-- (16 bytes)

⚠Anti-Pattern— Using VARCHAR(36) UUID as a clustered primary key

Random UUID values inserted as the clustered index key (InnoDB PRIMARY KEY) cause random page splits and severe insert fragmentation.

✓ Instead: Use UUID_SHORT() for monotonic 64-bit IDs, or store UUIDs in BINARY(16) and keep an AUTO_INCREMENT PK for clustering.

UUID() returns a 36-character string with hyphens. Storing it as VARCHAR(36) wastes space and makes index comparisons slower. Use UNHEX(REPLACE(UUID(),'-','')) to get a 16-byte binary suitable for a BINARY(16) primary key column.

example

SELECT HEX(UNHEX(REPLACE(UUID(), '-', ''))) AS compact_uuid;

→6CCD780CBABA102695645B8C656024DB

◆MariaDB Note

Returns a UUID v1 (time-based). The MAC address portion may be random on VMs.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses gen_random_uuid() (v4) or uuid_generate_v1() via extension.

UUID_SHORT

MariaDB 5.5+→ BIGINT UNSIGNED

Returns a short universally unique 64-bit integer that is monotonically increasing per server. Combines server_id, startup time, and a per-session sequence counter.

Signature

UUID_SHORT()

Examples

64-bit unique integer

sql

SELECT UUID_SHORT();

→-- 92395783831158784

Monotonically increasing

sql

SELECT UUID_SHORT() < UUID_SHORT();

→-- 1

Use as primary key

sql

INSERT INTO records (id, data) VALUES (UUID_SHORT(), 'hello');

→-- 1 row affected

Capture for reuse

sql

SELECT UUID_SHORT() INTO @id; SELECT @id;

→-- 92395783831158784

Pair with timestamp

sql

SELECT UUID_SHORT() AS id, NOW() AS created;

→-- (id | timestamp)

⚠Anti-Pattern— Using UUID_SHORT() with duplicate server_id values

UUID_SHORT() incorporates @@server_id. If multiple servers have the same server_id (misconfigured replication), UUID_SHORT() can produce collisions.

✓ Instead: Ensure every server in the topology has a unique @@server_id before using UUID_SHORT() as a distributed unique key.

UUID_SHORT() returns a monotonically increasing BIGINT that avoids the index fragmentation of random UUIDs while still being unique across servers with distinct server_id values. It inserts in order, keeping B-tree indexes efficient.

example

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

→1 row inserted with a compact, sortable BIGINT id

◆MariaDB Note

The value encodes: (server_id << 56) + (server_startup_time_sec << 24) + per-connection auto-increment.

⇄ vs MySQL: Identical in MySQL.

⇄ vs PostgreSQL: PostgreSQL uses sequences or gen_random_uuid(); no direct monotonic 64-bit UUID function.

SYS_GUID

Available since MariaDB 10.3 Oracle compatibility mode.→ VARCHAR(32)

Oracle compatibility function that generates a globally unique identifier. Returns a 32-character hexadecimal string (UUID without hyphens). Only available when the SQL_MODE includes 'ORACLE' or when running MariaDB's Oracle compatibility mode.

Signature

SYS_GUID()

Examples

UUID in Oracle format

sql

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

→-- '6CCD780CBABA102695645B8C656024DB'

Always 32 hex characters

sql

SET SQL_MODE='ORACLE'; SELECT LENGTH(SYS_GUID());

→-- 32

Use as row identifier

sql

SET SQL_MODE='ORACLE'; INSERT INTO t (id) VALUES (SYS_GUID());

→-- 1 row affected

Each call produces unique value

sql

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

→-- 0

Standard SQL equivalent

sql

SELECT REPLACE(UUID(), '-', '') AS same_as_sys_guid;

→-- '6ccd780cbaba102695645b8c656024db'

⚠Anti-Pattern— Using SYS_GUID() without Oracle SQL mode enabled

SYS_GUID() is only available in Oracle compatibility mode (sql_mode=ORACLE). Calling it in standard mode raises an error.

✓ Instead: Use UUID() or REPLACE(UUID(), '-', '') in standard MariaDB code.

SYS_GUID() returns the same UUID as UUID() but without hyphens, matching Oracle's SYS_GUID() return format. Use it only when porting Oracle PL/SQL that relies on the 32-character no-hyphen format.

example

SELECT SYS_GUID();

→'6CCD780CBABA102695645B8C656024DB' (32 chars, no hyphens)

◆MariaDB Note

Only available in Oracle SQL mode (sql_mode contains 'ORACLE'). Returns uppercase hex without hyphens.

⇄ vs MySQL: MySQL does not have SYS_GUID(). This is a MariaDB Oracle compatibility extension.

⇄ vs PostgreSQL: PostgreSQL uses gen_random_uuid() which returns UUID v4.

Related MariaDB Categories

Control Flow →Aggregate Functions →

MariaDB System & Info Functions

Jump to function

What are MariaDB System & Info Functions?

VERSION

Signature

Examples

DATABASE

Signature

Examples

SCHEMA

Signature

Examples

USER

Signature

Examples

CURRENT_USER

Signatures

Examples

SESSION_USER

Signature

Examples

SYSTEM_USER

Signature

Examples

CONNECTION_ID

Signature

Examples

LAST_INSERT_ID

Signatures

Parameters

Examples

ROW_COUNT

Signature

Examples

FOUND_ROWS

Signature

Examples

BENCHMARK

Signature

Parameters

Examples

CHARSET

Signature

Parameters

Examples

COERCIBILITY

Signature

Parameters

Examples

COLLATION

Signature

Parameters

Examples

CURRENT_ROLE

Signature

Examples

DEFAULT

Signature

Parameters

Examples

GET_LOCK

Signature

Parameters

Examples

RELEASE_LOCK

Signature

Parameters

Examples

IS_FREE_LOCK

Signature

Parameters

Examples

IS_USED_LOCK

Signature

Parameters

Examples

RELEASE_ALL_LOCKS

Signature

Examples

MASTER_GTID_WAIT