The SET option in mysqlimport / LOAD DATA INFILE : transforming values before insert


Let’s say you want to load a text file into a mysql table, but some mandatory columns are missing in the file or you have to modify some data first.
Well, the LOAD DATA INFILE statement has the handful SET clause for transforming values before assigning them to columns.
Take this example:

LOAD DATA INFILE 'my_users.txt'
(@id, @ignore_col_1, @ignore_col_2, user_name, user_group)
SET loaded_from = 'manual import', id = (@id + 1000)

which loads a file like


The 1st column of the file becomes the @id variable (later transformed by SET), the 2nd and 3rd columns are ignored (they become variables not used by SET), the “user_name” and “user_group” columns are loaded directly. An extra column “loaded_from” (that was not in the file) is also loaded by SET.

Here’s the result:

id user_name user_group loaded_from
1003 Daniel admin manual import
1004 John newbie manual import


So.. it would be nice to have this SET feature in mysqlimport too.
But it’s still not officially supported in the current versions (5.x)! (I’ve filled a BUG, let’s hope it gets validated).

When you call mysqlimport, it ends up creating a LOAD DATA INFILE statement, but there’s no trace of the SET clause. Here’s the src code:

end= add_load_option(end, escaped, " ESCAPED BY");
end= add_load_option(end, lines_terminated, " LINES TERMINATED BY");
if (opt_ignore_lines >= 0) end= strmov(longlong10_to_str(opt_ignore_lines,
strmov(end, " IGNORE "),10), " LINES");
// COLUMN definition is the last thing before executing the statement:
if (opt_columns) end= strmov(strmov(strmov(end, " ("), opt_columns),")");
*end= '\0';
if (mysql_query(mysql, sql_statement)){


Ugly solution: SQL-injecting the SET clause in the --columns option! LOL
You must leave unbalanced parenthesis because the program concats "(" + "--columns" + ")"



cols="@id, @ignore_col_1, @ignore_col_2, user_name, user_group) \
SET loaded_from = 'manual import', id = (@id + 1000"
# ATTENTION, you must leave an unbalanced parenthesis!!!!

mysqlimport5 --columns="$cols" "$filename"

mysqlimport will then run:

LOAD DATA INFILE 'my_users.txt'
@id, @ignore_col_1, @ignore_col_2, user_name, user_group) SET loaded_from = 'manual import', id = (@id + 1000

If you have found a better workaround, please share! 🙂

Leave a Reply