MySQL动态行转列在自有表中失效，请求技术排查

问题概况 #

尝试将自有表行转列时触发1064语法错误，但参考的动态转列逻辑在测试表中可正常运行。本次仅使用tbl_members_contacts表（主键为MemberID+ContactTypeID组合外键）进行转置操作。

表结构与数据 #

-- phpMyAdmin SQL Dump
-- version 4.9.7
-- Host: localhost:3306
-- Generation Time: Nov 17, 2022 at 02:53 PM
-- Server version: 10.3.22-MariaDB
-- PHP Version: 7.4.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `sssorth_events2`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_members`
--

CREATE TABLE `tbl_members` (
  `MemberID` int(11) NOT NULL,
  `FirstName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LastName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `tbl_members`
--

INSERT INTO `tbl_members` (`MemberID`, `FirstName`, `LastName`) VALUES
(1, 'Olav', 'Jensen'),
(2, 'Thor', 'Hansen'),
(3, 'Henrik', 'Koppang');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_members_contacts`
--

CREATE TABLE `tbl_members_contacts` (
  `MemberID` int(11) NOT NULL,
  `ContactTypeID` int(11) NOT NULL,
  `ContactInfo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ContactPref` tinyint(4) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `tbl_members_contacts`
--

INSERT INTO `tbl_members_contacts` (`MemberID`, `ContactTypeID`, `ContactInfo`, `ContactPref`) VALUES
(1, 1, 'olav_jensen@gmail.com', 0),
(1, 3, '+66 1845 5556', 0),
(2, 1, 'thor5567@hotmail.com', 0),
(2, 3, '+66 0850 555 244', 0),
(3, 1, 'henrikkoppang@gmail.com', 0),
(3, 3, '+66927745546', 0);

-- --------------------------------------------------------

--
-- Table structure for table `tbl_members_contacts_type`
--

CREATE TABLE `tbl_members_contacts_type` (
  `ContactTypeID` int(11) NOT NULL,
  `ContactType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `tbl_members_contacts_type`
--

INSERT INTO `tbl_members_contacts_type` (`ContactTypeID`, `ContactType`) VALUES
(1, 'Email 1'),
(2, 'Email 2'),
(3, 'Phone 1'),
(4, 'Phone 2'),
(5, 'LINE'),
(6, 'WhatsAp'),
(7, 'WeChat'),
(8, 'Skype');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_members`
--
ALTER TABLE `tbl_members`
  ADD PRIMARY KEY (`MemberID`);

--
-- Indexes for table `tbl_members_contacts`
--
ALTER TABLE `tbl_members_contacts`
  ADD PRIMARY KEY (`MemberID`,`ContactTypeID`),
  ADD KEY `ContactTypeID` (`ContactInfo`),
  ADD KEY `ContactTypeID1` (`ContactTypeID`),
  ADD KEY `MemberID` (`MemberID`);

--
-- Indexes for table `tbl_members_contacts_type`
--
ALTER TABLE `tbl_members_contacts_type`
  ADD PRIMARY KEY (`ContactTypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_members`
--
ALTER TABLE `tbl_members`
  MODIFY `MemberID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=286;

--
-- AUTO_INCREMENT for table `tbl_members_contacts_type`
--
ALTER TABLE `tbl_members_contacts_type`
  MODIFY `ContactTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tbl_members_contacts`
--
ALTER TABLE `tbl_members_contacts`
  ADD CONSTRAINT `tbl_members_contact_typeTable1` FOREIGN KEY (`ContactTypeID`) REFERENCES `tbl_members_contacts_type` (`ContactTypeID`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

报错的转置SQL代码 #

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when ContactTypeID = ''',
      ContactTypeID,
      ''' then ContactInfo end) ',
      ContactTypeID
    )
  ) INTO @sql
FROM
  tbl_members_contacts;
SET @sql = CONCAT('SELECT MemberID, ', @sql, ' 
                  FROM tbl_members_contacts 
                   GROUP BY MemberID');

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

错误信息 #

#   Time        Action  Message Duration / Fetch
1   15:30:46    3   SET @sql = NULL 0 row(s) affected   0.000 sec
2   15:30:46    3   SELECT
   GROUP_CONCAT(DISTINCT
     CONCAT(
       'max(case when ContactTypeID = ''',
       ContactTypeID,
       ''' then ContactInfo end) ',
       ContactTypeID
     )
   ) INTO @sql
 FROM
   tbl_members_contacts 1 row(s) affected   0.000 sec
3   15:30:46    3   SET @sql = CONCAT('SELECT MemberID, ', @sql, ' 
                   FROM tbl_members_contacts 
                    GROUP BY MemberID') 0 row(s) affected   0.000 sec
4   15:30:46    0   PREPARE stmt FROM @sql  Error Code: 1064. 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 '1,max(case when ContactTypeID = '3' then ContactInfo end) 3 
                   F' at line 1 0.000 sec

问题原因与修复方案 #

原因分析 #

错误根源在于动态生成的列别名格式不合法：直接使用数字类型的ContactTypeID作为列别名，MySQL中以数字开头的标识符必须用反引号（`）包裹，否则会被解析为语法错误。

修复后的SQL代码 #

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when ContactTypeID = ''',
      ContactTypeID,
      ''' then ContactInfo end) `',
      ContactTypeID,
      '`'
    )
  ) INTO @sql
FROM
  tbl_members_contacts;
SET @sql = CONCAT('SELECT MemberID, ', @sql, ' 
                  FROM tbl_members_contacts 
                   GROUP BY MemberID');

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

关键修复点 #

在生成列别名的部分，给ContactTypeID添加反引号包裹：

'`', ContactTypeID, '`'

这确保MySQL将数字ID识别为合法的列名标识符，避免语法解析错误。

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