mysql - 如何用另一个表中的匹配值替换/更新列中每个字符串的所有实例？

Question

在逗号分隔的字符串中存储数据不取决于我，这不是我可以在我的数据库中更改的东西，所以请耐心等待。我已经在网上和 stackoverflow 上做了很多搜索，但我找不到解决这个问题的方法，如果使用 MySQL 甚至可能的话。

我正在尝试用表 2 中的匹配值替换表 1 中每个唯一字符串的所有实例。我已经尝试过通配符、替换、更新、加入等，但我只是不确定如何让它发挥作用。我知道一种解决方案是对每个字符串使用 replace()，但 table2 有超过 200 行，因此这意味着嵌套超过 200 次。

这就是我想要完成的。我有两个表，表 1:

+------+-------------+
| Item | Code        |
+------+-------------+
| 1    | 614         |
+------+-------------+
| 2    | 212,614,415 |
+------+-------------+
| 3    | 212,303     |
+------+-------------+
| ...  | ...         |
+------+-------------+

和表2:

+------+-------------------+
| Code | Name              |
+------+-------------------+
| 614  | Columbus, OH      |
+------+-------------------+
| 212  | New York, NY      |
+------+-------------------+
| 415  | San Francisco, CA |
+------+-------------------+
| 303  | Ft. Worth, TX     |
+------+-------------------+
| ...  | ...               |
+------+-------------------+

我想用表 2 中的相应值替换表 1 中的代码以产生此结果:

+------+---------------------------------------------+
| Item | Code                                        |
+------+---------------------------------------------+
| 1    | Columbus, OH                                |
+------+---------------------------------------------+
| 2    | New York, NY,Columbus, OH,San Francisco, CA |
+------+---------------------------------------------+
| 3    | New York, NY,Ft. Worth, TX                  |
+------+---------------------------------------------+
| ...  | ...                                         |
+------+---------------------------------------------+

Answer 1

应该这样做(请参阅下面的最后一个查询)。我在连接中包含了逗号，这样 id 和 12 之类的东西与你拥有的 id 和 212 不匹配(例如)。

drop table if exists table1;

drop table if exists table2;

create table table1(
    item int,
    code varchar(64)
);

create table table2(
    code int,
    name varchar(64)
);

insert into table1 values (1, '614');
insert into table1 values (2, '212,614,415');
insert into table1 values (3, '212,303');

insert into table2 values(212, 'New York, NY');
insert into table2 values(303, 'Ft. Worth, TX');
insert into table2 values(415, 'San Francisco, CA');
insert into table2 values(614, 'Columbus, OH');

select * from table1

+ --------- + --------- +
| item      | code      |
+ --------- + --------- +
| 1         | 614       |
| 2         | 212,614,415 |
| 3         | 212,303   |
+ --------- + --------- +
3 rows

select * from table2

+ --------- + --------- +
| code      | name      |
+ --------- + --------- +
| 212       | New York, NY |
| 303       | Ft. Worth, TX |
| 415       | San Francisco, CA |
| 614       | Columbus, OH |
+ --------- + --------- +
4 rows

select 
    t1.item,
    t2.name
from
    table1 t1 join table2 t2 on (
        t1.code = t2.code
        or t1.code like concat(t2.code, ',%')
        or t1.code like concat('%,', t2.code, ',%')
        or t1.code like concat('%,', t2.code)
    )
order by t1.item

+ --------- + --------- +
| item      | name      |
+ --------- + --------- +
| 1         | Columbus, OH |
| 2         | Columbus, OH |
| 2         | New York, NY |
| 2         | San Francisco, CA |
| 3         | Ft. Worth, TX |
| 3         | New York, NY |
+ --------- + --------- +
6 rows

编辑:或者如果你想像这样保持数据非规范化:

select 
    t1.item,
    group_concat(t2.name)
from
    table1 t1 join table2 t2 on (
        t1.code = t2.code
        or t1.code like concat(t2.code, ',%')
        or t1.code like concat('%,', t2.code, ',%')
        or t1.code like concat('%,', t2.code)
    )
group by t1.item
order by t1.item

+ --------- + -------------------------- +
| item      | group_concat(t2.name)      |
+ --------- + -------------------------- +
| 1         | Columbus, OH               |
| 2         | Columbus, OH,New York, NY,San Francisco, CA |
| 3         | Ft. Worth, TX,New York, NY |
+ --------- + -------------------------- +
3 rows