SQL Function – LOAD DATA

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

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

主要想把 Microsoft Excel 轉成 MariaDB 的資料庫 ,Excel 檔案需事前先轉成 CSV 格式或是 OpenDocument .ods 檔案格式(建議),除了可以透過 Mysql 指令 外還可以透過 PHPMyAdmin – https://benjr.tw/97834 來快速轉檔.

LOAD DATA 語法如下：

MariaDB [(none)]> HELP LOAD DATA;
Name: 'LOAD DATA'
Description:
Syntax:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_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,...]

以 Linux 的 /etc/passwd 為範例,可以利用 SQL 語法 LOAD DATA INFILE (Import) 來將檔案內容轉換成到資料庫.

[root@localhost ~]# cat /etc/passwd 
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

[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

先將資料庫的資料表 (table) 建立好,欄位則是依據 passwd 格式所定 Username , Password , UID , GID , IDInfo , HomeDir , Shell.

MariaDB [dbtest]> CREATE TABLE passwd ( Username VARCHAR(100) , Password VARCHAR(100) , UID INT(11) , GID INT(11) , IDInfo VARCHAR(100) , HomeDir VARCHAR(100) , Shell VARCHAR(100) );
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)

現在透過 SQL 語法 LOAD DATA INFILE 把 /etc/passwd 轉入到資料庫.

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

如果要指定欄位.

MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;

指令說明:

  • LOAD DATA INFILE ‘/etc/passwd’
    指定載入檔案位置,需注意權限問題 (Errcode: 2 – Error reading file) – https://dev.mysql.com/doc/refman/5.6/en/global-error-reference.html , 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
    當指定 INFILE 表示檔案是從 Client Host 傳送到 Server 來讀取資料.
  • INTO TABLE dbtest.passwd
    指定資料要輸入的 資料庫.資料表
  • FIELDS TERMINATED BY ‘:’
    資料的分隔字元,以 /etc/passwd 來說 : 就是下一個欄位的資料.
  • ENCLOSED BY ‘”‘
    指定在 “” 裡面的資料為單一欄位資料.
  • LINES TERMINATED BY ‘\n’
    需注意 Linux 下的換行字元為 \n ,而 Windows 下的換行字元為 \r\n ,代表接下來的資料是新的一筆紀錄 (Record).
  • IGNORE 1 ROWS;
    如果當你的資料第一行為欄位名稱,使用該參數即可忽略第一行的資料.

其他參數:

  • SET col_name = expr
    該欄位不在檔案裡,是固定字串時,就可以透過這個方式來設定.

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

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 來執行 (需注意 ENCLOSED BY ‘”‘ 這個地方,因為 Linux 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 |
+----------+----------+------+------+--------+---------+-----------+

下面我們來看一下一些比較特別的格式要怎麼處裡.

[root@localhost ~]# vi /var/ftp/passwd1
root:1:x:0:0:root:/root:/bin/bash
root:2:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

在第一與第二行的第一個欄位資料為 root:1 與 root:2 ,可以透過下面只種方式來解決.

ENCLOSED BY ‘”‘

使用雙引號 (Double Quotes) “This is Text” 來表示裡面是一欄的資料.

"root:1":x:0:0:root:/root:/bin/bash
"root:2":x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

MariaDB [dbtest]> TRUNCATE passwd;
Query OK, 0 rows affected (0.017 sec)

MariaDB [dbtest]> LOAD DATA INFILE '/var/ftp/passwd1' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;
Query OK, 4 rows affected (0.004 sec)                
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [dbtest]> SELECT * FROM passwd;
+----------+----------+------+------+--------+---------+---------------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell         |
+----------+----------+------+------+--------+---------+---------------+
| root:1   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| root:2   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| bin      | x        |    1 |    1 | bin    | /bin    | /sbin/nologin |
| daemon   | x        |    2 |    2 | daemon | /sbin   | /sbin/nologin |
+----------+----------+------+------+--------+---------+---------------+
4 rows in set (0.001 sec)

跳脫字元 (Escape Character)

使用預設的跳脫字元 (Escape Character) \ 避開 FIELDS TERMINATED BY ‘:’ 的欄位分隔符號.

root\:1:x:0:0:root:/root:/bin/bash
root\:2:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

MariaDB [dbtest]> TRUNCATE passwd;
Query OK, 0 rows affected (0.013 sec)

MariaDB [dbtest]> LOAD DATA INFILE '/var/ftp/passwd1' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;
Query OK, 4 rows affected (0.004 sec)                
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [dbtest]> SELECT * FROM passwd;
+----------+----------+------+------+--------+---------+---------------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell         |
+----------+----------+------+------+--------+---------+---------------+
| root:1   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| root:2   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| bin      | x        |    1 |    1 | bin    | /bin    | /sbin/nologin |
| daemon   | x        |    2 |    2 | daemon | /sbin   | /sbin/nologin |
+----------+----------+------+------+--------+---------+---------------+
4 rows in set (0.000 sec)

自訂跳脫字元 (Escape Character)

前面使用預設的跳脫字元 (Escape Character) \ ,我們可以透過 ESCAPED BY ‘^’ 自訂跳脫字元(需注意非所有字元皆可當自訂跳脫字元).

root^:1:x:0:0:root:/root:/bin/bash
root^:2:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

MariaDB [dbtest]> TRUNCATE passwd;
Query OK, 0 rows affected (0.018 sec)

MariaDB [dbtest]> LOAD DATA INFILE '/var/ftp/passwd1' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' ESCAPED BY '^' LINES TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;
Query OK, 4 rows affected (0.004 sec)                
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [dbtest]> SELECT * FROM passwd;
+----------+----------+------+------+--------+---------+---------------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell         |
+----------+----------+------+------+--------+---------+---------------+
| root:1   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| root:2   | x        |    0 |    0 | root   | /root   | /bin/bash     |
| bin      | x        |    1 |    1 | bin    | /bin    | /sbin/nologin |
| daemon   | x        |    2 |    2 | daemon | /sbin   | /sbin/nologin |
+----------+----------+------+------+--------+---------+---------------+
4 rows in set (0.001 sec)

Starting By

在 LINES STARTING BY 指定行開頭為特定字串,只需處理這些行的資料即可.

user:root:x:0:0:root:/root:/bin/bash
user:root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

MariaDB [dbtest]> TRUNCATE passwd;
Query OK, 0 rows affected (0.014 sec)

MariaDB [dbtest]> LOAD DATA INFILE '/var/ftp/passwd1' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES STARTING BY 'user:' TERMINATED BY '\n' (Username , Password , UID  , GID , IDInfo , HomeDir , Shell) ;
Query OK, 2 rows affected (0.002 sec)                
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [dbtest]> SELECT * FROM passwd;
+----------+----------+------+------+--------+---------+-----------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell     |
+----------+----------+------+------+--------+---------+-----------+
| root     | x        |    0 |    0 | root   | /root   | /bin/bash |
| root     | x        |    0 |    0 | root   | /root   | /bin/bash |
+----------+----------+------+------+--------+---------+-----------+
2 rows in set (0.001 sec)

IGNORE NUMBRT LINES

如果你要匯入的資料第一行是欄位名稱則可以使用 IGNORE 1 LINES (跳過第一行)

Username:Password:UID:GID:IDInfo:HomeDir:Shell
root:x:0:0:root:/root:/bin/bash
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin

MariaDB [dbtest]> TRUNCATE passwd;
Query OK, 0 rows affected (0.018 sec)

MariaDB [dbtest]> LOAD DATA INFILE '/var/ftp/passwd1' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (Username , Password , UID  , GID , IDInfo , HomeDir , Shell);
Query OK, 4 rows affected (0.003 sec)                
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [dbtest]> SELECT * FROM passwd;
+----------+----------+------+------+--------+---------+---------------+
| Username | Password | UID  | GID  | IDInfo | HomeDir | Shell         |
+----------+----------+------+------+--------+---------+---------------+
| root     | x        |    0 |    0 | root   | /root   | /bin/bash     |
| root     | x        |    0 |    0 | root   | /root   | /bin/bash     |
| bin      | x        |    1 |    1 | bin    | /bin    | /sbin/nologin |
| daemon   | x        |    2 |    2 | daemon | /sbin   | /sbin/nologin |
+----------+----------+------+------+--------+---------+---------------+
4 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

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