Шаблон регулярного выражения не работает с регулярным выражением MySQL

У меня есть приведенное ниже регулярное выражение, которое отлично работает с тестером Regex (спасибо horcrux). Однако он не возвращает совпадений, когда я использую его с регулярным выражением MySQL.

select query from search s where s.query regexp '^((&|^)(serviceType=SALE|propertyType=HOUSE|city=1)){1,3}$'

Вышеупомянутое должно совпадать с нижним

serviceType=SALE&propertyType=HOUSE&city=1
propertyType=HOUSE&serviceType=SALE&city=1
city=1&propertyType=HOUSE&serviceType=SALE
city=1&serviceType=SALE&propertyType=HOUSE
serviceType=SALE&propertyType=HOUSE
serviceType=SALE

но не эти

serviceType=SALE&propertyType=HOUSE&city=2
propertyType=HOUSE&city=2&serviceType=SALE
city=2&propertyType=HOUSE&serviceType=SALE
serviceType=SALE&propertyType=FARM&city=1
serviceType=SALE&propertyType=UNIT
serviceType=RENTAL&propertyType=HOUSE
serviceType=RENTAL

mysql regex regexp-like
person zoro74    schedule 16.04.2019    source источник
comment
^(serviceType=SALE|propertyType=HOUSE|city=1)(&(serviceType=SALE|propertyType=HOUSE|city=1)){0,2}$ работает? В чем ошибка? Есть ли?   -  person Wiktor Stribiżew    schedule 16.04.2019
comment
У меня отлично работает: dbfiddle.uk/   -  person Nick    schedule 16.04.2019
comment
Спасибо за быстрый ответ. Он работает в тестере, но, к сожалению, в регулярном выражении MySQL нет совпадений.   -  person zoro74    schedule 16.04.2019
comment
Работает и с MySQL 5.7: db-fiddle.com/f/8CYKKJ2gs1EUQ57vo1Gmyu/0   -  person Nick    schedule 16.04.2019
comment
@zoro74 zoro74 обе ссылки, которые я разместил, используют MySQL...   -  person Nick    schedule 16.04.2019
comment
Спасибо @nick, теперь это действительно работает, когда я использовал запрос для вставки тестовых значений в таблицу. Первоначально я копировал/вставлял значения непосредственно в таблицу поиска, что, по-видимому, и вызвало проблему. Еще раз спасибо за быстрый ответ   -  person zoro74    schedule 16.04.2019
comment
@ zoro74 zoro74, возможно, вы случайно получили пробелы в значениях при тестировании. В любом случае, приятно слышать, что это работает.   -  person Nick    schedule 16.04.2019

Ответы (2)


arrow_upward
0
arrow_downward

Похоже, вы скорее хотите, чтобы строка соответствовала всем парам значений ключа, а не только некоторым, что совпадает с вашим текущим шаблоном.

Попробуйте ANDвыполнить REGEXP операций, по одной для каждой пары ключ-значение.

s.query REGEXP '(&|^)serviceType=SALE(&|$)'
        AND s.query REGEXP '(&|^)propertyType=HOUSE(&|$)'
        AND s.query REGEXP '(&|^)city=1(&|$)'
person sticky bit    schedule 16.04.2019

arrow_upward
0
arrow_downward

Сложный обходной путь без использования регулярных выражений, этот пост просто предназначен для того, чтобы показать, что это возможно.
Хитрость заключается в том, чтобы создать генератор чисел MySQL и использовать вложенные функции SUBSTRING_INDEX(), чтобы разрезать строки на токены.

Запрос

 SELECT 
   separated_key_values.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS separated_key_values
ORDER BY 
 separated_key_values.query ASC

Результат

| query                                      | property_key | property_value |
| ------------------------------------------ | ------------ | -------------- |
| city=1&propertyType=HOUSE&serviceType=SALE | city         | 1              |
| city=1&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=1&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | city         | 1              |
| city=1&serviceType=SALE&propertyType=HOUSE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | city         | 2              |
| city=2&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&city=2&serviceType=SALE | city         | 2              |
| propertyType=HOUSE&city=2&serviceType=SALE | serviceType  | SALE           |
| propertyType=HOUSE&city=2&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&serviceType=SALE&city=1 | city         | 1              |
| propertyType=HOUSE&serviceType=SALE&city=1 | serviceType  | SALE           |
| propertyType=HOUSE&serviceType=SALE&city=1 | propertyType | HOUSE          |
| serviceType=RENTAL                         | serviceType  | RENTAL         |
| serviceType=RENTAL&propertyType=HOUSE      | propertyType | HOUSE          |
| serviceType=RENTAL&propertyType=HOUSE      | serviceType  | RENTAL         |
| serviceType=SALE                           | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | propertyType | FARM           |
| serviceType=SALE&propertyType=FARM&city=1  | city         | 1              |
| serviceType=SALE&propertyType=HOUSE        | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE        | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=1 | city         | 1              |
| serviceType=SALE&propertyType=HOUSE&city=1 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=1 | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=2 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=2 | city         | 2              |
| serviceType=SALE&propertyType=HOUSE&city=2 | serviceType  | SALE           |
| serviceType=SALE&propertyType=UNIT         | propertyType | UNIT           |
| serviceType=SALE&propertyType=UNIT         | serviceType  | SALE           |

см. демонстрацию

После этого это так же просто, как добавление условной агрегации.

Запрос

SELECT 
  separated_key_values.query

, 

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) AS has_serviceType_SALE

, 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) AS has_propertyType_HOUSE

, 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )  AS has_City_1

, (

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )   

  ) AS has_mask 

, COUNT(*)

FROM (
SELECT 
   search_alias.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS search_alias
) AS separated_key_values
GROUP BY 
 separated_key_values.query
HAVING 
 has_mask = COUNT(*)

Результат

| query                                      | has_serviceType_SALE | has_propertyType_HOUSE | has_City_1 | has_mask | COUNT(*) |
| ------------------------------------------ | -------------------- | ---------------------- | ---------- | -------- | -------- |
| city=1&propertyType=HOUSE&serviceType=SALE | 1                    | 1                      | 1          | 3        | 3        |
| city=1&serviceType=SALE&propertyType=HOUSE | 1                    | 1                      | 1          | 3        | 3        |
| propertyType=HOUSE&serviceType=SALE&city=1 | 1                    | 1                      | 1          | 3        | 3        |
| serviceType=SALE                           | 1                    | 0                      | 0          | 1        | 1        |
| serviceType=SALE&propertyType=HOUSE        | 1                    | 1                      | 0          | 2        | 2        |
| serviceType=SALE&propertyType=HOUSE&city=1 | 1                    | 1                      | 1          | 3        | 3        |

см. демонстрацию

Вы также можете поместить вывод столбца в предложение HAVING, чтобы не выводить эти столбцы.
См. демонстрацию

Примечание
Это не будет масштабироваться на больших таблицах, скорее всего, запрос регулярного выражения также не будет масштабироваться, потому что индексы, скорее всего, нельзя использовать.

Обходной метод может состоять в том, чтобы использовать временную таблицу с правильным индексом и использовать первый запрос для предварительного заполнения и выполнения условного агрегирования для временной таблицы, которая индексируется.

person Raymond Nijland    schedule 16.04.2019