簡介
MySQL 儲存格式中，常用TEXT或BLOB來儲存長字串
兩者之間的差異主要為: TEXT 只能儲存字元資料，BLOB 可以保存二進位資料
問題
這是MySQL 5實力養成暨評量裡的4-57.『對於字串型態的TEXT類型，下列敘述何者正確？』
答案：(A) 包含四種資料型態(TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT) (B) 不可以設定初始值
這題的關鍵就在於初始值是否可以設，當然本人只依據過往網頁設計的經驗來猜(C)結果可想而知，我們再好好讀一下手冊吧，請看
MySQL 5.7 Reference Manual :: 11 Data Types :: 11.4 String Types :: 11.4.3 The BLOB and TEXT Types
跟
MySQL 5.1参考手册 :: 11. 列类型::11.4. String类型::11.4.3. BLOB和TEXT类型
轉繁體中文於下：
mysql blob是一個二進制大物件，可以容納可變數量的資料。有4種BLOB類型：
- TinyBlob： 最大 255位元組
- Blob： 最大 65K
- MediumBlob：最大 16M
- LongBlob： 最大 4G
它們只是可容納值的最大長度不同。
有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和儲存需求。
參見11.5節，「列類型儲存需求」。
BLOB 列被視為二進制字串(字節字串)。TEXT列被視為非二進制字串(字元字串)。BLOB列沒有字元編碼，並且排序和比較基於欄位值字節的數值值。TEXT列有一個字元編碼，並且根據字元編碼的 校對規則對值進行排序和比較。
在TEXT或BLOB列的儲存或檢索過程中，不存在大小寫轉換。
當未運行在嚴格模式時，如果您為BLOB或TEXT欄位分配一個超過該欄位類型的最大長度的值，值被截取以保證適合。如果截掉的字元不是空格，將會產生一條警告。使用嚴格SQL模式，會產生錯誤，並且值將被拒絕而不是截取並給出警告。參見5.3.2節，「SQL伺服器模式」。
在大多數方面，可以將BLOB欄位視為能夠足夠大的VARBINARY欄位。同樣，可以將TEXT欄位視為VARCHAR欄位。BLOB和TEXT在以下幾個方面不同於VARBINARY和VARCHAR：
· 當保存或檢索BLOB和TEXT欄位的值時不刪除尾部空格。(這與VARBINARY和VARCHAR欄位相同）。
請注意比較時將用空格對TEXT進行擴充以適合比較的物件，正如CHAR和VARCHAR。
· 對於BLOB和TEXT欄位的索引，必須指定索引前綴的長度。對於CHAR和VARCHAR，前綴長度是可選的。參見7.4.3節，「列索引」。
· BLOB和TEXT欄位不能有初始值。
LONG和LONG VARCHAR對應MEDIUMTEXT資料型態。這是為了保證相容性。如果TEXT欄位型態使用BINARY屬性，將為欄位分配列字元編碼的二元校對規則。
MySQL連接程式/ODBC將BLOB值定義為LONGVARBINARY，將TEXT值定義為LONGVARCHAR。
由於BLOB和TEXT值可能會非常長，使用它們時可能遇到一些約束：
· 當排序時只使用該欄位的前max_sort_length個字節。max_sort_length的 預設值是1024；該值可以在啟動mysqld伺服器時使用–max_sort_length選項進行更改。參見5.3.3節，「伺服器系統變數」。
運行時增加max_sort_length的值可以在排序或組合時使更多的字節有意義。任何客戶端可以更改其會話max_sort_length變數的值：
mysql> SET max_sort_length = 2000; mysql> SELECT id, comment FROM tbl_name -> ORDER BY comment;
當您想要使超過max_sort_length的字節有意義，對含長值的BLOB或TEXT欄位使用GROUP BY或ORDER BY的另一種方式是將欄位值轉換為固定長度的物件。標準方法是使用SUBSTRING函數。例如，下面的語句對comment列的2000個字節進行排序：
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name -> ORDER BY SUBSTRING(comment,1,2000);
· BLOB或TEXT物件的最大大小由其類型確定，但在客戶端和伺服器之間實際可以傳遞的最大值由可用內存數量和通信緩存區大小確定。您可以通過更改max_allowed_packet變數的值更改消息緩存區的大小，但必須同時修改伺服器和客戶端程式。例如，可以使用mysql和mysqldump來更改客戶端的max_allowed_packet值。參見7.5.2節，「調節伺服器參數」、8.3節，「mysql：MySQL命令行工具」和8.8節，「mysqldump：資料庫備份程式」。
每個BLOB或TEXT值分別由內部分配的物件資料表示。這與其它欄位類型形成對比，後者是當打開資料表時為每1欄位分配儲存引擎。
※2022/04/06 後記
多年來我對mysql blob不熟的原因就在於，我對把檔案這種資料放在資料庫中總沒有覺得很好，在《三種永遠不要放到mysql資料庫裡的東西》就提到圖片、檔案、二進位制資料永遠不要放到mysql資料庫裡，這真的是有中我的想法，朋友可以去看看為何有不用blob的原因。
※2022/05/07
今天再來分享關於PHP操作mysql blob欄位的方法
(1)操作新聞內容
<?php
mysql_connect( "localhost", "root", "password"); //連線資料庫
mysql_select_db( "database"); //選定資料庫
//資料插入：
$CONTENT="測試內容"; //$CONTENT為新聞內容
$COMPRESS_CONTENT = bin2hex(gzcompress($CONTENT));
$result=mysql_query( "insert into news (content) value ('$COMPRESS_CONTENT')");//資料插入到資料庫news表中
//展示：
$query = "select data from testtable where filename=$filename";
$result = mysql_query($query);
$COMPRESS_CONTENT=@gzuncompress($result["COMPRESS_CONTENT"]);
echo $COMPRESS_CONTENT;
?>
(2)儲存圖片
<?php
mysql_connect( "localhost", "root", "password"); //連線資料庫
mysql_select_db( "database"); //選定資料庫
//儲存：
$filename="" //這裡填入圖片路徑
$COMPRESS_CONTENT = addslashes(fread(fopen($filename, "r"), filesize($filename)));//開啟檔案並規範化資料存入變數$data中
$result=mysql_query( "insert into news (content) value ('$COMPRESS_CONTENT')");//資料插入到資料庫test表中
//展示：
ob_end_clean();
Header( "Content-type: image/gif");
$query = "select data from testtable where filename=$filename";
$result = mysql_query($query);
echo $result["COMPRESS_CONTENT"];
?>
※2022/06/09
有關blob mysql的PHP程式操作範例，可以參閱《Insert File into MySQL Blob with PHP》英文Youtube影片。
1 則留言