CREATE DEFINER=`root`@`%` FUNCTION `func_split`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END;
错误提示:
ERROR 1064 (42000): 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 '' at line 1
ERROR 1064 (42000): 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 'declare result varchar(255) default '';
set result = reverse(substring_' at line 3我是通过SecureCRT访问对方的数据库。通过后台执行的sql指令。可是在控制台执行sql文件会报错。而在界面操作创建则不然! 求大神啊啊 !!
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
SHOW WARNINGS;
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(' at line 1
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT ''; 到底是什么原因呢 ?
DELIMITER$$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8^M
BEGIN ^M
# 拆分传入的字符串,返回拆分后的新字符串 ^M
DECLARE result VARCHAR(255) DEFAULT ''; ^M
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1)); ^M
RETURN result; ^M
END;$$^M
DELIMITER$$
我是把导出的sql文件放在对方的服务器上,用SecureCRT工具访问对方数据库,直接用source **.sql导入。
我是把导出的sql文件放在对方的服务器上,用SecureCRT工具访问对方数据库,直接用source **.sql导入。
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Empty set (0.00 sec)mysql> A.TXTDELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
SHOW WARNINGS;
DELIMITER $$:空1格
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3
还是不通过 ~~----------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
按照楼上的方法,我新建一个a.sql 内容为
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
SHOW WARNINGS;然后保存。
用mysql -urooot -pXXXXX ;
新建数据库 create database qi;
然后:source a.sql。
报错的内容是:
mysql> source fun.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1064 (42000): 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 '' at line 1
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
先说明下我的mysql版本是5.1.23的。
按照楼上的方法,我新建一个a.sql 内容为
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
SHOW WARNINGS;然后保存。
用mysql -urooot -pXXXXX ;
新建数据库 create database qi;
然后:source a.sql。
报错的内容是:
mysql> source fun.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1064 (42000): 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 '' at line 1
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
先说明下我的mysql版本是5.1.23的。
按照楼上的方法,我新建一个a.sql 内容为
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
SHOW WARNINGS;然后保存。
用mysql -urooot -pXXXXX ;
新建数据库 create database qi;
然后:source a.sql。
报错的内容是:
mysql> source fun.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1064 (42000): 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 '' at line 1
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX' at line 3 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `func_split`$$
Query OK, 0 rows affected, 1 warning (0.06 sec)mysql> CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5
),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
-> BEGIN
-> # 拆分传入的字符串,返回拆分后的新字符串
-> DECLARE result VARCHAR(255) DEFAULT '';
-> SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_
delimiter,f_order)),f_delimiter,1));
-> RETURN result;
-> END;$$
Query OK, 0 rows affected (0.02 sec)mysql> DELIMITER ;
mysql>
[code=BatchFile]
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
[/code]mysql> DROP FUNCTION IF EXISTS `func_split`;
Query OK, 0 rows affected (0.00 sec)mysql> source a.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show create function func_split;
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
| Function | sql_mode | Create Function | character_set_client | collation_connect
ion | Database Collation |
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
| func_split | | CREATE DEFINER=`root`@`localhost` FUNCTION `func_split
`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS varchar(255
) CHARSET utf8
BEGIN DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimit
er,f_order)),f_delimiter,1));
RETURN result;
END | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
1 row in set (0.02 sec)mysql>
MySQL-Front 在数据库上右键另存为 只存表结构 、不存数据 ~
我是在给客户的服务器修改呢 , 跟cmd是一个样子的 ~~ 你可以理解为是cmd !!
DELIMITER $$
DROP FUNCTION IF EXISTS `func_split`$$
DELIMITER ;上述命令提示什么
这是我在本机下新建的数据库然后添加函数 ~ 可是还是保存 ! 下面我贴上我的代码 :C:\Documents and Settings\Administrator>mysql -uroot -pism
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.1.45-community-log MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql>
mysql>
mysql> source d:\abc.sql;
ERROR:
Unknown command '\a'.
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
mysql>
mysql>
mysql>
mysql> create database qualityinsight_v2;
Query OK, 1 row affected (0.02 sec)mysql>
mysql>
mysql> use qualityinsight_v2;
Database changed
mysql>
mysql>
mysql> source d:\abc.sql;
ERROR:
Unknown command '\a'.
Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ
L DATA in its declaration and binary logging is enabled (you *might* want to use
the less safe log_bin_trust_function_creators variable)
mysql>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.1.45-community-log MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql>
mysql>
mysql> source d:\abc.sql;
ERROR:
Unknown command '\a'.
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
mysql>
mysql>
mysql>
mysql> create database qualityinsight_v2;
Query OK, 1 row affected (0.02 sec)mysql>
mysql>
mysql> use qualityinsight_v2;
Database changed
mysql>
mysql>
mysql> source d:\abc.sql;
ERROR:
Unknown command '\a'.
Query OK, 0 rows affected, 1 warning (0.00 sec)ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ
L DATA in its declaration and binary logging is enabled (you *might* want to use
the less safe log_bin_trust_function_creators variable)
mysql>
set global log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
# 拆分传入的字符串,返回拆分后的新字符串
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `func_split`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5
),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
-> BEGIN
-> # 拆分传入的字符串,返回拆分后的新字符串
-> DECLARE result VARCHAR(255) DEFAULT '';
-> SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_
delimiter,f_order)),f_delimiter,1));
-> RETURN result;
-> END;$$
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQ
L DATA in its declaration and binary logging is enabled (you *might* want to use
the less safe log_bin_trust_function_creators variable)
mysql> DELIMITER ;
同样还是报source的错误。 难以理解哦 。
正解 ! 设置一下就好了 !!
mysql> CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5
),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
-> BEGIN
-> # 拆分传入的字符串,返回拆分后的新字符串
-> DECLARE result VARCHAR(255) DEFAULT '';
-> SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_
delimiter,f_order)),f_delimiter,1));
-> RETURN result;
-> END;$$
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
mysql> use qualityinsight_v2
Database changed
mysql>
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `func_split`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
ERROR 1064 (42000): 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 '' at line 1
mysql> BEGIN
-> # 拆分传入的字符串,返回拆分后的新字符串
-> DECLARE result VARCHAR(255) DEFAULT '';
-> SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
-> RETURN result;
-> END;$$
-> DELIMITER ;
ERROR 1064 (42000): 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 'DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(R' at line 3这是我刚才在对方的服务器又复制创建了一下 。 我连set global log_bin_trust_function_creators=1;也同样设置了 ~ 可是还是报错 !
DROP FUNCTION IF EXISTS `func_split`$$
CREATE FUNCTION `func_split`(f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE result VARCHAR(255) DEFAULT '';
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END;$$
DELIMITER ;
把注释先去掉试一下。非常奇怪的问题。
初步怀疑是Mysql版本的问题 ~ 因为我本机的版本和客户服务器上的版本不一样 ~~ 有可能把 。
如:
String createDelimiter = "delimiter $$";
statement.executeQuery(createDelimiter);
// String dropPro = " DROP PROCEDURE IF EXISTS 'pro_syfs1' $$ ";
// statement.executeUpdate(dropPro);
System.out.println("1");
// createTable = "CREATE PROCEDURE pro_syfs1() ";
// createTable = "BEGIN ";
// createTable = "select * from fp_kc; ";
// createTable = "end $$";
//// createTable = "delimiter ;";
// statement.executeUpdate(createTable);总是报错,望高手帮助。
java.sql.SQLException: 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
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2851)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1625)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2291)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2226)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1159)
at GSIBC.TestProcedure.main(TestProcedure.java:42)