該實(shí)例將練習(xí)MySQL的導(dǎo)入導(dǎo)出操作

創(chuàng)新互聯(lián)公司一直在為企業(yè)提供服務(wù)，多年的磨煉，使我們?cè)趧?chuàng)意設(shè)計(jì)，網(wǎng)絡(luò)營(yíng)銷(xiāo)推廣到技術(shù)研發(fā)擁有了開(kāi)發(fā)經(jīng)驗(yàn)。我們擅長(zhǎng)傾聽(tīng)企業(yè)需求，挖掘用戶(hù)對(duì)產(chǎn)品需求服務(wù)價(jià)值，為企業(yè)制作有用的創(chuàng)意設(shè)計(jì)體驗(yàn)。核心團(tuán)隊(duì)擁有超過(guò)10多年以上行業(yè)經(jīng)驗(yàn)，涵蓋創(chuàng)意，策化，開(kāi)發(fā)等專(zhuān)業(yè)領(lǐng)域，公司涉及領(lǐng)域有基礎(chǔ)互聯(lián)網(wǎng)服務(wù)多線服務(wù)器托管、成都App定制開(kāi)發(fā)、手機(jī)移動(dòng)建站、網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)絡(luò)整合營(yíng)銷(xiāo)。

實(shí)例：

1. 將/etc/passwd文件導(dǎo)入userdb庫(kù)user表并給每條記錄加編號(hào)

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

• 新建userdb庫(kù)，切換到userdb庫(kù)，并且設(shè)置如下字段；

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版本之后，導(dǎo)入文件只能在secure_file_priv指定的文件夾下。如果直接導(dǎo)入會(huì)報(bào)錯(cuò)。執(zhí)行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)

• ????執(zhí)行導(dǎo)入操作

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

-讀取/var/lib/mysql-files/passwd文件內(nèi)容，以“:”為分隔，導(dǎo)入到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'，因?yàn)檫@是默認(rèn)的情況（每行一條原始記錄），除非需要以其他字符分割行，才需要用到這個(gè)。

• 確認(rèn)導(dǎo)入結(jié)果

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表中的每條記錄添加自動(dòng)編號(hào)，并驗(yàn)證自動(dòng)編寫(xiě)結(jié)果

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數(shù)據(jù)庫(kù)中導(dǎo)出查詢(xún)結(jié)果

? ? ? ? ?-以將userdb庫(kù)user表中UID小于100的前10條記錄導(dǎo)出為/myload/user2.txt文件為例

? ? ? ?首先，修改配置文件中存放導(dǎo)出導(dǎo)入目錄及查看修改結(jié)果

[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/?|

? ? ? 導(dǎo)出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)

? ? ? 確認(rèn)導(dǎo)出結(jié)果

[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