When using the LOAD DATA INFILE statement in MySQL to import data from a file into a table, you can convert general column types to numeric types by using user variables and the SET clause. Here’s how you can do it:

Suppose you have a file named data.txt with the following content:

1,John,25
2,Jane,30
3,David,28

And you want to load this data into a MySQL table named users, where the third column (age) should be converted from a general type to a numeric type.

Here’s the LOAD DATA INFILE statement with the conversion:

LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, @age)
SET age = CAST(@age AS UNSIGNED);

Explanation of the statement:

• FIELDS TERMINATED BY ',': Specifies that fields in the file are separated by commas.
• LINES TERMINATED BY '\n': Specifies that lines in the file are terminated by newlines.
• (id, name, @age): Defines the columns in the table (id, name) and a user variable @age to store the value of the third column from the file.
• SET age = CAST(@age AS UNSIGNED): Converts the value stored in @age to an unsigned integer using the CAST function and assigns it to the age column in the table.

Adjust the file path, table name, and column names according to your specific use case. You may also need to modify the data type conversion (e.g., using SIGNED instead of UNSIGNED) based on the actual data in your file.