如何从 PHP 在 mySQL 中插入/创建存储过程?

时间:2022-11-22
本文介绍了如何从 PHP 在 mySQL 中插入/创建存储过程?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我有许多用 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 访问权限(毕竟,我刚刚创建了具有相同连接、创建用户、插入表等的数据库).

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 中插入/创建存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

上一篇:使用 PHP 从 SQL 存储过程中获取返回值 下一篇:有没有办法将数组绑定到 mysqli prepare

相关文章

最新文章