dowjgrm6787 2015-12-13 18:07
浏览 307
已采纳

MYSQL搜索字段中的特定单词或数字,不包括包含这些单词或数字的单词或数字

I am searching welds.welder_id and welds.bal_welder_id which are lists of unique welder IDs separated by spaces by the users.

The record set looks like 99,199,99 w259,w259 259 5-a

99,199,259,5-a and w259 are unique welder id numbers

I cannot use the MYSQL INSTR() function by itself as a search for "99" will pull up records with "199"

Users on each project format their welder IDs a different way (000,a000,0aa) usually to match their customer's records.

I really want to avoid using PHP code for a number of reasons.

To select records with "w259" in the welder_id OR in the bal_welder_id columns, my query looks like this.

SELECT * FROM `welds` 
WHERE `omit`=0 
AND(    (`welder_id`='w259' OR `bal_welder_id`='w259')
     OR (`welder_id` LIKE 'w259 %' OR `bal_welder_id` LIKE 'w259 %')
     OR (`welder_id` LIKE '% w259' OR `bal_welder_id` LIKE '% w259')
     OR (INSTR(`welder_id`, ' w259 ') > 0 OR INSTR(`bal_welder_id`,' w259 ') > 0))
ORDER BY `date_welded` DESC 
LIMIT 100;

It works but it takes 0.0030 seconds with 1300 test records on my workstation's SSD.

The actual DB will have hundreds of thousands after a year or two.

Is there a better way?

Thanks.

  • 写回答

1条回答 默认 最新

  • duanji1482 2015-12-13 19:15
    关注

    If I understand your question correctly, one option is to use FIND_IN_SET(str, strlist) string function, which returns the position of the string str in the comma separated string list strlist, for example:

    SELECT FIND_IN_SET('b','a,b,c,d');
    

    will return 2. Since your string is not separated by commas, but by spaces, you could use REPLACE() to replace spaces with commas. Your query can be like this:

    SELECT * FROM `welds` 
    WHERE
      `omit`=0
      AND
      (FIND_IN_SET('w259', REPLACE(welder_id, ' ', ','))>0
      OR
      FIND_IN_SET('w259', REPLACE(bal_welder_id, ' ', ','))>0)
    

    The optimizer however cannot to much, since FIND_IN_SET cannot make use of an index, if present. I would suggest you to normalize your table, if it is possible.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿