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
Search the entire web