Specialized Types: ENUM, BOOLEAN, JSON0%

Specialized Types: ENUM, BOOLEAN, JSON

Beginner12 min read•Updated: 2026-09-12

Specialized Data Types: ENUM, BOOLEAN, and Native JSON

Beyond numbers, text, and dates, MySQL provides specialized data types designed for specific software engineering patterns: ENUM for restricted choice lists, BOOLEAN for true/false flags, and JSON for modern semi-structured document storage.

1. `ENUM`: Constrained String Choices

An ENUM is a string object whose value must be chosen from an explicit list of permitted values declared at table creation time:

1CREATE TABLE support_tickets (
2    ticket_id INT PRIMARY KEY AUTO_INCREMENT,
3    title VARCHAR(200) NOT NULL,
4    priority ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Medium',
5    status ENUM('Open', 'In_Progress', 'Resolved', 'Closed') NOT NULL DEFAULT 'Open'
6);

How ENUM Works Under the Hood

Although you query and insert strings (like 'High'), MySQL stores them internally as 1-byte or 2-byte integers representing index positions ('Low' = 1, 'Medium' = 2, 'High' = 3, 'Critical' = 4).
This makes ENUM extremely compact on disk and fast to compare.
If you attempt to insert an invalid value (e.g., 'Urgent'), MySQL in strict mode rejects the query with an error!

Important Warning

[!CAUTION] Avoid ENUM if the list of values changes frequently! Adding a new value to an ENUM requires an ALTER TABLE statement. If values change often, use a separate lookup table with a foreign key instead.

2. `BOOLEAN` / `BOOL`: Truth Values in MySQL

Does MySQL have a true native Boolean type? No!

In MySQL, BOOLEAN and BOOL are syntactic synonyms for TINYINT(1):

The literal keyword TRUE evaluates to the integer 1.
The literal keyword FALSE evaluates to the integer 0.

1CREATE TABLE customer_accounts (
2    account_id INT PRIMARY KEY AUTO_INCREMENT,
3    email VARCHAR(100) NOT NULL,
4    is_active BOOLEAN NOT NULL DEFAULT TRUE,
5    email_verified BOOLEAN NOT NULL DEFAULT FALSE
6);
7 
8-- Querying Boolean columns:
9SELECT * FROM customer_accounts WHERE is_active = TRUE;
10-- Equivalent to:
11SELECT * FROM customer_accounts WHERE is_active = 1;

3. Native `JSON` Data Type in MySQL 8.0+

MySQL provides native support for RFC 7159 JSON documents. Unlike storing JSON as a raw TEXT string, MySQL's native JSON data type:

1
Validates Syntax: Automatically rejects malformed JSON documents on insert.
2
Optimized Binary Format: Stored in an internal binary format that allows the server to read nested keys without parsing the entire JSON string!

1CREATE TABLE product_catalog (
2    product_id INT PRIMARY KEY AUTO_INCREMENT,
3    product_name VARCHAR(150) NOT NULL,
4    specifications JSON NOT NULL
5);
6 
7-- Inserting structured JSON documents:
8INSERT INTO product_catalog (product_name, specifications) VALUES
9('MacBook Pro M3', '{"brand": "Apple", "ram_gb": 16, "storage_gb": 512, "color": "Space Black"}'),
10('ThinkPad X1', '{"brand": "Lenovo", "ram_gb": 32, "storage_gb": 1024, "ports": ["USB-C", "HDMI"]}');

Querying JSON with Operators (`->` and `->>`)

-> (JSON Extract): Extracts value preserving JSON quotes (e.g., "Apple").
->> (JSON Unquote Extract): Extracts value as clean unquoted text (e.g., Apple).

1-- Find all laptops with 32 GB RAM:
2SELECT 
3    product_name,
4    specifications->>'$.brand' AS brand,
5    specifications->'$.ram_gb' AS ram
6FROM product_catalog
7WHERE specifications->'$.ram_gb' >= 32;

4. Best Practices & Common Pitfalls

Do Not Overuse JSON for Everything: Relational tables with typed columns are faster to index, enforce foreign keys, and perform joins on. Use JSON only for semi-structured, rapidly evolving, or sparse attributes.
Index JSON with Generated Columns: If you frequently query a JSON field (e.g., WHERE specifications->'$.brand' = 'Apple'), create a virtual generated column on that JSON path and put a standard B-Tree index on it!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In MySQL, what underlying data type is BOOLEAN internally an alias for?

Question 2

How does MySQL store the values of an ENUM('Small', 'Medium', 'Large') column on disk?

Question 3

What is the difference between the -> and ->> JSON extraction operators in MySQL?

Question 4

What happens when you attempt to insert an invalid string (not listed in the ENUM definition) in strict SQL mode?

Question 5

Why is MySQL's native JSON type superior to storing JSON in a standard TEXT column?

Next Lesson

Creating Tables Syntax & Rules

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Date & Time Types	Creating Tables Syntax & Rules

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Creating Tables Syntax & Rules

Specialized Types: ENUM, BOOLEAN, JSON

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Specialized Data Types: ENUM, BOOLEAN, and Native JSON

1. `ENUM`: Constrained String Choices

An ENUM is a string object whose value must be chosen from an explicit list of permitted values declared at table creation time:

1CREATE TABLE support_tickets (
2    ticket_id INT PRIMARY KEY AUTO_INCREMENT,
3    title VARCHAR(200) NOT NULL,
4    priority ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Medium',
5    status ENUM('Open', 'In_Progress', 'Resolved', 'Closed') NOT NULL DEFAULT 'Open'
6);

How ENUM Works Under the Hood

Although you query and insert strings (like 'High'), MySQL stores them internally as 1-byte or 2-byte integers representing index positions ('Low' = 1, 'Medium' = 2, 'High' = 3, 'Critical' = 4).
This makes ENUM extremely compact on disk and fast to compare.
If you attempt to insert an invalid value (e.g., 'Urgent'), MySQL in strict mode rejects the query with an error!

Important Warning

2. `BOOLEAN` / `BOOL`: Truth Values in MySQL

Does MySQL have a true native Boolean type? No!

In MySQL, BOOLEAN and BOOL are syntactic synonyms for TINYINT(1):

The literal keyword TRUE evaluates to the integer 1.
The literal keyword FALSE evaluates to the integer 0.

1CREATE TABLE customer_accounts (
2    account_id INT PRIMARY KEY AUTO_INCREMENT,
3    email VARCHAR(100) NOT NULL,
4    is_active BOOLEAN NOT NULL DEFAULT TRUE,
5    email_verified BOOLEAN NOT NULL DEFAULT FALSE
6);
7 
8-- Querying Boolean columns:
9SELECT * FROM customer_accounts WHERE is_active = TRUE;
10-- Equivalent to:
11SELECT * FROM customer_accounts WHERE is_active = 1;

3. Native `JSON` Data Type in MySQL 8.0+

MySQL provides native support for RFC 7159 JSON documents. Unlike storing JSON as a raw TEXT string, MySQL's native JSON data type:

1
Validates Syntax: Automatically rejects malformed JSON documents on insert.
2
Optimized Binary Format: Stored in an internal binary format that allows the server to read nested keys without parsing the entire JSON string!

1CREATE TABLE product_catalog (
2    product_id INT PRIMARY KEY AUTO_INCREMENT,
3    product_name VARCHAR(150) NOT NULL,
4    specifications JSON NOT NULL
5);
6 
7-- Inserting structured JSON documents:
8INSERT INTO product_catalog (product_name, specifications) VALUES
9('MacBook Pro M3', '{"brand": "Apple", "ram_gb": 16, "storage_gb": 512, "color": "Space Black"}'),
10('ThinkPad X1', '{"brand": "Lenovo", "ram_gb": 32, "storage_gb": 1024, "ports": ["USB-C", "HDMI"]}');

Querying JSON with Operators (`->` and `->>`)

-> (JSON Extract): Extracts value preserving JSON quotes (e.g., "Apple").
->> (JSON Unquote Extract): Extracts value as clean unquoted text (e.g., Apple).

1-- Find all laptops with 32 GB RAM:
2SELECT 
3    product_name,
4    specifications->>'$.brand' AS brand,
5    specifications->'$.ram_gb' AS ram
6FROM product_catalog
7WHERE specifications->'$.ram_gb' >= 32;

4. Best Practices & Common Pitfalls

Do Not Overuse JSON for Everything: Relational tables with typed columns are faster to index, enforce foreign keys, and perform joins on. Use JSON only for semi-structured, rapidly evolving, or sparse attributes.
Index JSON with Generated Columns: If you frequently query a JSON field (e.g., WHERE specifications->'$.brand' = 'Apple'), create a virtual generated column on that JSON path and put a standard B-Tree index on it!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In MySQL, what underlying data type is BOOLEAN internally an alias for?

Question 2

How does MySQL store the values of an ENUM('Small', 'Medium', 'Large') column on disk?

Question 3

What is the difference between the -> and ->> JSON extraction operators in MySQL?

Question 4

What happens when you attempt to insert an invalid string (not listed in the ENUM definition) in strict SQL mode?

Question 5

Why is MySQL's native JSON type superior to storing JSON in a standard TEXT column?

Next Lesson

Creating Tables Syntax & Rules

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Date & Time Types	Creating Tables Syntax & Rules

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Specialized Data Types: ENUM, BOOLEAN, and Native JSON

1. ENUM: Constrained String Choices

How ENUM Works Under the Hood

2. BOOLEAN / BOOL: Truth Values in MySQL

3. Native JSON Data Type in MySQL 8.0+

Querying JSON with Operators (-> and ->>)

4. Best Practices & Common Pitfalls

Multiple Choice Questions

Creating Tables Syntax & Rules

Related Lessons

Practice Quiz

Loading Page Content...

Specialized Data Types: ENUM, BOOLEAN, and Native JSON

1. ENUM: Constrained String Choices

How ENUM Works Under the Hood

2. BOOLEAN / BOOL: Truth Values in MySQL

3. Native JSON Data Type in MySQL 8.0+

Querying JSON with Operators (-> and ->>)

4. Best Practices & Common Pitfalls

Multiple Choice Questions

Creating Tables Syntax & Rules

Related Lessons

Practice Quiz

1. `ENUM`: Constrained String Choices

2. `BOOLEAN` / `BOOL`: Truth Values in MySQL

3. Native `JSON` Data Type in MySQL 8.0+

Querying JSON with Operators (`->` and `->>`)

1. `ENUM`: Constrained String Choices

2. `BOOLEAN` / `BOOL`: Truth Values in MySQL

3. Native `JSON` Data Type in MySQL 8.0+

Querying JSON with Operators (`->` and `->>`)