The first time I wrote a MySQL FUNCTION, I kept getting errors. 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 The 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 several attempts, the code is modified 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 ; Problem solved. By default, the delimiter is a semicolon;. Summarize The above is all the content of this article about the solution to the problem that navicat cannot create functions. I hope it will be helpful to everyone. Interested friends can continue to refer to this site: a brief introduction to the difference between Redis and MySQL, analysis of MYSQL subquery and nested query optimization examples, several important MySQL variables, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site! You may also be interested in:
|
<<: VMware Workstation 14 Pro installation Ubuntu 16.04 tutorial
>>: How to clean up Alibaba Cloud MySQL space
When exporting data to operations, it is inevitab...
How to make a simple web calculator using HTML, C...
First, there is only one change event. changeleve...
Introduction The use of is null, is not null, and...
Preface Use nginx for load balancing. As the fron...
Html semantics seems to be a commonplace issue. G...
Table of contents Document Object Model (DOM) DOM...
This article shares the installation and configur...
For work needs, I found a lot of information on t...
Event bubbling, event capturing, and event delega...
The pitfalls 1. Many tutorials on the Internet wr...
Table of contents Initial Vue Building a Vue deve...
Even though it's not Halloween, it's wort...
Table of contents Function call optimization Func...
About JS, CSS CSS: Stylesheet at the top Avoid CS...