how to create schema in mysql and how to import existing record into the database?

I have file with name data that contain information like in my linux machine

  2 Mar 1 1234 141.98.80.59
  1 Mar 1 1234 171.239.249.233
  5 Mar 1 admin 116.110.119.156
  4 Mar 1 admin1 177.154.8.15
  2 Mar 1 admin 141.98.80.63
 

I have created a database in Mysql with name “invaliduser” and inside that i have create table “invalidlog” and i created a schema like this

CREATE TABLE invalidlog(
    Count INT,
    Month CHAR(10),
    Date INT(10),
    User VARCHAR(50),
    IP_ADD INT UNSIGNED
);

when i am trying to import the data file in mysql with the following command

mysql -u root -p invaliduser < data

it is showing me error:

ERROR 1064 (42000) at line 1: 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 ‘2 Mar 1 1234 141.98.80.59 1 Mar 1 1234 171.239.249.233 5 Mar 1 admin’ at line 1

This is a correct way to create schema?

Answer

You cannot simply import the data into your table, you will need to generate valid SQL syntax from the file. This can be done with awk:

awk -v sinq="'" '{ print "INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ("sinq$1sinq","sinq$2sinq","sinq$3sinq","sinq$4sinq","sinq$5sinq");"}' file

Set up a variable sinq to be a single quote and then use this along with the space separated fields to generate the required sql.

Output:

INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ('2','Mar','1','1234','141.98.80.59');
INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ('1','Mar','1','1234','171.239.249.233');
INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ('5','Mar','1','admin','116.110.119.156');
INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ('4','Mar','1','admin1','177.154.8.15');
INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ('2','Mar','1','admin','141.98.80.6');

The awk command can then be integrated with mysql:

mysql -u root -p pass invaliduser <<< "$(awk -v sinq="'" '{ print "INSERT INTO invalidlog (Count,Month,Date,User,IPADD) VALUES ("sinq$1sinq","sinq$2sinq","sinq$3sinq","sinq$4sinq","sinq$5sinq");"}' file)"