Insert into mysql from linux terminal

Question

I have a question. i can insert into mysql database from linux terminal a record with this command:

mysql dbTest insert into tablename values(1,"b","c")

Now i have a file in Linux with some records for example:

$cat file

2, "c", "e"
3, "r", "q"
4, "t", "w"
5, "y", "e"
6, "u", "r"
7, "g", "u"
8, "f", "j"
9, "v", "k"

i don't know how can i insert all records to the file to mysql database from linux terminal.

I intent with a bash file but i don't know =(

Answer 1

Doing a series of inserts is not the best choice performance-wise. Since your input data exist as CSV you'd be better off doing a bulk load as @Kevin suggested:

mysql dbTest -e "LOAD DATA INFILE './file' INTO TABLE tablename FIELDS TERMINATED BY ','"

Answer 2

You can create a custom sql file using sed.

From a terminal execute the following code:

sed 's/\(^[0-9"]*, [a-z"]*, [a-z]*$\)/INSERT INTO tablename VALUES(\1);/g' source_file > sql_script.sql

After you can easily use the source command to insert the records.

$ mysql -u mysqlusername -p -h host
Enter password: your_secret_password

Mysql > use yourdatabasename
Mysql > source sql_script.sql