mysql - 查找 Mysql 存储过程中的错误。无法理解错误

Question

此存储过程可能存在什么问题？我无法理解这段代码中的语法错误。代码中的 sql 查询运行得很好，但是当我尝试将其编写为存储过程时，它会抛出语法错误。错误是:

[ERROR in query 1] PROCEDURE 1bot already exists

[ERROR in query 2] 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 'DELIMITER' at line 1

DELIMITER ;;
CREATE PROCEDURE `1bot`()

BEGIN
SELECT  
COUNT(distinct orders.customer_id) as Total_Customers, 
date_format(orders.created_at,'%M %Y') as month, 
COUNT(orders.`increment_id`) as Unique_single_orders, 
SUM(orders.`base_subtotal`) as total_rev,  
SUM(billing.`total` + 5) as COGS, (
SUM(orders.`base_subtotal`) - SUM(billing.total + 5)) as marketing_expense, 
(AVG(orders.`base_subtotal`) - AVG(billing.total + 5)) as avg_acquisition_cost

FROM `sales_flat_order` as orders

LEFT JOIN `sales_flat_order_item` as items on orders.`entity_id` = items.`order_id` 

LEFT JOIN `vingo_billing` as billing on orders.`increment_id` = billing.`order_number`

WHERE orders.`created_at` between '2016-09-01 00:00:00' and '2017-01-23'

AND items.sku LIKE "%1bot%"

AND orders.status != "closed"
and orders.shipping_invoiced != '15'
and billing.total > 0
and orders.`total_qty_ordered` < 2;

END ;;
DELIMITER ;

Answer 1

PROCEDURE 1bot already exists

这是不言自明的。显然您已经创建了一个名为 1bot 的过程。 MySQL 会给你一个错误，因为另一种方法是默默地用你定义的过程替换现有的过程，这可能是不同的。 MySQL 正在保护您免于破坏您可能需要的过程。

如果您想要破坏现有过程并用新代码替换它，则必须先删除它:

DROP PROCEDURE `1bot`;;
CREATE PROCEDURE `1bot`() ...

请注意，如果您尝试创建表并命名已存在的表，则会出现类似的错误。

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 'DELIMITER'

您应该知道DELIMITER语句只能在mysql命令行客户端中识别。目的是更改语句分隔符，通常为 ; 或 \G，否则您无法使用包含多个分号的主体定义存储过程或触发器-分隔语句。

而在大多数其他界面中，例如phpMyAdmin、MySQL Workbench 或在应用程序中运行 SQL 语句时，无论如何，您一次只能运行一个语句，因此不会出现歧义。因此，在这些接口(interface)中，DELIMITER 是不必要的，也是不允许的。