mysql的load data,高速將文字檔,插入資料庫中

標籤：string   nbsp   char   wal   路徑名   規則   用戶端   bak   csv檔案   

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 忽略第一行的標題列。
  
  [email protected]的使用
    假設資料檔案如下：
 
    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‘;
  這種方法的好處是，匯出的資料可以自己規定格式，並且匯出的是純資料，不存在建表資訊，你可以直接匯入另外一個同資料庫的不同表中.

 

mysql的load data,高速將文字檔,插入資料庫中