Executing a sql query multiple times in MySQL similar to GO in TSQL(SQL Server)

Question

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.

What do you mean by "multiple times?" The easiest way would be to write a stored procedure. — kmoser
– kmoser, Commented Aug 16, 2021 at 18:19
I wanted to run the above command 100 times for a demo and show the query running via the command line utility. — Anup Warrier
– Anup Warrier, Commented Aug 16, 2021 at 18:23

Bill Karwin · Accepted Answer · 2021-08-16 18:22:54Z

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.

answered Aug 16, 2021 at 18:22

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anup Warrier Over a year ago

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.

Bill Karwin Over a year ago

I'd write a script in Python.

kmoser Over a year ago

Depending on your OS you could write a shell script with a loop that repeatedly executes something like mysql <query.sql, where query.sql is a textfile containing the SQL query. stackoverflow.com/questions/8055694/…

Collectives™ on Stack Overflow

Executing a sql query multiple times in MySQL similar to GO in TSQL(SQL Server)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related