Binary comparison gives incorrect MYSQL result

Question

This DB is called Employees:

+--+----------------+
|ID|Ability_required|
+--+----------------+
|1 |   0000000111   |
|2 |   0000001111   |
|3 |   0000000111   |
|4 |   0000001101   |
|5 |   0000001111   |
+--+----------------+

And this SQL query:

SELECT ID FROM `Employees`
WHERE `Ability_required` & b'0000001111' = b'0000001111'

Why is my MYSQL returning lines number 1 and 3? What am I doing wrong here?

Check the image for an example:

Answer 1

You save numbers as strings, and bitwise and works with integers. MySQL will perform an implicit conversion, however, it assumes that the string represents a decimal number.

Convert the string manually using the MySQL function : CONV

SELECT * FROM `Employees`
WHERE CONV(`Ability_required`, 2, 10) & b'0000001111' = b'0000001111';

ID Ability_required
2  0000001111
5  0000001111

I would suggest using a field INT. Or normalize the table if possible.

Answer 2

, Especialidad_Nails , 0 1.

, , MySQL, : '0000000111' 111, b'1101111' -it, , ( !).

-, MySQL SET datatype - - (, , , ), , , , , FIND_IN_SET().

, , , . BOOLEAN.

, , ( @SalmanA ) MySQL CONV() .

Answer 3

capability_required. VARCHAR (10), , (x) . b'001111 ' , .

b'001111' = 15
b'000111' = 7
b'001101' = 13

_ INT BIT (8) TINYINT, .

,

SELECT ID, (`Ability_required` & b'0000001111') FROM `Employees` WHERE `Ability_required` & b'0000001111' = b'0000001111'

, , _ .

:

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ability` int(11) DEFAULT '0',
  `b_ability` bit(8) DEFAULT NULL,
  `v_ability` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

delete from employees;

insert into employees (ability, b_ability, v_ability) values (7,  b'000111', '000111');
insert into employees (ability, b_ability, v_ability) values (15, b'001111', '001111');
insert into employees (ability, b_ability, v_ability) values (7,  b'000111', '000111');
insert into employees (ability, b_ability, v_ability) values (13, b'001101', '001101');
insert into employees (ability, b_ability, v_ability) values (15, b'001111', '001111');

select *, (b'001111' & v_ability) from employees where v_ability & b'001111' = b'001111';

If you already have the data that you want to save in these columns, you can convert it using the function (mysql is not my strong point, it may be inefficient, but the function works):

CREATE FUNCTION `vcharbit_to_int`(bitdata varchar(16)) RETURNS int(11)
    DETERMINISTIC
BEGIN
     declare i INT DEFAULT length(bitdata);
     declare v INT default 0;
     declare b VARCHAR(1);
     declare ip INT default 1;

     while i > 0 do
        set b := SUBSTR(bitdata, i, 1);
        if b = '1' then 
            set v := v + ip;
        END IF;
        set i := i - 1;
        set ip := ip * 2;
     end while;

     RETURN v;
END

Inquiry:

select id, v_ability from employees where vcharbit_to_int(v_ability) & b'001111' = b'001111';

You will get the expected results.