I got an export of a MySQL table (version 5.7.42) containing a bit(15) column named permissions, the value was shown like this: 111111101110111. I imported that dump to a new MySQL server (version 8.0.34), now the value of that column is shown like this: 32767.
So, why 111111101110111 has converted to 32767 after export/import? Also, how can I make it back to 1 and 0 model? You know, each character of that value means an accessibility for my project. Please take a look at this code:
IF (IFNULL((@permission & b'1000000' > 0), 0) < 1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "No access to leave a comment";
END IF;
The conversion from 111111101110111 to 32767 is happening because the value is being treated as a 15-bit integer, and the binary representation 111111101110111 is indeed equivalent to the integer 32767.
In MySQL 8.0, the BIT data type represents the number as a binary string in certain contexts (like when fetching the value from a result set in some clients), but in other contexts, it might treat it as an integer. The inconsistency may cause confusion.
You can convert the integer value back to a binary string using the following SQL statement:
SELECT BIN(permissions + 0) AS permissions FROM your_table;
Adding 0 ensures that the value is treated as a number, and then BIN converts it to a binary string.
To make it look like a 15-character string, you may need to pad it with leading zeros. You can do this with the LPAD function:
SELECT LPAD(BIN(permissions + 0), 15, '0') AS permissions FROM your_table;