Build Groups/ Segments in Mysql-CodePudding

I have a table like:

| Page                         | Clicks |
| ---------------------------- | ------ |
| example.com/blog/guide-a/    | 3000   |
| example.com/blog/guide-b/    | 1500   |
| example.com/cities/new-york/ | 800    |
| example.com/cities/berlin/   | 600    |
| example.com/cities/london/   | 400    |
| example.com/country/germany/ | 300    |
| example.com/country/austria/ | 500    |

Now I need a column called "subfolder". It should look like:

| Page                         | Clicks | Subfolder |
| ---------------------------- | ------ | --------- |
| example.com/blog/guide-a/    | 3000   | blog      |
| example.com/blog/guide-b/    | 1500   | blog      |
| example.com/cities/new-york/ | 800    | cities    |
| example.com/cities/berlin/   | 600    | cities    |
| example.com/cities/london/   | 400    | cities    |
| example.com/country/germany/ | 300    | country   |
| example.com/country/austria/ | 500    | country   |

Any Ideas?

CodePudding user response：

You can generate this Content with SUBSTR_INDEX

Sample

MariaDB [test]> SELECT SUBSTRING_INDEX( SUBSTRING_INDEX('example.com/blog/guide-a/', '/', 2) , '/', -1);
 ---------------------------------------------------------------------------------- 
| SUBSTRING_INDEX( SUBSTRING_INDEX('example.com/blog/guide-a/', '/', 2) , '/', -1) |
 ---------------------------------------------------------------------------------- 
| blog                                                                             |
 ---------------------------------------------------------------------------------- 
1 row in set (0.000 sec)

Or you generate a virtual persistent Column. It will fill the new column automatic. This Sample is for MariaDB. In MySQL the Syntax is a little bit different.

Sample

MariaDB [test]> CREATE TABLE `mysubfolder` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `Page` VARCHAR(64) DEFAULT NULL,
    ->   `Clicks` INT(11) DEFAULT NULL,
    ->   `Subfolder` VARCHAR(64) AS ( SUBSTRING_INDEX( SUBSTRING_INDEX(Page, '/', 2) , '/', -1) ) PERSISTENT,
    ->   PRIMARY KEY (`id`),
    ->   KEY (Subfolder)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.033 sec)

MariaDB [test]> INSERT INTO `mysubfolder` (`id`, `Page`, `Clicks`)
    -> VALUES
    -> (1, 'example.com/cities/new-york/', 1234 );
Query OK, 1 row affected (0.007 sec)

MariaDB [test]> SELECT * FROM mysubfolder;
 ---- ------------------------------ -------- ----------- 
| id | Page                         | Clicks | Subfolder |
 ---- ------------------------------ -------- ----------- 
|  1 | example.com/cities/new-york/ |   1234 | cities    |
 ---- ------------------------------ -------- ----------- 
1 row in set (0.001 sec)

MariaDB [test]>