Question 280144 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL load data insert, split values to multiple rows

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search