支持长bitfield（位域）通配符查询的数据库有哪些？

Absolutely—there are several databases that support pattern-based queries on long bitfields, including the wildcard-style matching you’re describing with 0011XXXXXX (fixed first four bits: 0011, rest can be anything). Let me walk you through the most common options and how to implement this kind of query:

PostgreSQL #

PostgreSQL has first-class support for bit strings with the bit(n) (fixed-length) and varbit(n) (variable-length) types. You have two solid approaches here:

• Pattern matching with LIKE: Use underscores (_) to represent any single bit. For your 10-bit example, the query would look like:

  SELECT * FROM your_table WHERE bitfield LIKE B'0011______';
  

• Bitwise operations (more efficient): Use a mask to isolate the fixed bits and compare the result. This works great with indexes (you can add a B-tree index on the bitfield for faster lookups):

  -- Mask is 1111000000 (first 4 bits set), compare to 0011000000
  SELECT * FROM your_table WHERE (bitfield & B'1111000000') = B'0011000000';
  

MySQL/MariaDB #

MySQL and MariaDB support BIT types (up to 64 bits) plus binary string types (BINARY/VARBINARY) for longer bitfields. Here’s how to query your pattern:

• Bitwise comparison: For a 10-bit field, use bitwise AND with a mask to target the fixed bits:

  SELECT * FROM your_table WHERE (bitfield & 0b1111000000) = 0b0011000000;
  

• String pattern matching: If you store the bitfield as a string of 0s and 1s, use a LIKE clause:

  SELECT * FROM your_table WHERE bitfield LIKE '0011______';
  

Redis #

While Redis is a key-value store, it has robust bit manipulation capabilities, and with the RedisSearch extension, you can index and query bitfields directly:

• Bitwise operations with BITFIELD: For individual keys, you can use the BITFIELD command with a mask to check the fixed bits. To find all keys matching the pattern, combine SCAN with a Lua script that validates each key’s bitfield.
• RedisSearch pattern queries: Define an index with a BITFIELD field type, then query using wildcard patterns:

  FT.SEARCH your_index "@bitfield:0011*"
  

MongoDB #

MongoDB lets you store bitfields as BinData or string representations, with two ways to query your pattern:

• Regex matching (string storage): If you store the bitfield as a string, use a regular expression to target the fixed prefix:

  db.yourCollection.find({ bitfield: /^0011.{6}$/ })
  

  (The .{6} ensures the rest of the 10-bit string can be any value.)
• Bitwise operations (BinData storage): Use the $bit operator to compare against a mask:

  db.yourCollection.find({
    bitfield: {
      $bit: {
        and: 0b1111000000, // Mask to isolate first 4 bits
        eq: 0b0011000000   // Expected result
      }
    }
  })
  

Pro Tip #

For large datasets, prefer bitwise operations over string pattern matching—most databases can optimize these queries with indexes, leading to much faster lookups.

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