Can a while loop be done in MySql w/o a stored procedure?

Question

I have a long and complex MySql statement that inserts records into a table. However it has gotten so complex that it takes forever on my 2k records table (I killed it after 22,000 seconds, it used to take 500s).

As a test I try running it in 100 records loops e.g. While ID<101 and ID<200.

So I figured I'd just set up some loop to step through 100 records at a time until it has inserted all the records.

I don't want/need to set up a procedure, I'm just wondering if there is some syntax to put into the statement itself vs creating a stored procedure which every example seems to include.

Answer 1

No, there is no such syntax in MySQL. If you need to write loops or other programming constructs, the assumption is that you will write code in some client application. You can choose whatever language you find most productive in. I'd use Python for quick single-use scripts, and probably Go for tools that I want to be used many times.

From its earliest days, SQL was intended to be a data-manipulation language, with integration in other programming languages to provide more general-purpose programming constructs.

---

MariaDB is a different database product that started as a fork of MySQL. They have introduced syntax to use compound statements outside of stored routines (https://mariadb.com/kb/en/using-compound-statements-outside-of-stored-programs/). But I can't see the appeal. If you're more comfortable using a conventional programming language, then do that.