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 |
+------------------+------------+
