Navicat cannot be used to create functions.

Navicat cannot be used to create functions.

The first time I wrote MySQL FUNCTION, I always reported an error,

Err] 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 ''company _ id' int) RETURNS varchar (20) CHARSET utf8

BEGIN

Original function:

CREATE DEFINER=`33323`@`%` FUNCTION `createSaleCode`(`benginStr` varchar,`company_id` int) RETURNS varchar(20) CHARSET utf8 BEGIN   DECLARE nearnum VARCHAR(20);   DECLARE nowdatepre VARCHAR(20);  DECLARE numout VARCHAR(20);   SELECT a.sale_code INTO nearnum FROM d_sale a WHERE a.company_id = company_id ORDER BY a.sale_id DESC limit 1;  SELECT concat(extract(year_month from now()),LPAD(extract(day from now()), 2, 0)) INTO nowdatepre;  IF locate(nowdatepre,nearnum)>0    THEN      set numout = nearnum +1;     ELSE      set numout = concat(beginStr,nowdatepre,'00001');    END IF;  RETURN numout; END 

This function cannot be executed on Navicat. After multiple attempts, the code is changed to the following:

delimiter $$ CREATE DEFINER=`12212`@`%` FUNCTION createSaleCode(benginStr varchar(20),company_id int(11) ) RETURNS varchar(20) CHARSET utf8 BEGIN   DECLARE nearnum VARCHAR(20);   DECLARE nowdatepre VARCHAR(20);  DECLARE numout VARCHAR(20);   SELECT a.sale_code INTO nearnum FROM d_sale a WHERE a.company_id = company_id ORDER BY a.sale_id DESC limit 1;  SELECT concat(extract(year_month from now()),LPAD(extract(day from now()), 2, 0)) INTO nowdatepre;  IF locate(nowdatepre,nearnum)>0    THEN      set numout = nearnum +1;     ELSE      set numout = concat(beginStr,nowdatepre,'00001');    END IF;  RETURN numout; END$$ delimiter ; 

Solve the problem.

By default, delimiter is a semicolon ;.

Summary

The above is all about navicat's failure to create a function solution. I hope it will be helpful to you. If you are interested, you can continue to refer to this site: brief the differences between Redis and MySQL, MYSQL subquery and nested query optimization instance parsing, and several important MySQL variables, if you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support!