mySQL，查找和替换，在一个表中使用查找，在另一个表中替换

I currently have two tables, which aren't joined.

我目前有两个表,没有加入。

tbl_prod
cat_ids        prod_txt
1,2,3          some text

tbl_cat
category_id   cat_name
1             A
2             M
3             P

I need to perform a query which will update the contents of the 'cat_ids' column so the contents looks like this:

我需要执行一个查询,它将更新'cat_ids'列的内容,以便内容如下所示:

  tbl_prod
  cat_ids        prod_txt
  A,M,P          some text

Is this even possible in mySQL, or might I be better off creating a script to do this. Thanks

这在mySQL中是否可行,或者我最好创建一个脚本来执行此操作。谢谢

1 个解决方案

#1

Correct way:

Normalize your tables. Storing foreign keys as csv column is really bad idea.

规范化表格。将外键存储为csv列是非常糟糕的主意。

Workaround:

I don't have control of the output, so normalizing isn't really an option here

我无法控制输出,因此规范化在这里不是一个选择

SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids,
       MAX(prod_txt) AS prod_txt
FROM tbl_prod AS tp
JOIN tbl_cat AS tc
  ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
GROUP BY cat_ids;

SqlFiddleDemo

Output:

╔══════════╦═══════════╗
║ cat_ids  ║ prod_txt  ║
╠══════════╬═══════════╣
║ A,M,P    ║ some text ║
╚══════════╩═══════════╝

Update:

UPDATE tbl_prod t
join (SELECT GROUP_CONCAT(tc.cat_name ORDER BY tc.category_id) AS cat_ids_rep,
             MAX(tp.cat_ids) AS cat_ids 
     FROM tbl_prod AS tp
     JOIN tbl_cat AS tc
       ON FIND_IN_SET(tc.category_id, tp.cat_ids) > 0
      GROUP BY cat_ids
    ) AS sub
ON t.cat_ids = sub.cat_ids
SET t.cat_ids = sub.cat_ids_rep;

SqlFiddleDemo2

#1