Re: [请益] mysql搜寻问题

楼主: weiclin (CC)   2016-03-29 14:24:50
※ 引述《villix (瓜子被蜀国的狗吃了)》之铭言:
: 不好意思请问一下,如果现在有两张table, table1有需要搜寻的字串,
: table2里面是有句子的字串,可以将table1的所有字串搜寻出来当作array
: 然后在table2里面搜寻有table1字串的句子吗?希望是可以将table1的所有
: 字串在table2里面做fulltext search 然后做count这样~~
大概是这样?
<?php
try {
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE 'keywords' ('keyword' TEXT);");
$pdo->exec("INSERT INTO 'keywords' ('keyword') VALUES ('google'),('bing'),('yahoo');");
$pdo->exec("CREATE TABLE 'messages' ('message' TEXT);");
$pdo->exec("INSERT INTO 'messages' ('message') VALUES ('how about google.com?'),('why not bing?'),('is yahoo good?');");
$pdo->exec("INSERT INTO 'messages' ('message') VALUES ('Captain America'),('Gundam Mark II'),('google AlphaGO');");
$result = $pdo->query("SELECT M.*, K.* FROM messages M
INNER JOIN (SELECT keyword FROM keywords) K
ON M.message LIKE '%' || K.keyword || '%'");
if ($result) {
foreach($result as $row) {
echo "found keyword [{$row['keyword']}] in message [{$row['message']}]\n";
}
}
$result = $pdo->query("SELECT COUNT(M.message) AS cnt, E.keyword AS keyword FROM messages M
INNER JOIN (SELECT keyword FROM keywords) E
ON M.message LIKE '%' || E.keyword || '%' GROUP BY E.keyword");
if ($result) {
foreach($result as $row) {
echo "the keyword [{$row['keyword']}] was founded in {$row['cnt']} messages\n";
}
}
} catch (PDOException $e) {
echo "Error!: {$e->getMessage()}\n";
}
执行结果:
$ php inner_join.php
found keyword [google] in message [how about google.com?]
found keyword [bing] in message [why not bing?]
found keyword [yahoo] in message [is yahoo good?]
found keyword [google] in message [google AlphaGO]
the keyword [bing] was founded in 1 messages
the keyword [google] was founded in 2 messages
the keyword [yahoo] was founded in 1 messages
作者: shadowjohn (转角遇到爱)   2016-03-30 00:57:00
写的不错
作者: liaosankai (低温烘焙)   2016-03-30 11:36:00
感谢,学到新 LIKE 写法
作者: villix (瓜子被蜀国的狗吃了)   2016-03-30 12:39:00
看起来很不错~感谢大大=w=
作者: shadowjohn (转角遇到爱)   2016-03-30 15:24:00
不过like的部分应该用prepare-s... like ?
楼主: weiclin (CC)   2016-03-30 16:09:00
我认为用不到 prepare, 也没办法使用 prepare
作者: MOONRAKER (㊣牛鹤鳗毛人)   2016-03-30 16:31:00
http://goo.gl/bn4p7q 这不就用了 作法相通http://goo.gl/bGSoz0 要pdo也有pdo的
楼主: weiclin (CC)   2016-03-30 16:40:00
你看看这篇,理由差不多 http://goo.gl/6c8VpA
作者: shadowjohn (转角遇到爱)   2016-03-31 09:34:00
嗯嗯,查sql自身字段可以不用用,也不需要用的:D
作者: GALINE (天真可爱CQD)   2016-04-01 08:57:00
这招有点帅,不过关键字或 message 的时候感觉 full tablescan 会有点苦。资料不大或是线下作业比较适合这样搞“关键字或 message 多的时候”如果是线上服务需要搜寻,建议还是弄个搜寻引擎来像是 Solr 或 Elasticsearch....

Links booklink

Contact Us: admin [ a t ] ucptt.com