MySQL不同版本间bit(15)列导出导入后值显示变更的原因及恢复二进制格式方法问询

Hey Martin, let's break down this issue step by step—this is a common gotcha when moving between MySQL 5.7 and 8.0 with bit columns, and your scenario has two key parts to address.

一、Why did 111111101110111 turn into 32767 after import? #

First, let's clear up a common misconception: MySQL 5.7 and 8.0 have default display behaviors for bit(n) columns that differ—5.7 returns binary strings, 8.0 returns decimal values. But if that were the only issue, your original binary 111111101110111 would convert to decimal 32631, not 32767. So that's not the root cause here.

The real issue is almost certainly a data parsing mismatch during export/import:

• If you exported the bit(15) column as plain text (e.g., CSV, manual copy-paste) from 5.7, you ended up with the binary string 111111101110111.
• When importing this into MySQL 8.0, the server likely interpreted this string as a decimal number instead of a binary literal. That string as a decimal is way larger than the maximum value a bit(15) column can hold (32767, which is 2^15 - 1 or 15 bits all set to 1).
• MySQL automatically truncates values that exceed the column's range to the maximum allowed value—hence you're seeing 32767.

二、How to get back the binary display (or fix the data) #

We need to handle two scenarios depending on whether your data was actually corrupted or just displayed differently:

Scenario 1: Data is intact, just displayed as decimal #

If the underlying binary data is still correct (you can verify by running SELECT BIN(permissions) FROM your_table;—it should return 111111101110111), you can restore the binary display in a few ways:

• Ad-hoc queries: Use LPAD() with BIN() to ensure you get a 15-bit string with leading zeros:

  SELECT LPAD(BIN(permissions), 15, '0') AS permissions_binary FROM your_table;
  

• Permanent view for consistency: Create a view so you don't have to type the function every time:

  CREATE VIEW your_table_permissions AS
  SELECT *, LPAD(BIN(permissions), 15, '0') AS permissions_binary FROM your_table;
  

  Now query the view instead of the base table to see the binary format by default.

Scenario 2: Data was corrupted to 32767 #

If BIN(permissions) returns 111111111111111 (all 1s), your data was truncated during import. You'll need to re-export and re-import correctly:

1. Re-export from MySQL 5.7 with mysqldump: This tool preserves the binary storage format of bit columns, avoiding text parsing errors:

   mysqldump -u your_username -p --databases your_db --tables your_table > permissions_backup.sql
   

2. Import to MySQL 8.0: Use the official mysql client to load the dump, which will correctly interpret the bit data:

   mysql -u your_username -p your_db < permissions_backup.sql
   

3. Verify the data with the LPAD(BIN(...)) query above to confirm the binary string is back to 111111101110111.

三、Your permission check code will work fine #

Good news: Your existing permission validation logic (IF (IFNULL((@permission & b'1000000' > 0), 0) < 1) THEN ...) is fully compatible with MySQL 8.0. The bitwise & operation works directly on the underlying binary data, regardless of whether the column is displayed as decimal or binary. As long as the data is correct, your permission checks will behave exactly as they did in 5.7.

内容的提问来源于stack exchange，提问作者Martin AJ