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
Because I need to use Ubuntu+Python 3.6 version t...
In actual projects, there are relationships betwe...
Today, I encountered a little problem when I was ...
Table of contents 1. Problem description: 2. Trou...
Port Mapping Before the Docker container is start...
Preface The file system is responsible for organi...
Every time I design a web page or a form, I am tr...
The uniapp applet will have a similar drop-down p...
Table of contents Preface Prototypal inheritance ...
1. Overview Zabbix is a very powerful and most ...
1. Why do packaging? Facilitates overall code cal...
First download the dependencies: cnpm i -S vue-uu...
Table of contents Preface Configure yum source, e...
1. Install xshell6 2. Create a server connection ...
For those who don't know how to install the s...