MySQL - Insert variable into column while using INFILE

Question

I'm writing an application in Java where I need to insert text files into a MySQL table. These files can be tens of gigabytes. I've decided to use LOCAL INFILE to do this for performance reasons. The problem I'm running into is that I need to also insert a value based on a variable at the same time.

Assume the following table.

foo_string | bar_int

Assume the following data

a
bb
cccc

I'm first getting an id of a value from another table and storing it in a local int. Then I go to execute the following query to insert the values from myvalues.txt

statement.executeUpdate( "LOAD DATA LOCAL INFILE 'myvalues.txt' INTO TABLE  mytable FIELDS TERMINATED BY '' LINES TERMINATED BY '\\n'");

The above works as expected but I'd also like to insert a value into the second (bar_int) column at the same time. What is the best way to do this?

I'd update the row in another statement before the push of infile. Is it the same value for each row or a different value based on some fields in the file? — MadaManu
– MadaManu, Commented Jan 3, 2020 at 3:01
Each files data set will contain the same value for bar_int which is unknown to me until I query for it from the other table — user316114
– user316114, Commented Jan 3, 2020 at 3:05

Gordon Linoff · Accepted Answer · 2020-01-03 03:05:26Z

1

You can use a column list and then a SET clause:

LOAD DATA LOCAL INFILE 'myvalues.txt'
     INTO TABLE mytable
     FIELDS TERMINATED BY '' LINES TERMINATED BY '\\n'
     (foo_string)
     SET bar_int = 1;

This reads the one column from the file into the column foo_string. It sets bar_int to a specific value.

answered Jan 3, 2020 at 3:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - Insert variable into column while using INFILE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related