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.
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;
select 1, there would be a create procedure... statement.
IF isn't permitted?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()
ifstatement 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.