{"id":11697,"date":"2023-10-27T19:16:29","date_gmt":"2023-10-28T02:16:29","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=11697"},"modified":"2024-01-29T20:28:16","modified_gmt":"2024-01-30T03:28:16","slug":"mysql-binary","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-binary\/","title":{"rendered":"MySQL BINARY Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL BINARY<\/code> data type and how to use it to store fixed-length binary data.<\/p>\n\n\n\n

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

The BINARY<\/code> data type is used to store fixed-length binary data. For example, you can use BINARY<\/code> data type for columns that store hashes and checksums such as SHA-256<\/code> because these values have a fixed length.<\/p>\n\n\n\n

To declare a column that uses the BINARY<\/code> data type, you specify the maximum length of binary data it can hold:<\/p>\n\n\n

column_name BINARY(size);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the size<\/code> specifies the number of bytes that the column name can store. <\/p>\n\n\n\n
Right-padding with 0x00 (zero bytes)<\/h3>\n\n\n\n
When you insert a binary value<\/a> whose length is less than the specified length for the BINARY<\/code> column, MySQL will automatically pad the value with zero bytes (0x00) on the right side to reach the defined size<\/code> length.<\/p>\n\n\n\n
No trailing byte removal for retrievals<\/h3>\n\n\n\n
When you retrieve a value from the BINARY<\/code> column, MySQL does not remove any trailing zero bytes that were padded during insertion. <\/p>\n\n\n\n
In other words, if you inserted a binary value and it was right-padded with zero bytes, those zero bytes will be present when you retrieve the data.<\/p>\n\n\n\n
All bytes are significant in comparisons<\/h3>\n\n\n\n
When comparing BINARY<\/code> value in the WHERE<\/a><\/code> clause, ORDER<\/a><\/code> clause, or DISTINCT<\/a><\/code>, MySQL considers all bytes. <\/p>\n\n\n\n
This means that even trailing zero bytes can affect the comparison results, and MySQL will not consider two BINARY<\/code> values are equal unless all of their bytes match.<\/p>\n\n\n\n
0x00 and space differ in comparisons<\/h3>\n\n\n\n
MySQL treats the zero bytes (0x00) and the space character (0x20) differently in comparisons. <\/p>\n\n\n\n
If you have a BINARY<\/code> column with values that contain zero bytes and space characters, MySQL will not consider these values to be equal. <\/p>\n\n\n\n
Additionally, MySQL places null bytes before space characters in sorting operations (e.g., ORDER<\/code> BY<\/code>).<\/p>\n\n\n\n
MySQL BINARY data type example<\/h2>\n\n\n\n
We’ll take an example of using the BINARY<\/code> data type to store SHA<\/code>-256 hashes.<\/p>\n\n\n\n
First, create a table<\/a> to store the SHA-256<\/code> hashes:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> binary_demo(\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    data<\/span> BINARY<\/span>(32<\/span>) -- 32 bytes for SHA-256<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The binary_demo<\/code> has two columns:<\/p>\n\n\n\n