php - 在没有 Join 的情况下，在 3 个表中查找带有 field_in_set 的标签

Question

我有 3 个表:

1. link_art_a
2. link_art_b
3. link_art_c

我现在想在一侧搜索“car”和这个输出。

表格几乎相同，我需要的字段:

id, name , mod_name , picture , hits。

字段就在需要的地方，用逗号隔开，例如:

Car , truck , SUV , bike

Field: "tag"

我已经FIND_IN_SET 次尝试，但我总是得到数据库中的所有条目，而不是“car”。

到目前为止我尝试了什么:

SELECT id, name, mod_name, picture, hits from link_art_a 
    UNION ALL
    SELECT id, name, mod_name, picture, hits from link_art_b 
    UNION ALL
    SELECT id, name, mod_name, picture, hits from link_art_c 
    where   
    find_in_field('Car', tag) > 0 order by name asc

有谁知道我是如何得到我想要的结果的？

编辑:你好，

问题是我得到了所有记录，包括那些不包含所需单词(例如“汽车”)的记录。

MySQL 转储:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

CREATE TABLE `link_art_a` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `mod_name` varchar(200) NOT NULL,
  `picture` varchar(100) NOT NULL,
  `hits` bigint(20) NOT NULL,
  `tag` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `link_art_a` (`id`, `name`, `mod_name`, `picture`, `hits`, `tag`) VALUES
(1, 'A8', 'a8.html', 'default.jpg', 251, 'car,sports car,sport,fast'),
(2, 'VW Beetle', 'vw-beetle', 'default.jpg', 269, 'car,fun,slow');

CREATE TABLE `link_art_b` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `mod_name` varchar(200) NOT NULL,
  `picture` varchar(100) NOT NULL,
  `hits` bigint(20) NOT NULL,
  `tag` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `link_art_b` (`id`, `name`, `mod_name`, `picture`, `hits`, `tag`) VALUES
(1, 'Surfboard', 'surfboard', 'default.jpg', 142, 'fun,sport,water'),
(2, 'Sport boat', 'sport-boat', 'default.jog', 163, 'sport,fun,water,fast');

CREATE TABLE `link_art_c` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `mod_name` varchar(200) NOT NULL,
  `picture` varchar(100) NOT NULL,
  `hits` bigint(20) NOT NULL,
  `tag` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `link_art_c` (`id`, `name`, `mod_name`, `picture`, `hits`, `tag`) VALUES
(1, 'Houseboat', 'houseboat', 'default.jog', 144, 'house,boat,water'),
(2, 'Speedboat', 'speedboat', 'default.jpg', 142, 'water,boot,speed,fast');


ALTER TABLE `link_art_a` ADD PRIMARY KEY (`id`), ADD KEY `tag` (`tag`);
ALTER TABLE `link_art_b` ADD PRIMARY KEY (`id`),  ADD KEY `tag` (`tag`);
ALTER TABLE `link_art_c` ADD PRIMARY KEY (`id`),  ADD KEY `tag` (`tag`);


ALTER TABLE `link_art_a`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `link_art_b`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `link_art_c`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;COMMIT;

Answer 1

您的 where 子句仅适用于最后一个 select。

您必须将where 添加到所有select 语句

像这样:

SELECT id, name, mod_name, picture, hits from link_art_a
where   
find_in_set('Car', tag) > 0 

UNION ALL
SELECT id, name, mod_name, picture, hits from link_art_b 
where   
find_in_set('Car', tag) > 0 

UNION ALL
SELECT id, name, mod_name, picture, hits from link_art_c 
where   
find_in_set('Car', tag) > 0 

注意事项:

1. 在这种情况下，您必须删除 order by。
2. 它是 find_in_set() 而不是 find_in_field()

或者全部添加

SELECT id, name, mod_name, picture, hits from
(
 SELECT id, name, mod_name, picture, hits, tag from link_art_a 
 UNION ALL
 SELECT id, name, mod_name, picture, hits, tag from link_art_b 
 UNION ALL
 SELECT id, name, mod_name, picture, hits, tag from link_art_c 
) as t1
where   
find_in_set('Car', t1.tag) > 0 order by t1.name asc