我正在从PHP中读取一个TEXT文件,并尝试从中执行命令,比如创建一个DB以及它所拥有的所有表和过程。我的代码创建表,但不创建文件中给定的存储过程。
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END$$
PDO没有创建SP,如何才能完成此任务?我试着一行一行地执行所有的代码部分,但没有任何效果
我正在尝试制作一个DB安装程序脚本。
好吧,PMA帮我回答了这个问题
为了克服这个问题,你需要删除过程中的分隔符部分,这样你的查询就会变成:
DROP PROCEDURE IF EXISTS `add_hits`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
declare hits_bk int;
select hits into hits_bk from db_books where Book_ID = id;
update db_books set hits=hits_bk+1 where Book_ID = id;
END;
现在查询就可以工作了
感谢@Your Common Sense和@RiggsFilly的帮助。
PHP通常只允许一次执行一个查询,因此不需要$$
分隔符。
$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$pdo->exec('DROP PROCEDURE IF EXISTS `add_hits`');
$pdo->exec('CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
declare hits_bk int;
select hits into hits_bk from db_books where Book_ID = id;
update db_books set hits=hits_bk+1 where Book_ID = id;
END');
为了保持主题并回答问题
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END$$
首次出现$$
将终止DDL,因此
DROP PROCEDURE IF EXISTS `add_hits`$$
所以我认为应该是
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_hits`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_hits`( In id varchar(255))
BEGIN
select hits into @hits from db_books where Book_ID = id;
update db_books set hits=@hits+1 where Book_ID = id;
END
$$