如何从 PHP 在 mySQL 中插入/创建存储过程?
我有许多使用 MySQL Workbench 制作的存储过程。当使用 MySQL 工作台将它们放入我的测试数据库时,它们工作得很好。
现在我正在准备用于部署的数据库创建脚本,这是唯一给我带来麻烦的脚本。当我使用命令行/mysql shell 时,该脚本运行良好。仅当我使用 PHP mysql(i) 接口执行脚本时,它才会失败。不予评论。
我使用 MySQL Workbench 为我生成的过程创建脚本;也就是说,它具有以下模式:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
在脚本开始处,然后对每个过程重复:
DELIMITER $$
USE `dbname`$$
CREATE PROCEDURE `procname`(IN inputparameters)
BEGIN
... 过程在此处
;
END$$
DELIMITER ;
如果从 MySQL Workbench 运行,该脚本可以正常工作。如果我从 mysql 命令行尝试同样的操作,它也运行得很好。但是当我通过 PHP mysqli 接口执行它时(使用 mysqli_multi_query 执行它,它对于创建和填充数据库的其他脚本运行良好),它无法完成任何事情。界面上没有返回错误,没有结果(!)。 我得到的只是“假”,仅此而已。错误代码为0,无错误消息。
这对我来说是一个很大的问题,我希望你能给我指出正确的方向 - 我怎样才能解决这个问题并从 PHP 安装程序?
PS:授予并验证 root/admin 访问权限(毕竟,我刚刚使用相同的连接创建了数据库,创建了用户,插入了表等)。
I've got a number of stored procedures made with the MySQL Workbench. They work just fine when use MySQL workbench to put them into my test DB.
Now I am preparing the DB creation scripts for deployment, and this is the only one giving me trouble. When I use the command line / mysql shell, the script works perfectly well to. It's only when I use the PHP mysql(i) interface to execute the script - it fails. Without comment.
I use the procedure creation scripts as MySQL workbench generates for me; that is, it has this pattern:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
at the start of the script, then repeating for each procedure:
DELIMITER $
USE `dbname`$
CREATE PROCEDURE `procname`(IN inputparameters)
BEGIN
... procedure goes here
;
END$
DELIMITER ;
This script works fine if run from MySQL Workbench. It also runs fine if I try the same from mysql commandline. But it fails to accomplish anything when I execute it through the PHP mysqli interface ( executing it with mysqli_multi_query, which works fine for the other scripts creating and populating the DB). There is no error returned on the interface, no results (!).
All I get is "false", and that's it. error code is at 0, no error message.
It's a big WTF for me, and I hope you can point me in the right direction - how can I fix this and install the procedures from PHP?
PS: root/admin access is given and verified (after all, I just created the DB with the very same connection, created users, inserted tables and so on).
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我还没有测试过,但我不会对 mysqli_multi_query() 期望每个查询具有相同的分隔符感到惊讶。
尝试将存储过程创建打包在单个查询中,而不使用 DELIMITER 修饰符?
因此,不要
只是这样做
并告诉我们它是否有效:)
I haven't tested, but I won't be surprised by
mysqli_multi_query()
expecting to have the same delimiter of each queries.Try to pack the stored procedure creation in a single query, without using the DELIMITER modifier ?
So instead of
Just do this
And tell us if it works :)
总结一下:
DELIMITER 是在客户端实现的,而不是在服务器端实现的。
如果你有一个好的驱动程序或 API,它可能会解决这个问题。
PHP mysql / mysqli,到目前为止,还没有。
如果您需要使用不同的分隔符(例如,因为默认分隔符出现在脚本内),您必须自己对 SQL 进行编码/转义或将其分解,这样您就不需要更改分隔符。 PHP 没有帮助。
To sum it up:
DELIMITER is implemented client-side, not serverside.
If you have a good driver or API, it may take care of this.
PHP mysql / mysqli, as of now, do not.
If you need to use a different delimiter (e.g. because the default delimiter appears inside scripts), you have to encode/escape your SQL yourself or break it up so you don't need to change the delimiter. No help from PHP here.
DELIMITER 是 mysql 客户端应用程序实际使用的东西。我相信客户端应用程序负责分解它发送到 Mysql 的查询。例如,这就是 PHPMyAdmin 所做的事情。
不用花一整夜的时间编写脚本来将 MySQL 解析为查询,而是使用我编写的代码。您可以在 scriptToQueries 函数中找到它,如下所示:
http ://wush.net/svn/luckyapps/pie/trunk/framework/classes/Db/Mysql.php
享受
编辑:自从写这个答案以来,我已将代码移植到免费的 Q 平台中,您可以在其中学习代码: https://github.com /EGreg/Platform/blob/master/platform/classes/Db/Mysql.php#L824
DELIMITER is something that the mysql client application actually uses. I believe that the client application is responsible for breaking up the queries that it sends to Mysql. That is what PHPMyAdmin does, for example.
Instead of spending a whole night writing a script to parse MySQL into queries, use the code I wrote. You will find it in the scriptToQueries function, here:
http://wush.net/svn/luckyapps/pie/trunk/framework/classes/Db/Mysql.php
ENJOY
EDIT: Since writing this answer I have ported the code into the free Q platform in which you can study the code: https://github.com/EGreg/Platform/blob/master/platform/classes/Db/Mysql.php#L824