Dave P Dave P - 1 month ago 10
MySQL Question

Can I neatly skip all or part of a mySQL script (esp that contains creation of stored procedures)?

I have a mySQL script, that I have to execute, but for a bunch of useless reasons (and more importantly boring), I might want it to skip most of that script.

Let's say before each part of the script I want to check

select okToRun from options
returns true before running the script.

So my script looks something like this

DELIMITER $$

DROP PROCEDURE IF EXISTS procedure1$$
CREATE PROCEDURE procedure1()
BEGIN
IF (select okToRun from options) THEN
... -- do some stuff
END IF;
END$$

CALL procedure1()$$

DROP PROCEDURE IF EXISTS procedure2$$
CREATE PROCEDURE procedure2()
BEGIN
IF (select okToRun from options) THEN
... -- do some other stuff
END IF;
END$$

DROP PROCEDURE IF EXISTS procedure3$$
CREATE PROCEDURE procedure3()
BEGIN
IF (select okToRun from options) THEN
... -- do even more stuff!
END IF;
END$$

CALL procedure2()$$
CALL procedure3()$$


I hate repeated code, so how can I put the check right at the start of the script, rather than having to keep repeating myself?

I thought I'd be clever and wrap the whole thing in one stored procedure and do my if statement there... but you can't really create a stored procedure within another. And I need to keep the other stuff within their own stored procedures.

I also realise it'd be better to do the check before executing the script at all, but, well, I can't ;)

If the answer simply is that I can't do this, fair enough. But I want to be sure before repeating code like a big fat heathen.

Answer

The MySQL client script syntax does not support conditionals or loops or other programming logic.

The best idea I could come up with is to use prepared statements, but unfortunately CREATE PROCEDURE is not supported in this manner.

SET @do_create := true;
SET @create_proc1 := IF(@do_create, 'CREATE PROCEDURE ...', 'DO 1');
PREPARE stmt FROM @create_proc1

I got this error:

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

The list of commands supported in this way is documented here: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

In MySQL 5.7, they introduced a new client shell called mysqlsh that is supposed to support a more featureful scripting environment, allowing you to write Python code and so on. I haven't tried this tool yet.

But you could do the same thing by writing your own script in Python or any other language you want. Then you can code the conditional logic to create procedures or whatever. That's your best bet.