How to read the value in a BINARY column in MySQL

Question

I want to add a BINARY(35) column to a table in which values are written whose bits are each assigned to a specific meaning.

i.e. "000110001010..."

1st bit: day 1, 2nd bit: day 2, etc..

I've found out how to write the value into the table

INSERT INTO MYTABLE VALUES(x'03011...');

but how do I retrieve it from the database?

If I cast the column as a character string, I'll loose everything past the first x'00' (NULL) in the value. In my application, its entirely possible that they'll still be '1's past this.

Because I'm using the C++ connector, I've only its API functions to retrieve the data so I'll need to know the type of the data retrieved. The API does not have a getBinary() function. If any of you can tell me which function to use, I'd really appreciate it.

If you're avoiding columns to begin with for some weird reason, why do you bother with binary? Stick a number in if you're so bent for saving space and making things harder for yourself. Your "binary" corresponds to a number. Also, this is the wrong way to use the databases, you could have gone with a text file. — N.B.
– N.B., Commented Nov 8, 2015 at 8:59

user3341576 · Accepted Answer · 2015-11-08 19:43:19Z

1

Got the answer from another Q&A site.

SELECT HEX(mycolumn) FROM MYTABLE;

If anyone wants to read more about this: Hexidecimal Literals: https://dev.mysql.com/doc/refman/5.7/en/hexadecimal-literals.html

Bit-Field Literals: https://dev.mysql.com/doc/refman/5.7/en/bit-field-literals.html

answered Nov 8, 2015 at 19:43

user3341576

1911 gold badge4 silver badges16 bronze badges

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

Comments

MIKE · Accepted Answer · 2015-11-07 18:33:09Z

0

Substring(cast column as varchar), 1,1)

answered Nov 7, 2015 at 18:33

MIKE

1,06910 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to read the value in a BINARY column in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related