如何执行mysql命令DELIMITER

时间:2022-09-23 15:12:17

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

我需要执行MySQL命令DELIMITER |来自PHP,但是mysql_query()因错误而失败。我发现mysql_query()不支持使用DELIMITER,因为此命令仅适用于MySQL控制台

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.

但是当我打开phpMyAdmin时,有一个选项可以在SQL选项卡上更改DELIMITER并且它可以工作,但我不知道如何。

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

是否可以从PHP更改分隔符?在调用使用多个的CREATE TRIGGER命令之前,我需要它做。

4 个解决方案

#1


11  

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.

CLI中需要使用分隔符来指示SQL语句的结束位置,因为CLI将继续读取并执行更多语句,直到您告知它停止(例如,使用exit或Control-D)。但它实际上读的只是一串人物;它以某种方式需要弄清楚一个语句的结束和下一个语句的开始。这就是分隔符的作用。

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.

在PHP中,每个函数调用都执行一个语句。一个函数调用中不能有多个语句,因此不需要一种方法来分隔它们。该语句是整个字符串。旧的mysql_query以及较新的mysqli_query和PDO也是如此。当然,如果你真的想要将多个查询传递给一个函数,那么就有mysqli_multi_query。

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.

在存储过程/触发器/函数/等的情况下,可以有多个语句,但这些语句由MySQL本身处理(并且始终是;,AFAIK)。因此,就PHP而言,这仍然是一个声明。

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

您在phpMyAdmin中看到的分隔符设置可能用于拆分语句,可能是在PHP代码中完成的。它必须这样做,因为它接受由多个语句组成的用户输入,但每个函数调用只能传递一个语句。 (我没有检查phpMyAdmin代码以完全确定这一点)。

#2


1  

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

这取决于。我使用MySQLi的multi_query()函数并离开;就像在做一个程序创建时一样,实际完成了(PHP 5.6和MySQL 5.6)。我认为你不需要关心分隔符,只留下目标代码然后可能会工作。您可能只尝试将SQL查询为

CREATE XXXX
BEGIN
    sth;
END

Just have a try.

试一试。

#3


0  

Would this mysql forumpost help?

这个mysql forumpost会有帮助吗?

Basically, it says you should be able to use mysqli and multi_query for this.

基本上,它说你应该能够使用mysqli和multi_query。

#4


-1  

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

你需要一个存储的例程(过程,函数)来包含所有必要的查询,所以在PHP中,你可以优雅地调用存储的例程

#1


11  

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.

CLI中需要使用分隔符来指示SQL语句的结束位置,因为CLI将继续读取并执行更多语句,直到您告知它停止(例如,使用exit或Control-D)。但它实际上读的只是一串人物;它以某种方式需要弄清楚一个语句的结束和下一个语句的开始。这就是分隔符的作用。

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.

在PHP中,每个函数调用都执行一个语句。一个函数调用中不能有多个语句,因此不需要一种方法来分隔它们。该语句是整个字符串。旧的mysql_query以及较新的mysqli_query和PDO也是如此。当然,如果你真的想要将多个查询传递给一个函数,那么就有mysqli_multi_query。

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.

在存储过程/触发器/函数/等的情况下,可以有多个语句,但这些语句由MySQL本身处理(并且始终是;,AFAIK)。因此,就PHP而言,这仍然是一个声明。

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

您在phpMyAdmin中看到的分隔符设置可能用于拆分语句,可能是在PHP代码中完成的。它必须这样做,因为它接受由多个语句组成的用户输入,但每个函数调用只能传递一个语句。 (我没有检查phpMyAdmin代码以完全确定这一点)。

#2


1  

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

这取决于。我使用MySQLi的multi_query()函数并离开;就像在做一个程序创建时一样,实际完成了(PHP 5.6和MySQL 5.6)。我认为你不需要关心分隔符,只留下目标代码然后可能会工作。您可能只尝试将SQL查询为

CREATE XXXX
BEGIN
    sth;
END

Just have a try.

试一试。

#3


0  

Would this mysql forumpost help?

这个mysql forumpost会有帮助吗?

Basically, it says you should be able to use mysqli and multi_query for this.

基本上,它说你应该能够使用mysqli和multi_query。

#4


-1  

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

你需要一个存储的例程(过程,函数)来包含所有必要的查询,所以在PHP中,你可以优雅地调用存储的例程