MySQL: Retrieve large data from table based off a file

Use case: There is a file with millions of account numbers and we want to retrieve name, email and phone numbers for those account numbers.
There are mulitple ways to achive this. We can write a script to loop through each account number from the file, make database queries to get the information, we can also import the file with account numbers into a temporary table, the join this table with other tables to get the information. Here we will go through the steps of the latter method.

1. Create a tmp table.

CREATE TABLE `tmp_account_numbers` (
  `account_number` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (account_number)
);

Assume each line in the account file contains an account number, and the file is in /tmp/account_numbers.csv

account_number
111111
222222
333333
444444
555555
666666
777777
888888
999999
100001
100002

2. Load account numbers into the tmp table.

LOAD DATA INFILE "/tmp/account_numbers.csv"
INTO TABLE tmp_account_numbers
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

3. Export the account_number, name, email, phone into /tmp/customer_contacts.csv

SELECT tan.account_number, c.name, c.email, c.phone
FROM tmp_account_numbers tan
JOIN customer c ON c.account_number=tan.account_number
INTO OUTFILE "/tmp/customer_contacts.csv"
fields TERMINATED BY ','
LINES TERMINATED BY '\r\n';

4. Delete the tmp table.

drop table tmp_account_numbers;

Search within Codexpedia

Custom Search

Search the entire web

Custom Search