SQL 資料型態 – TEXT Data & LOAD_FILE 函數

這邊來看一下 TEXT Data 這個資料型態.

測試環境為 CentOS 7 x86_64 虛擬機.

Data Type	Storage Required
TINYBLOB , TINYTEXT	字元上限 255 bytes ( 2^8 -1 ),使用 1 bytes 紀錄前面字元使用長度.
BLOB , TEXT	字元上限 64 KB ( 2^16 -1 ),使用 2 bytes 紀錄前面字元使用長度.
MEDIUMBLOB , MEDIUMTEXT	字元上限 16 MB (24^2 -1),使用 3 bytes 紀錄前面字元使用長度.
LONGBLOB , LONGTEXT	字元上限 4 GB (2^32 -1),使用 4 bytes 紀錄前面字元使用長度.

TEXT 與 BLOB 的差別:
BLOB 主要是用來儲存二進製資料 (可以用來存儲圖像 images, 影片 videos, 以及可執行檔 executables), 而 TEXT 主要是用來儲存字元內容.

通常整篇文章會用到 Text 的儲存欄位,問題來了,使用 SQL – SELECT 文章是要怎麼比對? 除了 SELECT 外還可以使用 SELECT Full Text Index 來搜尋,請參考 – https://benjr.tw/101644 (需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 ).

要注意,透過編輯器閱讀文章的時候是看不到一些控制字元,通常在每一行最後會有 換行與 水平定位符號,所以使用 SELECT 比對時要把這些考慮進去.

\r -> 0d(16進制) CR (Carriage Return) 水平定位符號.
\n -> 0A(16進制) NewLine 換行符號
關於 ASCII 符號的定義請參考 https://zh.wikipedia.org/wiki/ASCII

在 Linux 下可以透過 hexdump – https://benjr.tw/28613 來觀察.

Linux 下所產生的檔案,可以看到在 Linux 下的換行 (NewLine) 為 0A(16進制) 字元.

[root@localhost ~]# hexdump -C test.txt 
00000000  31 32 33 0a 31 31 31 0a                           |123.111.|
00000008

Windows 下所產生的檔案,可以看到在 Windows 下的換行為 0d 0A(16進制) – Carriage Return + NewLine 字元.

[root@localhost ~]# hexdump -C w.txt
00000000  41 42 43 0d 0a 31 32 33                           |ABC..123|
00000008

所以每一行的最後,會有 \r\n 或是 \n ,看當初是從什麼系統建立的
\r -> A carriage return character
\n -> A newline (linefeed) character

來試試看文章的搜尋,先建立測試用的資料庫與資料表(欄位 datacontent 將儲存文章內容).

[root@localhost ~]# 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.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 testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE texttest ( id int(11) AUTO_INCREMENT Primary key, datacontent text); 
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> DESCRIBE texttest;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| datacontent | text    | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

要將檔案上傳到 SQL Server 時,需使用 LOAD_FILE 函數,先建立測試用資料.

[root@localhost ~]# cat /var/lib/mysql/test.txt 
ABC
123

先確定一下可以載入檔案.

MariaDB [testdb]> SELECT LOAD_FILE('/var/lib/mysql/test.txt');
+--------------------------------------+
| LOAD_FILE('/var/lib/mysql/test.txt') |
+--------------------------------------+
| ABC
123
                             |
+--------------------------------------+
1 row in set (0.00 sec)

函數說明:
LOAD_FILE 函數指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案),需注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.

關於使用者存取 DateBase (Table) 權限請參考 https://benjr.tw/98033

但要注意你有使用主從式資料庫 (Master Slave Replication – https://benjr.tw/102278 ) 時,當 SLAVE 沒有相對應檔案可以讀取時,會讓欄位資料都變成 NULL.

另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取,可以使用SQL 指令 SHOW VARIABLES LIKE ‘max_allowed_packet’; 來檢視.

MariaDB [testdb]> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

如果有設定 secure_file_priv 時會至預設路徑存取.

MariaDB [testdb]> SHOW VARIABLES LIKE 'secure_file_priv'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

剛剛測試沒問題後就可以透過 INSERT 把檔案內容儲存到指定欄位.

MariaDB [testdb]> INSERT INTO texttest (datacontent) VALUES (LOAD_FILE('/var/lib/mysql/test.txt'));
Query OK, 1 row affected (0.03 sec)

MariaDB [testdb]> SELECT * FROM texttest;
+----+-------------+
| id | datacontent |
+----+-------------+
|  1 | ABC
123
    |
+----+-------------+
1 row in set (0.00 sec)

SELECT 搜尋時記得要在每一行的行尾加入 \n 包含最後一行.

MariaDB [testdb]> SELECT * FROM texttest where datacontent LIKE 'ABC\n123\n'\G
*************************** 1. row ***************************
         id: 1
datacontent: ABC
123

1 row in set (0.00 sec)