mysql:查询中的未知列错误

Question

我正在将此查询写入 shell 脚本，因此所有双引号都被转义并且时间戳是变量。

如果我不在终端上使用“order by”，这个查询就有效。

还有一件奇怪的事情，如果在 PHPMYADMIN 中执行查询部分，那么 “order by” 会起作用。但是如果我在终端中运行这个查询，它会给我 UNKNOWN COLUMN QID error

SELECT 'GUIDE-NAME','QUESTION-ID','ACTION','TIMESTAMPS' 
UNION 
SELECT 
SUBSTRING(B.msg,LOCATE('q_id',B.msg)+5, (LOCATE('\"',B.msg)+2) ) AS qid, B.ts 
FROM TABLE B, TABLE A 
WHERE 
       LOCATE('q_id',B.msg) > 0 
       AND A.g_id=B.g_id and B.ts > from_unixtime($DateWeekBefore) 
       AND B.action 
       IN(\"review_question\",\"send_back",\"send_review\",\"publish\",\"quiet_publish\")  
ORDER BY qid 
INTO OUTFILE '/tmp/$vr9$DateWeek.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n'

Answer 1

MySQL 尝试将 order by 应用于所有查询，而不是子选择。为了分隔子选择，请使用括号:

(select ....) union (select.... order by something)

好像INTO OUTFILE不能直接用在union中，所以需要做一个union作为子查询:

SELECT * FROM ( 
    (
        SELECT 'GUIDE-NAME','QUESTION-ID','ACTION','TIMESTAMPS'
    ) UNION (
        SELECT.... ORDER BY qid
    ) 
) a 
INTO OUTFILE ....