MariaDB 10.1.1: Compound statements
Every now and then there is a need to execute certain SQL statements conditionally. Easy, if you do it from your PHP (or Java or whatever) application. But if all you have is pure SQL? There are two techniques that MariaDB and MySQL use in the
mysql_fix_privilege_tables.sql script (applied by mysql_upgrade tool).
- Create a stored procedure with
IFstatements inside, call it once and drop it. This requires the user to have the
CREATE ROUTINEprivilege and
mysql.proctable must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
- Use dynamic SQL, like
SET @str = IF (@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log ( event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL ) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; DROP PREPARE stmt;
which is not very readable and doesn’t work well if you need to execute many statements conditionally.
This may be standard, but I never understood why one cannot simply use SQL control statements (besides
CALL that is) directly from the mysql command line tool prompt. Imagine a bash variant that only supports
while in scripts, but not in the interactive mode from the command line — how would you like it?
May be Antony Curtis was asking himself similar questions when he contributed a patch for compound statements in MDEV-5317. Either way, we thought it’s a great idea and implemented this feature, based on the Antony’s contribution.
Now one can use
REPEAT statements directly in SQL scripts and from the mysql command line prompt — outside of stored programs. For example, one can rewrite the above as
IF @have_csv = 'YES' THEN CREATE TABLE IF NOT EXISTS general_log ( event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL ) engine=CSV CHARACTER SET utf8 comment="General log"; END IF
One can use
BEGIN ... END blocks and loops without having
CREATE ROUTINE privilege or with corrupted (or missing
mysql.proc table). This all works as you would expect it to, with no artificial “standard says so” limitations.
Still, there are some limitations to keep in mind:
- You cannot use a simple
BEGINto start a block, this is historically used to start a transaction. Use the standard syntax
BEGIN NOT ATOMIC.
- Compound statements from the mysql command line prompt cannot start with a label.
- Not all statements that can be used in the stored program are supported from the mysql command line prompt, only those listed above are.
These limitations, though, only apply to the top-level statement. For example, if you need a labeled loop or
SIGNAL, you start a block and put your statement inside it:
BEGIN NOT ATOMIC DECLARE v1 INT DEFAULT 500; label1: WHILE v1 > 0 DO INSERT t1 (v) VALUES (v1); SET v1 = v1 - 1; IF (SELECT COUNT(*) FROM t1) > 500 THEN LEAVE label1; END IF; END WHILE label1; END