MySQL如何匯入格式化數據

在MySQL中如何匯入格式化資料？這篇文章就來跟大家介紹MySQL導入格式化資料的方法，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

在有些場景下，我們需要把一批特定格式的資料匯入到mysql資料庫中，做法有很多，使用shell腳本、python都可以，今天來介紹兩個更方便的指令，mysqlimport 和load data

mysqlimport

使用方法

mysqlimport [options] db_name textfile1 [textfile2 ...]
# db_name 对应数据库名称
# textfile 文件名，对应要插入的数据库表名
# 比如：mysqlimport a8 campaign.txt 会把campaign.txt中的数据插入到a8数据库中的campaign表

#注意： campaign.txt檔案必須在/usr/local/mysql/var/a8/目錄下，也就是資料庫所在目錄

如果不在對應的目錄下，會傳回下列錯誤：

mysqlimport -uroot -p123456 -hlocalhost -P3306 a8 campaign.txt 
/usr/local/mysql/bin/mysqlimport: Error: File '/usr/local/mysql/var/a8/campaign.txt' not found (Errcode: 2), when using table: campaign

常用選項

--columns=id,name,creator...
# 对应的数据表列名，定义被导入文件中的每一列对应的数据库表列名

--fields-terminated-by=
# 文件字段以什么分隔，参数为字符串，默认为\t

--fields-enclosed-by=
# 数据域用什么符号扩起来，默认为空，一般可以是双引号、括号等

--fields-optionally-enclosed-by=
# 数据域可以用什么符号括起来，因为为只有部分数据用这些符号括起来

--fields-escaped-by=
# 转义字符，参数为字符，默认为\

--lines-terminated-by=
# 数据行以什么结束，参数为字符串，windows默认为\r\n

--user=user_name 或 -u user_name
--password=[password] 或 -p[password]
--host=host_name 或 -h hostname
--port=port_num，或 -P port_num
# 定义用户名、密码、mysql服务器地址和用于连接的TCP/IP端口号，默认为mysql默认端口3306

--ignore-lines=n
# 忽视数据文件的前n行，因为很多数据文件前面有表头

--delete -D
# 在把文件中的数据插入前删除表中原先的数据

--local -L
# 指定从客户端电脑读入数据文件，否则从服务器电脑读取

--lock-tables -l
# 处理文本文件前锁定所有表以便写入，确保所有表在服务器上保持同步

--protocol={TCP | SOCKET | PIPE | MEMORY}
使用的连接协议

--force -f
#忽视错误。例如，如果某个文本文件的表不存在，继续处理其它文件，不使用--force，如果表不存在则mysqlimport退出

--compress -C
# 压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）

--silent，-s
# 沉默模式，只有出现错误时才输出

--socket=path，-S path
# 当连接localhost时使用的套接字文件(为默认主机)

--verbose，-v
# 冗长模式。打印出程序操作的详细信息。

--version，-V
# 显示版本信息并退出。

load data

#使用方法

mysql> load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields]
[terminated by '\t']
[OPTIONALLY] enclosed by '']
[escaped by '\' ]]
[lines terminated by 'n']
[ignore number lines]
[(id,name,creator)]

說明：

load data infile語句是將文字資料匯入資料表。使用這個指令之前，mysqld進程（服務）必須已經在運作。使用前請確保有檔案的讀取權限

1、如果你指定關鍵字low_priority，那麼MySQL將會等到沒有其他人讀這個表的時候，才把插入資料。可以使用如下的命令：<pre class="brush:php;toolbar:false">mysql&gt; load data low_priority infile &quot;/home/root/data.sql&quot; into table campaign;</pre>2、如果指定local關鍵字，則表示從客戶主機讀取檔案。如果local沒指定，則檔案必須位於伺服器上。
3、replace和ignore關鍵字控制對現有的唯一鍵記錄的重複的處理。如果你指定replace，新行將取代有相同的唯一鍵值的現有行。如果你指定ignore，跳過有唯一鍵的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複鍵時，出現一個錯誤，並且文字檔案的餘下部分被忽略。例如：

mysql> load data low_priority infile "/home/root/data.sql" replace into table campaign;

4、分隔符號

1）fields關鍵字指定了檔案欄位的分割格式，如果用到這個關鍵字，MySQL剖析器希望看到至少有下面的一個選項：

terminated by：分隔符，字段是以什么字符作为分隔符
enclosed by：字段括起字符，例：` "周丽","10","学习很好" ` 这样的一行，就需要这么写 ` ENCLOSED BY '"' `
escaped by：转义字符
lines terminated by：描述字段的分隔符，默认情况下是tab字符（\t） 
ignore number lines:用来忽略导入文件的开始的行。例如：number＝1，则忽略导入文件的第一行数据。

例如：

mysql> load data infile "/home/root/data.sql" replace into table campaign fields terminated by',' enclosed by '"';

2）lines 關鍵字指定了每筆記錄的分隔符號預設為'n'即為換行符

如果兩個欄位都指定了那fields必須在lines之前。如果不指定fields關鍵字缺省值和這樣寫是相同的：

fields terminated by'\t' enclosed by ' '' ' escaped by'\\'

如果你不指定一個lines子句，預設值和這樣寫是相同的：

lines terminated by'\n'

例如：

mysql> load data infile "/root/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';

5、load data infile 可以按指定的列把文件導入到資料庫中。當我們要把資料的一部分內容導入的時候，需要加入一些欄位（列/欄位/field）到MySQL資料庫中，以適應一些額外的需要。比方說，我們要從Access資料庫升級到MySQL資料庫的時候下面的範例顯示如何向指定的欄位(field)匯入資料：

mysql> load data infile "/home/root/campaign.txt" into table campaign(id, name, creator);

6、當在伺服器主機上尋找文件時，伺服器使用下列規則：

 ●　如果給予絕對路徑名，則伺服器使用路徑名稱。 ###### ●　若給予一個有一個或多個前置元件的相對路徑名，伺服器相對伺服器的資料目錄搜尋檔案。 ###### ●　如果給予一個沒有前置元件的一個檔名，伺服器在目前資料庫的資料庫目錄尋找檔案。 ######例如： /campaign.txt 是從伺服器的資料目錄讀取，而作為 campaign.txt 是從目前資料庫的資料庫目錄下讀取。 ######相關影片教學推薦：《###MySQL教學###》######以上就是這篇文章的全部內容，希望能對大家的學習有所幫助。更多精彩內容大家可以追蹤php中文網相關教學欄位！ ！ ！ ###

以上是MySQL如何匯入格式化數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！