MYSQL把字符串按逗号拆分

最新推荐文章于 2024-07-21 03:42:00 发布

lv941002

最新推荐文章于 2024-07-21 03:42:00 发布

阅读量1.1w

点赞数 1

本文链接：https://blog.csdn.net/lv941002/article/details/81632486

版权

在mysql中,如何把传入的字符串按照一定的分割符拆分,然后写入表中.

mysql> create table tt(name varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tt;
Empty set (0.00 sec)

mysql>

BEGIN
DECLARE V_START  INTEGER;
DECLARE V_LOCATE INTEGER;
SET V_START=1;
  BEGIN
     DECLARE V_STR_BEFORE VARCHAR(4000);
     WHILE INSTR(v_str,',')>0 DO
     SET V_LOCATE =INSTR(v_str,',');
     SET V_STR_BEFORE=SUBSTRING(v_str,V_START,V_LOCATE-V_START);
     INSERT INTO tt(NAME) VALUES(V_STR_BEFORE);
     commit;
     SET V_STR =SUBSTRING(v_str,V_LOCATE+1);
     END WHILE;
     INSERT INTO tt(NAME) VALUES(V_STR);
  END; 
  select * from tt;
END

mysql> call test1('x,y,z,t')
    -> ;
+------+
| name |
+------+
| x    |
| y    |
| z    |
| t    |
+------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>