MySql中使用查询在单词之间查找匹配

Question

我有一个连接到mysql数据库的Java程序。我想要做的是获取用户输入并显示与用户输入相匹配的列。该列包含编程语言的列表。我需要帮助的sql查询需要做这件事。例如，如果第一行中的列包含语言Javascript、C++、C#，而第二行中的列包含Java，并且如果我使用查询

"SELECT * from Language WHERE Programming Languages LIKE'%"+input.getText()+"%'")

如果用户要搜索Java，则该表将显示包含Java和Javascript的列，因为它包含单词Java。如果有第三行，并且该列包含C，并且用户要搜索C，那么它将显示包含C++和C#的列。

我尝试使用这个正则表达式。

`"SELECT * from Language WHERE Programming Language REGEXP'[[<:]]"+input.getText()+"[[:>:]]'")`

这解决了Java和Javascript的问题，但是搜索C会显示带有C#和C++的列。此外，搜索C#将不显示任何内容，搜索C++将导致此错误重复操作符操作数无效。My Table

表的列数据：

Javascript HTML CSS PHP
C# Java
C
C++ C#

在第一个查询中，我尝试在%前面放一个空格，这是用来区分的。我现在的问题是如何在一列中搜索多个单词，上面的查询按确切顺序对两个或三个输入起作用。搜索javascript和html可以显示，但不会显示javascript和css，因为它不是顺序的。

Answer 1

让我猜猜，您将所有开发人员存储到一个表中，其中一列存储该开发人员掌握的语言。

当有人输入一种语言时，你想搜索掌握这门语言的所有开发人员吗？

如果是这样的话，我会这样做，在您的语言列中，在语言列的末尾添加一个额外的空格(使用_作为下面的空格)

Javascript_HTML_CSS_PHP_
C#_Java_
C++_C#_
C_
Python_Java_LUA_

然后，在您的Java代码中，您可以使用以下代码来完成此操作。

SELECT * from Language WHERE Programming_Languages LIKE'%"+input.getText()+"_%'")

请注意，不要在Java代码中连接sql字符串。你可以这样做：

statment = con.prepare("SELECT * from Language WHERE Programming_Languages LIKE ?")
statment.setString(1, "%" + input.getText() + "_%")

Answer 2

这里的问题是您的数据库模型不是正确的normalized。在可能的情况下，建议不要在数据库的一列中存储多个值。相反，作为第一步，使用单独的数据库来存储编程语言，并使用唯一索引将值映射到人：

people

id    | name | other stuff
86511 | Bob  | ...         
86513 | TBA  | ...

。

languages

language | user_id
C#       | 86511
Java     | 86511
C++      | 86513
C#       | 86513

现在，您只需在表语言中搜索相应的语言，并使用JOIN子句将其映射到用户：

SELECT *
FROM languages
  INNER JOIN people ON languages.user_id = people.id
WHERE languages.language = 'C#';

但是，仍然有优化的空间:现在languages表中的每种编程语言都有多个条目。这需要更长的搜索时间，并且可能导致数据库不一致，例如，如果您意外地仅在同一语言的一个条目中有一个打字错误。为了避免这一切，我们可以引入第三个表，为每种语言只保存一个条目，并将第二个表重新构造为只有一个映射：

 people

id    | name | other stuff
86511 | Bob  | ...         
86513 | TBA  | ...

。

lang_mapping

lang_id | user_id
1       | 86511
2       | 86511
3       | 86513
1       | 86513   

。

languages

id | language
1  | C#
2  | Java
3  | C++

您仍然可以通过向查询引入另一个连接来查询表并将语言连接到usery：

SELECT *
FROM languages
  INNER JOIN lang_mapping ON lang_mapping.lang_id = language.id
  INNER JOIN people ON lang_mapping.user_id = people.id
WHERE languages.language = 'C#'

NOw每种语言只有一个条目，您可以轻松地搜索每种语言并获得所有映射的用户，并且您可以添加单个新的语言或映射，而无需修改用户表。

这是数据模型的规范化形式。

Answer 3

以下是执行查询的一种方法。这是从这个post派生的。

这需要一个临时表，其中的行数由最大标记数的数字填充(由空格分隔的语言字符串，例如，字符串"Javascript HTML CSS PHP“有4个标记)。

创建一个包含数字的临时表：

create temporary table temp_numbers as
    select 1 as num
    union all select 2 as num
    union all select 3 as num
    union all select 4 as num
    union all select 5 as num
;

包含编程语言字符串列的表：

create table test_langs (
    col1 varchar(10) NOT NULL,
    col2 varchar(50) NOT NULL
);

插入一些测试数据：

insert into test_langs values ('ID-1', 'Javascript HTML CSS PHP');
insert into test_langs values ('ID-2', 'C# Java');
insert into test_langs values ('ID-3', 'C');
insert into test_langs values ('ID-4', 'C# C++');
insert into test_langs values ('ID-5', 'Python Java LUA');

查询：

此select获取与输入字符串“C”的精确匹配；

select col1, token from (
    select col1, substring_index(
        substring_index(col2, ' ', num), 
        ' ', 
        -1
    ) as token
from test_langs
join temp_numbers
    on char_length(col2) - char_length(replace(col2, ' ', '')) >= num - 1
) as individual_progamming_languages
where token='C';

结果是：

+------+-------+
| col1 | token |
+------+-------+
| ID-3 | C     |
+------+-------+