mysql的load data,高速将文本文件,插入数据库中

时间:2021-05-08 23:21:03

1语法


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

    [ 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,...] ]

关键字解释
  
  LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'

    [ REPLACE | IGNORE ]    

    INTO TABLE tbl_name

  1LOW_PRIORITY:   加载数据被延迟,直道没有其他客户端从表中读取为止  
   CONCURRENT:   当加载的数据正在进行的时,如果有用户请求则开启新的线程来获取数据  

  2如果指定了LOCAL 关键字,它将对连接的客户端做出解释:

    • 如果指定了LOCAL,客户端主机上的客户端组件读取文件并发送到服务器。可以给出文件的完整路径以确定其精确位置。如果给出的是相对路径,则文件名是相对于客户端组件启动时所在的目录。
    • 如果没有指定LOCAL,文件是位于服务器的主机上,并且直接被服务器读取。
      当从服务器主机定位文件时,服务器使用下列规则:
        • 如果给定完整的路径,服务器使用该路径名。
        • 如果给定一个或多个前置构件的相对路径,服务器以相对服务器的数据目录搜索文件。
        • 如果给定没有前置构件的文件名,服务器从当前数据库的数据库目录搜寻文件。

  3REPLACE 和IGNORE 关键字处理那些与已存在的主键值重复的输入记录
    如果指定了REPLACE,输入行将会代替已存在的行(也就是说,主索引值相同的行将作为存在的行)。参考REPLACE 语法 。
    如果指定了IGNORE,与已存在行主键值重复的输入行将被跳过。如果不指定二者中的任一个,则操作行为将依赖是否指定了LOCAL关键字。
    没有指定LOCAL,则如果发现有重复的键值,将产生一个错误,并忽略文本文件的其余部分。
    如果指定了LOCAL,则缺省的操作行为将与指定了IGNORE 的相同;这是因为,在操作过程中,服务器没有办法终止文件的传送。

  [ FIELDS

      [ TERMINATED BY 'string' ]

      [ [OPTIONALLY] ENCLOSED BY 'char' ]   //enclosed by描述的是字段的括起字符。

      [ ESCAPED BY 'char'  ]          //escaped by描述的转义字符。默认的是反斜杠 (backslash:\ )  

  ]
  [ LINES 

      [ STARTING BY 'string' ]

      [ TERMINATED BY 'string' ]

    ]
  4
  数据格式:
  "中华之子","100","威武的不行了"
  "大米","63","我爱吃"
  "苹果","15","好吃" 
     load data infile 'D:/aa.txt' ignore into table 表名 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\r\n' (表中的字段1,表中的字段2,表中的字段3);  
     terminated by ','  用什么风格字符
     enclosed by '"'    字符用什么包起来的
     lines terminated by '\r\n'   行的末尾换行符是什么(liunx改为\n)


  [ IGNORE number LINES ]

   [ (col_name_or_user_var,...) ]

   [ SET col_name = expr,...] ]

   5IGNORE number LINES选项可被用来忽略在文件的某一行

      假设数据文件如下:
 
      Book1.csv

        编号,名称,说明
        1,测试数据1,"测试CSV文件中,有逗号"
        2,测试数据2,"测试CSV文件中有""双引号"""
        3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
        4,测试数据4,普通数据
 
        mysql> CREATE TABLE Test_Book1 (
            ->   id    int,
            ->   name  VARCHAR(10),
            ->   data  VARCHAR(100)
            -> );
        Query OK, 0 rows affected (0.05 sec)
         
         
        下面的 lines terminated by '\r\n' 是 要求换行符号,为 windows的换行
        下面的 ignore 1 lines是 忽略第一行的标题行。
        mysql> LOAD DATA INFILE 'f:/Book1.csv'
            -> INTO TABLE Test_Book1
            -> FIELDS TERMINATED BY ','
            -> OPTIONALLY ENCLOSED BY '"'
            -> lines terminated by '\r\n'
            -> ignore 1 lines
            -> (id, name, data);

        mysql> select * from test_book1;
        +------+-----------+--------------------------------+
        | id   | name      | data                           |
        +------+-----------+--------------------------------+
        |    1 | 测试数据1 | 测试CSV文件中,有逗号           |
        |    2 | 测试数据2 | 测试CSV文件中有"双引号"        |
        |    3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |
        |    4 | 测试数据4 | 普通数据                       |
        +------+-----------+--------------------------------+

        ignore 1 lines 忽略第一行的标题行。
  
  6@dummy的使用
    假设数据文件如下:
 
    Book1.csv


      1,测试数据1,"测试CSV文件中,有逗号"
      2,测试数据2,"测试CSV文件中有""双引号"""
      3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
      4,测试数据4,普通数据

     mysql> CREATE TABLE Test_Book1 (
            ->   id    int,
            ->   name  VARCHAR(10),
            ->   data  VARCHAR(100)
            -> );
      Query OK, 0 rows affected (0.05 sec)
    我只需要第一,二列的数据的时候
      mysql> LOAD DATA INFILE 'f:/test_main.txt'
          -> INTO TABLE test_main6
          -> FIELDS TERMINATED BY ','
          -> OPTIONALLY ENCLOSED BY ''''
          -> (id,name, @dummy);

        mysql> select * from test_book1;
        +------+-----------+--------------------------------+
        | id   | name      | data                           |
        +------+-----------+--------------------------------+
        |    1 | 测试数据1 |                                |
        |    2 | 测试数据2 |                                |
        |    3 | 测试数据3 |                                |
        |    4 | 测试数据4 |                                |
        +------+-----------+--------------------------------+

        这里的关键点, 在于那个   @dummy。
        对你而言, 如果文件有 10列, 你只导 2 , 7 的话, 那就是
          @dummy,列2,@dummy,@dummy,@dummy,@dummy,列7,@dummy,@dummy,@dummy

  7set子句
      SET 子句可以提供不是来源于输入文件的值。下面的语句将column3 设置为当前的日期和时间:
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, column2)  
          SET column3 = CURRENT_TIMESTAMP;  
      可以让某一列的值参与一些操作
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, @var1)  
          SET column2 = @var1/100;  
      也可以让前后列的数据产生关系(假设第一列是一个日期格式的数据,我的第三列可以根据第一列的日期计算出星期信息然后存入数据库)
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, column2)  
          SET column3 = SET name=CASE WHEN DATE_FORMAT(date,'%w') =1 THEN "星期一" 
                                      WHEN DATE_FORMAT(date,'%w') =2 THEN "星期二" 
                                      end;

      使用列/变量列表和SET子句要受到一下限制:
        • SET 子句中的赋值列名应当只能在赋值操作符的左边。
        • 在SET 赋值语句中,可以使用子查询。此查询返回一个将被赋予列的值,它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。
        • 对于列/变量列表或SET 子句,由于使用IGNORE 子句而被忽略的行就不会被处理。
        • 由于用户变量没有显示宽度,当导入的数据是行固定格式时,将不能使用用户变量。

 

8设置存入时候使用的字符

gbk  utf8

 load data infile 'C:\\Users\\edgewalk\\Desktop\\test.txt' ignore into table test character set gbk fields terminated by ','
lines terminated by '\n' (
date,
money,
desctipt
)

2使用load data infile导入导出备份数据

  如果要导出一个表中的部分字段或者部分符合条件的记录,需要用到了mysql的into outfile 和 load data infile 。 例如下面的mysql命令是把select的mytable表中的数据导出到C:\\Users\\7q\\Desktop\\test1.txt。
  select * from test into outfile 'C:\\Users\\7q\\Desktop\\test1.txt' fields terminated by ','  lines terminated by '\r\n' ;

  假如要导入刚才备份的数据,可以使用load file方法,例如下面的mysql命令,把导出的数据导入了mytable_bak的表中:
  load data infile 'C:\\Users\\7q\\Desktop\\test1.txt' into table mytable_bak fields terminated by ',' lines terminated by '\r\n';
  这种方法的好处是,导出的数据可以自己规定格式,并且导出的是纯数据,不存在建表信息,你可以直接导入另外一个同数据库的不同表中.