SQL – insert data from text file

測試環境為 CentOS 7 x86_64 (虛擬機)

如何利用 SQL 的 insert 語法將文字檔案的內容輸入到資料庫裡.

for loop

資料如下:

[root@localhost ~]# cat /tmp/fruit
Apple
Banana
Cherry

先將資料庫的資料表 (table) 建立好.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database dbtest;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use dbtest;
Database changed

MariaDB [(none)]> create table fruit ( id int(11) AUTO_INCREMENT Primary key, name char(20) , uptime timestamp); 
Query OK, 0 rows affected (0.01 sec)

MariaDB [dbtest]> DESCRIBE fruit;
+--------+-----------+------+-----+-------------------+-----------------------------+
| Field  | Type      | Null | Key | Default           | Extra                       |
+--------+-----------+------+-----+-------------------+-----------------------------+
| id     | int(11)   | NO   | PRI | NULL              | auto_increment              |
| name   | char(20)  | YES  |     | NULL              |                             |
| uptime | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
MariaDB [sbtest]> quit;
Bye

其實也很簡單是需要透過 Linux bash script 的 for 即可依序把資料輸入到資料庫.

[root@localhost ~]# vi textintosql.sh
#!/bin/bash
for i in $( cat /tmp/fruit )
do
echo "INSERT INTO dbtest.fruit (name) values ('$i');" | mysql -u root -p111111;
done
[root@localhost ~]# chmod a+x textintosql.sh
[root@localhost ~]# ./textintosql.sh

檢視一下剛剛輸入的資料.

[root@localhost ~]# echo "select * from dbtest.fruit;" | mysql -u root -p111111; 
id	name	uptime
1	Apple	2019-04-24 20:46:56
2	Banana	2019-04-24 20:46:56
3	Cherry	2019-04-24 20:46:56

SQL – LOAD DATA INFILE (Import)

但資料通常沒有這麼簡單,可以利用 SQL – LOAD DATA INFILE (Import) 的功能.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use dbtest;
Database changed
MariaDB [dbtest]> create table passwd ( Username char(20) , Password char(20) , UID int(11) , GID int(11) , IDInfo char(20) , HomeDir char(20) , Shell char(20) );
Query OK, 0 rows affected (0.05 sec)
MariaDB [dbtest]> describe passwd;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Username | char(20) | YES  |     | NULL    |       |
| Password | char(20) | YES  |     | NULL    |       |
| UID      | int(11)  | YES  |     | NULL    |       |
| GID      | int(11)  | YES  |     | NULL    |       |
| IDInfo   | char(20) | YES  |     | NULL    |       |
| HomeDir  | char(20) | YES  |     | NULL    |       |
| Shell    | char(20) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)
MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 46 rows affected, 10 warnings (0.00 sec)   
Records: 46  Deleted: 0  Skipped: 0  Warnings: 10

指令說明:

  • LOAD DATA INFILE ‘/etc/passwd’
    指定載入檔案位置,需注意權限問題 (Errcode: 2 – Error reading file) – https://dev.mysql.com/doc/refman/5.6/en/global-error-reference.html
  • INTO TABLE dbtest.passwd
    指定資料要輸入的 資料庫.資料表
  • FIELDS TERMINATED BY ‘:’
    資料的分隔字元,以 /etc/passwd 來說 : 就是下一個欄位的資料.
  • ENCLOSED BY ‘”‘
    指定包圍欄位資料的符號.
  • LINES TERMINATED BY ‘\n’
    資料的換行字元,代表接下來的資料是新的一筆紀錄 (Record).
  • IGNORE 1 ROWS;
    表示忽略第一行的資料 (當第一行資料是欄位名稱).

檢視一下,可以看到剛剛輸入的資料.

MariaDB [dbtest]> select * from dbtest.passwd limit 5;
+----------+----------+------+------+--------+----------------+---------------+
| Username | Password | UID  | GID  | IDInfo | 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 |
+----------+----------+------+------+--------+----------------+---------------+
5 rows in set (0.00 sec)

這指令可以直接在 Linux command Line 或是 Bash script 來執行(需注意 script 無法直接使用 ” 須以 \” 表示).

[root@localhost ~]# mysql -u root -p111111 -e "LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

資料的確輸入了第二次.

[root@localhost ~]# mysql -u root -p111111 -e "select * from dbtest.passwd where username='root';"
+----------+----------+------+------+--------+---------+-----------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell     |
+----------+----------+------+------+--------+---------+-----------+
| root     | x        |    0 |    0 | root   | /root   | /bin/bash |
| root     | x        |    0 |    0 | root   | /root   | /bin/bash |
+----------+----------+------+------+--------+---------+-----------+
沒有解決問題,試試搜尋本站其他內容

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料