Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Binary , hexadecimal and octal values in mysql

Tags:

mysql

hex

octal

bin

I am very interested in working with binary , hexadecimal and octal systems in mysql database. First of all please give me an advice why we need them during storing information , because of ton of information or why ?

Also which type of values must be stored in marked systems ?

In addition here are bit operator like "<<"

here is example => SELECT 50<<2 AS example;

this gives us result 200 , anyone can explain how it is calculating it ?

Thanks for answering :))

like image 386
nanobash Avatar asked May 16 '12 19:05

nanobash


People also ask

What is binary data type in MySQL?

The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary ( _bin ) collation of the column character set (or of the table default character set if no column character set is specified). In this case, comparison and sorting are based on numeric character code values.

What is hex in MySQL?

HEX() : This function in MySQL is used to return an equivalent hexadecimal string value of a string or numeric Input. If the input is a string then each byte of each character in the string is converted to two hexadecimal digits.

What is 0x in MySQL?

The 0x notation is based on ODBC, for which hexadecimal strings are often used to supply values for BLOB columns.

What is Unhex?

(transitive) To remove a hex or curse from.


2 Answers

First of all please give me an advice why we need them during storing information

Computers store data in binary. Sometimes it's useful for us to think in terms of the actual bits that are stored, in which case our familiar decimal system can be a little awkward (as conversions are not straightforward); we could write the bits out in full, but that's often too cumbersome since even quite small numbers take up a lot of space to write (e.g. decimal 24521 is binary 101111111001001).

Instead, we tend to use bases which are some power of 2, since they're more compact than binary whilst having the property that each 'digit' represents an exact number of bits in the binary representation. For example, a hexadecimal (base-16) digit represents four bits (a "nibble") with the digits 0 through to F (decimal 15 / binary 1111); an octal (base-8) digit represents three bits with the digits 0 through to 7 (binary 111).

Our earlier example of decimal 24521 would be 5FC9 in hex or 57711 in octal: starting from the right you can see that each digit respectively represents 4 and 3 bits in the above binary representation. Therefore it is (relatively) easy for us humans to visualise the binary representation whilst looking at these compact representations in other bases.

Also which type of values must be stored in marked systems?

I'm not sure what you mean by this. As indicated above, the same values can be represented in all of these systems. In MySQL, we can indicate a binary literal by prepending it with 0b and a hexadecimal literal by prepending it with 0x. MySQL does not support octal literals.

anyone can explain how it is calculating it ?

The << operator performs a bitwise left-shift. That is, it shifts the bits of the left-hand operand left by the number of places given by the right-hand operand.

For each position the bits of an integer are shifted left, the value represented by those bits increases two-fold. It's similar to the effect of shifting digits left in our decimal system, whereby values increase ten-fold (for example, 50 shifted one place to the left gives 500, which is a ten-fold increase; in binary 110 (decimal 6) shifted one place left gives 1100 (decimal 12), which is a two-fold increase).

In your case, shifting the bits of the number 50 (i.e. 110010) two places to the left yields 2 two-fold increases (i.e. a four-fold increase overall): 11001000 is decimal 200.

like image 188
eggyal Avatar answered Sep 21 '22 05:09

eggyal


Your first two questions are too vague to answer, but the third one is concrete enough that I'll respond.

The <<2 is shifting the bits to the left 2 places, as documented here. This is the equivalent of multiplying 50 by 2^2:

mysql> SELECT 50<<2 AS example;
+---------+
| example |
+---------+
|     200 |
+---------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT 50 * POW(2,2) AS example;
+---------+
| example |
+---------+
|     200 |
+---------+
1 row in set (0.00 sec)
like image 31
Ike Walker Avatar answered Sep 20 '22 05:09

Ike Walker