MySQL搜索w /有序通配符，并提取他们的值 [英] MySQL Search w/ Ordered Wildcards, and Extracting Their Values

问题描述

是否可以进行通配符搜索，提取通配符的值并将其放入MySQL结果中？优选地，我也想标记通配符。继续阅读以查看我的意思。

Is it possible to do a wildcard search, and extract the values of the wildcards and put them into a MySQL result? Preferably, I'd also like to label the wildcards. Continue reading to see what I mean.

示例：
考虑具有以下结构的数据库表。

EXAMPLE: Consider a database table with the following structure.

+----+-------------------+
| id | content           |
+========================+
| 1  |  %1.%2.%3         |
+----+-------------------+
| 2  |  Hey %name.       |
+----+-------------------+

首先，请注意这只是一个例子。我不是在单个数据库字段中存储多个值。请参阅：存储在数据库列中的逗号分隔列表真的那么糟？

First, note that this is just an example. I am not storing multiple values in a single database field. See here: Is storing a comma separated list in a database column really that bad?

相反，我想知道是否可能在数据库中有通配符，然后比较与用户输入。换句话说，我想接受用户输入，如 2.bla。^ 7 或类似的，然后返回相应的行 - ％。％。然后我想采取这些通配符，并返回它们作为MySQL结果的一部分。下面是一个例子PHP mysql_fetch_assoc（）结果基于我正在寻找。结果显然不需要像这样布局，但这是我正在寻找的结果类型。

Instead, I want to know if it is possible to have wildcards in a database, and then compare that with user input. In other words, I would like to take user input, such as 2.bla.^7 or similar, and then return that respective row--%.%.%. I would then like to take those wildcards, and return them as part of the MySQL result. Below is an example PHP mysql_fetch_assoc() result based on what I'm looking for. The result obviously doesn't need to be laid out exactly like this, but this is the sort of result that I'm looking for.

示例：

/************************
 * Input:    2.bla.^7   *
 ************************/
Array
(
    [0] => Array
    (
        [id] => 1,
        [content] => %1.%2.%3
    ),

    [1] => Array
    (
        [1] => 2,
        [2] => bla,
        [3] => ^7
    )
)

/************************
 * Input:   Hey Matt.   *
 ************************/
Array
(
    [0] => Array
    (
        [id] => 2,
        [content] => Hey %1.
    ),

    [1] => Array
    (
        [name] => Matt
    )
)

$ b b

---

我知道我在寻找的东西可能相当复杂，或是专门针对我独特的情况。如果你能指出我的方向正确，我会非常感激！我甚至不知道这是否可能 - 如果不是，一个替代的解决方案将非常感谢！

---

I understand that what I'm looking for is likely fairly complicated, or specialized for my unique case. If you can point me in the right direction, I would hugely appreciate it! I don't even know if this is possible--if it's not, an alternative solution would be greatly appreciated! However, I want the database structure to stay as close to the same as possible.

非常感谢你！

推荐答案

确实没有现成的解决方案。这是我要做的：

Indeed there is no ready-to-go solution. Here is what I would do:

1）添加3个特殊列 like_pattern ， regexp_pattern 和占位符。在将数据加载到表中之前，或在BEFORE INSERT / UPDATE TRIGGER之前，它们的值从列 content 中计算得出。

1) Add 3 special columns like_pattern, regexp_pattern and placeholders. Their values are calculated from the column content either in PHP before loading data into the table, or in BEFORE INSERT/UPDATE TRIGGERs.

• like_pattern VARCHAR（N） - 用于逆向搜索的LIKE模式


• regexp_pattern VARCHAR（N） - 用于稍后在PHP中提取零件的正则表达式模式


• 占位符VARCHAR c> - 以逗号分隔的占位符名称列表



• like_pattern VARCHAR(N) - LIKE pattern for reverse search
• regexp_pattern VARCHAR(N) - Regexp pattern for extracting parts later in PHP
• placeholders VARCHAR(N) - Comma-separated list of placeholder names

以下是示例：

+----+-------------+--------------+--------------------+--------------+
| id | content     | like_pattern | regexp_pattern     | placeholders |
+====+=============+==============+====================+==============+
| 1  |  %1.%2.%3   | %.%.%        | ^(.*)\.(.*)\.(.*)$ | 1,2,3        |
+----+-------------+--------------+-+------------------+--------------+
| 2  |  Hey %name. | Hey %.       | ^Hey (.*)\.$       | name         |
+----+-------------+--------------+--------------------+--------------+

$ b b

2）对于使用 like_pattern 的给定字符串搜索记录：

2) For a given string search record using like_pattern:

SELECT * FROM patterns WHERE 'Hey Nathanael.' LIKE like_pattern;

请注意，简单索引无法加速此查询，因为我们执行反向搜索，

Be aware that simple index can not speed up this query because we perform a reverse search, so depending on how big your table is you probably will need a different solution.

3）匹配字符串'Hey Nathanael'，并返回 regexp_pattern 使用PHP的 preg_match（）或 ereg（）。

3) Match the string 'Hey Nathanael.' against returned regexp_pattern using PHP's preg_match() or ereg(). You will get placeholder values.

4）将它们与列占位符中的占位符名称组合。

4) Combine them with placeholder names from the column placeholders.

就是这样。

这篇关于MySQL搜索w /有序通配符，并提取他们的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！