Set and Clear Bit Flags Atomically in MySQL

Bit flags are convenient when a row carries a small, fixed set of Boolean properties. The awkward part is updating several of them at once without disturbing any unrelated bit. Reading the value into an application, changing it, and writing it back works in a single-user test, but it also creates an unnecessary read-modify-write race. MySQL can perform the complete update atomically in one statement.

Assume bit positions are numbered from zero. Setting bit 3 means OR-ing with 1 << 3 = 8; clearing bit 4 means AND-ing with the complement of 1 << 4 = 16. The direct update is:

UPDATE account
SET flags = (flags & ~16) | 8
WHERE id = 5;

The parentheses are intentional. They make the order of the bitwise operations explicit and keep the expression readable when masks later become parameters.

Replacing Selected Bits

Start with the more general problem. Let mask select every position that may change, and let value contain the desired values at those positions. Then:

(flags & ~mask) | (value & mask)

In Boolean notation, for the complete bit vectors \(F\), \(M\), and \(V\), the update is

\[ F' = (F \land \neg M) \lor (V \land M). \]

The identity follows one bit at a time:

The first term removes the selected old bits. The second term inserts their replacements. Masking value matters: without value & mask, any set bit outside the selected region would leak into the result.

If value is constructed exclusively from selected positions, then \(V \land \neg M = 0\), and therefore \(V \land M = V\). Only under that invariant may the expression be shortened to:

(flags & ~mask) | value

Assigning One Flag from a Boolean

For a current value x, a selection mask b, and a Boolean f that is exactly zero or one, setting the selected bits when f = 1 and clearing them when f = 0 can be written directly as:

f ? (x | b) : (x & ~b)

At every position where b is zero, the result remains x. Where b is one, the result becomes f. Since -f is an all-zero word for zero and an all-one word for one in two's complement, the same bitwise multiplexer has the branch-free form:

x ^ (b & (x ^ -f))

For one flag at position p, substitute b = 1 << p:

x ^ ((x ^ -f) & (1 << p))

The corresponding atomic MySQL update uses the current column value as x. Validate in the application that :flag_value is exactly 0 or 1 before executing it:

UPDATE account
SET flags = flags ^ (
  (flags ^ -CAST(:flag_value AS SIGNED))
  & (CAST(1 AS UNSIGNED) << :position)
)
WHERE id = :id;

This is the single-bit specialization of the general masked replacement above. The ternary form is easier to read as a specification; the XOR form exposes how the same choice can be applied without branching.

Separate Set and Clear Masks

Many applications naturally produce two masks instead: set_mask lists bits that must become one, while clear_mask lists bits that must become zero. The update then becomes:

(flags & ~clear_mask) | set_mask

Every bit absent from both masks remains untouched. A bit present only in clear_mask is cleared, and one present in set_mask is set. If the masks overlap, this ordering gives set wins semantics because the final OR restores that bit. Reject overlaps in the application when they indicate a caller error:

if (($setMask & $clearMask) !== 0) {
    throw new InvalidArgumentException('Set and clear masks overlap');
}

If the intended rule is instead clear wins, reverse the logical order:

(flags | set_mask) & ~clear_mask

Parameterized MySQL Update

Use an unsigned column and cast bound masks explicitly so MySQL evaluates the expression numerically:

CREATE TABLE account (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    flags BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

UPDATE account
SET flags = (flags & ~CAST(:clear_mask AS UNSIGNED))
          | CAST(:set_mask AS UNSIGNED)
WHERE id = :id;

A modern PHP application can execute the statement with PDO. Binding masks as decimal strings also avoids accidentally converting the highest unsigned bit through PHP's signed integer representation:

<?php
$setMask = 1 << 3;
$clearMask = 1 << 4;

if (($setMask & $clearMask) !== 0) {
    throw new InvalidArgumentException('Set and clear masks overlap');
}

$statement = $pdo->prepare(
    'UPDATE account
     SET flags = (flags & ~CAST(:clear_mask AS UNSIGNED))
               | CAST(:set_mask AS UNSIGNED)
     WHERE id = :id'
);

$statement->execute([
    ':clear_mask' => (string) $clearMask,
    ':set_mask' => (string) $setMask,
    ':id' => $id,
]);

For the general replacement operation, bind both mask and value:

UPDATE account
SET flags = (flags & ~CAST(:mask AS UNSIGNED))
          | (CAST(:value AS UNSIGNED) & CAST(:mask AS UNSIGNED))
WHERE id = :id;

Why One Statement Matters

The expression uses the row's current flags value inside the UPDATE. With InnoDB, concurrent updates to the same row are serialized by the row lock, so each statement transforms the value left by the preceding statement. By contrast, an application-side sequence of SELECT, local modification, and UPDATE can overwrite a change committed between the read and write unless the transaction locks the row explicitly.

The operation is also idempotent: applying the same set and clear masks twice produces the same value as applying them once. That is useful when an update may be retried after a transient database error.

Width and Signedness

INT UNSIGNED provides 32 flag positions and BIGINT UNSIGNED provides 64. Keep the column, masks, and application model at the same width. In particular, bit 63 cannot be represented as a positive signed PHP integer on a 64-bit build; construct such masks as unsigned decimal strings or inside MySQL, for example CAST(1 AS UNSIGNED) << 63.

Bitmasks remain a good fit only for a small and stable vocabulary. If flags need foreign keys, individual metadata, open-ended growth, or frequent ad hoc filtering, a normalized relation is usually clearer. For application-side sets wider than MySQL's 64-bit numeric bit operations, BitSet.js provides arbitrary-size bit vectors, but the database representation then needs a deliberate binary or normalized design.

Practical Summary

• Replace selected bits with (flags & ~mask) | (value & mask).
• Set and clear independent groups with (flags & ~clear_mask) | set_mask.
• Define what overlap means; the latter expression makes setting win.
• Keep masks unsigned and within the width of the stored column.
• Perform the transformation in one UPDATE rather than in application-side read-modify-write code.