How does MySQL insert data via load data infile

Question

I am loading data from a file. MySQL doesn't seem to insert line by line. So how does mysql load the data. Does it do one big chunk at the end after reading the file? Reading a file with 50000 rows.

I am just guessing. I ran a load data infile command and would do a select on the table got nothing. Ran same select a minute later and got nothing. After the command was done running. ran the same select statment and data was there. So my guess is it chucking at the end. But have no clue. — user3525290
– user3525290, Commented Apr 10, 2018 at 16:57

Barmar · Accepted Answer · 2018-04-10 17:11:52Z

3

If the table is InnoDB, each statement is executed as a transaction unless you start a transaction explicitly. So it's as if you'd written:

START TRANSACTION;
LOAD DATA INFILE ...;
COMMIT;

So the result of loading the file is made visible atomically.

See the documentation on autocommit mode.

answered Apr 10, 2018 at 17:11

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

4 Comments

user3525290 Over a year ago

I just did [code]mysql --local-infile database [code]. The tablle is InnoDB and I get nothing until the table command finishes.

Barmar Over a year ago

That makes it equivalent to LOAD DATA LOCAL INFILE ..., but it's still in a transaction.

Barmar Over a year ago

BTW, use backticks in comments, not [code]foo[/code]

user3525290 Over a year ago

Thanks mysql --local-infile database

Collectives™ on Stack Overflow

How does MySQL insert data via load data infile

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related