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

Question

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?

Mike Brant · Accepted Answer · 2016-10-17T13:23:20.787

105

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

edited Oct 17 '16 at 13:23

answered Sep 16 '13 at 22:53

Mike Brant

70,514
10
99
103

well I tried doing a google search on displaying variables and PRINT is the only thing that I found. – CodeBlue Sep 16 '13 at 22:55
18

Ok, but knowing what to search is what puts you at 26k and me at 2k. – CodeBlue Sep 17 '13 at 14:03
1

I would argue knowing what to search is what puts me at 185. – HungryBeagle Mar 14 '17 at 13:03
1

For global variables, this won't work, check Ryan Shillington's solution below. – Skippy le Grand Gourou Jan 23 '20 at 16:15

Ryan Shillington · Answer 2 · 2019-12-13T14:47:37.800

10

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%';

edited Dec 13 '19 at 14:47

answered Oct 17 '18 at 13:33

Ryan Shillington

23,006
14
93
108

Now bonus question: Is it possible to only display a system variable's value, without also displaying its name again? So that it could be used to simply query the value of the specific variable? – Raven Apr 28 '22 at 17:47
@Raven that requires you to run the MySQL command line with specific options to not print the headers. – Ryan Shillington Apr 29 '22 at 23:49
Thanks! I think I have found a better way by now though: https://stackoverflow.com/a/72074905/3907364 – Raven May 01 '22 at 07:46

Wilson Hauck · Answer 3 · 2019-12-13T12:28:28.193

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.

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

3 Answers3

Linked