您的位置: 首页  >  技术问答  >  如何使用PDO在PHP中创建一个包含DELIMITER的存储过程?

如何使用PDO在PHP中创建一个包含DELIMITER的存储过程?

分类: 技术问答 • 2022-04-06 22:26:45
问题描述：

在我的模型中，我定义了一些过程.该代码(由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?

答

来自评论:

我不想删除DELIMITER语句.实际上，我需要使它工作，而无需手动执行每条语句

I don't want remove the DELIMITER statements. And actually I wnat to get it working without to execute every statement manually

那不是它的工作方式.

要了解原因，您需要了解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客户端程序定义包含分号字符的存储程序，则会出现问题.默认情况下，mysql本身会将分号识别为语句定界符，因此您必须临时重新定义该定界符，以使mysql将整个存储的程序定义传递给服务器.

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to 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.

相关推荐