MySql中的分隔符错误

Question

我使用以下sql:

DELIMITER $$
DROP PROCEDURE IF EXISTS `get_auto_increment_settings`$$
CREATE PROCEDURE `get_auto_increment_settings`()
BEGIN   
    select @@global.auto_increment_offset as 'offset', @@global.auto_increment_increment as 'increment' ;
END $$
DELIMITER ;

我将其存储在db_auto_increment_settings_procedure中。当我试图从ant执行此命令时，我面临以下错误:

[sql] Executing resource: /mysql/install/db_auto_increment_settings_procedure.sql
[sql] Failed to execute:  DELIMITER
[sql] com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'DELIMITER' at line 1
[sql] Failed to execute:  DELIMITER ;
[sql] com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'DELIMITER' at line 1

Answer 1

分隔符仅用于mysql客户端(不用于API, driver…等等)。
所以，它不会工作。

查看错误信息:-

[sql] Executing resource: /mysql/install/db_auto_increment_settings_procedure.sql  
[sql] Failed to execute:  DELIMITER

mysql -u root -pxxx -h yyy < YOUR_SQL.sql

如果适用，您只需使用mysql客户端手动创建存储过程，
而且是一种无需流汗的解决方案。

如果你需要动态创建它，
本文档可能会提供一些有见地的信息
http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html

Answer 2

您的过程只有一个命令，因此不需要分隔符-

DROP PROCEDURE IF EXISTS `get_auto_increment_settings`;
CREATE PROCEDURE `get_auto_increment_settings`()
SELECT @@global.auto_increment_offset AS 'offset', @@global.auto_increment_increment AS 'increment';