BLOB or VARBINARY to store simple Strings that are converted into byte[ ]?

Question

I've read that In MySQL, BLOBs are often used to store image files, file path pointers, video & audio files, and any other big data objects.

But can BLOBs be used for simple Strings that are converted into byte[] (like names, address, etc.) or would VARBINARY suffice?

Or does it not really impact the database size and query performance?

Answer 1

Main differences between VARBINARY and BLOB are:

• VARBINARY must have length specification, BLOB must not (but may);
• Index by BLOB must have prefix length specification, VARBINARY must not (but may);
• VARBINARY may have default value, BLOB cannot.

can BLOBs be used for simple Strings that are converted into byte[] (like names, address, etc.) or would VARBINARY suffice?

Both variants are possible. The influence on the query performance depends of the query.

Answer 2

does it not really impact the database size and query performance.

As per MySQL documentation The BLOB and TEXT Types In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

• For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 8.3.5, "Column Indexes"".
• BLOB and TEXT columns cannot have DEFAULT values
• Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 8.4.4, "Internal Temporary Table Use in MySQL"). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns.
• The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump enable you to change the client-side max_allowed_packet value. See Section 5.1.1, "Configuring the Server", Section 4.5.1, "mysql — The MySQL Command-Line Tool", and Section 4.5.4, "mysqldump — A Database Backup Program". You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see Section 11.8, "Data Type Storage Requirements"

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

In some cases, it may be desirable to store binary data such as media files in BLOB or TEXT columns. You may find MySQL's string handling functions useful for working with such data. See Section 12.5, "String Functions". For security and other reasons, it is usually preferable to do so using application code rather than giving application users the FILE privilege.