带有嵌套 IF...END IF 的 MySQL 存储函数，语法错误，在 '' 附近使用正确的语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9048931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL stored function with nested IF... END IF, error in syntax, right syntax to use near ''
提问by batfastad
I have a function that I currently use in PHP which compiles a mailing address from separate fields but takes into account different formats used in different regions. I'm trying to replicate this as a MySQL stored function. I realise that it's often faster to do this sort of thing in code rather than in the database but our intranet has a way for people to enter raw MySQL SELECT commands in read-only so they can construct advanced searches and save the queries. This particular function will be used so that users can output their advanced search query results to a label layout.
我有一个目前在 PHP 中使用的函数，它从不同的字段编译邮寄地址，但考虑到不同地区使用的不同格式。我正在尝试将其复制为 MySQL 存储函数。我意识到在代码中而不是在数据库中执行此类操作通常更快，但我们的内部网提供了一种方法，人们可以以只读方式输入原始 MySQL SELECT 命令，以便他们可以构建高级搜索并保存查询。将使用此特定功能，以便用户可以将其高级搜索查询结果输出到标签布局。
When I try and store the function using phpMyAdmin 3.4.9 (latest stable) I get the following error:
当我尝试使用 phpMyAdmin 3.4.9（最新稳定版）存储该函数时，出现以下错误：
#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 51
I also installed the latest MySQL Workbench and get the same error but also it's highlighted an "SQL syntax error near 'END'" so it's not just a bug in phpMyAdmin (though it could be a bug in both phpMyAdmin and MySQL Workbench).
我还安装了最新的 MySQL Workbench 并得到相同的错误，但它突出显示了“'END'附近的 SQL 语法错误”，因此它不仅仅是 phpMyAdmin 中的错误（尽管它可能是 phpMyAdmin 和 MySQL Workbench 中的错误）。
Here's the function query:
这是函数查询：
DELIMITER ;;
DROP FUNCTION IF EXISTS ADDRESS_BUILD;;
CREATE FUNCTION ADDRESS_BUILD(contact VARCHAR(50), company VARCHAR(100), add1 VARCHAR(255), add2 VARCHAR(255), add3 VARCHAR(255), town_city VARCHAR(50), county_state VARCHAR(50), postcode_zip VARCHAR(50), country VARCHAR(100), `separator` VARCHAR(10), type VARCHAR(10))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE address TEXT;
DECLARE line TEXT;
IF LENGTH(TRIM(contact))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(contact)); END IF;
IF LENGTH(TRIM(company))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(company)); END IF;
IF LENGTH(TRIM(add1))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add1)); END IF;
IF LENGTH(TRIM(add2))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add2)); END IF;
IF LENGTH(TRIM(add3))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add3)); END IF;
IF country='United States of America' OR country='USA' OR country='Canada' OR country='CA' THEN
/* NORTH AMERICA, ALL ON 1 LINE */
IF LENGTH(TRIM(town_city))>0 THEN
IF type='mail' THEN SET line=CONCAT_WS('', TRIM(town_city), ' ');
ELSE SET line=CONCAT_WS('', line, TRIM(town_city), ', ');
END IF;
END IF;
IF LENGTH(TRIM(county_state))>0 THEN
IF type='mail' THEN SET line=CONCAT_WS('', line, TRIM(county_state), ' ');
ELSE SET line=CONCAT_WS('', line, TRIM(county_state), ' ');
END IF;
END IF;
IF LENGTH(TRIM(postcode_zip))>0 THEN SET line=CONCAT_WS('', line, TRIM(postcode_zip)); END IF;
SET address=CONCAT_WS(`separator`, address, TRIM(line));
ELSE IF country='United Kingdom' OR country='UK' THEN
/* UK, ASCENDING LOCALITY SEPARATE LINES */
IF LENGTH(TRIM(town_city))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(town_city)); END IF;
IF LENGTH(TRIM(county_state))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(county_state)); END IF;
IF LENGTH(TRIM(postcode_zip))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(postcode_zip)); END IF;
ELSE
/* EUROPE EVERYWHERE ELSE, ALL ON 1 LINE POSTCODE FIRST */
IF LENGTH(TRIM(postcode_zip))>0 THEN SET line=CONCAT_WS('', line, TRIM(postcode_zip)); END IF;
IF LENGTH(TRIM(town_city))>0 THEN SET line=CONCAT_WS('', line, ' ', TRIM(town_city)); END IF;
IF LENGTH(TRIM(county_state))>0 THEN SET line=CONCAT_WS('', line, ' ', TRIM(county_state)); END IF;
IF LENGTH(TRIM(line))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(line)); END IF;
END IF;
IF country='United States of America' THEN SET address=CONCAT_WS(`separator`, address, 'USA');
ELSE IF LENGTH(TRIM(country))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(country));
END IF;
RETURN address;
END;;Line 51 is near the END IF, RETURN and END clauses but I can't spot anything wrong with that.
第 51 行靠近 END IF、RETURN 和 END 子句，但我找不到任何问题。
Can anyone see what's causing this problem in both MySQL Workbench and phpMyAdmin?
Once I've got the function stored, then I can test it out and tweak the logic.
任何人都可以在 MySQL Workbench 和 phpMyAdmin 中看到导致此问题的原因吗？
一旦我存储了函数，我就可以测试它并调整逻辑。
Also if there's any stuff in the function that could be streamlined then let me know. There's not many examples out there so I've patched this together somewhat.
另外，如果函数中有任何可以简化的内容，请告诉我。那里的例子并不多，所以我已经把它拼凑在一起了。
回答by batfastad
Ok I'm going to answer my own question. Thanks to Yahia I took another look at my IF statement syntax.
Turns out I screwed up the query!
好的，我要回答我自己的问题。感谢 Yahia，我重新审视了我的 IF 语句语法。
原来我搞砸了查询！
I've got 2 ELSE IFclauses above.
The proper syntax according to http://dev.mysql.com/doc/refman/5.0/en/if.htmlis actually ELSEIF
I just assumed that because of END IFthat it should also be ELSE IFwith a space, without actually making sure from the docs.
我ELSE IF上面有2个条款。
根据http://dev.mysql.com/doc/refman/5.0/en/if.html的正确语法实际上是ELSEIF
我只是假设因为END IF它也应该ELSE IF有一个空格，而实际上没有从文档中确定.
So MySQL was quite correctly interpreting the ELSE IF as an ELSE then the start of another nested IF, instead of another branch of the same level.
因此，MySQL 非常正确地将 ELSE IF 解释为 ELSE，然后是另一个嵌套 IF 的开始，而不是同一级别的另一个分支。
The above query works perfectly in phpMyAdmin once you correct the ELSE IFs but I've got a few tweaks to the logic to make.
一旦您更正了 ELSE IF，上面的查询就可以在 phpMyAdmin 中完美运行，但我对逻辑进行了一些调整。
So all my fault and RTM!
所以都是我的错和RTM！
回答by alditis
Other solution. Try with a function:
其他解决方案。尝试一个函数：
DROP FUNCTION IF EXISTS test;
DELIMITER $$
CREATE FUNCTION test(name VARCHAR(255), id INT) RETURNS INT
BEGIN
DECLARE var_resp INT DEFAULT 0;
IF (LENGTH(TRIM(name)) > 0) THEN
UPDATE mytableset IDName= name WHERE mytable.ID = id
SET var_resp = 1;
END IF;
RETURN var_resp;
END $$
DELIMITER ;
Regards.
问候。
回答by Yahia
you are missing an END IF;- change the last lines to:
您缺少一个END IF;- 将最后几行更改为：
IF country='United States of America' THEN SET address=CONCAT_WS(`separator`, address, 'USA');
ELSE IF LENGTH(TRIM(country))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(country));
END IF;
END IF;
RETURN address;
END;;
