分享程序员开发的那些事...
更新时间：2022-09-11 08:07:51
一、创建数据库及表
建立数据库
|
1
|
create database dic character set utf8; |
建表
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE TABLE `dic` ( `id` int(22) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`principal` varchar(213) CHARACTER SET utf8 NOT NULL COMMENT '负责人',
`use` varchar(222) CHARACTER SET utf8 NOT NULL COMMENT '用途',
`type` varchar(222) CHARACTER SET utf8 NOT NULL COMMENT '机器类型',
`model` varchar(222) CHARACTER SET utf8 NOT NULL COMMENT '机器型号',
`place` varchar(222) CHARACTER SET utf8 NOT NULL COMMENT '位置',
`IP` varchar(222) CHARACTER SET utf8 NOT NULL COMMENT 'IP',
`addtime` datetime NOT NULL COMMENT '时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8; |
二、编写相关脚本
注意事项
mysql -u用户名 -p密码 -e "命令;" 多命令都写在双引号里面，通过分号分割。
INSERT INTO `表名` (`字段`,`字段`) VALUES (值,值);
以下两个脚本都可以实现
|
1
2
3
4
5
6
7
8
|
#!/bin/bashread -p "please enter you mysql password:" passwd
TABLE="dic"
query="NULL,'张伟易','新dns服务器1','实体机','DELL R410','14层A5','10.240.210.61','2015-06-06 13:04:09'"
echo $query
/usr/bin/mysql dic -u root -p$passwd <<EOF
INSERT INTO $TABLE VALUE($query);
#EOF |
|
1
2
3
4
5
6
|
#!/bin/bashread -p "please enter you mysql password:" passwdTABLE="dic"query="NULL,'张伟易','新dns服务器1','实体机','DELL R410','14层A5','10.240.210.61','2015-06-06 13:04:09'"echo $query/usr/bin/mysql dic -u root -p$passwd -e "INSERT INTO $TABLE VALUE($query);" |
此外，还可以通过将普通文本格式化输出后导入到数据库中
这里以test1作为示例
|
1
2
|
[root@daohang lxh]# cat test1小明 新服务器1 实体机 DELL R410 14层A5 10.240.210.61 |
脚本如下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
#!/bin/bash>test_newread -p "please enter you mysql password:" passwdMYSQL=/usr/bin/mysqlTABLE="dic"TIME=`date +%F\ %T`P="\e[01;35m"#sed "s/\(^.*$\)/\1,$TIME/" test >> test_new##在每行行尾添加时间cat test1 | sed 's/^/NULL\t/g' |sed 's/\t/,/g' |sed "s/\(^.*$\)/\1,$TIME/" >>test_newcat test_new |while read line#echo "$TIME" >>mysql_err_logdoecho $linequery=`echo "$line" |awk -F "," '{ printf("%s,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\"", $1, $2, $3, $4, $5, $6, $7 , $8)}'`echo $query 1>>mysql_err_log 2>&1echo $query$MYSQL dic -u root -p$passwd <<EOFINSERT INTO $TABLE VALUES($query);EOFdone#echo -e " $P***********************$TIME**********************************" >>mysql_err_log |
执行脚本
|
1
2
3
|
[root@daohang lxh]# sh mysqltest.shNULL,小明,新服务器1,实体机,DELL R410,14层A5,10.240.210.61,2015-06-06 14:26:07NULL,"小明","新服务器1","实体机","DELL R410","14层A5","10.240.210.61","2015-06-06 14:26:07" |
插入成功
本文转自 xoyabc 51CTO博客，原文链接：http://blog.51cto.com/xoyabc/1659178，如需转载请自行联系原作者
