MYSQL查询成jdbc准备语句，错误: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

Question

我有以下正在工作的MYSQL查询(它使两个表上有一个枢轴连接)：

SET @sql = NULL;
SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = 2 AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

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

我想将这个查询插入到java准备语句中。如果我只是复制它，我将得到一个格式错误的sql语法错误：

st = connection.prepareStatement(
              "SET @sql = NULL; "
            + "SELECT "
            + "GROUP_CONCAT(DISTINCT "
            + "CONCAT("
            + "'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')"
            + ") INTO @sql "
            + "FROM search_keywords sk "
            + "WHERE sk.product_id = ? AND sk.active = ?; "
            + "SET @sql = CONCAT('SELECT eval_date, ', @sql, ' "
            + "FROM keyword_ranking AS kr  "
            + "LEFT JOIN  search_keywords AS sk  "
            + "ON kr.search_keywords_id = sk.id  "
            + "GROUP BY eval_date'); "
            + "PREPARE stmt FROM @sql; "
            + "EXECUTE stmt; "
            + "DEALLOCATE PREPARE stmt; "
        );

错误：

错误com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：：您的SQL语法有错误；请检查与MySQL服务器版本对应的手册，以获得在“SELECT GROUP_CONCAT”附近使用的正确语法(第1行为“sk.keywords=CONCAT(‘，sk.keyword)”。

Answer 1

谢谢你斯坦和希尔科。我相信你们俩都是对的。我首先尝试了这些事务，但在那里我也得到了这个错误，所以要么我处理事务错误，要么我仍然不得不以某种方式摆脱单引号。当我尝试了许多不同的逃避方式时，我想尝试一下存储过程。这个管用！

对于有兴趣创建存储过程的其他人，我将粘贴我的代码如下：

在Mysql中存储过程：

CREATE DEFINER=`gobiran`@`%` PROCEDURE `getKeywordHistoryByProductId`(IN PRODUCT_ID INT(11))
BEGIN

SET @sql = NULL;

SELECT  
 GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(sk.keywords = ''', sk.keywords,''', kr.rank, NULL)) AS ',  CONCAT('`',sk.keywords, '`')
    )
) INTO @sql
 FROM 
     search_keywords sk
    WHERE sk.product_id = PRODUCT_ID AND sk.active = 'Y'; 

SET @sql = CONCAT('SELECT eval_date, ', @sql, '  
                   FROM keyword_ranking AS kr 
                   LEFT JOIN  search_keywords AS sk 
                    ON kr.search_keywords_id = sk.id 
                   GROUP BY eval_date');

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

END

调用存储过程：

ResultSet rs = null;
        Connection connection = DataBaseHelper.getConnection();
        CallableStatement stmt = null;
        try {

            stmt = connection.prepareCall("{ call getKeywordHistoryByProductId(?) }");
            stmt.setInt(1, productId);
            rs = stmt.executeQuery();

// Thats it, now work with the Resultset ... 
            ResultSetMetaData md = rs.getMetaData();

            int columns = md.getColumnCount();
            List<Object[]> result = new ArrayList<Object[]>();
            String[] header = new String[columns];
            int index = 0;
            while (rs.next()){
                Object[] entry = new Object[columns];
                for (int i = 0; i < columns; i++) {
                    if(index == 0) {
                        header[i] = md.getColumnName(i+1);
                    }
                    entry[i] = rs.getObject(i+1);
                }
                if(index == 0) {
                    result.add(header);
                }
                result.add(entry);
                index++;                
            }

..。只需格式:最后完成try / catch / finnaly子句并关闭stmt、connection、rs。