By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE import (
line varchar(512) DEFAULT NULL
)
INSERT INTO import values ('11111111,22222222,33333333')
select * from import
| line |
|---|
| 11111111,22222222,33333333 |
create table phone (
num int
)
insert into phone (num)
select
SUBSTRING_INDEX(SUBSTRING_INDEX(import.line, ',', numbers.n), ',', -1) name
from
(select 1 n union all
select 2 union all select 3 union all
select 4 union all select 5) numbers INNER JOIN import
on CHAR_LENGTH(import.line)
-CHAR_LENGTH(REPLACE(import. line, ',', ''))>=numbers.n-1
order by
n
Records: 3 Duplicates: 0 Warnings: 0
select * from phone
| num |
|---|
| 11111111 |
| 22222222 |
| 33333333 |
truncate phone
insert into phone (num)
with recursive
N as ( select 1 as n union select n + 1 from N inner join import
on n <= length(import.line) - length(replace(import.line, ',', '')))
select distinct substring_index(substring_index(import.line, ',', n), ',', -1)
num from N inner join import
Records: 3 Duplicates: 0 Warnings: 0
select * from phone
| num |
|---|
| 11111111 |
| 22222222 |
| 33333333 |
truncate phone
insert into phone
select j.line
from import i
join json_table(
replace(json_array(i.line), ',', '","'),
'$[*]' columns (line varchar(50) path '$')
) j
Records: 3 Duplicates: 0 Warnings: 0
select * from phone
| num |
|---|
| 11111111 |
| 22222222 |
| 33333333 |