What does "binary string" means in MySQL

Question

I'm reading the doc of MySQL. Where I found the UNHEX function is described as:

For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'

What is a "binary string"? Is it a general term or a term specifically used in MySQL?

I thought that if it is a "binary" data, then it shouldn't be called as a "string"(array of chars). So I'm wondering what is "binary string"?? does it means "array of bytes"?

I assume that eventually every datatype will be converted into binary data. And so does the "String Types" in MySQL. What makes the difference from a "string" and a "binary string" is that the "character string" has some information about what encoding schema and collation to be used when MySQL covert it from binary to a character string.

So what I don't quite understand is that why UNHEX('4D7953514C') returns a "character string" 'MySQL' but not a "binary string" represented by a sequence of 0s and 1s(0100 1101 0111 1001 0101 0011 0101 0001 0100 1100).

in good old C it's this: en.wikipedia.org/wiki/Bit_array

split
– split

2018-08-25 15:35:45 +00:00
Commented Aug 25, 2018 at 15:35 — split
– split, Commented Aug 25, 2018 at 15:35

Lajos Arpad · Accepted Answer · 2018-08-25 15:38:22Z

3

The term "binary string" in general means a sequence of 0s and 1s. In the case of the MySQL function unhex, it takes a string of hexadecimal values and converts it into a binary number, which will be represented as a string. Nothing fancy here. The result will be a string, which is the result of converting from hexadecimal number to binary number. A hexadecimal number is of four digits when converting into binary. The resulting sequence of 0s and 1s will be the binary representation of the resulting string.

answered Aug 25, 2018 at 15:38

Lajos Arpad

80.4k42 gold badges123 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yaquawa Over a year ago

Thanks! I edited my question a little bit. Just to make sure if I understood correctly. By "which will be represented as a string", do you mean an "array of chars" but not an "array of 0s and 1s"? If it is, what encoding schema to be used when MySQL convert a binary sequence to "array of chars"? Why MySQL want to convert the binary sequence to "array of chars"? Why not just literal binary data…?

Lajos Arpad Over a year ago

@yaquawa The input is a string of hexadecimal digits and the output is a string of bytes, or a string of 0s and 1s, if you will. It will be represented as a string, but it is essentially a binary number. You can use the encoding you prefer to vary the output. Here the difference between binary string and string is semantic. While a string in most cases is a result of user input, a text, in this case, the binary string is the result of a number base conversion. Strings, in most cases are texts, here they are numbers. The way how we look at them makes the difference.

Shadow · Accepted Answer · 2018-08-25 15:44:04Z

3

As mysql manual on binary and varbinary data type says, binary strings

contain byte strings rather than character strings. This means they have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in the values.

Essentialy, they are arrays of bytes.

answered Aug 25, 2018 at 15:44

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

1 Comment

theMyth Over a year ago

What does this mean though ? I have seen so many answers where the same quote, i created a table and added some characters like A,B,C with the binary type, but they did not store them as binary ?

Collectives™ on Stack Overflow

What does "binary string" means in MySQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related