☰

如何将文本数据插入到mysql？

tag-icon • tag-icon ssh mysql scripts
如何将文本数据插入到mysql？

我通过 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

相关内容