备注:测试数据库版本为MySQL 8.0
一.需求
数据准备
create view v5 as
select 'mo,larry,curly' as name
union all
select 'tina,gina,jaunita,regina,leena' as name;
mysql> select * from v5;
+--------------------------------+
| name |
+--------------------------------+
| mo,larry,curly |
| tina,gina,jaunita,regina,leena |
+--------------------------------+
2 rows in set (0.00 sec)
需要取出航中的第二个姓名
larry
gina
二.解决方案
其实用substring_index 即可
代码:
SELECT name, SUBSTRING_INDEX(name,',',2) name1, SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) name2
FROM v5;
测试记录:
mysql> create view v5 as
-> select 'mo,larry,curly' as name
-> union all
-> select 'tina,gina,jaunita,regina,leena' as name;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from v5;
+--------------------------------+
| name |
+--------------------------------+
| mo,larry,curly |
| tina,gina,jaunita,regina,leena |
+--------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT name, SUBSTRING_INDEX(name,',',2) name1, SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) name2
-> FROM v5;
+--------------------------------+-----------+-------+
| name | name1 | name2 |
+--------------------------------+-----------+-------+
| mo,larry,curly | mo,larry | larry |
| tina,gina,jaunita,regina,leena | tina,gina | gina |
+--------------------------------+-----------+-------+
2 rows in set (0.00 sec)