0

I am trying to store bitflags in MySQL to track user permissions for certain resources. However, I am experiencing some strange behavior with the binary field type and I cannot find a way to select a blob as a binary-string (i.e. ones and zeros) or decimal.

I am using MySQL v5.7.42 and Workbench v8.0.34. There is a pending ticket to update the MySQL version, but for the time being I am stuck using v5.

Here is an example I created to demonstrate the issue. In my script_rights table, the rights column is a BINARY(12) type:

INSERT INTO
    squid.script_rights
    (
        script_id,
        user,
        rights,
        granted_by
    )
VALUES
    (
        1,
        1234,
        b'10000', # This is the number 16, in binary
        5678
    );
    
SELECT
        script_id,
        user,
        CAST(rights AS CHAR) AS rights_cast,
        CONV(rights, 2, 10) AS rights_conv,
        granted_by
FROM
    squid.script_rights
WHERE
    user = 1234;

When I run the statements, I am getting useless data instead of a proper binary string:

script_id user rights_cast rights_conv granted_by
Unquoted 1 1234 0 5678
Quoted '1' '1234' '\0\0\0\0\0\0\0\0\0\0\0' '0' '5678'

The desired result should yield the value 000000010000 or 16 for the rights_cast or rights_conv field.

Is there a way I can achieve htis?

4
  • Use SET datatype instead of BINARY. the rights column is a BINARY(12) type 12-byte string? you need to track ~90 different permissions?
    – Akina
    Apr 1 at 19:48
  • How big is the blob you need to store? 0b and 0x are more friendly with INT than BINARY. The biggest int is 64 bits.
    – Rick James
    Apr 1 at 22:05
  • @Akina it looks like I have a misunderstanding of the binary data type. I want 12 bits, not bytes! Apr 2 at 12:36
  • @CardinalSystem I want 12 bits, not bytes! If so then you need in either BINARY(2) or SMALLINT. Also investigate SET datatype. Maybe it will be more useful for you..
    – Akina
    Apr 2 at 12:55

1 Answer 1

0

When you insert b'10000' into a column which have BINARY(12) datatype then this value which have 1-byte size is placed into the most first column byte, and the next 11 bytes are zero-padded. I.e. final value which is saved into this column with your INSERT query is (in hex) 10 00 00 00 00 00 00 00 00 00 00 00

CREATE TABLE test (rights BINARY(12));
INSERT INTO test VALUES (b'10000');
SELECT HEX(rights) FROM test;
HEX(rights)
100000000000000000000000

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.