douxun3496 2014-02-26 06:33
浏览 71
已采纳

在mysql中附加逗号分隔的字符串时出现语法错误

I have following query

SELECT diamondsList.*, dealers.* FROM diamondsList JOIN dealers ON diamondsList.dealerId = dealers.id WHERE price >= :minPrice AND price <= :maxPrice AND carat >= :minCarat AND carat <= :maxCarat AND clarity IN (SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL) AND color IN (J,H,D,E,F,G,I) AND diamondsType IN (BR,HS,CUS,RAD,AS,PRIN,OV,PS,MQ,EM)

But I AM getting error

   {"error":"SQLSTATE[42000]: Syntax error or access violation: 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 'FL) AND color IN (J,H,D,E,F,G,I) AND diamondsType IN (BR,HS,CUS,RAD,AS,PRIN,OV,P' at line 1"}

What syntax error is there near 'FL?

Not that query upto

SELECT diamondsList.*, dealers.* FROM diamondsList JOIN dealers ON diamondsList.dealerId = dealers.id WHERE price >= :minPrice AND price <= :maxPrice AND carat >= :minCarat AND carat <= :maxCarat

works perfectly.

  • 写回答

3条回答 默认 最新

  • dongyou4411 2014-02-26 06:41
    关注

    I have a string "SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL" How would i do this

    If you have values in CSV format, then you need FIND_IN_SET to search in it.

    Change:

    clarity IN (SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL)
    

    To:

    find_in_set( clarity, 'SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL' )
    

    And it seems, the same issue persists in some other parts of your query.
    You need to apply the same FIND_IN_SET on those parts.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部