MySQL load data insert, split values to multiple rows

Question

Using the LOAD DATA LOCAL INFILE

num column Store every 6 digits

How can I parameterize the load command to iterate through the array?

Mysql version 8.0.23

In File text

id length 1,
cnt length 2,
num length 500
--------------------
1 3AB1001AB1002AB1003

table A

id	cnt	num
1	3	AB1001AB1002AB1003

i want output like this table

table B

id	cnt	num
1	3	AB1001
1	3	AB1002
1	3	AB1003

Answer 1

I was able to test this table:

create table mytable (
  id int,
  cnt int,
  num varchar(500)
);

I put the line of text you show into a file p.csv:

1 3AB1001AB1002AB1003

I ran the mysql client with the option to enable local data loading:

$ mysql --local-data

Then I loaded the data file this way:

mysql> load data local infile 'p.csv' into table mytable (@temp)
set id = substr(@temp, 1, 1),
    cnt = substr(@temp, 2, 2),
    num = substr(@temp, 3);

It worked!

mysql> select * from mytable;
+------+------+---------------------+
| id   | cnt  | num                 |
+------+------+---------------------+
|    1 |    3 | 3AB1001AB1002AB1003 |
+------+------+---------------------+

Read more about the LOAD DATA INFILE statement here: https://dev.mysql.com/doc/refman/8.0/en/load-data.html

And read about the SUBSTR() function here: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substr

Answer 2

You can use a trigger to split a row from table 'a' into 3 rows for 'b'. The trigger is following:

`CREATE TRIGGER triger1 AFTER INSERT ON a FOR EACH ROW BEGIN
SET @i = 0;
REPEAT
SET @part = SUBSTRING(NEW.num, 1 + @i*6, 6);
INSERT INTO b VALUE(NEW.id, NEW.cnt, @part);
SET @i = @i + 1;
UNTIL @i = NEW.cnt
END REPEAT;
END;`

Before insert data using the LOAD DATA LOCAL INFILE, you have to create a trigger on table 'a'. Regards.