知名网站制作全包,江西企业网站建设费用,标书制作员工资很低吗,代刷网站开发Usage: mysqlimport [OPTIONS] database textfile …mysqlimport 程序是一个将以特定格式存放的文本数据(如通过“select * into OUTFILE from …”所生成的数据文件)导入到指定的MySQL Server 中的工具程序#xff0c;比如将一个标准的csv 文件导入到某指定数据库的指定表中。…Usage: mysqlimport [OPTIONS] database textfile …mysqlimport 程序是一个将以特定格式存放的文本数据(如通过“select * into OUTFILE from …”所生成的数据文件)导入到指定的MySQL Server 中的工具程序比如将一个标准的csv 文件导入到某指定数据库的指定表中。mysqlimport 工具实际上也只是“load data infile”命令的一个包装实现。默认从以下路径中文件读取默认参数/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf1、常用选项--fields-terminated-by字符串设置字符串为字段之间的分隔符可以为单个或多个字符。默认值为制表符“\t”。-L, –local表示从客户端任意路径读取文件导入表中未设置该选项时默认只从datadir下同名数据库目录下读取文件导入--ignore-linesn表示可以忽略前n行。-l, –lock-tables写入时锁定所有表-p, –password[name]指定用户密码-u, –username指定登入MySQL用户名-h, –hostname指定远程连接的服务器-c, –columnsname往表里导入指定字段如–columns’Name,Age,Gender’-C, –compress在客户端和服务器之间启用压缩传递所有信息其它可用选项和默认参数设置可以使用mysqlimport -help查询2、用法示例例1基本用法mysql create table classes3 like classes;Query OK, 0 rows affected (0.07 sec)[rootwww tmp]# mysqlimport -u root --localhellodb classes3.sql --fields-terminated-by|hellodb.classes3: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0mysql select * from classes3;-----------------------------------| ClassID | Class | NumOfStu |-----------------------------------| 1 | Shaolin Pai | 10 || 2 | Emei Pai | 7 || 3 | QingCheng Pai | 11 || 4 | Wudang Pai | 12 || 5 | Riyue Shenjiao | 31 || 6 | Lianshan Pai | 27 || 7 | Ming Jiao | 27 || 8 | Xiaoyao Pai | 15 || 9 | HuaShan Pai | 32 || 10 | Fuwei Biaoju | 19 |-----------------------------------10 rows in set (0.00 sec)例2指定–local选项可以从本机任意路径导入数据mysql create table classes2 likeclasses;Query OK, 0 rows affected (0.14 sec):[rootwww tmp]# cp classes2.sql /tmp[rootwww tmp]# mysqlimport -u root --localhellodb /tmp/classes2.sqlhellodb.classes2: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0mysql select * from classes2;-----------------------------------| ClassID | Class | NumOfStu |-----------------------------------| 1 | Shaolin Pai | 10 || 2 | Emei Pai | 7 || 3 | QingCheng Pai | 11 || 4 | Wudang Pai | 12 || 5 | Riyue Shenjiao | 31 || 6 | Lianshan Pai | 27 || 7 | Ming Jiao | 27 || 8 | Xiaoyao Pai | 15 || 9 | HuaShan Pai | 32 || 10 | Fuwei Biaoju | 19 |-----------------------------------10 rows in set (0.00 sec)例3未指定–local选项无法从my.cnf中定义的其它路径中往表里导入数据mysql delete from classes2;Query OK, 10 rows affected (0.01 sec)[rootwww ~]# head /tmp/classes2.sql -n 31 ShaolinPai 102 EmeiPai 73 QingChengPai 11[rootwww ~]# mysqlimport -u root hellodb/tmp/classes2.sqlmysqlimport: Error: 29, File /tmp/classes2.sqlnot found (Errcode: 13), when using table: classes2例4未指定–local选项默认只从mysql数据存放路径同名数据库目录下读取文件导入表中必须指定绝对路径。mysql delete from students1;Query OK, 27 rows affected (2.60 sec)[rootwww tmp]# sed s/\t/\|/gstudents.sql students1.sql[rootwww tmp]# head -n 2 students1.sql1|Shi Zhongyu|22|M|2|32|Shi Potian|22|M|1|7[rootwww tmp]# cd[rootwww ~]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated|mysqlimport: Error: 13, Cant get stat of/var/lib/mysql/hellodb/students1.sql (Errcode: 2), when using table:students1未设置–local选项时默认只从mysql数据存放路径同名数据库目录下读取文件导入[rootwww ~]# mysqlimport -u mhauser-p888888 hellodb /var/lib/mysql/tmp/students1.sql--fields-terminated|hellodb.students1: Records: 27 Deleted: 0 Skipped: 0 Warnings: 0例5数据库存放表目录下同名文件导入表中只需指定文件名mysql delete from students1;Query OK, 27 rows affected (0.47 sec)[rootwww ~]# cd /var/lib/mysql/hellodb/[rootwww hellodb]# cp ../tmp/students1.sql.将数据移到hellodb目录下成功导入数据[rootwww hellodb]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated|hellodb.students1: Records: 27 Deleted: 0 Skipped: 0 Warnings: 0--fields-terminated|指定字段分隔符mysql select * from students1 limit5,3;---------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |---------------------------------------------------| 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL |---------------------------------------------------3 rows in set (0.00 sec)例6忽略前5行数据导入表中[rootwww tmp]# mysqlimport -u root --localhellodb classes2.sql --ignore-lines5hellodb.classes2: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0--ignore-linesn指定忽略前n行mysql select * from classes2;---------------------------------| ClassID | Class | NumOfStu |---------------------------------| 6 | Lianshan Pai | 27 || 7 | Ming Jiao | 27|| 8 | Xiaoyao Pai | 15 || 9 | HuaShan Pai | 32 || 10 | Fuwei Biaoju | 19 |---------------------------------5 rows in set (0.00 sec)例7往非空表中导入数据[rootwww hellodb]# students1.sql[rootwww hellodb]# vim students1.sql[rootwww hellodb]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated|hellodb.students1: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0[rootwww hellodb]# more students1.sql|Meng Qi D|17|M|2|3|SuoLong|22|M|1|7|Xiang Kesi|43|M|2|16|KaiDuo|52|M|4|4|JoBa|12|M|3|1|Nami|18|F|4|1[rootwww hellodb]#mysql select * from students1 limit27,6;----------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |----------------------------------------------------| 28 | Meng Qi D | 17 | M | 2 | 3 || 29 | SuoLong | 22 | M | 1 | 7 || 30 | Xiang Kesi | 43 | M | 2 | 16 || 31 | KaiDuo | 52 | M | 4 | 4 || 32 | JoBa | 12 | M | 3 | 1 || 33 | Nami | 18 | F | 4 | 1 |----------------------------------------------------6 rows in set (0.17 sec)mysql select count(*) from students1 ;----------| count(*) |----------| 33 |----------1 row in set (0.03 sec)数据会追加在表后例8、远程连接MySQL服务器导入特定字段mysql drop table students1;Query OK, 0 rows affected (2.89 sec)mysql create table students1 likestudents;Query OK, 0 rows affected (1.57 sec)[roottest mysql]# more /tmp/students1.sqlMeng Qi D|17|MSuoLong|22|MXiang Kesi|43|MKaiDuo|52|MJoBa|12|M|Nami|18|F|Luo Bing|25|FWu Suopu|20|M[roottest mysql]# mysqlimport -h192.168.88.131 -u mhauser -p888888 hellodb --local --fields-terminated-by| /tmp/students1.sql--columnsName,Age,Genderhellodb.students1: Records: 8 Deleted: 0 Skipped: 0 Warnings: 0--columns’Name,Age,Gender’指定导入那些字段-h 192.168.88.131指定远程登录主机名mysql select * from students1;----------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |----------------------------------------------------| 1 | Meng Qi D | 17 | M | NULL | NULL || 2 | SuoLong | 22 | M | NULL | NULL || 3 | Xiang Kesi | 43 | M | NULL | NULL || 4 | KaiDuo | 52 | M | NULL | NULL || 5 | JoBa | 12 | M | NULL | NULL || 6 | Nami | 18 | F | NULL | NULL || 7 | Luo Bing | 25 | F | NULL | NULL || 8 | Wu Suopu | 20 | M | NULL | NULL |----------------------------------------------------8 rows in set (0.01 sec)例9、远程连接MySQL服务器导入特定字段采用压缩传递数据的形式mysql drop table students1;Query OK, 0 rows affected (0.75 sec)mysql create table students1 likestudents;Query OK, 0 rows affected (0.66 sec)[roottest mysql]# mysqlimport -h192.168.88.131 -u mhauser -p888888 hellodb --local -C --fields-terminated-by| /tmp/students1.sql--columnsName,ClassID,Genderhellodb.students1: Records: 8 Deleted: 0 Skipped: 0 Warnings: 0-C指定压缩方式传递数据mysql select * from students1;----------------------------------------------------| StuID | Name | Age | Gender | ClassID | TeacherID |----------------------------------------------------| 1 | Meng Qi D | 0 | M | 17 | NULL || 2 | SuoLong | 0 | M | 22 | NULL || 3 | Xiang Kesi | 0 | M | 43 | NULL || 4 | KaiDuo | 0 | M | 52 | NULL || 5 | JoBa | 0 | M | 12 | NULL || 6 | Nami | 0 | F | 18 | NULL || 7 | Luo Bing | 0 | F | 25 | NULL || 8 | Wu Suopu | 0 | M | 20 | NULL |----------------------------------------------------8 rows in set (0.00 sec)https://www.cnblogs.com/shamo89/tag/mysql/default.html
