Learning Focus

Use this lesson to understand BIT with practical syntax and examples.

MySQL BIT Data Type: Complete Guide

---

What is BIT?

BIT is a MySQL data type designed to store binary values (0/1) or bit-field masks. It's ideal for compact storage of multiple boolean flags or binary patterns in a single column.

Key Features

Feature	Details
Storage	1 to 64 bits (variable length)
Syntax	BIT(M) where M = number of bits (1-64)
Default	M=1 if not specified
Synonyms	None
Values	Binary (b'101'), integers, or hex

---

When to Use BIT?

[OK] Best for:

• Storing multiple boolean flags in a single column
• Bitmask patterns (e.g., permissions, feature toggles)
• Compact storage of binary data (e.g., hardware registers)

[X] Avoid for:

• Single true/false values (use BOOLEAN/TINYINT(1))
• Human-readable data (use CHAR/VARCHAR)
• Large binary data (use BLOB)

---

Syntax & Usage

Declaration

column_name BIT(M)

Example:

CREATE TABLE user_permissions (
    user_id INT PRIMARY KEY,
    flags BIT(8)  -- Stores 8 true/false flags (e.g., b'10101010')
);

Inserting Values

-- Binary literal:
INSERT INTO perms (flags) VALUES (b'1010');

-- Integer (converted to binary):
INSERT INTO perms (flags) VALUES (10);  -- 10 = 1010 in binary

-- Hex:
INSERT INTO perms (flags) VALUES (0xA);  -- A (hex) = 1010 (binary)

---

Retrieving BIT Values

Default Behavior

SELECT flags FROM perms;
-- Returns binary -> E (non-printable ASCII for b'10101010')

Readable Formats

-- Binary string:
SELECT BIN(flags) FROM perms;  -- Returns '1010'

-- Unsigned integer:
SELECT CAST(flags AS UNSIGNED) FROM perms;  -- Returns 10

---

BIT Operations

Bitwise Operators

-- Check if 3rd bit is set (bit positions start at 0):
SELECT * FROM perms WHERE flags & b'100';

-- Set 2nd bit:
UPDATE perms SET flags = flags | b'10';

-- Clear 1st bit:
UPDATE perms SET flags = flags & ~b'1';

Use Case: Feature Flags

CREATE TABLE features (
    id INT PRIMARY KEY,
    settings BIT(4)  -- Bits: [dark_mode, notifications, 2FA, admin]
);

-- Enable dark_mode (bit 0) and 2FA (bit 2):
INSERT INTO features (settings) VALUES (b'0101');

---

Storage Requirements

Bits (M)	Storage
1-8	1 byte
9-16	2 bytes
17-24	3 bytes
...	...
57-64	8 bytes

---

BIT vs Other Types

Type	Pros	Cons
BIT	Compact storage for bits	Complex queries
TINYINT	Easier to use	Wastes space for bits
SET	Human-readable	Limited to 64 options
VARCHAR	Readable	High storage overhead

---

Common Issues

Truncation

INSERT INTO perms (flags) VALUES (b'111100001');  -- Error if BIT(8) column

Implicit Conversion

SELECT * FROM perms WHERE flags = '10';  -- Fails! Use `b'1010'` or `CAST`

---

Best Practices

1. Name bits clearly: Use comments/documentation

   /* BIT(4): [3]Admin [2]2FA [1]Notifications [0]DarkMode */
   

2. Use with applications, not ad-hoc queries

3. Test bitwise operations thoroughly

4. Avoid for single flags: Use BOOLEAN instead

---

Summary

• BIT stores 1-64 bits efficiently
• Best for: Multi-flag systems, binary masks
• Watch for: Readability issues, truncation

---

Next Topics

1. Binary/VARBINARY (fixed/variable binary data)
2. Bitwise Functions (BIT_COUNT, <<, >>)
3. Boolean Logic in MySQL

Concept Map

flowchart LR
    A[Schema Context] --> B[BIT]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_bit (id INT PRIMARY KEY, value BIT);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: DOUBLE - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.