如何正确创建包含CONCAT函数的SQL视图且避免MySQL语法报错

问题描述 #

我不清楚问题出在哪里，已经尝试了多种方案，包括多次调整表顺序、修改CONCAT后的引号，以及在语句末尾添加ORDER BY employee_id，但仍未解决问题。

报错SQL代码 #

CREATE TABLE SUPPLIER (
  supplier_id INT,
  company_name VARCHAR(50),
  country VARCHAR(30),
  sales_contact_name VARCHAR(60),
  email VARCHAR(50),
  PRIMARY KEY (supplier_id))
  ;

CREATE TABLE COFFEE (
  coffee_id INT,
  shop_id INT,
  supplier_id INT, 
  coffee_name VARCHAR(30),
  price_per_pound NUMERIC(5,2),
  PRIMARY KEY (coffee_id))
  ;


CREATE TABLE COFFEE_SHOP (
  shop_id INT,
  shop_name VARCHAR(50),
  city VARCHAR(50),
  state CHAR(2),
  PRIMARY KEY (shop_id))
;

  

CREATE TABLE EMPLOYEE (
  employee_id INT,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hire_date DATE,
  job_title VARCHAR(30),
  shop_id INT,
  PRIMARY KEY (employee_id))
  ;

  

  
   ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_shop_id  FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id);

 ALTER TABLE COFFEE ADD FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id);

  ALTER TABLE COFFEE ADD CONSTRAINT fk_supplier_id  FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id);

  
CREATE VIEW mocha AS 
SELECT CONCAT(first_name, ' ', last_name) AS employee_full_name
employee_id
hire_date
job_title
shop_id
FROM EMPLOYEE
;

报错信息 #

您的SQL语法存在错误，请查阅对应MySQL服务器版本的官方手册，查找第3行'employee_id
hire_date
job_title
shop_id
FROM EMPLOYEE'附近的正确语法规则

问题原因与解决方案 #

错误出现在CREATE VIEW mocha的SELECT查询部分：多个查询字段之间缺少逗号分隔，SQL解析器无法识别换行作为字段分隔符，因此触发语法报错。

修正后的视图创建语句如下：

CREATE VIEW mocha AS 
SELECT 
  CONCAT(first_name, ' ', last_name) AS employee_full_name,
  employee_id,
  hire_date,
  job_title,
  shop_id
FROM EMPLOYEE;

如果需要按员工ID排序，在FROM EMPLOYEE后补充ORDER BY employee_id即可。

内容的提问来源于stack exchange，提问作者munchrr