問題描述
我有許多用 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 中插入/創建存儲過程?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！