在MySQL查询中使用Like和Concat

I'm loading text files into my db and trying to do some quick matching between a table that lists names of organizations, and a table that holds the text file and potential matches to those organizations.

我正在将文本文件加载到我的数据库中，并尝试在列出组织名称的表和包含文本文件的表以及与这些组织可能匹配的表之间进行快速匹配。

I load the file using LOAD INFILE CONCURRENT and don't have any problems with that.

我使用LOAD INFILE CONCURRENT加载文件，并没有任何问题。

The twist comes from the fact that the field I'm trying to match in the raw text table (occupationoraffiliation) has more than just organization names in it. So I'm trying to use LIKE with wildcards to match the strings.

扭曲来自于我在原始文本表（Occuporaffiliation）中尝试匹配的字段不仅仅包含组织名称。所以我试图使用LIKE和通配符来匹配字符串。

To match the text, I'm trying to use this query:

为了匹配文本，我正在尝试使用此查询：

UPDATE raw_faca JOIN orgs AS o
    ON raw_faca.org_id IS NULL AND raw_faca.occupationoraffiliation LIKE CONCAT('%',o.org_name,'%')
SET raw_faca.org_id = o.org_id;

I've also tried without CONCAT:

我也试过没有CONCAT：

UPDATE raw_faca JOIN orgs AS o
    ON raw_faca.org_id IS NULL AND raw_faca.occupationoraffiliation LIKE ('%' + o.org_name + '%')
SET raw_faca.org_id = o.org_id;

The raw_faca table has ~40,000 rows and the orgs table has ~ 20,000 rows. I have indexes on all the The query has been running for a couple of hours or so -- this seems like way too long for the operation. Is the comparison I'm trying to run just that inefficient or am I doing something spectacularly stupid here? I was hoping to avoid going line-by-line with an external php or python script.

raw_faca表有大约40,000行，orgs表有大约20,000行。我有所有的索引查询已经运行了几个小时左右 - 这似乎太长了操作。比较我试图运行效率低下还是我在做一些非常愚蠢的事情？我希望避免与外部php或python脚本一行一行。

In response to comments below about using Match . . . Against, I've tried the following query as well:

回应以下有关使用Match的评论。 。 。反对，我也尝试了以下查询：

UPDATE raw_faca JOIN orgs AS o ON raw_faca.org_id IS NULL AND MATCH(raw_faca.occupationoraffiliation) AGAINST (o.org_name IN NATURAL LANGUAGE MODE)
SET raw_faca.org_id = o.org_id; 

And it's giving me this error:

它给了我这个错误：

incorrect arguments to AGAINST

Any thoughts?

有什么想法吗？

1 个解决方案

#1

A LIKE clause with a leading wild card is not going to be able to take advantage of any indexes.

带有前导外卡的LIKE子句无法利用任何索引。