php - MySQL 使用 LIKE 匹配基于另一个表填充字段

Question

我知道这不是迄今为止最干净的代码，但我不明白为什么我不能让它工作。

我希望使用字段 c.ClientNumber 中的客户编号填充字段 m.customersTemp。但仅当找到来自 c.EmailAddress 的 LIKE 匹配时 m.Emails... m.Emails 是一个包含电子邮件列表的字段。代码如下。

UPDATE market m, customer c
SET m.customersTemp = CONCAT(m.customersTemp, c.ClientNumber)
WHERE m.Emails LIKE CONCAT('%', TRIM(c.EMailAddress), '%')
AND TRIM(c.EMailAddress)<>''

字段 m.customersTemp 中的结果只显示一个值(客户编号)...而且我知道有很多匹配项。

TABLE CUSTOMER
ClientNumber    | EMailAddress
1234              a@a.com
4567              b@b.com
2222              
1111              d@d.com

------------------------------------------------------------- 
|                        TABLE MARKET                       |
-------------------------------------------------------------
| ID            | Emails                     | customersTemp|
-------------------------------------------------------------
|1              | a@a.com, b@b.com, c@c.com  |              |
|2              | a@a.com, b@b.com, g@g.com  |              |
|3              | e@e.com                    |              |
|4              | f@f.com                    |              |
-------------------------------------------------------------

ID 1 和 2 处的 customersTemp 结果仅为 1 个 ClientNumber。 4567

Answer 1

不要忘记阅读底部的警告，了解为什么永远不要这样保存数据。

您可以在备份副本上进行测试。我不会针对您的主表运行它。类似于有人说:“在这里，试试这个删除命令，我认为它会起作用。”

-- drop table customer;
create table customer
(   ClientNumber int,
    EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'john@john.com'),
(4567,'joe@joe.com'),
(2222,''), 
(1111,'somone@someone.com'),
(5454,'john@john.com');

-- drop table market;
create table market
(   Emails varchar(100),
    customersTemp varchar(100)
);
insert market(Emails,customersTemp) values
('john@john.com',''),
('joe@joe.com',''),
('test@test.com',''),
('more@more.com','');

更新语句:

UPDATE market
INNER JOIN 
(   SELECT c.EMailAddress as e,GROUP_CONCAT(c.ClientNumber ORDER BY c.ClientNumber) theList
    FROM customer c
    GROUP BY c.EMailAddress
) xDerived1
ON market.EMails = xDerived1.e
SET market.customersTemp = xDerived1.theList;

结果:

select * from market;
+---------------+---------------+
| Emails        | customersTemp |
+---------------+---------------+
| john@john.com | 1234,5454     |
| joe@joe.com   | 4567          |
| test@test.com |               |
| more@more.com |               |
+---------------+---------------+

版本 2

drop table customer;
create table customer
(   ClientNumber int,
    EMailAddress varchar(100)
);
insert customer (ClientNumber,EMailAddress) values
(1234,'a@a.com'),
(4567,'b@b.com'),
(2222,''), 
(1111,'d@d.com'),
(8484,'g@g.com');
-- select * from customer;

drop table market;
create table market
(   id int auto_increment primary key,
    Emails varchar(100),
    customersTemp varchar(3000)
);
insert market(Emails,customersTemp) values
('a@a.com,b@b.com,c@c.com',''),
('a@a.com,b@b.com,g@g.com',''),
('e@e.com',''),
('f@f.com','');
-- select * from market;

drop table if exists marketHelper7;
create table marketHelper7
(   -- btw this might be the kind of table
    -- as an intersect/junction table that you
    -- should have to begin with
    -- and not have your CSV stuff 
    cid int not null,
    mid int not null
);

insert marketHelper7 (cid,mid)
select c.ClientNumber,m.id as MarketId
from customer c
join market m
on find_in_set(c.EMailAddress,m.Emails)>0;

update market set customersTemp=''; -- do a reset

UPDATE market m
join 
(   SELECT mh.mid as i,GROUP_CONCAT(mh.cid ORDER BY mh.cid) theList
    FROM marketHelper7 mh
    GROUP BY mh.mid
) xDerived1
ON m.id = xDerived1.i
SET m.customersTemp = xDerived1.theList;

drop table marketHelper7;

.

select * from market;
+----+-------------------------+----------------+
| id | Emails                  | customersTemp  |
+----+-------------------------+----------------+
|  1 | a@a.com,b@b.com,c@c.com | 1234,4567      |
|  2 | a@a.com,b@b.com,g@g.com | 1234,4567,8484 |
|  3 | e@e.com                 |                |
|  4 | f@f.com                 |                |
+----+-------------------------+----------------+

上面的版本 2 有帮助表。

警告:顺便说一句，永远不要这样保存你的数据。这太疯狂了，表现也很糟糕。请在此处查看我在 Junction Tables(多对多)上的回答(类似于关联表或“项目有”表 a.k.a. 一对多)。它们都是在查询期间利用数据规范化最佳实践和快速索引的相同概念。此外，您会更快乐，而不是不断地与您的数据作斗争，或者想知道您是否使用 group_concat 破坏了缓冲区大小。

请注意，group_concat() 可以灵活地选择分隔符，并在函数调用中嵌入排序依据。

group_concat 输出的最大长度取决于系统变量 group_concat_max_len，它可能默认为 1K，但可以设置为至少 4GB。

group_concat() 上的 Percona article，以及 group_concat() 和 find_in_set() 的手册页。