php - 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 ;.

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).