Making use of file data in a mysql query

Question

I have a file of comma-separated user ids. And I'd like to SELECT for these users.

That's to say I imagine it ought to be looking like this:

mysql> SELECT u.token, u.user_id FROM users u WHERE u.user_id IN (SELECT * LOAD_FILE('/home/alex/lzmigration/users'));

Somehow this LOAD_FILE thingy does not work. I'm not sure I understand why.

I'm getting this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD_FILE('/home/alex/lzmigration/users'))' at line 1

Here is the version info.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.35-27 |
+-----------+

@Luuk Thanks, I made this query work: SELECT LOAD_FILE('/var/lib/mysql-files/users.txt'), now it returns the contents of the file but the comma is now encoded as C. And of course the initially intended query does not work. Is there a way to fix the encoding? The mysql docs does not seem to be verbose on this func. — Alex
– Alex, Commented May 14, 2024 at 13:50
@Luuk not sure I understand how to use the CSV engine in this case...could you please elaborate? — Alex
– Alex, Commented May 14, 2024 at 13:53
but the comma is now encoded as C sorry, no clue what you do mean. If values are correct, you can use LOAD DATA (and not LOAD_FILE() to import data into an existing table. see: dev.mysql.com/doc/refman/8.0/en/load-data.html. Downside it that you need to create a table first. — Luuk
– Luuk, Commented May 14, 2024 at 14:34

Barmar · Accepted Answer · 2024-05-14 15:48:06Z

0

To match against a comma-separated string, you must use the FIND_IN_SET() function; IN is for a literal list in the SQL or for a subquery that returns multiple rows.

SELECT u.token, u.user_id 
FROM users u 
WHERE FIND_IN_SET(u.user_id, LOAD_FILE('/home/alex/lzmigration/users'));

Make sure there are no spaces around the comma in the file, as FIND_IN_SET() will not ignore them.

answered May 14, 2024 at 15:48

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

Luuk · Accepted Answer · 2024-05-14 15:53:00Z

Starting with a file abc.csv, with the following contents:

You could do:

select CONVERT(LOAD_FILE('/var/lib/mysql-files/abc.csv') using utf8mb4)\G

NOTE: /var/lib/mysql-files/ is the output of show variables like '%secure_file_priv%'; which produces:

*************************** 1. row ***************************
CONVERT(LOAD_FILE('/var/lib/mysql-files/abc.csv') using utf8mb4): a,b,c
1,2,3
4,5,6
7,8,9
1 row in set (0.00 sec)

But then you have to parse that one string into the fields needed, an you wil not have a table defined.

Alternative solution

From the dorectory where your abc.csv file is type:

echo .q | sqlite3 -batch -cmd ".mode csv" -cmd ".import abc.csv abc" -cmd ".dump"

NOTE:

sqlite3.exe needed (on Windows, see: https://sqlite.org/download.html; when you are on Linux, you need to know where to get this 😉)

output:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "abc"(
"a" TEXT, "b" TEXT, "c" TEXT);
INSERT INTO abc VALUES('1','2','3');
INSERT INTO abc VALUES('4','5','6');
INSERT INTO abc VALUES('7','8','9');
COMMIT;

When you save this output to a file you basically have the SQL script needed to create the table (including the INSERT statements)

Downside is that all columns will be of TYPE TEXT.

Collectives™ on Stack Overflow

Making use of file data in a mysql query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related