douchunji1885
2014-05-26 13:57
浏览 76
已采纳

如何在PHP中使用带有PDO的MySQL用户函数?

This is what I've coined up, the code doesn't work and I have no idea what I'm doing wrong.

For example I don't know how I'm supposed to register my function - how to inject it in relation to the query etc. Could someone tell me how to write this piece of code correctly?

error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
 error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELI
MITER $$
CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANG' at line 1' in mysql_test.php:18
Stack trace:
#0 mysql_test.php(18): PDO->exec('DELIMITER $$
C...')
#1 {main}
  thrown in mysql_test.php on line 18

the code:

$db = new PDO("mysql:host=localhost;dbname=test_db;charset=utf8", 'root');
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$db->exec("CREATE TABLE IF NOT EXISTS test_table (col1 INT, col2 INT);");
for ($i = 0; $i<10; $i++) {
  $db->exec("INSERT INTO test_table VALUES(".rand().",".rand().");");
}
$mysql_function = <<<'NOW'
DELIMITER $$
CREATE FUNCTION my_sum(int1 INT, int2 INT)
  RETURNS INT
  LANGUAGE SQL
BEGIN
  RETURN int1+int2;
END;
$$
DELIMITER ;
NOW;
$db->exec($mysql_function);
$db->query("SELECT my_sum(col1, col2) FROM test_table")->fetchAll();

图片转代码服务由CSDN问答提供 功能建议

这是我创造的,代码不起作用,我不知道我是什么 做错了。

例如我不知道我应该如何注册我的函数 - 如何根据查询等注入它。有人能告诉我如何编写这段代码 正确吗?

错误:

 致命错误:未捕获异常'PDOException',消息为'SQLSTATE [42000]:语法错误或访问冲突 :1064 SQL语法中有
错误; 检查与MySQL服务器版本对应的手册,以便在'DELI 
MITER $$'附近使用正确的语法
创建函数my_sum(int1 INT,int2 INT)
在mysql_test.php中的第1行返回INT 
 LANG'  :18 
堆栈跟踪:
#0 mysql_test.php(18):PDO-&gt; exec('DELIMITER $$ 
 
C ...')
#1 {main} 
在mysql_test.php中抛出 在第18行
   
 
 

代码:

  $ db = new PDO(“mysql:host = localhost;  dbname = test_db; charset = utf8“,'root'); 
 $ db-&gt; setAttribute(PDO :: ATTR_ERRMODE,PDO :: ERRMODE_EXCEPTION); 
 $ db-&gt; exec(”CREATE TABLE IF NOT NOT EXISTS test_table  (col1 INT,col2 INT);“); 
for($ i = 0; $ i&lt; 10; $ i ++){
 $ db-&gt; exec(”INSERT INTO test_table VALUES(“。rand()。”  ,“。rand()。”);“); 
} 
 $ mysql_function =&lt;&lt;&lt;'NOW'
DELIMITER $$ 
CREATE FUNCTION my_sum(int1 INT,int2 INT)
 RETURNS INT \  n LANGUAGE SQL 
BEGIN 
 RETURN int1 + int2; 
END; 
 $$ 
DELIMITER; 
NOW; 
 $ db-&gt; exec($ mysql_function); 
 $ db-&gt; query(“SELECT my_sum  (col1,col2)FROM test_table“) - &gt; fetch  All(); 
   
 
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanlushen8940 2014-05-26 14:08
    已采纳

    The mysql driver does not support multi-statement queries. Therefore, DELIMITER makes no sense in a query, and the result is that it is a syntax error to try and use it.

    You should be able to just have:

    $mysql_function = <<<'NOW'
    CREATE FUNCTION my_sum(int1 INT, int2 INT)
      RETURNS INT
      LANGUAGE SQL
    BEGIN
      RETURN int1+int2;
    END;
    NOW;
    

    However, CREATE FUNCTION is a Data Definition Statement and you should probably never have such statements in your PHP-run queries. Table structures and functions should be defined when you're setting up the server, using one-time-use queries such as from a .sql file, through the mysql command line, or an administration GUI like phpMyAdmin.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题