我有3个表：
- 链接arta
- 链接art_b
- 链接art_c
我现在想在一侧搜索"汽车"和此输出。
表格几乎相同，我需要的字段：
id, name , mod_name , picture , hits.
字段位于需要的位置，用逗号分隔，例如：
Car , truck , SUV , bike
字段："标记"
我有FIND_IN_SET的尝试，但我总是在数据库中获得所有条目，而不是使用"汽车"。
到目前为止我尝试过的：
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
有人知道我是如何得到想要的结果的吗？
编辑：你好，
问题是，我得到了所有的记录，包括那些不包含所需单词（如"car"）的记录。
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;
您的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
注意：
- 在这种情况下，必须删除
order by - 是
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