MySQL System Functions: VERSION, DATABASE, USER

Some functions tell you about MySQL itself: its version, who you are, which database you are in.

query.sql

SQL

SELECT VERSION() IS NOT NULL AS has_version,
       DATABASE() AS current_database,
       CURRENT_USER() AS account_used_for_access,
       USER() IS NOT NULL AS client_user,
       CONNECTION_ID() > 0 AS has_connection_id,
       @@character_set_client AS client_charset;

Output

OUTPUT

+-------------+------------------+-------------------------+-------------+-------------------+----------------+
| has_version | current_database | account_used_for_access | client_user | has_connection_id | client_charset |
+-------------+------------------+-------------------------+-------------+-------------------+----------------+
|           1 | learn            | root@localhost          |           1 |                 1 | utf8mb4        |
+-------------+------------------+-------------------------+-------------+-------------------+----------------+
1 row in set

Function	Returns
`VERSION()`	The server version, e.g. `8.4.11`
`DATABASE()` / `SCHEMA()`	The current database, or `NULL`
`CURRENT_USER()`	The account MySQL used to check your privileges (e.g. `app@%`)
`USER()` / `SESSION_USER()`	The user name and host you connected as
`CONNECTION_ID()`	Your connection’s thread id (use it with `KILL`)
`LAST_INSERT_ID()`	The last generated `AUTO_INCREMENT` value in this session
`ROW_COUNT()`	Rows affected by the previous statement
`FOUND_ROWS()`	Deprecated

USER() and CURRENT_USER() can differ. If you connect as asha@laptop but the server matches you to the account 'asha'@'%', USER() shows asha@laptop and CURRENT_USER() shows asha@%. CURRENT_USER() is the one that matters for permissions.

Server variables

Settings are called system variables. Read one with @@name, or list many with SHOW VARIABLES:

query.sql

SQL

SELECT @@global.max_connections > 0 AS has_connection_limit,
       @@session.sql_mode LIKE '%STRICT_TRANS_TABLES%' AS strict_mode,
       @@autocommit AS autocommit,
       @@transaction_isolation AS isolation_level,
       @@innodb_buffer_pool_size > 0 AS has_buffer_pool;

Output

OUTPUT

+----------------------+-------------+------------+-----------------+-----------------+
| has_connection_limit | strict_mode | autocommit | isolation_level | has_buffer_pool |
+----------------------+-------------+------------+-----------------+-----------------+
|                    1 |           1 |          1 | REPEATABLE-READ |               1 |
+----------------------+-------------+------------+-----------------+-----------------+
1 row in set

query.sql

SQL

SHOW VARIABLES LIKE 'character_set_server';

Output

OUTPUT

+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set

@@global.x is the server-wide value, used by new connections.
@@session.x (or just @@x) is the value for your connection. A SET SESSION changes only yours.
Some variables are read-only, and some can be changed only in the configuration file.

query.sql

SQL

SET SESSION sql_mode = 'ANSI_QUOTES';
SELECT @@session.sql_mode AS my_mode;
SET SESSION sql_mode = DEFAULT;
SELECT @@session.sql_mode LIKE '%ANSI_QUOTES%' AS ansi_quotes_on;

Output

OUTPUT

+-------------+
| my_mode     |
+-------------+
| ANSI_QUOTES |
+-------------+
1 row in set

+----------------+
| ansi_quotes_on |
+----------------+
|              0 |
+----------------+
1 row in set

Server status

SHOW STATUS shows counters that grow while the server runs:

query.sql

SQL

SHOW GLOBAL STATUS LIKE 'Uptime';

Output

OUTPUT

+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 523412 |
+---------------+--------+

(The number differs on every server and every second.) Useful ones: Threads_connected, Questions, Slow_queries, Innodb_buffer_pool_reads, Created_tmp_disk_tables. Watch them over time, not once.

What is running right now

query.sql

SQL

SELECT COUNT(*) >= 1 AS my_connection_is_listed FROM information_schema.processlist WHERE id = CONNECTION_ID();

Output

OUTPUT

+-------------------------+
| my_connection_is_listed |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set

SHOW FULL PROCESSLIST lists every connection and what it is doing. It is the first thing to look at when the server feels slow (Finding Slow Queries).

Why these matter

Debugging: “Which user and database is this connection really using?” SELECT USER(), CURRENT_USER(), DATABASE(); answers it.
Compatibility checks: a deployment script can check VERSION() before it runs.
Audit columns: DEFAULT (CURRENT_USER()) or a trigger can record who changed a row.

lightbulb

A one-line sanity check

Before any risky change: SELECT DATABASE(), CURRENT_USER(), VERSION(), @@hostname;. It shows whether you are on the server you think you are.