我正在使用以下查询拆分列值。它适用于不区分大小写的场景,但我希望它适用于区分大小写的场景。
例如,在字符串“Oil is a product angites are”中,如果我的搜索关键字是“angites”,则返回false,如果搜索关键字是“angites”,则只返回true。mysql中有什么函数允许这样做吗?

SELECT
  SUBSTRING_INDEX(description, 'Ingredients', 1),
    if(LOCATE('Ingredients', description)>0, SUBSTRING_INDEX(description, 'Ingredients', -1), '')
FROM `product`

最佳答案

从mysql的LOCATE函数的文档中:
此函数是多字节安全的,并且只有在
至少有一个参数是二进制字符串。
也就是说,您需要cast/convert参数来执行区分大小写的匹配。
例如:如果第一条记录是Oil is a product Ingredients are...,而第二条记录是Oil is a product ingredients are...,则以下查询:

SELECT
  LOCATE('ingredients', description) AS match_both_1,
  LOCATE('Ingredients', description) AS match_both_2,
  LOCATE(CAST('ingredients' AS BINARY), CAST(description AS BINARY)) AS match_second,
  LOCATE(CAST('Ingredients' AS BINARY), CAST(description AS BINARY)) AS match_first
FROM product

会给你预期的结果:
| match_both_1 | match_both_2 | match_second | match_first |
|     18       |     18       |      0       |     18      |
|     18       |     18       |     18       |      0      |

DEMO

10-07 17:42