MySQL中导入txt文件

朴灿烈づ我的快乐病毒、 2024-02-17 21:59 12阅读 0赞

## 1 txt文件导入步骤 ##

### 1.1 表tt的格式： ###

CREATE TABLE `tt` (
      `ind` int NOT NULL auto_increment,
      `name` char(100) default NULL,
      PRIMARY KEY  (`ind`)
     )

### 1.2 文件d.txt的内容示例: ###

1,a
     2,b
     3,c

### 1.3 导入命令： ###

mysql> load data infile 'd.txt' into table tt
        -> fields terminated by','
        -> lines terminated by'\r\n'

注意的地方：  
1）、文件可以使用绝对路径如'c:/d.txt'，否则请将文件放在数据库根目录中  
2）、因为字段之间用了逗号隔开，所以必须fields terminated by','，否则导入失败  
3）、因为Winsows中行以“\\r\\n”隔开，所以必须lines terminated by'\\r\\n'，  
如果不设置这个参数，也可导入成功，但是会多导入一个“\\r”控制字符，可能在可视化  
MySQL工具中看不出字段异样，但是在MySQL命令行中显示会明显混乱。  
4）、如果表tt非空，且文件中的ind值在表中有重复，会提示错误，并导入失败。  
  
**只导入name字段，文件d.txt的内容:**

a
     b
     c

mysql> load data infile 'd.txt' into table tt
        -> lines terminated by'\r\n'
        -> (name);

load data 命令还支持更复杂的文本格式、文件编码等，可参考官方文档。

### 1.4 导出到数据到windows文本文件 ###

为了方便查看，也需要相同的设置

mysql> select * from tt into outfile 'd.txt'
        -> fields terminated by','
        -> lines terminated by'\r\n'

## 附：官方文档 ##

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
        [REPLACE | IGNORE]
        INTO TABLE tbl_name
        [FIELDS
            [TERMINATED BY 'string']
            [[OPTIONALLY] ENCLOSED BY 'char']
            [ESCAPED BY 'char']
        ]
        [LINES
            [STARTING BY 'string']
            [TERMINATED BY 'string']
        ]
        [IGNORE number LINES]
        [(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. 
    The filename must be given as a literal string.
    
    LOAD DATA INFILE is the complement of SELECT ... INTO OUTFILE. (See [select].) 
    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.
    
    For more information about the efficiency of INSERT versus LOAD DATA INFILE and speeding up LOAD DATA INFILE, 
    see [insert-speed].
    
    The character set indicated by the character_set_database system variable is used to interpret the information 
    in the file. 
    SET NAMES and the setting of character_set_client do not affect interpretation of input.
    
    Note that it is currently not possible to load data files that use the ucs2 character set.
    
    As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the filename.
    
    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 [mysqlimport].
    
    If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading
    from the table.
    
    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. 
    Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table 
    at the same time.
    
    The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:
    
    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 pathname to specify its exact location. 
      If given as a relative pathname, the name is interpreted relative to the directory in which the client 
      program was started.
    
    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 filename is an absolute pathname, the server uses it as given.
    
      o If the filename is a relative pathname with one or more leading components, the server searches for the 
      file relative to the server's data directory.
    
      o If a filename with no leading components is given, the server looks for the file in the database
      directory of the default database.
    
    Note that, 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;
    
    Windows pathnames are specified using forward slashes rather than backslashes. If you do use backslashes, 
    you must double them.
    
    For security reasons, when reading text files located on the server, the files must either reside in the 
    database directory or be readable by all. 
    Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See [privileges-provided].