How to copy a MySQL function?
I have tried using the export option in phpMyAdmin Routines Panel to copy functions from one database to another with no success.
The export option provides me with the following:
CREATE DEFINER=`root`@`localhost` FUNCTION `JSON_FIELD_NUM`(`col_name` TEXT CHARSET utf8, `data` TEXT CHARSET utf8) RETURNS text CHARSET utf8
NO SQL
BEGIN
RETURN
CONCAT('"',col_name,'":',
IF(ISNULL(data),0,data)
);
END
I get this error when I run it on a different database:
#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 '' at line 7
I tried adding DELIMITER $$
at the top and $$
after END
, but still no joy.
source to share
You must set the customer statement delimiter to a string other than ;
, so that it does not count the semicolon that ends the statement CONCAT()
also ends the statement CREATE FUNCTION
.
In MySQL command line tool, you can use DELIMITER
command . In phpMyAdmin, you will need to use a textbox DELIMITER
before clicking Go
.
source to share
This is a much more concise way:
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-data"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-create-info"
mysqldump ${MYSQLDUMP_OPTIONS} > StoredProcedures.sql
less StoredProcedures.sql
This will reset only the stored procedures.
Give it a try !!!
source to share