該實例將練習MySQL的導入導出操作

創新互聯建站服務項目包括扶余網站建設、扶余網站制作、扶余網頁制作以及扶余網絡營銷策劃等。多年來，我們專注于互聯網行業，利用自身積累的技術優勢、行業經驗、深度合作伙伴關系等，向廣大中小型企業、政府機構等提供互聯網行業的解決方案，扶余網站推廣取得了明顯的社會效益與經濟效益。目前，我們服務的客戶以成都為中心已經輻射到扶余省份的部分城市，未來相信會繼續擴大服務區域并繼續獲得客戶的支持與信任！

實例：

1. 將/etc/passwd文件導入userdb庫user表并給每條記錄加編號

2. 將userdb庫user表中UID小于100的前10條記錄導出，存為/mydata/user1.txt文件

• 新建userdb庫，切換到userdb庫，并且設置如下字段；

mysql>?create?database?userdb; Query?OK,?1?row?affected?(0.00?sec) mysql>?use?userdb; Database?changed mysql>?create?table?user( ????->?username?varchar(24)?not?null, ????->?password?varchar(48)?default?'x', ????->?uid?int(5)?not?null, ????->?gid?int(5)?not?null, ????->?fullname?varchar(48), ????->?homedir?varchar(64)?not?null, ????->?shell?varchar(24)?not?null ????->?); Query?OK,?0?rows?affected?(0.01?sec) mysql>?desc?user; +----------+-------------+------+-----+---------+-------+ |?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?| +----------+-------------+------+-----+---------+-------+ |?username?|?varchar(24)?|?NO???|?????|?NULL????|???????| |?password?|?varchar(48)?|?YES??|?????|?x???????|???????| |?uid??????|?int(5)??????|?NO???|?????|?NULL????|???????| |?gid??????|?int(5)??????|?NO???|?????|?NULL????|???????| |?fullname?|?varchar(48)?|?YES??|?????|?NULL????|???????| |?homedir??|?varchar(64)?|?NO???|?????|?NULL????|???????| |?shell????|?varchar(24)?|?NO???|?????|?NULL????|???????| +----------+-------------+------+-----+---------+-------+ 7?rows?in?set?(0.01?sec)

• 注：在MySQL 5.7.6版本之后，導入文件只能在secure_file_priv指定的文件夾下。如果直接導入會報錯。執行show variables like '%secure%'命令顯示文件目錄：

mysql>?load?data?infile?'/etc/passwd'?into?table?user?fields?terminated?by?':'; ERROR?1290?(HY000):?The?MySQL?server?is?running?with?the?--secure-file-priv?option?so?it?cannot?execute?this?statement mysql>?show?variables?like?'%secure%'; +--------------------------+-----------------------+ |?Variable_name????????????|?Value?????????????????| +--------------------------+-----------------------+ |?require_secure_transport?|?OFF???????????????????| |?secure_auth??????????????|?ON????????????????????| |?secure_file_priv?????????|?/var/lib/mysql-files/?| +--------------------------+-----------------------+ 3?rows?in?set?(0.00?sec)

• 執行導入操作

-將/etc/passwd文件復制到/var/lib/mysql-files/目錄下，

-讀取/var/lib/mysql-files/passwd文件內容，以“:”為分隔，導入到user表中：

[root@host50?~]#cp?/etc/passwd?/var/lib/mysql-files/ mysql>?LOAD?DATA?INFILE?'/var/lib/mysql-files/passwd' ->?INTO?TABLE?user ->?FIELDS?TERMINATED?BY?':'; Query?OK,?42?rows?affected?(0.11?sec) Records:?42??Deleted:?0??Skipped:?0??Warnings:?0

注：上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因為這是默認的情況（每行一條原始記錄），除非需要以其他字符分割行，才需要用到這個。

• 確認導入結果

mysql>?select?count(*)?from?user; +----------+ |?count(*)?| +----------+ |???????42?| +----------+ 1?row?in?set?(0.00?sec) mysql>?select?*?from?user?limit?10; +----------+----------+-----+-----+----------+-----------------+----------------+ |?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????| +----------+----------+-----+-----+----------+-----------------+----------------+ |?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????| |?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??| |?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??| |?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??| |?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??| |?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????| |?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?| |?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????| |?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??| |?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??| +----------+----------+-----+-----+----------+-----------------+----------------+ 10?rows?in?set?(0.00?sec)

• 為user表中的每條記錄添加自動編號，并驗證自動編寫結果

mysql>?alter?table?user?add?sn?int(4)?auto_increment?primary?key?first; Query?OK,?0?rows?affected?(0.03?sec) Records:?0??Duplicates:?0??Warnings:?0 mysql>?select?*?from?user?limit?10; +----+----------+----------+-----+-----+----------+-----------------+----------------+ |?sn?|?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????| +----+----------+----------+-----+-----+----------+-----------------+----------------+ |??1?|?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????| |??2?|?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??| |??3?|?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??| |??4?|?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??| |??5?|?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??| |??6?|?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????| |??7?|?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?| |??8?|?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????| |??9?|?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??| |?10?|?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??| +----+----------+----------+-----+-----+----------+-----------------+----------------+ 10?rows?in?set?(0.00?sec)

• 從MySQL數據庫中導出查詢結果

-以將userdb庫user表中UID小于100的前10條記錄導出為/myload/user2.txt文件為例

首先，修改配置文件中存放導出導入目錄及查看修改結果

[root@host50?~]#?mkdir??/myload??;??chown??mysql??/myload [root@host50?~]#?vim??/etc/my.cnf [mysqld] secure_file_priv="/myload" [root@dbsvr1?~]#?systemctl??restart?mysqld mysql>?show?variables?like?"secure_file_priv"; +------------------+----------+ |?Variable_name????|?Value???????| +------------------+----------+ |?secure_file_priv???|?/myload/?|

導出user表中UID小于100的前十條記錄

mysql>?select?*?from?userdb.user?where?uid<100 ????->?into?outfile?'/myload/user.txt' ????->?fields?terminated?by?":"; Query?OK,?26?rows?affected?(0.00?sec)

確認導出結果

[root@host50?~]#?wc?-l?/myload/user.txt 26?/myload/user.txt [root@host50?~]#?tail?/myload/user.txt? 25:tss:x:59:59:Account?used?by?the?trousers?package?to?sandbox?the?tcsd?daemon:/dev/null:/sbin/nologin 29:rpcuser:x:29:29:RPC?Service?User:/var/lib/nfs:/sbin/nologin 33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin 35:sshd:x:74:74:Privilege-separated?SSH:/var/empty/sshd:/sbin/nologin 36:avahi:x:70:70:Avahi?mDNS/DNS-SD?Stack:/var/run/avahi-daemon:/sbin/nologin 37:postfix:x:89:89::/var/spool/postfix:/sbin/nologin 38:ntp:x:38:38::/etc/ntp:/sbin/nologin 39:tcpdump:x:72:72::/:/sbin/nologin 41:apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin 42:mysql:x:27:27:MySQL?Server:/var/lib/mysql:/bin/false

另外有需要云服務器可以了解下創新互聯cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業上云的綜合解決方案，具有“安全穩定、簡單易用、服務可用性高、性價比高”等特點與優勢，專為企業上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

網站題目：MySQL數據導入導出（用于個人學習與回顧）-創新互聯
網頁路徑：http://www.yijiale78.com/article46/ceideg.html

成都網站建設公司_創新互聯，為您提供小程序開發、網站改版、外貿網站建設、網站內鏈、網站策劃、全網營銷推廣

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯