一、网友需求
文档中有类此如a b c0e f g0i j k0x f f的字符串，需要以0分行，以空格分列导入到mysql数据库的一张只有三个列的表中
二、mysqlimport使用说明
mysqlimport Ver 3.7 Distrib 5.5.9, for Win32 (x86)
Copyright (c) 2000, 2010, 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:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.5\my.ini C:\Program Files\MySQL\MySQL Server 5.5\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.
-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.
-W, --pipe Use named pipes to connect to server.
-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.
--shared-memory-base-name=name
Base name of shared memory.
-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 auto
columns (No default value)
compress FALSE
debug-check FALSE
debug-info FALSE
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 (No default value)
ignore FALSE
ignore-lines 0
lines-terminated-by (No default value)
local FALSE
lock-tables FALSE
low-priority FALSE
port 3306
replace FALSE
shared-memory-base-name (No default value)
silent FALSE
socket (No default value)
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
三、模拟实现网友需求
1、创建一张import表，结构如下
mysql> desc xifenfei.import; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | char(1) | YES | | NULL | | | b | char(1) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.02 sec)
2、在D盘根目录下创建一个import.data文件，内容如下
a b c0e f g0i j k0x f f
3、实现导入
C:\Users\XIFENFEI>mysqlimport -uroot -pxifenfei --fields-terminated-by=" " --lines-terminated-by="0" --columns=a,b,c -d xifenfei d:/import.data xifenfei.import: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 --表明导入记录4条，删除0条，跳过0条，警告0条
4、验证导入结果
mysql> select * from xifenfei.import; +------+------+------+ | a | b | c | +------+------+------+ | a | b | c | | e | f | g | | i | j | k | | x | f | f | +------+------+------+ 4 rows in set (0.00 sec)
四、mysqlimport使用补充说明
1、文件名的第一个字符串需要和数据库中的表名一致(如:这里的import.data对应的表名就是import)
2、直接通过字符串指定数据库名称(如:这里的xifenfei)
3、行/列分隔符都使用双引号括起来(可能还有其他方案，但是这样方便)
