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