在mysql引擎中授予mysql.proc选择权

Question

我已经对Maria DB进行了DB转储，并试图在mysql引擎上恢复它，但由于一些语法检查，它失败了。

以下过程失败，出现错误不确定如何更正查询

ERROR 1064 (42000) at line 23872: 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 'if not exists idx_company_reviews on company_reviews (review_to_company_id);

CREATE PROCEDURE `createCompanyReviews`()
begin
create temporary table if not exists company_reviews (
SELECT review_to_company_id,
ifnull(sum(rate) / count(rate), 0) as average,
count(rate)                        as amount
FROM (
SELECT
review_to_company_id,
ifnull(ceiling(avg(nullif(crc.rate_amount, 0))), cr.initial_rate_amount) AS rate
FROM T_COMPANY_REVIEW cr
LEFT JOIN T_COMPANY_REVIEW_CATEGORY crc ON cr.id = crc.company_review_id
WHERE cr.published = TRUE
GROUP BY cr.id
UNION ALL
SELECT review_to_company_id, rate_amount as rate
FROM T_COMPANY_REFERENCE
WHERE rate_amount IS NOT NULL
AND published = true
);

Answer 1

mysql模式中的表和视图是系统表，根本不应该被触及。

如果用户需要检索存储过程的信息/元数据，则应从INFORMATION_SCHEMA.ROUTINES中检索。访问INFORMATION_SCHEMA不需要特殊的GRANT。