Geek Answers Handbook

Why do I need to discard bit (1) as unsigned in mysql subquery?

For some reason, I have to explicitly specify the MySQL BIT (1) column as UNSIGNED in order to return 0 or 1 if I include this column as part of a subquery, but not as a regular query. Let me clarify ...

I have two database tables: contests and votes

CONTESTS - id, ..., ...
VOTES - ..., over BIT(1), ..., ...

If I run this query, I get results that I would expect ...

SELECT votes.over as vote_over FROM votes WHERE votes.contest_id = 38;

Result:

vote_over: 1    (ok, this makes sense)

If, however, I try to select vote.over as a subquery, I get a curious result ...

SELECT contests.id as contest_id,
    (SELECT over FROM votes WHERE votes.contest_id = contests.id) as vote_over
FROM contests
where contests.id = 38;

Result:

contest_id: 38
vote_over: 49    (WHAT?)

Why vote_over (column bit) 49 if it is a subquery, but 1 if it is included in the vanilla request above ??! And what is the meaning of 49? Why not 2377? 49 - 110001, and this problem would make a little more sense if I defined the "over" column as a bit type (6), but a bit (1). This makes no sense!

futzing , , , ...

SELECT `contests`.id as contest_id,
    (SELECT cast(`votes`.over as unsigned) FROM (`votes`) WHERE votes.contest_id = contests.id)
FROM (`contests`) as vote_over
where contests.id = 38;

:

contest_id: 38
vote_over: 1

, , , , , vote_over, unsigned .

: MySQL 5.6.14 . , , , . , , .

, - , , . , !

+4
1

, SELECT [bit_column] . , - .

http://dev.mysql.com/doc/refman/5.6/en/bit-field-literals.html:

. , 0 , ​​ BIN().

SELECT [bit_column] .

+1

More articles:


All Articles