One TINYINT Field, Four Notification Switches: Bitmask Pattern in Practice

Why Use a Single Field

The author compares three approaches for storing four notification switches:

4 independent columns ( sms_flag, email_flag, in_app_flag, wechat_flag): 4 fields, low query complexity, adding a channel requires adding a column.

1 JSON column : 1 field, high query complexity (requires parsing), good extensibility.

1 integer column + bitwise operations : 1 field, very low query complexity, adding a channel only adds a bit.

The bitwise solution uses a TINYINT column where each bit represents one switch (bit 0 = SMS, bit 1 = email, bit 2 = in-app, bit 3 = WeChat). This yields 16 possible combinations (2⁴) in a single byte.

State Design

bit3  bit2  bit1  bit0
WeChat InApp Email SMS
1000  0100  0010  0001

0000

(0) = All off 0001 (1) = SMS only 0101 (5) = SMS + InApp 1111 (15) = All on

MySQL Layer

3.1 Table Definition

CREATE TABLE user_notify_setting (
  user_id      BIGINT PRIMARY KEY,
  notify_switch TINYINT NOT NULL DEFAULT 0
    COMMENT 'Notification bitmap: bit0-SMS-1 bit1-Email-2 bit2-InApp-4 bit3-WeChat-8',
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) COMMENT='User notification preferences';

3.2 Common SQL Operations

-- Check if email (bit1 = 2) is enabled
SELECT * FROM user_notify_setting
WHERE user_id = 1001 AND (notify_switch & 2) = 2;

-- Enable WeChat (bit3 = 8)
UPDATE user_notify_setting SET notify_switch = notify_switch | 8 WHERE user_id = 1001;

-- Disable SMS (bit0 = 1)
UPDATE user_notify_setting SET notify_switch = notify_switch & ~1 WHERE user_id = 1001;

-- Toggle InApp (bit2 = 4)
UPDATE user_notify_setting SET notify_switch = notify_switch ^ 4 WHERE user_id = 1001;

-- Batch enable SMS+Email (1|2=3) for multiple users
UPDATE user_notify_setting SET notify_switch = notify_switch | 3 WHERE user_id IN (1001,1002,1003);

-- Reset to all off
UPDATE user_notify_setting SET notify_switch = 0 WHERE user_id = 1001;

-- Reset to all on (4 bits = 15)
UPDATE user_notify_setting SET notify_switch = 15 WHERE user_id = 1001;

3.3 Indexing and Query Performance

Bitwise predicates like (notify_switch & 2) = 2 cannot use a regular index. For high-frequency single-bit queries, the author recommends a generated column with an index:

ALTER TABLE user_notify_setting
ADD COLUMN email_on TINYINT GENERATED ALWAYS AS ((notify_switch >> 1) & 1) STORED,
ADD INDEX idx_email_on (email_on);

In practice, notify_switch is usually read together with the primary key user_id ; standalone bit-range queries are rare, so extra indexes are often unnecessary.

Java Layer

4.1 Enum Definition

@Getter @AllArgsConstructor
public enum NotifySwitch {
  SMS(0, "SMS"),
  EMAIL(1, "Email"),
  IN_APP(2, "In-App"),
  WECHAT(3, "WeChat");

  private final int bitIndex;
  private final String desc;

  public int mask() { return 1 << bitIndex; }
}

4.2 Core Utility Class

public class SwitchBitUtil {
  private SwitchBitUtil() {}

  public static boolean isOn(int value, NotifySwitch sw) {
    return (value & sw.mask()) != 0;
  }
  public static int turnOn(int value, NotifySwitch sw) {
    return value | sw.mask();
  }
  public static int turnOff(int value, NotifySwitch sw) {
    return value & ~sw.mask();
  }
  public static int toggle(int value, NotifySwitch sw) {
    return value ^ sw.mask();
  }
  public static int turnOn(int value, NotifySwitch... switches) {
    int mask = 0;
    for (NotifySwitch sw : switches) mask |= sw.mask();
    return value | mask;
  }
  public static int turnOff(int value, NotifySwitch... switches) {
    int mask = 0;
    for (NotifySwitch sw : switches) mask |= sw.mask();
    return value & ~mask;
  }
  public static boolean isAllOn(int value) {
    return (value & 0x0F) == 0x0F;
  }
  public static boolean isAllOff(int value) {
    return (value & 0x0F) == 0;
  }
  public static String describe(int value) {
    StringBuilder sb = new StringBuilder();
    for (NotifySwitch sw : NotifySwitch.values()) {
      if (isOn(value, sw)) {
        if (sb.length() > 0) sb.append(",");
        sb.append(sw.name());
      }
    }
    return sb.length() == 0 ? "NONE" : sb.toString();
  }
}

4.3 Service Layer

@Service @RequiredArgsConstructor
public class NotifySettingService {
  private final UserNotifySettingMapper mapper;

  public NotifySettingVO getSetting(Long userId) {
    UserNotifySetting entity = mapper.selectByUserId(userId);
    int value = entity.getNotifySwitch();
    NotifySettingVO vo = new NotifySettingVO();
    vo.setSmsOn(SwitchBitUtil.isOn(value, NotifySwitch.SMS));
    vo.setEmailOn(SwitchBitUtil.isOn(value, NotifySwitch.EMAIL));
    vo.setInAppOn(SwitchBitUtil.isOn(value, NotifySwitch.IN_APP));
    vo.setWechatOn(SwitchBitUtil.isOn(value, NotifySwitch.WECHAT));
    vo.setRawValue(value);
    vo.setDescription(SwitchBitUtil.describe(value));
    return vo;
  }

  public int toggleSwitch(Long userId, NotifySwitch sw) {
    UserNotifySetting entity = mapper.selectByUserId(userId);
    int newValue = SwitchBitUtil.toggle(entity.getNotifySwitch(), sw);
    mapper.updateSwitch(userId, newValue);
    return newValue;
  }

  public int batchSet(Long userId, List<NotifySwitch> onList) {
    int value = 0;
    for (NotifySwitch sw : onList) value = SwitchBitUtil.turnOn(value, sw);
    mapper.updateSwitch(userId, value);
    return value;
  }
}

4.4 Controller Layer

@RestController @RequestMapping("/api/notify-setting") @RequiredArgsConstructor
public class NotifySettingController {
  private final NotifySettingService service;

  @GetMapping("/{userId}")
  public Result<NotifySettingVO> get(@PathVariable Long userId) {
    return Result.ok(service.getSetting(userId));
  }

  @PostMapping("/{userId}/toggle")
  public Result<Integer> toggle(@PathVariable Long userId, @RequestParam String switchName) {
    NotifySwitch sw = NotifySwitch.valueOf(switchName.toUpperCase());
    return Result.ok(service.toggleSwitch(userId, sw));
  }

  @PostMapping("/{userId}/batch")
  public Result<Integer> batchSet(@PathVariable Long userId, @RequestBody List<String> switches) {
    List<NotifySwitch> list = switches.stream()
      .map(s -> NotifySwitch.valueOf(s.toUpperCase()))
      .collect(Collectors.toList());
    return Result.ok(service.batchSet(userId, list));
  }
}

4.5 Usage Example

int value = 0; // all off
value = SwitchBitUtil.turnOn(value, NotifySwitch.SMS, NotifySwitch.EMAIL); // 0b0011 = 3
value = SwitchBitUtil.turnOn(value, NotifySwitch.WECHAT);                 // 0b1011 = 11
value = SwitchBitUtil.turnOff(value, NotifySwitch.SMS);                    // 0b1010 = 10
boolean emailOn = SwitchBitUtil.isOn(value, NotifySwitch.EMAIL);           // true
value = SwitchBitUtil.toggle(value, NotifySwitch.IN_APP);                  // 0b1110 = 14
System.out.println(SwitchBitUtil.describe(value)); // EMAIL,IN_APP,WECHAT

MyBatis Mapper

@Mapper
public interface UserNotifySettingMapper {
  @Select("SELECT * FROM user_notify_setting WHERE user_id = #{userId}")
  UserNotifySetting selectByUserId(@Param("userId") Long userId);

  @Update("UPDATE user_notify_setting SET notify_switch = #{value} WHERE user_id = #{userId}")
  int updateSwitch(@Param("userId") Long userId, @Param("value") int value);
}

Complete Data Flow

Frontend request: { "switches": ["SMS", "WECHAT"] }
        │
        ▼
Controller: parse to List<NotifySwitch>
        │
        ▼
Service:  value = 0
          value |= (1<<0) → 0b0001 (SMS)
          value |= (1<<3) → 0b1001 (WECHAT)
          value = 9
        │
        ▼
MySQL:    UPDATE ... SET notify_switch = 9
        │
        ▼
Storage:  TINYINT = 9 (binary 1001)

Extensibility: From 4 to N Switches

Adding a fifth channel (e.g., DingTalk) only requires:

// Add to enum
DINGTALK(4, "DingTalk"), // 1 << 4 = 16

Database : TINYINT supports up to 7 bits (max 127), SMALLINT up to 15 bits, INT up to 31 bits — no schema change needed .

Existing data : New bits default to 0 (off), fully backward compatible.

Key Considerations

Field type : 4–8 switches → TINYINT; up to 16 → SMALLINT; up to 32 → INT.

Bit limit : Java long has 63 usable bits (sign bit excluded); real-world cases rarely exceed 16.

Concurrency : Use atomic SQL: UPDATE ... SET switch = switch | 8 WHERE id=? to avoid read-modify-write races.

Readability : Admin UIs must call describe() to show human-readable text, never raw numbers.

No negatives : Bitwise fields should be UNSIGNED (≥ 0).

Avoid over-engineering : If switches have complex interdependent business logic, consider separate columns or JSON.

Summary

One TINYINT field = 4 independent switches = 2⁴ = 16 combinations

Using the bitmask pattern :

MySQL side : & for query, | to enable, & ~ to disable, ^ to toggle — all in single atomic statements.

Java side : Enum defines semantics; utility class encapsulates operations; business code contains zero magic numbers.

This is a classic practice that is storage-efficient, query-fast, and zero-cost to extend , widely used for permission bits, feature flags, message read markers, and similar scenarios.