php - 如何查找给定句子中存在的列值？ mysql

Question

考虑下面的 mysql 表

id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          John

并考虑输入句子

Mark and Paul are very good friends since 2000.

预期输出

Mark , Paul

我想找到输入句子中出现的名字。mysql 是否提供任何选项来查找此内容。或者有什么想法吗？

Answer 1

这可以通过两种方式实现。使用mysql的'IN'和'REGEX'运算符。

通过使用“IN”运算符

<?php

$string = "Mark and Paul are very good friends since 2000.";
$sql_in = "'".implode("','",explode(" ",$string))."'";

//Will return all the records for which name match with any word in given sentence.
$mysql_query = "select * from table_name where name in($sql_in)";

?>

通过使用“REGEX”

<?php

$string = "Mark and Paul are very good friends since 2000.";
$regex_string = str_replace(' ','|',$string);

//Will return all the records for which name match with any word in given sentence.
$mysql_query = "select * from table_name where name REGEX '^(".$regex_string.")'";

?>