MariaDB 大量資料轉檔

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

mysql 指令

透過 mysql 指令,需要先建立好資料庫 Database 與資料表格 table ,我的資料表為 Title , Detail ,Solution 各 20 個字元.

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

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

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use Linux;
Database changed
MariaDB [Linux]> create table Ubuntu (Title char(20),Detail char(20),Solution char(20));
Query OK, 0 rows affected (0.01 sec)

MariaDB [Linux]> show tables;
+-----------------+
| Tables_in_Linux |
+-----------------+
| Ubuntu          |
+-----------------+
1 row in set (0.00 sec)

接下來就是將 CSV 檔案的資料導入到資料表格內.

MariaDB [Linux]> LOAD DATA INFILE '/var/lib/mysql/Linux/Ubuntu.csv' INTO TABLE Ubuntu FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 5 rows affected, 10 warnings (0.02 sec)    
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10

• LOAD DATA INFILE ‘/var/lib/mysql/Linux/Ubuntu.csv’
  CSV 檔案位址,如果出現了 Can’t get stat of ‘/root/Ubuntu.csv’ (Errcode:13 “Permission denied”) 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
• INTO TABLE Ubuntu
  讀取的檔案要寫入 Ubuntu 資料表格內.
• FIELDS TERMINATED BY ‘,’
  資料是以 , (逗號)為區隔.
• ENCLOSED BY ‘”‘
  資料多於一行時會以 ” (引號) 表示為同一欄位.
• LINES TERMINATED BY ‘\n’
  同一欄位的換行會以 \n (newLine) 做區隔.
• IGNORE 1 ROWS;
  因 CVS 的第一行是資料表的欄位名稱,前面已經定義好,所以不需要這內容.

資料表匯入後就可以使用 select 來查詢了.

MariaDB [Linux]> select * from Ubuntu;
+---------+-------------+----------------+
| Title   | Detail      | Solution       |
+---------+-------------+----------------+
| A       | AAA Test    | AAA Solution   |
| B       | BBB Test    | BBB Solution   |
| C       | CCC Test    | CCC Solution   |
| D       | DDD Test    | DDD Solution   |
| E       | EEE Test    | EEE Solution   |
+---------+-------------+----------------+
5 rows in set (0.01 sec)

PHPMyAdmin

關於 MySQL (MariaDB) 資料庫管理系統 PHPMyAdmin 的安裝請參考 http://benjr.tw/323

在 主頁(Home)/資料庫(Databases)/新建資料庫(Create Database) 新建一個資料庫 (database),在新建的資料庫 (Linux) 導入 (import) 新資料表 (Table)

PHPMyAdmin 支援大量的轉檔格式有

1. CSV – 用逗號 (,) 將值隔開的檔案格式 (在 Microsoft Excel 或是 Linux 下的 LibreOffice 都可以直接將檔按轉換成 csv)
   依據需要在 格式選項勾選 “使用檔案取代資料表資料” 選擇 欄位分隔符號,內容分隔符號, 內容跳脫符號 ,如果在資料的第一行為資料表名稱時,需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.
2. MediaWiki 表 – WiKi 的資料庫格式
3. OpenDocument Spreadsheet – OpenDocument 的格式( Linux 下的 LibreOffice 可以儲存的格式 .ods 之一).
   需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.
   
   需注意檔你的檔案格式有超連結 (hyperlink) 時,匯入時無法把它轉成文字檔,須先把 hyperlink 格式移除.
4. ESRI Shape File – 美國環境系統研究所公司（ESRI）所開發的一種屬於空間資料開放格式.
5. SQL – SQL 的資料庫
6. XML – XML 檔案格式

如果資料表名稱需要做修改,可以在 資料表 / 操作 / 資料表選項 來進行名稱修改.
如果是資料欄名稱需要做修改,可以在 資料表 / 結構 / 修改 來進行名稱修改.

在剛轉好的資料會顯示

這個資料表中沒有可以辦識(unique)的資料欄位 將無法執行修改、複製、刪除等相關的功能。

我們需要在 資料表 / 結構 的其中之一的資料欄位設定成為 unique .或是 Primary Key (主鍵) – 必須為 not null (不能為空) 並且要 unique (唯一) .關於 Primary , Unique , Index Key 請參考 http://benjr.tw/98266

設定好之後就可以在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .