如何让 mysqli 在 SQL 语句中使用分隔符? [英] How to get mysqli working with DELIMITERs in SQL statements?

问题描述

我正在使用 mysqli，现在尝试从 SQL 代码片段(由 MySQL Workbench 生成)获取视图到数据库中.

I'm using mysqli and trying now to get a view from an SQL code snippet (generated by MySQL Workbench) into a database.

$query = <<<QUERY
DROP VIEW IF EXISTS `myview` ;
SHOW WARNINGS;
DROP TABLE IF EXISTS `myview`;
SHOW WARNINGS;
DELIMITER $$
CREATE OR REPLACE VIEW `myview` AS

...view definition...

$$
DELIMITER ;

;
SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
QUERY;

$result = mysqli_multi_query($dbConnection, $query);

这行不通.没有错误，该语句似乎只是被忽略了.它仅适用于，如果我删除分隔符定义(行 DELIMITER $$、$$ 和 DELIMITER ;).

This doen't work. No errors, the statement seems just to be ignored. It only works, if I remove the delimiter definitions (lines DELIMITER $$, $$, and DELIMITER ;).

为什么它不起作用?在传递给 mysqli 函数和方法的 SQL 语句中使用分隔符可以/应该做什么?

Why is it not working? What can/should I do to use delimiters in SQL statements passed to mysqli functions and methods?

谢谢

推荐答案

这是帮助我理解这一点的来源...http://zend-framework-community.634137.n4.nabble.com/Problems-changed-the-sql-end-of-statement-delimiter-tp2124060p2124276.html

This is the source that helped me understand this... http://zend-framework-community.634137.n4.nabble.com/Problems-changing-the-sql-end-of-statement-delimiter-tp2124060p2124276.html

DELIMITER 不是真正的 MySQL 语句.

DELIMITER is not an actual MySQL Statement.

我相信这只是一些 MySQL 客户端实现的东西，以帮助同时传送一堆 sql 语句.

I believe it is just something that some MySQL clients have implemented to help shipping a bunch of sql statements at the same time.

mysqli 驱动程序没有实现这个功能.

所以，这应该可行.

$query = <<<QUERY
DROP VIEW IF EXISTS `myview` ;
SHOW WARNINGS;
DROP TABLE IF EXISTS `myview`;
SHOW WARNINGS;

CREATE OR REPLACE VIEW `myview` AS

...view definition...

;
SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
QUERY;

$result = mysqli_multi_query($dbConnection, $query);

我遇到了同样的问题，使用相同的 mysqli 驱动程序，使用 multi_query 函数(在创建过程时使用分隔符)并从我的 SQL 中删除 DELIMITER 工作.

I ran into the same problem, with the same mysqli driver, with multi_query function (using delimiters while creating procedures) and removing the DELIMITER from my SQL worked.

这篇关于如何让 mysqli 在 SQL 语句中使用分隔符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！