Can user variables be assigned of BIT datatype?

All we need is an easy explanation of the problem, so here it is.

Based on the documentation:

User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

As you see, user variables aren’t supported BIT datatype. Right? Ok, I have this code:

BEGIN
    SELECT active INTO @active FROM users WHERE id = new.user_id;
    IF (IFNULL((@active & b'100' > 0), 0) < 1) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You cannot vote";
    END IF;
END

Note: active column is BIT(10).

You know, my code surprisingly works as well .. Why? I mean how MySQL treats with @active variable as a BIT datatype?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

b'100' is just the number 4 represented in a different way. Similarly for the column active and the variable @active.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply