Mysql 查询以获取逗号分隔的 ids 数据的详细信息

Question

我有 2 个表，项目和成员:

CREATE TABLE IF NOT EXISTS `items` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`member` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如果，例如我在 items 中有一条记录，例如

INSERT INTO  `test`.`items` (
`id` ,
`name` ,
`member`
)
VALUES (
NULL ,  'xxxx',  '1, 2, 3'
);

在成员中:

INSERT INTO `members` (`id`, `name`) VALUES
(1, 'asdf'),
(2, 'qwert'),
(3, 'uiop'),
(4, 'jkl;');

我想用 members.name 显示 items.member 数据，比如 1#asdf, 2#qwert, 3#uiop??

我尝试了以下查询，

SELECT items.id, items.name, GROUP_CONCAT(CONCAT_WS('#', members.id, members.name) ) as member

FROM `items` 
LEFT JOIN members AS members on (members.id = items.member)
WHERE items.id = 1

但是结果和我想象的不太一样。还有其他方法可以通过一次调用查询显示数据吗？因为我正在使用 PHP，所以现在，我正在展开 items.member 并逐一循环，以显示 members.name。

Answer 1

您可以考虑使用 FIND_IN_SET()在您的加入标准中:

FROM items JOIN members ON FIND_IN_SET(members.id, items.member)

但是，请注意 FIND_IN_SET() 的定义:

A string list is a string composed of substrings separated by “,” characters.

因此 items.member 列不应包含任何空格(我想您可以使用 FIND_IN_SET(members.id, REPLACE(items.member, ' ', '')) - 但随着数据库的增长，这将非常昂贵)。

真的，你应该 normalise你的架构:

CREATE TABLE memberItems (
  item_id   INT(5) NOT NULL,
  member_id INT(5) NOT NULL,
  FOREIGN KEY   item_id REFERENCES   items (id),
  FOREIGN KEY member_id REFERENCES members (id)
);

INSERT INTO memberItems
  (item_id, member_id)
SELECT items.id, members.id
FROM   items
  JOIN members ON FIND_IN_SET(members.id, REPLACE(items.member,' ',''))
;

ALTER TABLE items DROP member;

这既是索引友好的(因此可以非常有效地查询)，又让数据库强制执行参照完整性。然后你可以这样做:

FROM items JOIN memberItems ON memberItems.item_id = items.id
           JOIN members     ON members.id = memberItems.member_id

另请注意，使用 GROUP_CONCAT() 以这种方式将单独的记录组合成一个字符串通常是不明智的:您的应用程序应该准备好遍历结果集以获取每个成员。