在 WEB 项目开发中，想在 Java 后台直接修改 MySQL 数据库的分隔符，在 MySQL 终端，执行下面的命令完全正常：
delimiter ||

但只要是通过 Java 修改：
ps.execute("delimiter ||");

就会抛出如下错误：

com.mysql.jdbc.exceptions.jdbc4.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

网上搜了一大圈，资料是少之又少，根本找不到一个好的解决办法。

八九年前就有人碰到了这种问题，详细见http://forums.mysql.com/read.php?39,130834,130834#msg-130834

DELIMITER is a command that is pre-parsed by a client that runs SQL scripts, e.g. mysql command-line tool or MySQL Query Browser.

It should be unnecessary to use DELIMITER if you're executing one statement at a time through JDBC or another programming interface. DELIMITER is necessary only when the semicolon inside a trigger or procedure body is ambiguous with the semicolon at the end of the the CREATE or ALTER statement. If you're using an API that executes only one statement at a time, there's no ambiguity.

------------------------------------------------------------------------------------------------------

Most SQL script implementations (not just MySQL) will have a subset of statements in the script language that should be caught by the client and handled there.

For instance, QUIT or EXIT is frequently in SQL scripting languages, and it wouldn't make much sense to pass that statement to the RDBMS server. It's a command for the client to do something, not the server.

See http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html for a list of mysql client commands. Any of these can appear in SQL scripts for MySQL, but should be handled by the client, and not be sent to the MySQL Server to execute.

Oh another comment about supporting DELIMITER.

If you're processing a SQL script, your code does need to pay attention to DELIMITER, so it knows how to parse the following statements. Statements can be multiple lines long. So instead of reading line by line, you need to read input from the script file up to the next occurrence of the characters defined by DELIMITER. The delimiter can be a single character or multiple characters:

DELIMITER !!

SELECT foo 
FROM bar!!

DELIMITER ;

SELECT baz 
FROM bling;

------------------------------------------------------------------------------------------------------

You don't need "delimiter" to create a stored procedure using Connector/J or any other client that isn't "mysql". The delimiter exists *only* to tell "mysql" that you're done typing, and that the command should be sent to the server. With other *libraries*, that's done by the very fact that you've called some method to send the string representing the command to the server.

Also, there is no good reason for client libraries to be parsing the SQL sent to them more than they absolutely need to, it will cause performance degradation for every one who uses them.

上面大概说的意思是，DELIMITER 是一个客户端工具的命令，在通过 JDBC 驱动或者其它程序接口执行一条语句的时候，是没有必要去修改 MySQL 默认的分隔符的，而只有当语句中有 trigger（触发器） 或者 procedure（存储过程） 时，才需要修改其默认分隔符。如果是每次通过 API 执行仅仅一条语句，是没有必要去修改它的。

最后试了好多次，才发现，在 Java 中创建存储过程或者是触发器，都不需要修改分隔符。SQL 默认执行后，可以通过工具查看已经成功创建了的存储过程和触发器，执行完全正常。