mysql - 使用 Mysql 触发器分解 URL 字段并将其部分存储在 MyISAM 表中

Question

我有一个 MySQL MyISAM 表，它在 url 字段中存储 URL。 URL 始终只是方案、子域和主机(例如 http://www.site.com 或 https://site2.edu )。

我需要将域、子域和方案存储在它们自己的字段中，以便我可以对它们进行索引和搜索。对于我的系统来说，对数百万行执行 LIKE 查询太慢了。

• 域名:site.com、site2.com
• 子域名:www、''
• 方案:http、https

如何创建 MySQL 触发器来执行此操作？每次更新时，都需要从 URL 字段中提取域、子域和方案，然后将它们存储在各自的字段中(例如，domain、subdomain、 scheme )这些字段只会由 MySQL 触发器写入。

我意识到解析 URL 通常并不简单，所以我最关心的是设置一个触发器来执行类似的操作。我可以对应用程序中 URL 解析的质量进行调整和权衡。

Answer 1

DELIMITER //

DROP TRIGGER IF EXISTS url_trigger_before_insert //

CREATE TRIGGER url_trigger_before_insert
 BEFORE INSERT ON url_table
 FOR EACH ROW 
 BEGIN
     DECLARE no_proto CHAR;
     SET NEW.scheme = SUBSTRING_INDEX(NEW.url, ':', 1);

     SET @no_proto = REPLACE(NEW.url,CONCAT(NEW.scheme,'://'),'');

     SET @no_proto = SUBSTRING_INDEX(@no_proto, '/', 1); 

     SET NEW.subdomain = SUBSTRING_INDEX(@no_proto, '.', 1);
     SET NEW.host = SUBSTRING_INDEX(@no_proto, '.', -2);

 END //

DROP TRIGGER IF EXISTS url_trigger_before_update //

CREATE TRIGGER url_trigger_before_update
 BEFORE UPDATE ON url_table
 FOR EACH ROW 
 BEGIN
     DECLARE no_proto CHAR;
     SET NEW.scheme = SUBSTRING_INDEX(NEW.url, ':', 1);

     SET @no_proto = REPLACE(NEW.url,CONCAT(NEW.scheme,'://'),'');

     SET @no_proto = SUBSTRING_INDEX(@no_proto, '/', 1); 

     SET NEW.subdomain = SUBSTRING_INDEX(@no_proto, '.', 1);
     SET NEW.host = SUBSTRING_INDEX(@no_proto, '.', -2);

 END //


DELIMITER ;