IF in MySQL script

Question

I have the following script:

use my_db;

if (2 < 3) then
    select 1;
end if;

When I execute this with command:

mysql --user=myuser --password=mypassword < script.sql

I get the following error:

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (2 < 3) then select 1' at line 1

Can anybody explain me why this? From mysql docs found here I think it should be working fine.

Amniocentesis

The if statement is only allowed in programming blocks, such as stored procedures, user-defined functions, and triggers. Put the conditional logic in a stored procedure and call the stored procedure from the script. – Centric

Answer 1

If you can change your statement, I would recommend it doing it this way:

select if(2<3, 'true','false') as amount

Or wrap your code in a procedure:

create procedure my_procedure() 
begin
  if (2 < 3) then
      select 1;
  end if;
end;

-- Execute the procedure
call my_procedure();

-- Drop the procedure
drop procedure my_procedure;

Locket

the point is that I want to create a procedure only if it doesn't exist. So, instead of select 1, there would be a create procedure... statement. – Amniocentesis

Ok, then you would have to wrap that check code into a procedure. – Locket

Still don't understand... :). How to check if IF isn't permitted? – Amniocentesis

PostgreSQL is more friendly. postgresql.org/docs/9.1/static/sql-do.html – Midsummer

Answer 2

Still don't understand... :). How to check if IF isn't permitted?

https://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html says:

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.

(emphasis mine)

I wouldn't bother with writing stored routines in MySQL. If you need to do conditional SQL queries, I'd recommend learning a scripting language. Python is a good choice.

#!/bin/env python

import MySQLdb

db = MySQLdb.connect()

if 2 < 3:
    cur = db.cursor()
    cur.query('select 1')
    print cur.fetchall()

Espresso

@Ako since I wrote this answer in 2017, we've seen the release of MySQL Shell, which allows developers to do logic statements in Python or Javascript. – Espresso

Thanks for the tip. Just to elaborate, using an external language to handle SQL logic is bad solution and will probably never be accepted as a standard. SQL is a standalone scripting language, why the extra complication. Py and JS require a VM, where the trust issue becomes the tide turner. – Shrivel

I don't think that's correct. From its earliest days, SQL was intended to be a domain-specific language, to be used as a complement to another programming language. Part 3 of the ANSI/ISO SQL standard describes the Call Level Interface (CLI) for using SQL from another language (the standard describes using SQL with Ada, C/C++, COBOL, Fortran, MUMPS, Pascal, PL/I). Part 10 of the standard describes the Object Language Bindings (OLB) for using SQL embedded in Java. – Espresso

Well if you log in to database engines CLI, we do not use these programming languages, do we? There are two different use cases, external and internal domain. If engine requires external dependency (like Py VM), it's security is compromised and most companies stop there. Main reason why CLI scripts for SQL were standardized. – Shrivel

The term CLI in the SQL standard does not refer to a command-line interface tool. It refers to the call interface. In today's parlance, it's more like the API. How to programmatically open connections, start transactions and cursors, fetch results, that sort of thing. – Espresso

External domains Call Level Interfaces are abstraction from the engine, why would we need a abstracted interface in the local domain if we already have scripts which engine can interpret? It's like clicking a mouse on local machine, but the click goes through a server before any action will take place. – Shrivel

Sorry, you lost me. Have a great day! – Espresso