How to execute custom delimiter query with mysqli

问题:

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 ;

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?

回答1:

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

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

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

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