Mysql2创建VIEW时出现语法错误

Question

我刚刚在 Windows 10 上设置了 Bash，安装了 libmysqlclient-dev 软件包，并正在运行一个 rake 任务，该任务执行以下查询以使用 ActiveRecord::Base.connection.execute 在 mysql 数据库上创建 VIEW

DROP TABLE IF EXISTS debtors_customer_balances;
CREATE OR REPLACE VIEW debtors_customer_balances AS
  SELECT
    customer_id,
    SUM(amount_cents) AS total_cents,
    SUM(CASE
          WHEN due_on >= CURDATE()
          THEN amount_cents
          ELSE 0
          END) AS current_cents,
    SUM(CASE
          WHEN due_on >= (CURDATE() - INTERVAL 7 DAY)
          AND  due_on < CURDATE()
          THEN amount_cents
          ELSE 0
          END) AS overdue7_cents,
    SUM(CASE
          WHEN due_on >= (CURDATE() - INTERVAL 14 DAY)
          AND  due_on < (CURDATE() - INTERVAL 7 DAY)
          THEN amount_cents
          ELSE 0
          END) AS overdue14_cents,
    SUM(CASE
          WHEN due_on >= (CURDATE() - INTERVAL 30 DAY)
          AND  due_on < (CURDATE() - INTERVAL 14 DAY)
          THEN amount_cents
          ELSE 0
          END) AS overdue30_cents,
    SUM(CASE
          WHEN due_on < (CURDATE() - INTERVAL 30 DAY)
          THEN amount_cents
          ELSE 0
          END) AS overdue30_plus_cents
  FROM
    debtors_balances
  GROUP BY
    customer_id;

但是，它抛出一个错误

Mysql2::Error: 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 'CREATE OR REPLACE VIEW debtors_customer_balances AS SELECT ' at line 2: DROP TABLE IF EXISTS debtors_customer_balances; CREATE OR REPLACE VIEW debtors_customer_balances AS SELECT customer_id, etc...

我无法弄清楚是什么导致了这个问题，因为查询在 Mac 上运行良好，只是在 Windows 的 bash 中我似乎遇到了这个语法错误。

我使用的 gem 是 mysql2 (0.3.18)

Answer 1

这是由第一行 DROP TABLE IF EXISTS Debors_customer_balances; 引起的，实际上应该是 DROP VIEW ... 。删除第一行，它应该可以工作。无论如何，您正在使用 CREATE OR REPLACE VIEW Debors_customer_balances 那么之前添加 DROP 语句的含义是什么？