mysql从学说原始sql创建过程的问题

2022-01-16 00:00:00 sql mysql symfony doctrine procedure

我正在使用 Symfony2 中的一个应用程序项目.通过注册，每个客户都会创建一个数据库.模式是在客户端登录时由验证服务创建的.应用程序需要一些数据才能工作，到目前为止，我使用了 ORM 固定装置.由于多种原因，我现在需要从固定装置负载转移到更接近数据库.我创建了一个存储过程(mysql)，它将替换所有数据加载.该过程有效，但我需要在每个数据库中与架构一起创建此过程.为此，我使用了学说原始 sql，但我无法通过 create procedure 语句的第一行传递问题.似乎与'delimiter $$'有关.

I am working with an application project in Symfony2. By registration, per client, a database is created. Schemas are created by a validation service when the client logs on. The application needs some data to work, and so far, I used ORM fixtures. For a number of reasons, I now need to move from fixtures load to get more close to the database. I have created a stored procedure (mysql) which will replace all data loading. The procedure works, but I need to create this procedure in each database together with the schema. I use doctrine raw sql for this purpose but I cannot get pass a problem with the first lines of the create procedure statement. It seems it's got to do with the 'delimiter $$'.

在使用 sql 语句执行服务时，我得到:"message":"执行 'delimiter $$ CREATE DEFINER=root@`localhost"

While executing the service with the sql statement i get: "message":"An exception occurred while executing 'delimiter $$ CREATE DEFINER=root@`localhost"

这似乎是因为换行但我不确定.有谁知道解决这个问题的方法吗?

It seems to be because of line-breaks but I am not sure. Anyone knows of some way around this?

推荐答案

问题在于delimiter命令只能在使用MySQL命令行工具时使用，如讨论http://dev.mysql.com/doc/refman/5.1/en/stored-programs-定义.html.要解决此问题，您需要删除要更改分隔符的部分并使用 ;作为你的分隔符.考虑以下几点:

The problem comes from the fact that delimiter command can only be used when using the MySQL Command-Line Tool as discussed http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html. To fix this problem you need to remove the part where you are changing the delimiter and just use ; as your delimiter. Consider the following:

delimiter $$

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
   SET @x = 0;
   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END$$

上述方法无效，但以下方法有效:

The above will not work however the following will:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

相关文章