how to execute mysql command DELIMITER

Question

I have huge problem (for me)

I need to execute the MySQL command DELIMITER | from PHP, but mysql_query() fails with an error. I found that mysql_query() doesn't support the use of DELIMITER, because this command only works in MySQL console

But when I open phpMyAdmin there is an option to change DELIMITER on the SQL tab and it works, but I don't know how.

Is it possible to change delimiter from PHP? I need it to do before calling a CREATE TRIGGER command that uses several ;.

Contortion

you should ask this on www.stackoverflow.com – Double

Answer 1

You probably don't need to change the delimiter.

The delimiter is needed in the CLI to tell where the SQL statement ends, because the CLI is going to keep reading and executing more statements until you tell it to stop (e.g., with exit or Control-D). But what it actually reads is just a stream of characters; it somehow needs to figure out where one statement ends and the next starts. That's what the delimiter does.

In PHP, each function call executes one statement. There can't be multiple statements in one function call, so there is no need for a way to delimit them. The statement is the entire string. This is true of the old mysql_query as well as the newer mysqli_query and PDO. Of course, there is mysqli_multi_query if you really want to pass multiple queries to one function.

In the case of a stored procedure/trigger/function/etc., there can be multiple statements, but that's handled by MySQL itself (and is always ;, AFAIK). So as far as PHP is concerned, that's still one statement.

The delimiter setting you're seeing in phpMyAdmin is probably being used to split statements apart, and is probably being done in PHP code. It has to do this because it is accepting user input consisting of multiple statements, but must pass only one statement per function call. (I haven't checked the phpMyAdmin code to be completely sure of this).

Doublure

Not to mention being able to inject delimiters would have catastrophic consequences... – Kocher

@Capsule: Well, it wouldn't in a properly coded app, but yeah. Would make some SQL injection exploits much worse. – Doublure

Since "properly coded app" is not de facto here, I prefer to mention it ;-) – Kocher

OP is using a delimiter in a Create trigger statement. This has nothing to do with CLI – Zellner

@YarekT OP is asking about changing the delimiter before a CREATE TRIGGER, which is required when using the CLI. OP is used to the CLI, that's what it has to do with the CLI. – Doublure

@Doublure I must have misread your answer. Having had the same problem myself I realised that semicolon has no special meaning coming from an adapter. I always thought that if you put a ; that physically ends parsing – Zellner

@YarekT I've revised my answer to hopefully make it clearer (and also update it a little since 2011). Please let me know if I've succeeded. – Doublure

@Doublure Great stuff. Have fun reaping reputation from hoards of google searches =D – Zellner

Note that it seems that PDO::exec() can execute multiple statements, at least with mysqlnd. – Confiture

Answer 2

It depends. I use MySQLi's multi_query() function and leave ; as is when doing a procedure creating, actually done (PHP 5.6 & MySQL 5.6). I think you do not need care delimiter, leave the target code only then might work. You might try to query SQL only as

CREATE XXXX
BEGIN
    sth;
END

Just have a try.

Socinian

Answer 3

you need a stored routine (procedure, function) to include all the necessary queries,
so in PHP, you can call the stored routine elegantly

Primate