Question 387142 in Mysql Question posted in
The official MySQL documentation can be found here.

Making use of file data in a mysql query

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

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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:

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search