您好,登錄后才能下訂單哦!
小編給大家分享一下MySQL數(shù)據(jù)導(dǎo)入和導(dǎo)出純數(shù)據(jù)的方式有哪些,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
MySQL的數(shù)據(jù)導(dǎo)入和導(dǎo)出純數(shù)據(jù)的方式,一般采用的是load data file 、mysqlimport 、select into outfile 、>/>>重定向的方式,這里主要介紹load data file和select into outfile 的方式。
一、MySQL導(dǎo)入和導(dǎo)出數(shù)據(jù):
1、load data file
簡(jiǎn)介:
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT ...INTO OUTFILE.
官方參考手冊(cè):http://dev.mysql.com/doc/refman/5.6/en/load-data.html
語(yǔ)法格式:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
load data file示例:
1)直接導(dǎo)入文件
LOAD DATA INFILE 'test_data.txt' INTO TABLE dbtest.t1;
2)設(shè)置列分隔符和行分隔符
LOAD DATA LOCAL INFILE 'test_data.txt' INTO TABLE t1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
3)導(dǎo)入到特定的列
LOAD DATA LOCAL INFILE 'test_data.txt' INTO TABLE t1 (col1,col2,col3);
參數(shù)說(shuō)明:
1)local參數(shù)
如果指定了LOCAL,被認(rèn)為與連接的客戶端有關(guān),則文件會(huì)被客戶主機(jī)上的客戶端讀取,并被發(fā)送到服務(wù)器。文件會(huì)被給予一個(gè)完整的路徑名稱,以指定確切的位置。如果給定的是一個(gè)相對(duì)的路徑名稱,則此名稱會(huì)被理解為相對(duì)于啟動(dòng)客戶端時(shí)所在的目錄。
如果LOCAL沒(méi)有被指定,則文件必須位于服務(wù)器主機(jī)上,并且被服務(wù)器直接讀取。
當(dāng)在服務(wù)器主機(jī)上為文件定位時(shí),服務(wù)器使用以下規(guī)則:
如果給定了一個(gè)絕對(duì)的路徑名稱,則服務(wù)器使用此路徑名稱。
如果給定了帶有一個(gè)或多個(gè)引導(dǎo)組件的相對(duì)路徑名稱,則服務(wù)器會(huì)搜索相對(duì)于服務(wù)器數(shù)據(jù)目錄的文件。
如果給定了一個(gè)不帶引導(dǎo)組件的文件名稱,則服務(wù)器會(huì)在默認(rèn)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄中尋找文件。
注意,這些規(guī)則意味著名為./myfile.txt的文件會(huì)從服務(wù)器數(shù)據(jù)目錄中被讀取,而名為myfile.txt的同樣的文件會(huì)從默認(rèn)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄中讀取。
從客戶端使用絕對(duì)路徑load數(shù)據(jù):LOAD DATA LOCAL INFILE '/import/test_data.txt' INTO TABLE dbtest.t1;
從服務(wù)器里使用相對(duì)路徑load數(shù)據(jù),下面的LOAD DATA語(yǔ)句會(huì)從dbtest數(shù)據(jù)庫(kù)目錄中讀取文件test_data.txt,因?yàn)閐b1是當(dāng)前數(shù)據(jù)庫(kù)。即使語(yǔ)句明確把文件載入到db2數(shù)據(jù)庫(kù)中的表里,也會(huì)從dbtest目錄中讀取。
USE dbtest;
LOAD DATA INFILE 'test_data.txt' INTO TABLE dbtest.t1;
總而言之:如果指定local關(guān)鍵詞,則表明從客戶主機(jī)讀文件。如果local沒(méi)指定,文件必須位于服務(wù)器上。
2)IGNORE number LINES參數(shù)
IGNORE number LINES選項(xiàng)可以被用于在文件的開(kāi)始處忽略行。可以使用IGNORE 1 LINES來(lái)跳過(guò)一個(gè)包含列名稱的起始標(biāo)題行:
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
3)REPLACE、IGNORE參數(shù)
如果您指定了REPLACE,則輸入行會(huì)替換原有行(與原有行一樣,對(duì)一個(gè)主索引或唯一索引具有相同值的行)。
如果您指定IGNORE,則把原有行復(fù)制到唯一關(guān)鍵字值的輸入行被跳過(guò)。
如果您這兩個(gè)選項(xiàng)都不指定,則運(yùn)行情況根據(jù)LOCAL關(guān)鍵詞是否被指定而定。不使用LOCAL時(shí),當(dāng)出現(xiàn)重復(fù)關(guān)鍵字值時(shí),會(huì)發(fā)生錯(cuò)誤,并且剩下的文本文件被忽略。使用LOCAL時(shí),默認(rèn)的運(yùn)行情況和IGNORE被指定時(shí)的情況相同;這是因?yàn)樵谶\(yùn)行中間,服務(wù)器沒(méi)有辦法中止文件的傳輸。
4)FIELDS參數(shù)
指定了文件字段的分割格式,列分隔符參數(shù)語(yǔ)法為
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
terminated by描述字段的分隔符,默認(rèn)情況下是tab字符(\t)
enclosed by描述的是字段的括起字符,如果您忽略了詞語(yǔ)OPTIONALLY,則所有的字段都被包含在ENCLOSED BY字符串中,如果您指定了OPTINALLY,則ENCLOSED BY字符只被用于包含具有字符串?dāng)?shù)據(jù)類型(比如CHAR, BINARY, TEXT或ENUM)的列中的值.
escaped by描述的轉(zhuǎn)義字符。默認(rèn)的是反斜杠(backslash:\ )
如果您不指定FIELDS子句,則默認(rèn)值為假設(shè)您寫(xiě)下如下語(yǔ)句時(shí)的值:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
當(dāng)讀取輸入值時(shí),默認(rèn)值會(huì)使LOAD DATA INFILE按如下方式運(yùn)行:
在新行處尋找行的邊界。
不會(huì)跳過(guò)任何行前綴。
在制表符處把行分解為字段。
不希望字段被包含在任何引號(hào)字符之中。
出現(xiàn)制表符、新行、或在'\'前有'\'時(shí),理解為作為字段值一部分的文字字符。
備注:如果您已經(jīng)在Windows系統(tǒng)中生成了文本文件,您可能必須使用LINES TERMINATED BY '\r\n'來(lái)正確地讀取文件,因?yàn)閃indows程序通常使用兩個(gè)字符作為一個(gè)行終止符。部分程序,當(dāng)編寫(xiě)文件時(shí),可能會(huì)使用\r作為行終止符。要讀取這樣的文件,應(yīng)使用LINES TERMINATED BY '\r'。要寫(xiě)入FIELDS ESCAPED BY '\\',您必須為待讀取的值指定兩個(gè)反斜杠,作為一個(gè)單反斜杠使用。
6)LINES參數(shù)
指定了每條記錄的分隔符默認(rèn)為’\n’即為換行分隔符,其語(yǔ)法為:
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
如果您不指定LINES子句,則默認(rèn)值為假設(shè)您寫(xiě)下如下語(yǔ)句時(shí)的值:
LINES TERMINATED BY '\n' STARTING BY ''
如果所有您希望讀入的行都含有一個(gè)您希望忽略的共用前綴,則您可以使用'prefix_string'來(lái)跳過(guò)前綴(和前綴前的字符)。如果某行不包括前綴,則整個(gè)行被跳過(guò)。注釋:prefix_string會(huì)出現(xiàn)在一行的中間。
ALTER TABLE...DISABLE KEYS
ALTER TABLE...ENABLE KEYS
2、select into outfile
簡(jiǎn)介:
官方參考手冊(cè):http://dev.mysql.com/doc/refman/5.6/en/select-into.html
語(yǔ)法格式:
SELECT ... INTO var_list ##selects column values and stores them into variables.
SELECT ... INTO OUTFILE ##writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
SELECT ... INTO DUMPFILE ##writes a single row to a file without any formatting.
select into outfile示例:
1)SELECT ... INTO OUTFILE 'file_name'
2)SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
3)select * from t1 into outfile '/tools/databak/dbtest_t1.txt'
fields TERMINATED BY ','
lines TERMINATED BY '\n' ;
參數(shù)說(shuō)明:(參考load data參數(shù))
3、mysqlimport(不推薦使用)
mysqlimport是操作系統(tǒng)命令,和load data實(shí)現(xiàn)功能相同,具體使用方法如下:
語(yǔ)法格式:
mysqlimport --defaults-file='' --default-character-set=utf8 --columns=id,name --delete --fields-terminated-by='' --fields-enclosed-by='' --fields-optionally-enclosed-by='' --fields-escaped-by='' --force -h -i --ignore-lines --lines-terminated-by='' -L -p -P -S -u dbname
使用示例:
mysqlimport -L -uroot dbtest /tools/databak/t1.txt --fields-terminated-by=',' --lines-terminated-by='\n'
參數(shù)說(shuō)明:
mysqlimport --help
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
--default-character-set=name
Set the default character set.
-c, --columns=name Use only these columns to import the data to. Give the
column names in a comma separated list. This is same as
giving columns to LOAD DATA INFILE.
-C, --compress Use compression in server/client protocol.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-auth=name Default authentication client-side plugin to use.
-d, --delete First delete all rows from table.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
--fields-terminated-by=name
Fields in the input file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the import file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the input file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the input file are escaped by the given
character.
-f, --force Continue even if we get an SQL error.
-?, --help Displays this help and exits.
-h, --host=name Connect to host.
-i, --ignore If duplicate unique key was found, keep old row.
--ignore-lines=# Ignore first n lines of data infile.
--lines-terminated-by=name
Lines in the input file are terminated by the given
string.
-L, --local Read all files through the client.
-l, --lock-tables Lock all tables for write (this disables threads).
--low-priority Use LOW_PRIORITY when updating the table.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-r, --replace If duplicate unique key was found, replace old row.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use --skip-secure-auth to disable.)
-s, --silent Be more silent.
-S, --socket=name The socket file to use for connection.
--ssl Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-crl=name Certificate revocation list (implies --ssl).
--ssl-crlpath=name Certificate revocation list path (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
--ssl-mode=name SSL connection mode.
--use-threads=# Load files in parallel. The argument is the number of
threads to use for loading data.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
以上是“MySQL數(shù)據(jù)導(dǎo)入和導(dǎo)出純數(shù)據(jù)的方式有哪些”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對(duì)大家有所幫助,如果還想學(xué)習(xí)更多知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。