在mysql中附加用逗号分隔的字符串时出现语法错误 [英] Syntax error while appending string separated by commas in mysql

问题描述

我有以下查询

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)

但是我遇到了错误

   {"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"}

'FL附近有什么语法错误?

What syntax error is there near 'FL?

不是该查询最多

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

工作完美.

推荐答案

我有一个字符串"SI2，VS1，SI1，VS2，VVS2，VVS1，IF，FL"我该怎么做

如果您具有CSV格式的值，则需要 FIND_IN_SET 进行搜索.

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

更改:

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

收件人:

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

而且，查询的其他部分似乎仍然存在相同的问题.
您需要在这些部分上应用相同的FIND_IN_SET.

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.

这篇关于在mysql中附加用逗号分隔的字符串时出现语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！