MySQL: Import CSV file data into temporary tables.

Sometimes is needed to query the database based on an external data file (.csv).
So we could consider to import this data to a temporary table to achieve this, and to ensure not to alter the database schema at all.

Let's say we have a table like this:

mysql> SELECT * from User;
+------+----------+-------------+------------+
| id   | name     | last_name   | sport      |
+------+----------+-------------+------------+
| 1000 | Oscar    | Soliz       | cycling    |
| 1001 | Wendy    | Cornejo     | athletics  |
| 1002 | Karen    | Torrez      | swimming   |
| 1003 | Rosemary | Quispe      | marathon   |
| 1004 | Stefany  | Coronado    | athletics  |
| 1005 | Jose     | Quintanilla | swimming   |
+----+------------+-----------+--------------+

And a CSV file which content some ids:

id
1002
1004
1005

Now, assume we have the following scenario:

"Given a list of User IDs (CSV file), we want to know the name and sport of these people."

Steps:

1. Create a temporary table:

CREATE TEMPORARY TABLE user_ids (id INT(11));

2. Import data from the CSV file:

LOAD DATA INFILE "/file_path/user_ids.csv"
INTO TABLE user_ids
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; # To ignore headers (first row)

3. Finally you can build the query and work like it is one more table in the database, e.g:

SELECT CONCAT(u.name, ' ', u.last_name) name, u.sport
FROM User u
WHERE u.id IN (SELECT id FROM user_ids);

+------------------+------------+
| name             | sport      |
+------------------+------------+
| Karen Torrez     | swimming   |
| Stefany Coronado | athletics  |
| Jose Quintanilla | swimming   |
+------------------+------------+