Another way to do this is to convert the records into SQL queries and to import the SQL queries into the database (as you would with an SQL dump) all at once.
This awk
command just converts the records into SQL queries:
awk '/ [0-9]+/ {print "INSERT INTO bar (field1, field2) VALUES (\""$3"\", \""$4"\");"}' inputfile
user@debian:~$ awk '/ [0-9]+/ {print "INSERT INTO bar (field1, field2) VALUES (\""$3"\", \""$4"\");"}' inputfile
INSERT INTO bar (field1, field2) VALUES ("fr-65111111", "F0:24:75:33:22:11");
INSERT INTO bar (field1, field2) VALUES ("fr-x0584444", "50:32:75:33:22:11");
INSERT INTO bar (field1, field2) VALUES ("fr-AA055555", "3C:AB:8E:33:22:11");
INSERT INTO bar (field1, field2) VALUES ("fr-1126666", "90:B6:86:33:22:11");
Using a process substitution you can directly import the output of the command into the database using this command:
mysql -u root -p foo < <(awk '/ [0-9]+/ {print "INSERT INTO bar (field1, field2) VALUES (\""$3"\", \""$4"\");"}' inputfile)
user@debian:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.44-0+deb8u1 (Debian)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE foo
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> USE foo
Database changed
mysql> CREATE TABLE bar (
-> id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> field1 VARCHAR(32) NOT NULL,
-> field2 VARCHAR(32) NOT NULL
-> )
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM bar
-> ;
Empty set (0.00 sec)
mysql> exit
Bye
user@debian:~$ mysql -u root -p foo < <(awk '/ [0-9]+/ {print "INSERT INTO bar (field1, field2) VALUES (\""$3"\", \""$4"\");"}' inputfile)
Enter password:
user@debian:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.5.44-0+deb8u1 (Debian)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE foo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM bar
-> ;
+----+-------------+-------------------+
| id | field1 | field2 |
+----+-------------+-------------------+
| 1 | fr-65111111 | F0:24:75:33:22:11 |
| 2 | fr-x0584444 | 50:32:75:33:22:11 |
| 3 | fr-AA055555 | 3C:AB:8E:33:22:11 |
| 4 | fr-1126666 | 90:B6:86:33:22:11 |
+----+-------------+-------------------+
4 rows in set (0.01 sec)
mysql> exit
Bye