{"id":11703,"date":"2023-10-27T20:10:20","date_gmt":"2023-10-28T03:10:20","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=11703"},"modified":"2023-10-27T20:34:18","modified_gmt":"2023-10-28T03:34:18","slug":"mysql-varbinary","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-varbinary\/","title":{"rendered":"MySQL VARBINARY Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL VARBINARY<\/code> data type to store variable-length binary data.<\/p>\n\n\n\n

Introduction to MySQL VARBINARY data type<\/h2>\n\n\n\n

The VARBINARY<\/code> data type is used to store variable-length binary data. It is similar to the BINARY<\/a> data type but allows you to store binary data of variable length<\/strong>. <\/p>\n\n\n\n

The following shows how to define a VARBINARY<\/code> column in a table:<\/p>\n\n\n

column_name VARBINARY(max_length)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, we define the column_name<\/code> with the VARBINARY<\/code> data type that can store up to max_length<\/code> bytes. The maximum value for the max_length<\/code> is 65,535<\/code> bytes, which is equivalent to 64KB<\/code>.<\/p>\n\n\n\n
In practice, you often use the VARBINARY<\/code> data type for storing variable binary data such as small images, audio files, and other non-textual data.<\/p>\n\n\n\n
If you sort VARBINARY<\/code> data, MySQL treats zero bytes (0x00) and space differently in sorting operations such as ORDER<\/code> BY<\/code> and DISTINCT<\/code>. It places the zero bytes (0x00) before the space.<\/p>\n\n\n\n
When you insert data whose length exceeds the max_length<\/code>, MySQL drops extra bytes and issues a warning if SQL strict mode is not enabled and an error if the SQL strict mode is enabled.<\/p>\n\n\n\n
MySQL VARBINARY data type example<\/h2>\n\n\n\n
We’ll take an example of using the VARBINARY<\/code> data type to define a column that stores data.<\/p>\n\n\n\n
First, create a new table<\/a> called varbinary_demo<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> varbinary_demo(\n   id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n   data<\/span> VARBINARY(256<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The table varbinary_demo has two columns:<\/p>\n\n\n\n
Unlike the BINARY<\/a> data type, When you insert<\/a> data into a VARBINARY<\/code> column, MySQL does not pad zero bytes  (0x00<\/code>) if the length of the data is not equal to the max_length<\/code> of the column. Additionally, MySQL will not strip any bytes when you retrieve data from a VARBINARY<\/code> column.<\/p>\n\n\n\n