Использование MYSQL CONCAT и SUBSTRING с URL-адресами http и https в базе данных

Если все ваши ссылки, которые нужно изменить, содержат подстроку .php?, вы можете сделать это:

UPDATE content_temp SET link = SUBSTRING_INDEX(link, '.php?', -1)
      WHERE link LIKE '%.php?%'

Совет от профессионала: Всегда полезно помещать предложение WHERE в операцию UPDATE: без него SQL обновляет все строки в таблице. Это может привести к загрязнению таблицы и затоплению журнала транзакций.

Похоже, у вас есть всего четыре возможных комбинации, которые мы рассматриваем. Первым может быть http или https, а вторым - http или https. Четыре возможных комбинации:

http:/  http:/
http:/  https:/
https:/ http:/
https:/ https:/

Пара предложений:

Во-первых, прежде чем мы выполним оператор UPDATE, в котором мы не уверены, что он будет делать то, что мы собираемся делать, мы должны записать оператор SELECT в контрольная работа выражения. Это позволяет нам увидеть результаты выражения, чтобы мы могли убедиться, что оно выполняет то, что мы намеревались, в различных условиях тестирования.

Во-вторых, должна быть возможность разделить комбинации (четыре возможности). Если изменения, которые мы применяем, «удаляют» первый http / https, оставляя только одно вхождение ... тогда модификация, которую мы применяем к одному из подмножеств, не будет создавать строку, а переместиться в другое подмножество. (Я понял, что хотел там сказать, вероятно, это было искажено.)

Если у меня такая строка

http 1   https 2 

и я изменил это с обновлением, чтобы быть

https 2 

Затем последующий прогон по строкам, который не будет соответствовать проверке на

https  https 

потому что в строке есть только одно вхождение http.

Предположим, что нас интересуют только строки, в которых строка link содержит два вхождения http:// / https://.

Мы могли бы использовать регулярное выражение для некоторого сопоставления, или мы можем подделать его с помощью некоторых сравнений LIKE

SELECT t.link
     , t.link LIKE '%http://%http://%'   AS c1 
     , t.link LIKE '%http://%https://%'  AS c2 
     , t.link LIKE '%https://%http://%'  AS c3 
     , t.link LIKE '%https://%https://%' AS c4
  FROM (

SELECT 'http://somedomain.com/work-a-link.php?http://someotherdomain.com/thisisworthkeeping.php' AS link
UNION ALL
SELECT 'http://somedomain.com/work-a-link.php?https://someotherdomain.com/thisisworthkeeping.php'
UNION ALL
SELECT 'https://somedomain.com/work-a-link.php?http://someotherdomain.com/thisisworthkeeping.php'
UNION ALL 
SELECT 'https://somedomain.com/work-a-link.php?https://someotherdomain.com/thisisworthkeeping.php'

       ) t
 WHERE t.link LIKE '%http%://%http%://%'
   AND t.link NOT LIKE '%http%://%http%://%http%://%'  

возвращает что-то вроде этого (с некоторой заменой строк в значениях ссылок, чтобы сократить их здесь) ...

link                                                           c1  c2  c3  c4
-------------------------------------------------------------- --  --  --  --
http://somedomain.com/walp?http://someotherdomain.com/tiwkp     1   0   0   0
http://somedomain.com/walp?https://someotherdomain.com/tiwkp    0   1   0   0
https://somedomain.com/walp?http://someotherdomain.com/tiwkp    0   0   1   0
https://somedomain.com/walp?https://someotherdomain.com/tiwkp   0   0   0   1

Мы включили условие в предложение WHERE, чтобы «отфильтровать» любой URL-адрес, в котором встречается более двух «http [s]: //». (Здесь есть небольшая подделка, подстановочный знак % может соответствовать любому количеству символов, поэтому мы не совсем проверяем https: // и http: // ... (опять же, мы могли бы реализовать регулярное выражение ( REGEXP или RLIKE), а точнее сравнение.

Но обратите внимание, как нам удалось «классифицировать» ссылку значений на c1, c2, c3 и c4.

Подтверждая, что каждая ссылка попадает в одну категорию, Мы можем включить дополнительное выражение в список SELECT

     , CASE 
       WHEN t.link LIKE '%http://%http://%'   THEN 'c1'
       WHEN t.link LIKE '%http://%https://%'  THEN 'c2'
       WHEN t.link LIKE '%https://%http://%'  THEN 'c3' 
       WHEN t.link LIKE '%https://%https://%' THEN 'c4'
       ELSE NULL
       END

Если мы находим подходящее условие WHEN, мы возвращаем THEN, и все готово. (Если есть перекрытие, ссылка, которая попадает в более чем одну категорию, мы не увидим ее в этом выражении.)

link                                                            c   c1  c2  c3  c4
-------------------------------------------------------------- --  --  --  --  --
http://somedomain.com/walp?http://someotherdomain.com/tiwkp    c1   1   0   0   0
http://somedomain.com/walp?https://someotherdomain.com/tiwkp   c2   0   1   0   0
https://somedomain.com/walp?http://someotherdomain.com/tiwkp   c3   0   0   1   0
https://somedomain.com/walp?https://someotherdomain.com/tiwkp  c4   0   0   0   1

Мы можем протестировать с различными значениями link, различными шаблонами и убедиться, что наша соответствующая «категоризация» работает так, как мы предполагаем.

(Этот метод использования оператора SELECT для проверки выражений и особенно функций, с которыми мы не знакомы ... с SUBSTRING_INDEX, что возвращается, когда строка поиска не найдена? Чувствительна ли функция REPLACE к регистру? И так далее. )

Мы можем настраивать и настраивать, экспериментировать и находить правильные комбинации, чтобы заставить его работать так, как мы хотим. Как только мы это получим,

Мы можем включить еще одно выражение в список SELECT. Мы скопируем только что добавленное, но на этот раз вместо того, чтобы возвращать литерал, мы включим другое выражение, которое выполняет функции SUBSTRING_INDEX и REPLACE.

Поскольку мы запускаем SELECT, мы знаем, что не собираемся искажать / изменять содержимое таблицы. Мы просто тестируем некоторые выражения, чтобы посмотреть, что они вернут.

     , CASE 
       WHEN t.link LIKE '%http://%http://%'    -- 'c1'
       THEN CONCAT('http://', SUBSTRING_INDEX(t.link, 'http://', -1))

       WHEN t.link LIKE '%http://%https://%'   -- 'c2'
       THEN CONCAT('https://', SUBSTRING_INDEX(t.link, 'https://', -1))

       WHEN t.link LIKE '%https://%http://%'   -- 'c3' 
       THEN CONCAT('http://', SUBSTRING_INDEX(t.link, 'http://', -1))

       WHEN t.link LIKE '%https://%https://%'  -- 'c4'
       THEN CONCAT('https://', SUBSTRING_INDEX(t.link, 'https://', -1))

       ELSE NULL
       END AS new_link

И очень скоро мы получим такой результат ...

link                                                           new_link 
-------------------------------------------------------------  ---------------------------------
http://somedomain.com/walp?http://someotherdomain.com/tiwkp    http://someotherdomain.com/tiwkp
http://somedomain.com/walp?https://someotherdomain.com/tiwkp   https://someotherdomain.com/tiwkp
https://somedomain.com/walp?http://someotherdomain.com/tiwkp   http://someotherdomain.com/tiwkp
https://somedomain.com/walp?https://someotherdomain.com/tiwkp  https://someotherdomain.com/tiwkp

Когда у нас есть работающее выражение, возвращающее значение new_link, которое мы хотим присвоить столбцу, вместо значения link,

(и мы сначала запускаем это против тестовой копии таблицы)

Мы можем преобразовать наш оператор SELECT в UPDATE

заменить SELECT ... FROM на UPDATE

и добавьте предложение SET, чтобы назначить выражение new_link для ссылки

(замените NULL в ELSE ссылкой на столбец, чтобы в случае, если мы выполнили все условия, которые мы проверяем в CASE, мы не будем изменять эту строку ...)

UPDATE mytesttable t
   SET t.link
     = CASE 
       WHEN t.link LIKE '%http://%http://%'    -- 'c1'
       THEN CONCAT('http://', SUBSTRING_INDEX(t.link, 'http://', -1))

       WHEN t.link LIKE '%http://%https://%'   -- 'c2'
       THEN CONCAT('https://', SUBSTRING_INDEX(t.link, 'https://', -1))

       WHEN t.link LIKE '%https://%http://%'   -- 'c3' 
       THEN CONCAT('http://', SUBSTRING_INDEX(t.link, 'http://', -1))

       WHEN t.link LIKE '%https://%https://%'  -- 'c4'
       THEN CONCAT('https://', SUBSTRING_INDEX(t.link, 'https://', -1))

       ELSE t.link
       END
 WHERE t.link LIKE '%http%://%http%://%'  
   AND t.link NOT LIKE '%http%://%http%://%http%://%'  

Но прежде чем мы запустим ОБНОВЛЕНИЕ, мы должны протестировать наши выражения с различными значениями ссылок, включая крайние и угловые случаи.

И снова обратите внимание, что эти сравнения LIKE, которые я написал, потенциально могут сопоставить шаткие строки, которые мы, возможно, не захотим сопоставить, например. http://BLAH http DERP :// flurb http://.

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

Мы могли бы получить более точное сопоставление с образцом, используя регулярные выражения, сравнения REGEXP (RLIKE).

Тема остается той же ... сначала проверьте выражения с помощью SELECT, а затем запустите UPDATE.