我创建了一个搜索查询来在多个字段中搜索表中的字符串。它适用于每个字符串，除非字符串仅包含一个或多个零，例如：%000%返回包含2个或更少零的行。
搜索字段：
`par_partno` varchar(20) utf8_general_ci
`par_group_id` varchar(16) utf8_bin
`par_desc` text utf8_general_ci
`par_details` text utf8_general_ci我已经试过了 par_partno 到char CAST(par_partno AS CHAR) 但结果是一样的。
查询：
select `par_id`, `pag_id`, `par_partno`, `par_group_id`,`par_cond`, `par_desc`,`par_price`,
`par_stock`, `par_feature`, `par_weight`, `par_details`, `par_related`, `par_meta`,
`par_picture`, `par_lastmod`
from `parts`
where concat(`par_partno`, `par_group_id`, `par_desc`, `par_details`) like "%000%"
order by `par_group_id` asc错误结果：
[
{"par_id":"145100","pag_id":"1","par_partno":"7101263500","par_group_id":"01-00-12","par_cond":"New","par_desc":"Pedal Assy W\/ Booster Mbc, Clutch Cylinder","par_price":"1450.00","par_stock":"1","par_feature":"0","par_weight":"0.1","par_details":"","par_related":null,"par_meta":null,"par_picture":"0","par_lastmod":"2019-01-04 18:14:12"},
{"par_id":"145106","pag_id":"2","par_partno":"7121051100","par_group_id":"01-01-102","par_cond":"New","par_desc":"Seal Intake Valve","par_price":"5.95","par_stock":"1","par_feature":"0","par_weight":"0.1","par_details":"","par_related":"[\"7121040061\"]","par_meta":null,"par_picture":"0","par_lastmod":"2019-01-04 18:14:12"},
{"par_id":"145169","pag_id":"2","par_partno":"7122015100","par_group_id":"01-01-91","par_cond":"New","par_desc":"Engine Gasket Set","par_price":"199.00","par_stock":"1","par_feature":"0","par_weight":"6.4","par_details":"","par_related":null,"par_meta":null,"par_picture":"0","par_lastmod":"2019-01-04 18:14:12"}
]
3条答案
按热度按时间dddzy1tm1#
robin参考答案的改进：
（太大/代码太多，无法评论）
只运行一个
LIKE查询，方法是将搜索的列合并为一个串联。此函数将所有选中的列合并为一个字符串，并用
-（不属于LIKE).这也会删除
LOWER()因为在处理数字时它是不需要的。ct2axkht2#
我可能误解了你的问题，但检查字符串不是一个串联的例如。
零件号：“7101263500”，“零件组id”：“01-00-12”
所以
710126350001-00-12
它包含一个“000”子串。
rdrgkggo3#
这些提示帮助我解决了这个问题。
我移除了concat并替换为：