如何使用mysqli multiquery执行自定义定界符查询 [英] How to execute custom delimiter query with mysqli multiquery

问题描述

我有以下sql脚本，我想通过多查询执行它

I have the following sql script and I want to execute it with multi-query

DELIMITER $$
DROP FUNCTION IF EXISTS `getAttendanceHistoryDates`$$

CREATE FUNCTION getAttendanceHistoryDates(processDate date)
  RETURNS TEXT
  DETERMINISTIC
  LANGUAGE SQL
BEGIN

  DECLARE minDate date;
  DECLARE startYear int;
  DECLARE endYear int;
  DECLARE dateString TEXT;

  SET minDate = (SELECT MIN(date) FROM `ohrm_attendance_report`);
  SET startYear = YEAR(minDate);
  SET endYear = YEAR(processDate);

  SET  dateString = processDate;
  WHILE startYear  < endYear DO
     SET  dateString = CONCAT(dateString,'|',CONCAT(startYear, '-12-31'));
     SET  startYear = startYear + 1; 
  END WHILE;

  RETURN dateString;
END;
$$
DELIMITER ;

有没有办法做到这一点?如果我只从脚本中删除DELIMITER $$和DELIMITER ;并将$$替换为;并执行多查询，是否可以正常工作?

Is there a way to do this? Will it work if I just remove DELIMITER $$ and DELIMITER ; from the script and replace $$ by ; and execute with multi-query?

推荐答案

否，无法通过MySQL API进行.分号分隔符不可配置.这实际上是在MySQL服务器端确定的.有关更多详细信息，请参见 http://dev. mysql.com/doc/refman/5.6/en/c-api-multiple-queries.html

No, it is not possible through the MySQL API. The semicolon separator is not configurable. It's really determined on the MySQL server side. For more details, see http://dev.mysql.com/doc/refman/5.6/en/c-api-multiple-queries.html

mysql命令行界面通过预分析输入和使用定界符分隔语句来支持DELIMITER.然后，它将分别执行每个语句.

The mysql command-line interface supports DELIMITER by pre-parsing the input and separating statements by the delimiter. Then it executes each statement individually.

没有理由需要使用多重查询.您应该将DROP FUNCTION和CREATE FUNCTION作为单独的查询运行.

There is no reason you need to use multi-query. You should run the DROP FUNCTION and the CREATE FUNCTION as individual queries.

使用多重查询通常不是一个好主意，因为它为错误的SQL注入问题创造了机会.

Using multi-query is a bad idea in general, because it creates an opportunity for bad SQL injection problems.

这篇关于如何使用mysqli multiquery执行自定义定界符查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！