如何从PHP在mySQL中插入/创建存储过程? [英] How to insert/create stored procedures in mySQL from PHP?
问题描述
我有许多使用MySQL Workbench制作的存储过程.当使用MySQL工作台将它们放入我的测试数据库时，它们工作得很好.
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.
现在，我正在准备数据库创建脚本以进行部署，这是唯一给我带来麻烦的脚本.当我使用命令行/mysql shell时，脚本可以很好地工作.只有当我使用PHP mysql(i)接口执行脚本时，它才会失败.没有评论.
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.
我使用过程创建脚本作为MySQL工作台为我生成的过程；也就是说，它具有以下模式:
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
...过程在这里
;
END$$
DELIMITER ;
如果从MySQL Workbench运行，此脚本可以正常工作.如果我从mysql命令行尝试同样的方法，它也可以正常运行.但是，当我通过PHP mysqli接口执行它时，它无法完成任何事情(使用mysqli_multi_query执行它，这对于创建和填充数据库的其他脚本而言效果很好).接口上没有返回错误，没有结果(！). 我得到的只是假"，仅此而已.错误代码为0，无错误消息.
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.
对我来说这是一个很大的WTF，希望您能为我指明正确的方向-如何解决此问题并从PHP安装过程?
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访问权限(毕竟，我只是用完全相同的连接创建了DB，创建了用户，插入了表等等).
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).
推荐答案
总结一下:
DELIMITER是在客户端而非服务器端实现的.
DELIMITER is implemented client-side, not serverside.
如果您有良好的驱动程序或API，它可能会解决此问题. 到目前为止，还没有PHP mysql/mysqli.
If you have a good driver or API, it may take care of this. PHP mysql / mysqli, as of now, do not.
如果您需要使用其他定界符(例如，由于默认定界符出现在脚本中)，则您必须自己编码/转义SQL或将其拆分，因此无需更改定界符.没有PHP的帮助.
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.
这篇关于如何从PHP在mySQL中插入/创建存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
