Mysql load data local命令詳解

1、load data local命令使用方法

不指定欄位名

此寫法資料檔案內欄位數必須跟資料庫表一致

load data local infile "C:\\test.txt" into table tableName CHARACTER SET gbk fields terminated by '|' LINES TERMINATED by '\n';

自定義列匯入（推薦使用）

在生產環境中推薦使用自定義列匯入，這樣如果是不需要的欄位，就可以不接這個欄位內容，這樣就不必改表結構。還可以做成資料庫設定等進行維護。

//不需要的列直接用@過濾掉就可以了(可以使用同一個變數名比如@a，或者使用我下面不同的變數名加上@符號)
//不需要的列的列名可以隨便定義，但是沒有加@符的需要跟資料庫表欄位名一一對應。
// 不需要的列也需要跟資料檔案中的欄位一一對應，類似於佔個坑，但是不進行資料繫結。
load data local infile "C:\\test.txt" into table tableName CHARACTER SET gbk fields terminated by '|' LINES TERMINATED by '\n' (c1,@2,c3,@4, c5,@6,c7,@8,c9,@10);
// set命令可以設定預設值,可以直接賦值, 也可以使用if判斷，跟update 差不多。
load data local infile "C:\\test.txt" into table tableName CHARACTER SET gbk fields terminated by '|' LINES TERMINATED by '\n'  (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) set c1 = '1', c9=if(c9='0000-00-00',now(),c9)

資料檔案資料樣例

1|test|3|tmp|5
6|string|8|a|10

2、命令解析

infile "C:\\test.txt"  // 這是你的資料檔案存放的位置  最好使用雙引號和使用跳脫符進行路徑跳脫
into table tableName  // 這是你資料庫的表名
CHARACTER SET gbk  // 這是以什麼字元編碼讀取你的資料檔案，當你的檔案和資料庫不對應的時候需要設定（對應的話就不需要）。當你的資料檔案是gbk的時候你需要設定成gbk。
fields terminated by '|'  // 這個是你的資料檔案欄位是以什麼方式進行分割的。這個是以豎線分割
LINES TERMINATED by '\n'  // 這個是你的資料檔案一條資料是以什麼方式區分的。這裡是以換行進行區分的。
(c1,c2,c3,c4,c5,c6,c7,c8,c9,@c10)   // 指定列匯入，跟資料庫表欄位對應，不需要的列加@符佔個坑位。不需要的列名可隨便定義，需要的列名跟資料庫欄位對應。
set c1 = '1', c9=if(c9='0000-00-00',now(),c9)  // 給匯入的資料設定值，這裡判斷的值是匯入後的值，比如這個例子是當一個時間型別的資料時在資料檔案裡是個空的時候匯入到表中對應是個0000-00-00  而不是null。所以這裡要用0000-00-00，而不是""。

3、注意事項

匯入的列不會為null 始終會有一個預設值，
當資料檔案為null的時候如果你的欄位型別是int型別，那麼匯入的值會為0，
如果是double型別，那麼匯入的就會為0.0資料庫保留幾位小數，則帶幾位小數
如果是varcher型別，則是空字串
最重要的一點，在資料庫版本為5.*的版本，會有一個預設時間的bug，當你時間格式為null 的時候，資料庫會預設幫你填充0000-00-00 然而這個時間不合法的，假如你需要查到java 中是無法進行繫結的，而且資料庫也不認這個時間，假如你要複製這條資料到另外一個表中，是無法插入的。所以在這裡建議大家時間設定成varchar型別，或者在load 的時候進行判斷把這個時間替換掉。