mysql

MySQL: Query value of (global) variable

I know it is possible to print global or session variables using

SHOW GLOBAL VARIABLES;

or

SHOW SESSION VARIABLES;

If I want to investigate a specific variable, I can use something like e.g.

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

which results in something of this form:

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

This works just fine for manual inspection of the respective values, but in order to programmatically query the values of such variables, I am bothered by two things in this format:

  1. It returns two columns: The variable name and its value. However, I am only interested in the value as the name I already know in advance (after all I specified it in the query)
  2. The actual value seems to be a string representation of the actual value. When setting this specific value, I have so far only seen integers 0 and 1 to be used, so I expect that this setting should actually be stored as a numeric type. However, here it is returned as a string (OFF). While easy enough to convert this into a boolean value, I am unsure whether this string representation might depend on things like locale, in which case the conversion to bool might end up getting tricky.

So my question is: Is there a way to concisely query only the value of a given (global) variable that returns its value as-is, without attempting to pretty-print/stringify it?

Solution

  • I found the answer to this thanks to the following answer at Query MySQL global variables with a SELECT

    If you know the name of a given global or session variable, you can query for its actual value via

    SELECT @@GLOBAL.<variableName>;

    or

    SELECT @@SESSION.<variableName>;

    respectively.

    So for the provided example, we can use

    SELECT @@GLOBAL.log_bin_trust_function_creators;

    to get

    +------------------------------------------+
| @@GLOBAL.log_bin_trust_function_creators |
+------------------------------------------+
|                                        0 |
+------------------------------------------+