当字段有空格时 MySQL 准备好的语句不起作用

Question

我正在尝试创建以下准备好的语句，该语句将为数据透视表输出动态行。如果“名称”字段没有空格，我的语句就有效。但是，当我向其中添加空格(例如 Project Strength Site Coordinator)时，我收到以下错误:

"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 'Strength Site Coordinator,MAX(IF(Name = 'TeenEatsSiteCoordinator', Name, NULL)) ' at line 1: PREPARE stmt FROM @sql"

我尝试研究这个问题，但没有发现任何东西。

这是一个SQL Fiddle .

感谢您提供的任何帮助!

CREATE TABLE `Contacts` (
  `Contact_Id` int(11) unsigned NOT NULL,
  `First_Name` varchar(50) NOT NULL,
  `Last_Name` varchar(50) NOT NULL
);

insert into Contacts values (1, 'John', 'Doe');
insert into Contacts values (2, 'Jane', 'Doe');

CREATE TABLE `Contact_Types` (
  `Contact_Type_Id` int(11) unsigned NOT NULL,
  `Name` varchar(50) NOT NULL
);

insert into Contact_Types values (1, 'ProjectStrengthSiteCoordinator');
insert into Contact_Types values (2, 'TeenEatsSiteCoordinator');

CREATE TABLE `Contacts_Contact_Types` (
  `Contact_Id` int(11) unsigned NOT NULL DEFAULT '0',
  `Contact_Type_Id` int(11) unsigned NOT NULL DEFAULT '0'
);

insert into Contacts_Contact_Types values (1, 1);
insert into Contacts_Contact_Types values (1, 2);
insert into Contacts_Contact_Types values (2, 1);

SET @sql = NULL;

SELECT
 GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(Name = ''',
      Name,
      ''', Name, NULL)) AS ',
      Name
    )
  ) INTO @sql
from Contact_Types;

SET @sql = CONCAT('SELECT c.First_Name, ', @sql, ' from Contacts c
left join Contacts_Contact_Types cct
  on c.Contact_Id = cct.Contact_Id
left join Contact_Types ct
  on cct.Contact_Type_Id = ct.Contact_Type_Id
group by c.Contact_Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

您需要在 as 部分对其进行转义:

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(Name = ''', Name, ''', Name, NULL)) AS `', Name, '`'
                                   )
                   ) INTO @sql
from Contact_Types;

请注意，group_concat() 采用多个参数，因此严格来说，嵌套的 concat() 是不必要的。