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();
  • 写回答

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条)

报告相同问题?

悬赏问题

  • ¥20 如何解决访问网址被屏蔽的情况
  • ¥15 本地运行的vue2项目,使用内网穿透,前端界面一直转圈圈
  • ¥20 powerbuilder datawindow控件导出Excel数据,可不可以不自动覆盖原数据,而是在后面新插入入数据。
  • ¥100 无轴承永磁同步电机控制
  • ¥15 eps里添加本地倾斜模型
  • ¥15 telegram 问题
  • ¥15 nrf52810-c三个a 程序
  • ¥50 求一个半透明没有锯齿的圆角窗体的实现例子
  • ¥15 STM32cubeMX里的FreeRTOS无法释放内存
  • ¥15 CATIA有些零件打开直接单机确定终止