MYSQL文字列をコンマで分割

Mysql String Split Comma

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>