MySQL的文本导入之loaddatalocal

数据文件格式：

成都创新互联拥有网站维护技术和项目管理团队，建立的售前、实施和售后服务体系，为客户提供定制化的做网站、网站建设、网站维护、达州托管服务器解决方案。为客户网站安全和日常运维提供整体管家式外包优质服务。我们的网站维护服务覆盖集团企业、上市公司、外企网站、成都做商城网站、政府网站等各类型客户群体，为全球千余家企业提供全方位网站维护、服务器维护解决方案。

Aeschylus time as he grows old teaches many lessons

Alexander Graham Bell Mr.Watson,come here.i want you!

Benjamin Franklin it is hard for an empty bag to stand upright

Benjamin Franklin little strokes fell great oaks

--字段之间是tab，其它是空格

导入命令：

drop table aa;

create table aa(a varchar(40),tt text);

load data local infile 'a.txt' into table aa;

select * from aa;

过程：

root@localhost[lhrdb]> drop table aa;

Query OK, 0 rows affected (0.17 sec)

root@localhost[lhrdb]> create table aa(a varchar(40),tt text);

Query OK, 0 rows affected (0.30 sec)

root@localhost[lhrdb]> load data local infile 'a.txt' into table aa;

Query OK, 4 rows affected (0.07 sec)

Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

root@localhost[lhrdb]> select * from aa;

+-----------------------+----------------------------------------------+

| a                     | tt                                           |

+-----------------------+----------------------------------------------+

| Aeschylus | time as he grows old teaches many lessons |

| Alexander Graham Bell | Mr.Watson,come here.i want you! |

| Benjamin Franklin | it is hard for an empty bag to stand upright |

| Benjamin Franklin | little strokes fell great oaks |

+-----------------------+----------------------------------------------+

4 rows in set (0.00 sec)

帮助：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

root@localhost[lhrdb]> help load data

Name: 'LOAD DATA'

Description:

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name,...)]

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number {LINES | ROWS}]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]

The LOAD DATA INFILE statement reads rows from a text file into a table

at a very high speed. LOAD DATA INFILE is the complement of SELECT ...

INTO OUTFILE. (See

http://dev.MySQL.com/doc/refman/5.7/en/select-into.html.) To write data

from a table to a file, use SELECT ... INTO OUTFILE. To read the file

back into a table, use LOAD DATA INFILE. The syntax of the FIELDS and

LINES clauses is the same for both statements. Both clauses are

optional, but FIELDS must precede LINES if both are specified.

You can also load data files by using the mysqlimport utility; it

operates by sending a LOAD DATA INFILE statement to the server. The

--local option causes mysqlimport to read data files from the client

host. You can specify the --compress option to get better performance

over slow networks if the client and server support the compressed

protocol. See http://dev.mysql.com/doc/refman/5.7/en/mysqlimport.html.

For more information about the efficiency of INSERT versus LOAD DATA

INFILE and speeding up LOAD DATA INFILE, see

http://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html.

The file name must be given as a literal string. On Windows, specify

backslashes in path names as forward slashes or doubled backslashes.

The character_set_filesystem system variable controls the

interpretation of the file name.

LOAD DATA supports explicit partition selection using the PARTITION

option with a comma-separated list of one or more names of partitions,

subpartitions, or both. When this option is used, if any rows from the

file cannot be inserted into any of the partitions or subpartitions

named in the list, the statement fails with the error Found a row not

matching the given partition set. For more information, see

http://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html.

For partitioned tables using storage engines that employ table locks,

such as MyISAM, LOAD DATA cannot prune any partition locks. This does

not apply to tables using storage engines which employ row-level

locking, such as InnoDB. For more information, see

http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking

.html.

The server uses the character set indicated by the

character_set_database system variable to interpret the information in

the file. SET NAMES and the setting of character_set_client do not

affect interpretation of input. If the contents of the input file use a

character set that differs from the default, it is usually preferable

to specify the character set of the file by using the CHARACTER SET

clause. A character set of binary specifies "no conversion."

LOAD DATA INFILE interprets all fields in the file as having the same

character set, regardless of the data types of the columns into which

field values are loaded. For proper interpretation of file contents,

you must ensure that it was written with the correct character set. For

example, if you write a data file with mysqldump -T or by issuing a

SELECT ... INTO OUTFILE statement in mysql, be sure to use a

--default-character-set option so that output is written in the

character set to be used when the file is loaded with LOAD DATA INFILE.

*Note*:

It is not possible to load data files that use the ucs2, utf16,

utf16le, or utf32 character set.

If you use LOW_PRIORITY, execution of the LOAD DATA statement is

delayed until no other clients are reading from the table. This affects

only storage engines that use only table-level locking (such as MyISAM,

MEMORY, and MERGE).

If you specify CONCURRENT with a MyISAM table that satisfies the

condition for concurrent inserts (that is, it contains no free blocks

in the middle), other threads can retrieve data from the table while

LOAD DATA is executing. This option affects the performance of LOAD

DATA a bit, even if no other thread is using the table at the same

time.

With row-based replication, CONCURRENT is replicated regardless of

MySQL version. With statement-based replication CONCURRENT is not

replicated prior to MySQL 5.5.1 (see Bug #34628). For more information,

see

http://dev.mysql.com/doc/refman/5.7/en/replication-features-load-data.h

tml.

The LOCAL keyword affects expected location of the file and error

handling, as described later. LOCAL works only if your server and your

client both have been configured to permit it. For example, if mysqld

was started with the local_infile system variable disabled, LOCAL does

not work. See

http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html.

The LOCAL keyword affects where the file is expected to be found:

o If LOCAL is specified, the file is read by the client program on the

client host and sent to the server. The file can be given as a full

path name to specify its exact location. If given as a relative path

name, the name is interpreted relative to the directory in which the

client program was started.

When using LOCAL with LOAD DATA, a copy of the file is created in the

server's temporary directory. This is not the directory determined by

the value of tmpdir or slave_load_tmpdir, but rather the operating

system's temporary directory, and is not configurable in the MySQL

Server. (Typically the system temporary directory is /tmp on Linux

systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for

the copy in this directory can cause the LOAD DATA LOCAL statement to

fail.

o If LOCAL is not specified, the file must be located on the server

host and is read directly by the server. The server uses the

following rules to locate the file:

o If the file name is an absolute path name, the server uses it as

given.

o If the file name is a relative path name with one or more leading

components, the server searches for the file relative to the

server's data directory.

o If a file name with no leading components is given, the server

looks for the file in the database directory of the default

database.

In the non-LOCAL case, these rules mean that a file named as

./myfile.txt is read from the server's data directory, whereas the file

named as myfile.txt is read from the database directory of the default

database. For example, if db1 is the default database, the following

LOAD DATA statement reads the file data.txt from the database directory

for db1, even though the statement explicitly loads the file into a

table in the db2 database:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Non-LOCAL load operations read text files located on the server. For

security reasons, such operations require that you have the FILE

privilege. See

http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html. Also,

non-LOCAL load operations are subject to the secure_file_priv system

variable setting. If the variable value is a nonempty directory name,

the file to be loaded must be located in that directory. If the

variable value is empty (which is insecure), the file need only be

readable by the server.

Using LOCAL is a bit slower than letting the server access the files

directly, because the contents of the file must be sent over the

connection by the client to the server. On the other hand, you do not

need the FILE privilege to load local files.

LOCAL also affects error handling:

o With LOAD DATA INFILE, data-interpretation and duplicate-key errors

terminate the operation.

o With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key

errors become warnings and the operation continues because the server

has no way to stop transmission of the file in the middle of the

operation. For duplicate-key errors, this is the same as if IGNORE is

specified. IGNORE is explained further later in this section.

The REPLACE and IGNORE keywords control handling of input rows that

duplicate existing rows on unique key values:

o If you specify REPLACE, input rows replace existing rows. In other

words, rows that have the same value for a primary key or unique

index as an existing row. See [HELP REPLACE].

o If you specify IGNORE, rows that duplicate an existing row on a

unique key value are discarded. For more information, see

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#ignore-strict-co

mparison.

o If you do not specify either option, the behavior depends on whether

the LOCAL keyword is specified. Without LOCAL, an error occurs when a

duplicate key value is found, and the rest of the text file is

ignored. With LOCAL, the default behavior is the same as if IGNORE is

specified; this is because the server has no way to stop transmission

of the file in the middle of the operation.

URL: http://dev.mysql.com/doc/refman/5.7/en/

load<            
            
                        

            当前标题：MySQL的文本导入之loaddatalocal            

            新闻来源：http://cqcxhl.cn/article/gejeci.html


    
    
        其他资讯
        
            
                    关于c语言常量的正确表示方法介绍-创新互联
                

                    win2008r2服务器安全配置步骤小结原创-创新互联
                

                    layui弹出层如何使用-创新互联
                

                    C#.NET添加图片水印-创新互联
                

                    unbound搭建递归dns-创新互联
                
        
    


    
        
            
                
                
                    分享至：
                    
                        
                        
                        
                        
                    
                    
                
            
            
                
                    关于我们
                    
                        公司简介
                        发展历程
                    
                
                
                    服务项目
                    
                        高端网站建设
                        小程序开发
                        APP开发
                        网络营销
                    
                
                
                    建站知识
                    
                        网站建设
                        网站设计
                        网站制作
                        小程序
                    
                
                
                    联系我们
                    
                        公司地址
                        人才招聘
                    
                
            
            
                
                    成都
                    重庆
                
                
                    
                        
                            
                            电话：028-86922220
                        
                        
                            
                            地址：成都市太升南路288号锦天国际A幢1002号
                        
                    
                    
                        
                            
                            电话：028-86922220
                        
                        
                            
                            地址：重庆市南岸区弹子石腾龙大道58号2栋21-6
                        
                    
                
            
        
    
    版权所有：成都创新互联科技有限公司重庆分公司
        渝ICP备2021005571号-4
    




    
        
            
            在线咨询
        
    
    
        
            
            服务热线
        
        服务热线：028-86922220
    
    
        
            
            TOP

重庆分公司，新征程启航

MySQL的文本导入之loaddatalocal

其他资讯