mysql在数据库中使用concat和substring以及http和https URL

这个问题围绕着使用concat和子字符串来更新mysql表中的内容
如果我有一个链接存储在mysql数据库中

https://this.example.com/work-a-link.php?https://that.example.com/thisisworthkeeping.php

下面的代码将删除第一个示例，并将url的第二个示例的前缀保留为http://url（在本例中，理论上应该是https://-因为第二个url以https开头：//

DROP TABLE IF EXISTS content_temp;
CREATE TABLE content_temp AS SELECT * FROM content GROUP BY Title ORDER BY ID ASC;
UPDATE content_temp SET link = CONCAT('http://', SUBSTRING_INDEX(link, 'https://', -1));
UPDATE content_temp SET link = CONCAT('http://', SUBSTRING_INDEX(link, 'http://', -1));
UPDATE content_temp SET link=replace(link,'http://https://','http://');
UPDATE content_temp SET link=replace(link,'http://http://','http://');

这就是问题所在，我正在寻求一个能帮我走出困境或为我指明正确方向的人的帮助——我想要任何帮助 https:// 在第二种情况下 https:// -而我那糟糕的代码却把它改成了 http:// 我想做到的是尊重和保留第二个示例是什么——不管是http还是https。
唯一的随机因素是。。。第一个示例可以是http或https
所以重新调整代码的最终结果是：
（一）

http://this.example.com/work-a-link.php?http://that.example.com/thisisworthkeeping.php

会给 http://that.example.com/thisisworthkeeping.php .
（二）

http://this.example.com/work-a-link.php?https://that.example.com/thisisworthkeeping.php

会给 https://that.example.com/thisisworthkeeping.php .
（三）

https://this.example.com/work-a-link.php?http://that.example.com/thisisworthkeeping.php

会给 http://that.example.com/thisisworthkeeping.php .
以及
（四）

https://this.example.com/work-a-link.php?https://that.example.com/thisisworthkeeping.php

会给 https://that.example.com/thisisworthkeeping.php .

看起来你总共有四种可能的组合。第一个可以是“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 子句来“过滤”出现两次以上“http[s]：//”的任何url(里面有一点假动作 % 通配符可以匹配任意数量的字符，因此我们不完全检查https://和http://(同样，我们可以实现更精确的正则表达式（regexp或rlike）比较。
但请注意，我们是如何将值链接“分类”为c1、c2、c3和c4的。
确认每个链接都属于一个类别，我们可以在选择列表中包含一个附加表达式

, 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语句测试表达式的技术，尤其是我们不熟悉的函数。。。对于子字符串索引，当找不到搜索字符串时返回什么？replace函数是否区分大小写？以此类推。）
我们可以调整和调整，实验和找到正确的组合，让它工作的方式，我们想要的。一旦我们知道了，
我们可以在选择列表中包含另一个表达式。我们将复制刚才添加的表达式，但是这次，我们将不返回文本，而是包含一个不同的表达式，一个用于 SUBSTRING_INDEX 以及 REPLACE 功能。
因为我们在经营一家 SELECT 我们知道我们不会把这张table上的东西弄坏的。我们只是测试一些表达式，看看它们返回什么。

, 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 子句将新的链接表达式指定给链接
（将else中的null替换为对列的引用，这样，如果我们在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测试表达式，然后运行更新。

mysql在数据库中使用concat和substring以及http和https URL

2条答案

相关问题

热门标签

最新问答