October 25 2013

mysqlimport

[root@ucjmh ~]# mysqlimport -hlep
mysqlimport  Ver 3.7 Distrib 5.5.41, for Linux (i686)
Copyright (c) 2000, 2014, 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.

Loads tables from text files in various formats.  The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command ‘LOAD DATA INFILE’ is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile…
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
–print-defaults        Print the program argument list and exit.
–no-defaults           Don’t read default options from any option file.
–defaults-file=#       Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.
–character-sets-dir=name
Directory for character set files.
–default-character-set=name
Set the default character set.
-c, –columns=name  Use only these columns to import the data to. Give the
column names in a comma separated list. This is same as
giving columns to LOAD DATA INFILE.
-C, –compress      Use compression in server/client protocol.
-#, –debug[=name]  Output debug log. Often this is ‘d:t:o,filename’.
–debug-check       Check memory and open file usage at exit.
–debug-info        Print some debug info at exit.
–default-auth=name Default authentication client-side plugin to use.
-d, –delete        First delete all rows from table.
–fields-terminated-by=name
Fields in the input file are terminated by the given
string.
–fields-enclosed-by=name
Fields in the import file are enclosed by the given
character.
–fields-optionally-enclosed-by=name
Fields in the input file are optionally enclosed by the
given character.
–fields-escaped-by=name
Fields in the input file are escaped by the given
character.
-f, –force         Continue even if we get an SQL error.
-?, –help          Displays this help and exits.
-h, –host=name     Connect to host.
-i, –ignore        If duplicate unique key was found, keep old row.
–ignore-lines=#    Ignore first n lines of data infile.
–lines-terminated-by=name
Lines in the input file are terminated by the given
string.
-L, –local         Read all files through the client.
-l, –lock-tables   Lock all tables for write (this disables threads).
–low-priority      Use LOW_PRIORITY when updating the table.
-p, –password[=name]
Password to use when connecting to server. If password is
not given it’s asked from the tty.
–plugin-dir=name   Directory for client-side plugins.
-P, –port=#        Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
–protocol=name     The protocol to use for connection (tcp, socket, pipe,
memory).
-r, –replace       If duplicate unique key was found, replace old row.
-s, –silent        Be more silent.
-S, –socket=name   The socket file to use for connection.
–ssl               Enable SSL for connection (automatically enabled with
other flags).
–ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
–ssl).
–ssl-capath=name   CA directory (check OpenSSL docs, implies –ssl).
–ssl-cert=name     X509 cert in PEM format (implies –ssl).
–ssl-cipher=name   SSL cipher to use (implies –ssl).
–ssl-key=name      X509 key in PEM format (implies –ssl).
–ssl-verify-server-cert
Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by
default.
–use-threads=#     Load files in parallel. The argument is the number of
threads to use for loading data.
-u, –user=name     User for login if not current user.
-v, –verbose       Print info about the various stages.
-V, –version       Output version information and exit.

Variables (–variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
——————————— —————————————-
character-sets-dir                (No default value)
default-character-set             utf8
columns                           (No default value)
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
default-auth                      (No default value)
delete                            FALSE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
force                             FALSE
host                              lep
ignore                            FALSE
ignore-lines                      0
lines-terminated-by               (No default value)
local                             FALSE
lock-tables                       FALSE
low-priority                      FALSE
plugin-dir                        (No default value)
port                              3306
replace                           FALSE
silent                            FALSE
socket                            /var/lib/mysql/mysql.sock
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
use-threads                       0
user                              (No default value)
verbose                           FALSE
mysqlimport位于mysql/bin目录中,是mysql的一个载入(或者说导入)数据的一个非常有效的工具。这是一个命令行工具。有两个参数 以及大量的选项可供选择。这个工具把一个文本文件(text file)导入到你指定的数据库和表中。比方说我们要从文件table.txt中把数据导入到数据库db中的表table中:

mysqlimport db table.txt
文件名不可以有多个.
-d or –delete      新数据导入数据表中之前删除数据数据表中的所有信息
-f or –force       不管是否遇到错误,mysqlimport将强制继续插入数据
-i or –ignore       mysqlimport跳过或者忽略那些有相同唯一
关键字的行, 导入文件中的数据将被忽略。
-l or -lock-tables  数据被插入之前锁住表,这样就防止了,
你在更新数据库时,用户的查询和更新受到影响。
-r or -replace        这个选项与-i选项的作用相反;此选项将替代
表中有相同唯一关键字的记录。
–fields-enclosed- by= char
指定文本文件中数据的记录时以什么括起的, 很多情况下
数据以双引号括起。 默认的情况下数据是没有被字符括起的。
–fields-terminated- by=char
指定各个数据的值之间的分隔符,在句号分隔的文件中,
分隔符是句号。您可以用此选项指定数据之间的分隔符。
默认的分隔符是跳格符(Tab)
–lines-terminated- by=str
此选项指定文本文件中行与行之间数据的分隔字符串
或者字符。 默认的情况下mysqlimport以newline为行分隔符。
您可以选择用一个字符串来替代一个单个的字符:
一个新行或者一个回车。
mysqlimport命令常用的选项还有-v 显示版本(version), -p 提示输入密码(password)等
当文件是
“1”, “ORD89876”, “1 Dozen Roses”, “19991226”
可以使用
–prl –fields-enclosed-by=” –fields-terminated-by=,

 

示:

shell> mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' test
shell> ed
a
100     Max Sydow
101     Count Dracula
.
w imptest.txt
32
q
shell> od -c imptest.txt
0000000   1   0   0  \t   M   a   x       S   y   d   o   w  \n   1   0
0000020   1  \t   C   o   u   n   t       D   r   a   c   u   l   a  \n
0000040
shell> mysqlimport --local test imptest.txt
test.imptest: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
shell> mysql -e 'SELECT * FROM imptest' test
+------+---------------+
| id   | n             |
+------+---------------+
|  100 | Max Sydow     |
|  101 | Count Dracula |
+------+---------------+


Copyright 2019. All rights reserved.

Posted 2013年10月25日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *