mysql - 通过比较来自两个不同表的字符串来填充字段

Question

我有两个表，一个包含关键字，另一个包含字符串值。我想添加一列，其中将插入对第一个表 ID 的引用。此插入值将基于第一行字符串的取值位置，并将检查它是否包含其他表中的任何关键字，如果发现字符串中包含关键字，则在此处插入该关键字的 ID (应该包含外键约束)。

我对如何实现这一目标感到困惑。应该是一个过程或触发器，因为这个字段应该在插入后填充。我正在尝试使用触发器，但无法计算出字符串函数。

我是 SQL 和学习的新手，所以尝试了 INSTR() 但它只需要 first 关键字。

还有哪种方式最好和最快，因为这两个表每个都包含大约 10000 个值。

一张 table 是体育用品

id - sport

其他表包含funlocater

id - countryid - tag - link - email - sportid

除了应该自动填充的 sportid 部分外，funlocater 中的数据是手动填充的。

例如。funlocater 中的一行包含

1 - 183 - skatebording competition at tawar mall - www.abcd.com/abcd?xyz - abcd@abcd.com - ???

如果funlocater的tag列的字符串中包含关键字skateboarding，现在sportid应该包含sports表中的sportid。

类似于 INSTR('skateboarding', funlocater.tag)但我想检查所有最匹配的值。我如何进行匹配工作？ INSTR() 完成这项工作但只取一个值。我想检查是否找到关键字，然后插入 sports.id 或检查下一个关键字。

Answer 1

首先我想说的是，你最好规范化你的数据库模式并将你的映射存储在一个表中，比如

CREATE TABLE funlocater_sports 
(
    funlocater_id INT NOT NULL, 
    sports_id INT NOT NULL,
    PRIMARY KEY pk_funlocater_sports(funlocater_id, sports_id),
    FOREIGN KEY fk_funlocater(funlocater_id) REFERENCES funlocater(id),
    FOREIGN KEY fk_sports(sports_id) REFERENCES sports(id)
);

不过，您可以通过这样的查询实现您的直接目标

UPDATE funlocater l JOIN
(
  SELECT f.id, GROUP_CONCAT(s.id) sport_ids
    FROM funlocater f JOIN sports s
      ON FIND_IN_SET(s.sport, REPLACE(f.tag, ' ', ',')) > 0
   GROUP BY f.id
) q ON l.id = q.id
   SET l.sportid = q.sport_ids

通过这种方式，您将从 funlocater 表的 sportid 列中的 sports 表中获取逗号分隔的运动 ID 列表。

这是 SQLFiddle 演示

如果你想使用触发器，那么最好在这样的函数中分解出公共(public)代码

CREATE FUNCTION tag_to_sportid(atag VARCHAR(512))
RETURNS VARCHAR(512) 
DETERMINISTIC
RETURN
  (
      SELECT GROUP_CONCAT(s.id)
        FROM (SELECT 1 id) f JOIN sports s
          ON FIND_IN_SET(s.sport, REPLACE(atag, ' ', ',')) > 0
       GROUP BY f.id
  );

然后触发

CREATE TRIGGER tg_funlocator_insert
BEFORE INSERT ON funlocater
FOR EACH ROW
  SET NEW.sportid = tag_to_sportid(NEW.tag);

CREATE TRIGGER tg_funlocator_update
BEFORE UPDATE ON funlocater
FOR EACH ROW
  SET NEW.sportid = tag_to_sportid(NEW.tag);

这是 SQLFiddle 演示