有张表数据大致如下：

+----+------------+-------------------+
| id | app_id     | ports             |
+----+------------+-------------------+
|  1 | 1546483210 | 9419;9420;9421    |
|  2 | 1546483146 | 20121             |
|  3 |       2167 | 47607~47610;59421 |
|  4 |       2173 | 80;443            |
|  5 |       2286 | 20121             |
|  6 | 1546483171 | 65383             |
|  7 |       2161 | 62016;2323        |
|  8 | 1546483170 | 65383             |
|  9 | 1546483170 | 4031              |
| 10 | 1546483123 | 9854              |
+----+------------+-------------------+
gherkin

需要把ports按“;”分割，且“~”的为区间段分割，最终结果如下

+----+------------+-------+
| id | app_id     | port  |
+----+------------+-------+
|  1 | 1546483210 |  9419 |
|  1 | 1546483210 |  9420 |
|  1 | 1546483210 |  9421 |
|  2 | 1546483146 | 20121 |
|  3 |       2167 | 47607 |
|  3 |       2167 | 47608 |
|  3 |       2167 | 47609 |
|  3 |       2167 | 47610 |
|  3 |       2167 | 59421 |
|  4 |       2173 |    80 |
|  4 |       2173 |   443 |
|  5 |       2286 | 20121 |
|  6 | 1546483171 | 65383 |
|  7 |       2161 | 62016 |
|  7 |       2161 |  2323 |
|  8 | 1546483170 | 65383 |
|  9 | 1546483170 |  4031 |
| 10 | 1546483123 |  9854 |
+----+------------+-------+
gherkin

主要利用mysql自带的自增序列表mysql.help_topic，sql如下：

select m.id,m.app_id, substring_index(m.tmp_port,'~',1) + n.help_topic_id as `port`
from
(select t1.id,t1.app_id,substring_index( substring_index(t1.ports, ';', t2.help_topic_id+1), ';' , -1 ) `tmp_port`
 from app_pro_port_info t1
  join mysql.help_topic t2 on t2.help_topic_id < LENGTH(t1.ports) - LENGTH(replace(t1.ports,';','')) + 1 
)m
 join mysql.help_topic n on substring_index(m.tmp_port,'~',-1) - substring_index(m.tmp_port,'~',1) +1 > n.help_topic_id
stylus