douxun3496
2014-02-26 14:33
浏览 59
已采纳

在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 14: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.

    点赞 打赏 评论
  • douliu7929 2014-02-26 14:34

    Your string values must be in quotes:

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

    should be

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

    This applies to all of the strings in your query (i.e. check your other IN() statements)

    点赞 打赏 评论
  • duanqie5741 2014-02-26 14:46

    Hi Umer PLease use the below 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")
    

    If "SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL" is a single string the you dont need to use IN() you can just use LIKE operator..

    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 LIKE "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")
    

    What syntax error is there near 'FL? Answer is before FL there IF and IF is a keyword in mySQL. so when you will user quotes the it will remove the error..

    And if you have a string of "SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL" and you want to find it in individual elements then use 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 FIND_IN_SET(clarity,"SI2,VS1,SI1,VS2,VVS2,VVS1,IF,FL")  
      AND FIND_IN_SET(color,"J,H,D,E,F,G,I") 
      AND FIND_IN_SET(diamondsType,"BR,HS,CUS,RAD,AS,PRIN,OV,PS,MQ,EM")
    

    Feel free to ask question if you still dont understand..

    点赞 打赏 评论

相关推荐 更多相似问题