How to display the value of a variable at the commandline in MySQL?

MysqlVariablesCommand Line

Mysql Problem Overview


I tried the following -

I created a variable at the command prompt as follows -

mysql> set @myId = 1;
Query OK, 0 rows affected (0.00 sec)

Then, to display it, I tried the following without success -

    mysql> show myId;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'myId' at line 1
    mysql> show @myId;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@myId' at line 1
    mysql> PRINT @myId;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'PRINT @myId' at line 1
    mysql> PRINT myId;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'PRINT myId' at line 1

So how can I display the value of @myId?

Mysql Solutions


Solution 1 - Mysql

Simply SELECT the variable like this:

SELECT @myId;

Here is the MySQL documentation on user-defined variables:

http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

Solution 2 - Mysql

If you're looking for a variable that you set yourself like the OP did, then @MikeBrant's answer is correct:

SELECT @myId;

But if you want to see the MySQL system variables (which is what I came here looking for), then you need to run:

show variables like '%slow%';

or possibly:

show global variables like '%slow%';

Solution 3 - Mysql

SHOW GLOBAL STATUS LIKE '%com_stmt%'; may be used to determine any SHOW GLOBAL STATUS current values using wildcard.

Likewise, SELECT @@thread_cache_size; may be used to display any specific SHOW GLOBAL VARIABLES current value.

There are more than 300 GLOBAL STATUS values.

There are more than 400 GLOBAL VARIABLES with or without values. (Could be empty placeholders).

You CAN NOT create a GLOBAL VARIABLE in MySQL.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionCodeBlueView Question on Stackoverflow
Solution 1 - MysqlMike BrantView Answer on Stackoverflow
Solution 2 - MysqlRyan ShillingtonView Answer on Stackoverflow
Solution 3 - MysqlWilson HauckView Answer on Stackoverflow