问题描述
在我的模型中,我定义了一些过程.该代码(由MySQL Workbench生成)包含DELIMITER
定义,因此过程如下:
In my model I defined some procedures. The code (generated by MySQL Workbench) contains DELIMITER
definitions, so the procedures look like:
-- schema
CREATE DATABASE ...
CREATE TABLE foo ...
-- procedures
DELIMITER $$
...
BEGIN
DECLARE ... ;
OPEN ... ;
SET ... ;
... ;
END$$
DELIMITER ;
现在,我需要通过 PDO 将SQL导入"到数据库中>.我试图将其作为 PDO#exec(...)
的输入,但注意到,在第一个DELIMITER
定义的行上停止执行.
Now I need to "import" the SQL to the database via PDO. I tried to pass it as input for the PDO#exec(...)
, but noticed, that the execution stops on the line of the first DELIMITER
definition.
我不想删除DELIMITER
语句.因此,SQL代码应保持不变.
I don't want remove the DELIMITER
statements. So the the SQL code should remain the same.
如何使用PDO执行包含DELIMITER
语句的SQL代码?
How to use PDO to execute SQL code containing DELIMITER
statements?
推荐答案
来自评论:
那不是它的工作方式.
要了解原因,您需要了解mysql
CLI和其他可以读取和执行转储文件的程序如何实际处理它.
To understand why, you need to understand how the mysql
CLI -- and any other program that can read and execute a dump file like this -- actually handles it.
DELIMITER
不是服务器可以理解的内容.
DELIMITER
is not something the server understands.
DELIMITER
用于告诉客户端解析器当前的语句定界符应该是什么,以便客户端解析器可以正确地拆分语句并一次将一个语句传递给执行服务器.
DELIMITER
is used to tell the client-side parser what the current statement delimiter should be, so that the client-side parser can correctly split the statements and deliver one at a time to the server for execution.
来自文档.请注意,每次在这里使用mysql
时,它都是指mysql
客户端实用程序-而不是服务器.
From the docs. Note carefully that mysql
, every time it is used here, refers to the mysql
client utility -- not the server.
要重新定义mysql
分隔符,请使用delimiter
命令. [...]分隔符更改为//
,以使整个定义可以作为单个语句传递到服务器,然后在调用该过程之前恢复为;
.这样可以将过程主体中使用的;
分隔符传递给服务器,而不是由mysql
本身解释.
To redefine the mysql
delimiter, use the delimiter
command. [...] The delimiter is changed to //
to enable the entire definition to be passed to the server as a single statement, and then restored to ;
before invoking the procedure. This enables the ;
delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql
itself.
https://dev.mysql.com /doc/refman/5.7/en/stored-programs-defining.html
因此,要处理这样的文件,您需要一个客户端解析器,该客户端解析器执行mysql
相同的操作...在这里,您正在编写的代码是(需要)客户端语句解析器.因此,您是需要编写逻辑来处理定界符的人.
So, to handle such a file, you need a client-side parser that does the same thing mysql
does... and here, the code you are writing is (needs to be) the client-side statement parser. So you are the one that needs to write the logic to handle the delimiter.
要执行所需的操作,您必须解释DELIMITER
语句,使用它们来跟踪当前的语句定界符,但不要将其发送到服务器.
To do what you want, you have to interpret the DELIMITER
statements, use them to keep track of the current statement delimiter, but do not send them to the server.
然后,您必须一次遍历输入的每一行,缓冲已读取的内容,直到在行末找到指定的分隔符,然后将结果语句发送到服务器-从发送的内容中排除实际的语句分隔符...因此,例如,您不会在过程主体之后发送结尾的$$
(除非当前的语句分隔符为;
,您可以发送或不发送-服务器不在乎.)然后清空缓冲区并再次开始读取,直到看到分隔符的另一个实例(并将该语句发送到服务器)或匹配DELIMITER
语句并设置代码的当前的分隔符变量以匹配它,以便您正确标识下一条语句的结尾.
Then, you have to read through the input one line at a time, buffering what you've read, until you find the specified delimiter at the end of the line, and send the resulting statement to the server -- excluding the actual statement delimiter from what you send... so, for example, you would not send the ending $$
after the procedure body (unless the current statement delimiter is ;
, which you can either send or not send -- the server doesn't care.) Then empty the buffer and start reading again until you see another instance of a delimiter (and send the statement to the server) or match a DELIMITER
statement and set your code's current delimiter variable to match it so that you correctly identify the end of the next statement.
这篇关于如何使用PDO在PHP中创建一个包含DELIMITER的存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!