load命令
load data infile 语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前,mysqld进程(服务)必须已经在运行。由于安全原因,当读取位于服务器上的文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用load data infile,在服务器主机上必须有file的权限。
基本语法:
load data [low_priority] [local] infile 'file_name.csv' [replace | ignore]
into table tab_name
[fields
[terminated by ',' ] #字段分隔符
[OPTIONALLY] enclosed by '"' ]
[escaped by '\' ]
]
[lines
[starting by 'string']
[terminated by '\n'] #换行符
]
[ignore number lines] #如:可以使用ignore 1 lines来跳过一个包含列名称的起始标题行
[(col_name1,col_name2,)] #导入到数据表的对应字段名
参数说明:
文件路径
1.如果指定了local,则文件会被客户主机上的客户端读取,并被发送到服务器。文件会被给予一个完整的路径名称,以指定确切的位置。如果给定的是一个相对的路径名称,则此名称会被理解为相对于启动客户端时所在的目录。
如将客户端上的/data/file_name.log导入到mysql服务端上:
load data local infile "/data/file_name.log" into table dbname.tabname;
2.如果LOCAL没有被指定,则文件必须位于服务器主机上,并且被服务器直接读取。
low_priority
如果指定关键词low_priority,那么MySQL将会等到没有其他人读这个表的时候,才把数据插入。如下的命令:
load data low_priority infile "file_name.log" into table test;
replace和ignore
replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。
例如:
load data low_priority infile "file_name.log" replace into table test;
fields
fields关键字指定了文件字段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
terminated by 分隔符 描述字段的分隔符,默认情况下是tab字符(\t)
enclosed by 字段扩起符号 描述的是字段的括起字符。
escaped by 描述的转义字符。默认的是反斜杠(backslash:\ )
例如:
load data local infile "/data/file_name.log" into table dbname.tabname fields terminated by',' enclosed by '"';
lines
1.terminated 关键字指定了每条记录的分隔符默认为 ‘\n’ 即为换行符
例如:
load data local infile "/data/file_name.log" into table dbname.tabname fields terminated by',' enclosed by '"' lines terminated by '\n';
2.starting关键字
如果所有您希望读入的行都含有一个您希望忽略的共用前缀,则您可以使用 ‘prefix_string’ 来跳过前缀(和前缀前的字符)。如果某行不包括前缀,则整个行被跳过。注:prefix_string 会出现在一行的中间。
以下面的test.txt文件为例:
xxx"row",1
something xxx"row",2
load data local infile "test.txt" into table test fields terminated by ',' lines starting by 'xxx'
最后得到数据为 (“row”,1)和(“row”,2)
示例:用shell脚本实现将日志文件导入数据库
dblog_import.sh
#!/bin/bash
#数据库日志文件名时间:日志文件每小时生成一个
dblog_date=$(date -d "1 hours ago" +"%m%d%H")
#数据库日志文件名格式:/data/output_logs/dblog/db062510.log
dblog_file=/data/dblog/db${dbvpn_date}.log
#程序运行日志文件
logfile=/data/scrips/data_import_sql.log
echo -e "\n$(date +'%Y-%m-%d %H:%M:%S')" >> ${logfile}
#数据库信息:
HOSTNAME="192.168.149.128"
PORT="3306"
USERNAME="root"
PASSWORD="123456"
DBNAME="test" #数据库名称
#日志导入指令
dblog_insert="load data local infile '${dblog_file}' into table ri_dbaudit fields terminated BY ',' enclosed BY '\"' lines terminated by '\n' (logtime,dst_ip,src_ip,action,tabname,dbname,port,otype,status,dbuser);"
#数据库日志导入
if [ -f ${dblog_file} -a -s ${dblog_file} ] ;then
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${dblog_insert}"
echo "${dblog_file} import success" >> ${logfile}
find "${dblog_file}" -delete #导完后删除日志文件
else
echo "Error: ${dblog_file} not exist" >> ${logfile}
fi
任务计划:每小时10分执行程序
10 * * * * sh /data/scrips/dblog_import.sh >> /data/scrips/data_import_sql.log 2>&1