如何更新bit(10)类型列？active列异常无法修改求助

Hey, let's break down what's going on with your active column and fix this step by step.

1. 为啥bit(10)会显示31位全1？ #

First off, most database clients (like MySQL Workbench, phpMyAdmin) don't show bit types "as-is"—they often convert them to integers or pad them to the system's default integer length (usually 32 bits). If your bit(10) column is actually storing a full 10 bits of 1s (that's decimal 1023), the client might display it as 32 bits of 1s (you're seeing 31 probably due to display truncation). That's often just a display quirk, not a storage issue.

But to be 100% sure, let's verify the column's actual definition first—run this:

DESCRIBE users;

Check that the Type for active is definitely bit(10). If it's not, someone might have altered the column type without you knowing, and you'll need to fix that first.

2. 为啥你的UPDATE语句没效果？ #

The main issue here is how you're assigning values to the bit column. When you use a decimal number like 101, the database has to convert it to binary—but sometimes client settings or conversion rules can mess this up, leading to silent failures.

Instead, use binary literals directly for bit columns—it's way more reliable. For example:

• If you want to set just the first bit (voting permission) to 1 and others to 0:

  UPDATE users SET active = b'0000000001' WHERE 1;
  

• If you meant decimal 101 (which is binary 1100101, padded to 10 bits it's 0001100101):

  UPDATE users SET active = b'0001100101' WHERE 1;
  

After running the update, check how many rows were affected to confirm it worked:

SELECT ROW_COUNT();

If it returns 0, either your table is empty, you don't have update permissions, or there's a trigger overriding the value. If it's greater than 0, the update worked—you just need to view the value correctly.

3. 怎么看真实的10位值？ #

To bypass the client's weird display, use SQL functions to see the actual value:

-- Get the raw binary string (no padding)
SELECT BIN(active) FROM users;
-- Convert to decimal to check the numeric value
SELECT CAST(active AS UNSIGNED) FROM users;

BIN() will show you the exact 10-bit binary string stored, so you can confirm if the update took effect.

Quick extra checks #

• Make sure there's no trigger on the users table that overwrites the active column every time you update it.
• Double-check that your database user has the UPDATE privilege for the users table.
• If you're using MySQL, check if strict SQL mode is enabled—it might be silently rejecting invalid assignments without throwing an error.

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