0

I'm trying to run the below command in MySQL multiple times. Is there an easy way like the GO command in TSQL/SQL Server for MySQL?

select
  now(),
  @@max_connections as "Max Connections",
  count(host) as "Current Connections"
from information_schema.processlist;

Wanted to keep it simple for a demo. Not looking for a SPROC.

kmoser
  • 8,780
  • 3
  • 24
  • 40
Anup Warrier
  • 1
  • 1

1 Answers1

1
mysql> set @query = 'select
    '>   now(),
    '>   @@max_connections as "Max Connections",
    '>   count(host) as "Current Connections"
    '> from information_schema.processlist; ';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute stmt;
+---------------------+-----------------+---------------------+
| now()               | Max Connections | Current Connections |
+---------------------+-----------------+---------------------+
| 2021-08-16 18:22:03 |             512 |                   1 |
+---------------------+-----------------+---------------------+
1 row in set (0.01 sec)

Then you can execute stmt as many times as you want in the same session (prepared statements have a scope of the current session).

See https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html for more information.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you Bill. This is helpful. How do I run the execute stmt like 100 times in loop without involvement? With SQL Server/TSQL, the GO command lets you do that easily. Like SELECT * FROM table GO 100; This will run the SELECT statement 100 times. – Anup Warrier Aug 16 '21 at 18:33
  • I'd write a script in Python. – Bill Karwin Aug 16 '21 at 18:35
  • Depending on your OS you could write a shell script with a loop that repeatedly executes something like `mysql – kmoser Aug 16 '21 at 19:17