MySQL报错1054：字段列表存在未知列'500001 '的排查求助

报错信息 #

Error Code: 1054. Unknown column '500001 ' in 'field list'

问题描述 #

我执行一段SQL查询时遇到了上面的错误，这很奇怪，因为我的字段列表里根本没声明500001，试了各种办法都没法运行，求帮忙解决，谢谢！

原查询代码 #

select usr.login, usr.name, usr.group,
usr.equity,LEVERAGE,
CASE WHEN usr.EQUITY < 10000 then 500
WHEN usr.EQUITY between 10001 and 50000 then 400
WHEN usr.EQUITY between 50001 and 100000 then 300
WHEN usr.EQUITY between 100001 and 250000 then 200
WHEN usr.EQUITY between 250001 and 500000 then 100
WHEN usr.EQUITY > 500001 then 50
ELSE 1
END AS MAXLEVARGE,
CASE WHEN usr.EQUITY between 10001 and 50000 and LEVERAGE > 400 then 'DROP TO 400'
WHEN usr.EQUITY between 50001 and 100000 and LEVERAGE > 300 then 'DROP TO 300'
WHEN usr.EQUITY between 100001 and 250000 and LEVERAGE > 200 then 'DROP TO 200'
WHEN usr.EQUITY between 250001 and 500000 and LEVERAGE > 100 then 'DROP TO 100'
WHEN usr.EQUITY > 500001  and LEVERAGE > 50 then 'DROP TO 50'
ELSE 'NORMAL'
END AS ACTION,
usr.agent_account,team
from aaa.USERS usr
inner join aaa.ibheirachytemp ib on ib.login=usr.AGENT_ACCOUNT
where usr.group not like '%p5%'
and usr.group not like '%test%'
and equity > 10000
group by usr.login;

问题原因 #

你第二个CASE语句的最后一个WHEN条件里，usr.EQUITY > 500001 中的500001后面跟着一个不可见的特殊空格字符（可能是全角空格或其他非标准空格），MySQL把这个带特殊空格的500001 识别成了一个列名，所以抛出了"未知列"的错误。

解决办法 #

删掉500001后面的那个特殊空格，换成普通半角空格（或者直接去掉空格，运算符>前后不需要空格也能正常运行）。

修正后的查询代码 #

select usr.login, usr.name, usr.group,
usr.equity,LEVERAGE,
CASE WHEN usr.EQUITY < 10000 then 500
WHEN usr.EQUITY between 10001 and 50000 then 400
WHEN usr.EQUITY between 50001 and 100000 then 300
WHEN usr.EQUITY between 100001 and 250000 then 200
WHEN usr.EQUITY between 250001 and 500000 then 100
WHEN usr.EQUITY > 500001 then 50
ELSE 1
END AS MAXLEVARGE,
CASE WHEN usr.EQUITY between 10001 and 50000 and LEVERAGE > 400 then 'DROP TO 400'
WHEN usr.EQUITY between 50001 and 100000 and LEVERAGE > 300 then 'DROP TO 300'
WHEN usr.EQUITY between 100001 and 250000 and LEVERAGE > 200 then 'DROP TO 200'
WHEN usr.EQUITY between 250001 and 500000 and LEVERAGE > 100 then 'DROP TO 100'
WHEN usr.EQUITY > 500001 and LEVERAGE > 50 then 'DROP TO 50'
ELSE 'NORMAL'
END AS ACTION,
usr.agent_account,team
from aaa.USERS usr
inner join aaa.ibheirachytemp ib on ib.login=usr.AGENT_ACCOUNT
where usr.group not like '%p5%'
and usr.group not like '%test%'
and equity > 10000
group by usr.login;

内容的提问来源于stack exchange，提问作者Hoàng Lê