我通过 ssh 从路由器获取此文本。但我想将用户 (fr-XXX) 和 macadress (F0:24:75:33:22:11) 插入到 mysql routerusers 表中。
Flags: M - mac-cookie
# USER DOMAIN MAC-ADDRESS EXPIRES-IN
0 M fr-65111111 F0:24:75:33:22:11 4d23h56m17s
1 M fr-x0584444 50:32:75:33:22:11 4d19h8m43s
2 M fr-AA055555 3C:AB:8E:33:22:11 4d22h17m28s
3 M fr-1126666 90:B6:86:33:22:11 4d19h57m31s
....
....
....
....
我认为首先将此文件编码为 json 然后插入直到用户完成
答案1
您可以使用此sed
过滤器将文本表转换为 json 对象:
cat your_text_file | \
sed -e "s/.*\(fr-\w*\)\s*\([0-9A-F:]*\).*/{"user":\"\1\",\"mac\":\"\2\"},/g"
您将获得如下对象:
{user:"fr-65111111","mac":"F0:24:75:33:22:11"},
{user:"fr-x0584444","mac":"50:32:75:33:22:11"},
{user:"fr-AA055555","mac":"3C:AB:8E:33:22:11"},
{user:"fr-1126666","mac":"90:B6:86:33:22:11"},
然后您可以进一步处理它以插入数据库,或者您可以直接更新命令sed
来生成 SQL 插入查询。
答案2
另一种方法是将记录转换为 SQL 查询,然后将 SQL 查询一次性导入数据库(就像使用 SQL 转储一样)。
此awk
命令只是将记录转换为 SQL 查询:
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");
使用过程替换,您可以使用以下命令将命令的输出直接导入数据库:
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