How to show all user-defined variables (MySQL)

Question

I set two user-defined variables as shown below but after some time, I forgot the names:

SET @a = 2, @b = 3;

So, does MySQL have the command that displays all user-defined variables?

Delp

Answer 1

Starting with MySQL 5.7, the performance schema exposes user variables.

See table performance_schema.user_variables_by_thread

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-user-variable-tables.html

Asparagine

@Mark Aug Thanks for reply, unfortunately, I use older version of MySQL server – Delp

I think this is the updated url (link provided is broken), but I cannot find anything about user variables. – Polyadelphous

Answer 2

If you have MariaDB (a binary "drop-in" equivalent of MySQL) there is a plugin available, provided by MariaDB itself.

MariaDB 10.2 (equivalent to MySQL 5.7) and above has a plugin that creates a "USER_VARIABLES" table.
Here is how to install the plugin.

Here is an example of its use:

SELECT * FROM information_schema.USER_VARIABLES ORDER BY VARIABLE_NAME;

+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| var           | 0              | INT           | utf8               |
| var2          | abc            | VARCHAR       | utf8               |
+---------------+----------------+---------------+--------------------+

MariaDB installs the plugin by default after version MariaDB 10.2.6.
The link above shows how to install it for prior versions.

Double check what version of "mysql" you're running, because sometimes people will refer to a MariaDB as MySQL, due to its use as a "binary drop in replacement" for MySQL. So it's possible that you are running a MariaDB database.

I am not aware of MySQL providing anything similar.

How to check which version of mysql you're running (the prompt is in bold)
From the command line:
$ mysql -v

From the mysql command client:
mysql> SHOW VARIABLES LIKE "%version%";

It is also shown when you first log into the mysql command client, which you can do via:
$ mysql -u your_mysql_username --password=your_mysql_password

Polyadelphous

What is the reason for the downvote ? To the best of my knowledge, everything posted above is correct and works. I have provide source links for validation, and follow up if anyone wants more info. The answer addresses the OP's question. Many people are running MariaDB, when they think they are running MySQL, and often MySQL is used as a "generic" term to mean "MySQL or MySQL-like" databases. Sort of like "Kleenex" is often said instead of "tissue". It is appropriate to provide info for the use case that MariaDB is installed on the user's server as it's a (common) MySQL drop in replacement. – Polyadelphous

Answer 3

With performance_schema.user_variables_by_thread, you can show all user-defined variables as shown below:

mysql> SELECT * FROM performance_schema.user_variables_by_thread;
+-----------+---------------+--------------------------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE                 |
+-----------+---------------+--------------------------------+
|        69 | first_name    | 0x4A6F686E                     |
|        69 | last_name     | 0x536D697468                   |
+-----------+---------------+--------------------------------+

Lantana