MYSQL实现平台的全局搜索

前言：

项目要求首页搜索框可以输入关键词直接搜索平台中信息列表、问题列表的信息。大家想的是直接对表like标题名返回列表就好了啊！

但是现在有这么个情况，信息列表和问题列表是两个单独的数据库表,肯定是要用union操作，可是有部分字段不相同，如下：

wejoy_new

 wejoy_questions

可以看到分类id的字段名不同，这个时候就会很尴尬，补空字段？NO! 我不想这样做！

解决方案 

 sql:

select title, content, cid, null as qid from wejoy_news where title like '%测试%'
union
select title, content, null as cid, qid from wejoy_questions where title like '%测试%';

查询时我们可以弄null来补齐，结果如下：

 这样就可以解决这个问题了！

什么？这样返回的列表还得判断来自哪个表用于前端判断？

输出时加个字段就好啦！

select title, content, cid, null as qid, 'wejoy_news' as source from wejoy_news where title like '%测试%'
union
select title, content, null as cid, qid, 'wejoy_questions' as source from wejoy_questions where title like '%测试%';

 thinkphp5查询代码

Db::table('wejoy_news')
->field('id,title,content,cid as cid,null as qid,\'news\' as source')
->where('title','like','%关键词%')
->union(function($query){
    $query->table('wejoy_questions')
    ->field('id,title,content,null as cid,qid as qid,\'questions\' as source')
    ->where('title','like','%关键词%');
})
->select();

$articles = new Articles();
$questions = new Questions();

$articles->field('id,title,content,cid as cid,null as qid,\'articles\' as source')
->where('title','like','%关键词%')
->union(function($query){
    $query->table('questions')
    ->field('id,title,content,null as cid,qid as qid,\'questions\' as source')
    ->where('title','like','%关键词%');
})
->select();