Part 8A: Type Casting, Info Functions, Insert Variations & Full-Text Search

Section A — Type Casting & Conversion
Section B — Information Functions
Section C — Insert Variations
Section D — Full-Text Search

Sample Tables Used Throughout This Page: The examples below assume these tables exist. You can create them to follow along.

Setup: Create Sample Tables

CREATE TABLE products (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(100),
    category  VARCHAR(50),
    price     DECIMAL(10,2),
    stock     INT,
    description TEXT
);

CREATE TABLE customers (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(80),
    email   VARCHAR(120) UNIQUE,
    city    VARCHAR(60),
    country VARCHAR(60)
);

CREATE TABLE orders (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id  INT,
    quantity    INT,
    order_date  DATE,
    total       DECIMAL(10,2)
);

CREATE TABLE articles (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    title          VARCHAR(200),
    body           TEXT,
    author         VARCHAR(80),
    published_date DATE
);

-- Insert sample data
INSERT INTO products (name, category, price, stock, description) VALUES
('Wireless Mouse',    'Electronics', 29.99,  150, 'Ergonomic wireless mouse with USB receiver'),
('Mechanical Keyboard','Electronics', 89.50,  75,  'RGB mechanical keyboard with blue switches'),
('Notebook A5',        'Stationery',  4.99,   500, 'Ruled notebook 200 pages'),
('USB-C Hub',          'Electronics', 45.00,  200, '7-port USB-C hub with HDMI output'),
('Desk Lamp',          'Furniture',   34.75,  90,  'LED desk lamp with adjustable brightness');

INSERT INTO customers (name, email, city, country) VALUES
('Alice Johnson', 'alice@example.com',  'New York',  'USA'),
('Bob Smith',     'bob@example.com',    'London',    'UK'),
('Carlos Rivera', 'carlos@example.com', 'Madrid',    'Spain'),
('Diana Chen',    'diana@example.com',  'Toronto',   'Canada');

INSERT INTO orders (customer_id, product_id, quantity, order_date, total) VALUES
(1, 1, 2, '2025-01-15', 59.98),
(2, 2, 1, '2025-01-20', 89.50),
(1, 4, 1, '2025-02-03', 45.00),
(3, 3, 10, '2025-02-10', 49.90),
(4, 5, 1, '2025-03-01', 34.75);

INSERT INTO articles (title, body, author, published_date) VALUES
('Getting Started with MySQL',
 'MySQL is a powerful open source relational database management system. It is widely used for web applications and supports complex queries, transactions, and indexing.',
 'Alice Johnson', '2025-01-10'),
('Database Performance Tuning',
 'Performance tuning involves optimizing queries, adding proper indexes, and configuring the database server settings. Query optimization is the most impactful step.',
 'Bob Smith', '2025-02-15'),
('Introduction to NoSQL Databases',
 'NoSQL databases provide flexible schemas and horizontal scaling. Unlike relational databases, they do not require fixed table structures. MongoDB and Redis are popular choices.',
 'Carlos Rivera', '2025-03-01'),
('MySQL Full-Text Search Guide',
 'Full-text search in MySQL allows natural language queries against text columns. It supports boolean mode, natural language mode, and query expansion for powerful text searching.',
 'Diana Chen', '2025-03-20'),
('Building Scalable Web Applications',
 'Scalable web applications require careful database design, caching strategies, and load balancing. MySQL replication and partitioning help handle growing data volumes.',
 'Alice Johnson', '2025-04-05');

Type Casting & Conversion

A.1 — CAST(value AS type)

What it does: Converts a value from one data type to another. Think of it like converting between units — you are telling MySQL "treat this number as text" or "treat this text as a date." This is useful when you need to compare or display values in a different format.

INT to VARCHAR (number to text)

SELECT
    12345               AS original_number,
    CAST(12345 AS CHAR)  AS as_text,
    CONCAT('Order #', CAST(12345 AS CHAR)) AS combined;

original_number	as_text	combined
12345	12345	Order #12345

VARCHAR to INT (text to number)

SELECT
    '42'                          AS text_value,
    CAST('42' AS UNSIGNED)        AS as_integer,
    CAST('42' AS UNSIGNED) + 8    AS math_works;

text_value	as_integer	math_works
42	42	50

VARCHAR to DATE (text to date)

SELECT
    '2025-06-15'                              AS text_date,
    CAST('2025-06-15' AS DATE)              AS real_date,
    DAYNAME(CAST('2025-06-15' AS DATE))   AS day_of_week;

text_date	real_date	day_of_week
2025-06-15	2025-06-15	Sunday

DECIMAL to INT (removes decimal places)

SELECT
    price                                AS original_price,
    CAST(price AS UNSIGNED)            AS rounded_down,
    CAST(price AS DECIMAL(10,0))    AS rounded
FROM products
LIMIT 4;

original_price	rounded_down	rounded
29.99	29	30
89.50	89	90
4.99	4	5
45.00	45	45

A.2 — CONVERT(value, type)

What it does: Works exactly like CAST but uses a different syntax. Instead of CAST(x AS type), you write CONVERT(x, type). Both produce the same result — it is purely a style choice.

CONVERT Syntax Examples

SELECT
    CONVERT(99.7, UNSIGNED)    AS decimal_to_int,
    CONVERT(2025, CHAR)        AS int_to_text,
    CONVERT('89', UNSIGNED)    AS text_to_int;

decimal_to_int	int_to_text	text_to_int
99	2025	89

CONVERT vs CAST — Same Result

SELECT
    CAST(price AS CHAR)     AS cast_result,
    CONVERT(price, CHAR)    AS convert_result
FROM products
WHERE id = 1;

cast_result	convert_result
29.99	29.99

A.3 — CONVERT(string USING charset)

What it does: Changes the character encoding of a string. Character sets define how letters and symbols are stored as bytes. For example, utf8mb4 supports emojis while latin1 only supports basic Western characters. This is useful when combining data from different sources.

Character Set Conversion

SELECT
    CONVERT('Hello World' USING utf8mb4) AS utf8_text,
    CONVERT('Hello World' USING latin1)  AS latin_text,
    CHARSET(CONVERT('Hello' USING utf8mb4)) AS confirmed_charset;

utf8_text	latin_text	confirmed_charset
Hello World	Hello World	utf8mb4

Note: The visible output may look the same, but the underlying byte representation differs. This matters when storing special characters or comparing strings from different sources.

A.4 — BINARY (Case-Sensitive Comparison)

What it does: By default, MySQL string comparisons are case-insensitive — meaning 'abc' equals 'ABC'. Adding BINARY forces a byte-by-byte comparison, making it case-sensitive. Lowercase 'a' (byte 97) is different from uppercase 'A' (byte 65).

Default vs BINARY Comparison

SELECT
    'abc' = 'ABC'             AS normal_compare,    -- case-insensitive
    BINARY 'abc' = 'ABC'      AS binary_compare,    -- case-sensitive
    'abc' = 'abc'             AS same_case,
    BINARY 'abc' = 'abc'      AS binary_same_case;

normal_compare	binary_compare	same_case	binary_same_case
1	0	1	1

Practical Example: Case-Sensitive Name Search

-- Without BINARY: finds both 'alice' and 'Alice'
SELECT name FROM customers WHERE name LIKE 'alice%';
-- Result: Alice Johnson

-- With BINARY: strict case match
SELECT name FROM customers WHERE BINARY name LIKE 'alice%';
-- Result: (empty set) -- no lowercase 'alice' exists

Query	Result
WHERE name LIKE 'alice%'	Alice Johnson
WHERE BINARY name LIKE 'alice%'	(empty set)

Note: In MySQL 8.0, BINARY as a cast operator is deprecated. Prefer using CAST(expr AS BINARY) or a binary collation like WHERE name COLLATE utf8mb4_bin = 'value'.

Information Functions

B.1 — DATABASE()

What it does: Returns the name of the database you are currently using. If you have not selected any database yet, it returns NULL.

Current Database Name

USE my_store;

SELECT DATABASE() AS current_db;

current_db
my_store

B.2 — USER() / CURRENT_USER()

What it does: USER() returns the username and hostname you connected with. CURRENT_USER() returns the account MySQL actually authenticated you as (which may differ if a proxy or wildcard host is used).

Current User Information

SELECT
    USER()          AS connected_as,
    CURRENT_USER() AS authenticated_as;

connected_as	authenticated_as
root@localhost	root@localhost

B.3 — VERSION()

What it does: Returns the MySQL server version as a string. Useful for checking compatibility or debugging.

MySQL Server Version

SELECT VERSION() AS mysql_version;

mysql_version
8.0.36

B.4 — CONNECTION_ID()

What it does: Returns a unique integer identifying your current connection to the MySQL server. Every time you connect, you get a different ID. Useful for debugging or killing a specific connection.

Current Connection ID

SELECT CONNECTION_ID() AS my_connection;

my_connection
47

B.5 — LAST_INSERT_ID()

What it does: After you INSERT a row into a table with an AUTO_INCREMENT column, this function returns the ID that was generated. This is essential when you need to insert into a parent table and then reference that new ID in a child table.

Practical: Insert Customer, Then Insert Order

-- Step 1: Insert a new customer
INSERT INTO customers (name, email, city, country)
VALUES ('Eve Parker', 'eve@example.com', 'Sydney', 'Australia');

-- Step 2: Get the auto-generated ID
SELECT LAST_INSERT_ID() AS new_customer_id;

new_customer_id
5

Step 3: Use it in the next INSERT

INSERT INTO orders (customer_id, product_id, quantity, order_date, total)
VALUES (LAST_INSERT_ID(), 2, 1, '2025-04-10', 89.50);

-- Verify: the order references customer 5
SELECT o.id, o.customer_id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 5;

id	customer_id	name
6	5	Eve Parker

Note: LAST_INSERT_ID() is connection-specific. Two users inserting at the same time will each see their own last ID, not the other's.

B.6 — FOUND_ROWS()

What it does: When you use SQL_CALC_FOUND_ROWS with a LIMIT, MySQL calculates how many total rows matched (ignoring the LIMIT). You then call FOUND_ROWS() to get that total. This is useful for pagination — "showing 1-10 of 47 results."

Pagination Example

-- Get first 2 products, but also count the total
SELECT SQL_CALC_FOUND_ROWS id, name, price
FROM products
ORDER BY price DESC
LIMIT 2;

id	name	price
2	Mechanical Keyboard	89.50
4	USB-C Hub	45.00

Now Get the Total Count

SELECT FOUND_ROWS() AS total_matching;

total_matching
5

Note: SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0.17+. The recommended approach is to run a separate SELECT COUNT(*) query instead. However, you will still see it in legacy code.

B.7 — ROW_COUNT()

What it does: Returns the number of rows affected by the previous INSERT, UPDATE, or DELETE statement. For SELECT statements, it returns -1.

Rows Affected by UPDATE

UPDATE products
SET stock = stock + 10
WHERE category = 'Electronics';

SELECT ROW_COUNT() AS rows_updated;

rows_updated
3

Rows Affected by DELETE

DELETE FROM orders WHERE total < 40;

SELECT ROW_COUNT() AS rows_deleted;

rows_deleted
1

B.8 — CHARSET()

What it does: Returns the character set of a given string expression. Helps you verify what encoding a column or literal is using.

Check Character Sets

SELECT
    CHARSET('Hello')       AS literal_charset,
    CHARSET(name)           AS column_charset,
    CHARSET(CONVERT('Hi' USING latin1)) AS converted_charset
FROM customers
LIMIT 1;

literal_charset	column_charset	converted_charset
utf8mb4	utf8mb4	latin1

B.9 — COLLATION()

What it does: Returns the collation of a string. A collation defines the rules for sorting and comparing characters — for example, whether 'a' comes before 'B', or whether accented characters like 'e' and 'é' are treated as equal.

Check Collation

SELECT
    COLLATION('Hello') AS literal_collation,
    COLLATION(name)    AS column_collation
FROM customers
LIMIT 1;

literal_collation	column_collation
utf8mb4_0900_ai_ci	utf8mb4_0900_ai_ci

Note: ai_ci stands for "accent insensitive, case insensitive" — the default in MySQL 8.0. This means 'cafe' and 'café' are treated as equal.

B.10 — UUID()

What it does: Generates a universally unique identifier — a 36-character string that is guaranteed to be unique across all computers. UUIDs are great for primary keys in distributed systems or when you need IDs that cannot be guessed.

Generate UUIDs

SELECT
    UUID() AS uuid_1,
    UUID() AS uuid_2;

uuid_1	uuid_2
a1b2c3d4-e5f6-7890-abcd-ef1234567890	b2c3d4e5-f6a7-8901-bcde-f12345678901

Practical: Use UUID as a Token

SELECT
    UUID()                        AS full_uuid,
    REPLACE(UUID(), '-', '')    AS compact_uuid,
    LENGTH(UUID())                AS uuid_length;

full_uuid	compact_uuid	uuid_length
c3d4e5f6-a7b8-9012-cdef-123456789abc	c3d4e5f6a7b89012cdef123456789abc	36

B.11 — UUID_SHORT()

What it does: Returns a short 64-bit unsigned integer that is unique (as long as certain conditions are met). It is much faster than UUID() and produces a simple number instead of a long string.

Short Unique Integer

SELECT
    UUID_SHORT() AS short_id_1,
    UUID_SHORT() AS short_id_2;

short_id_1	short_id_2
98745632101234567	98745632101234568

Note: UUID_SHORT() values are unique only if you have fewer than 256 server restarts and fewer than 16 million concurrent connections. For most applications, this is fine.

B.12 — SLEEP(seconds)

What it does: Pauses execution for the specified number of seconds. Returns 0 if the sleep completed normally. Useful for testing timeouts, simulating delays, or debugging concurrency.

Pause Execution

-- Pause for 2 seconds
SELECT SLEEP(2) AS result;
-- (query takes 2 seconds to return)

result
0

Practical: Verify Timing

SELECT NOW() AS before_sleep;
SELECT SLEEP(3);
SELECT NOW() AS after_sleep;

before_sleep		after_sleep
2025-04-10 14:00:00	→ 3 sec →	2025-04-10 14:00:03

Warning: Never use SLEEP() in production queries. It blocks the connection and wastes server resources. It is strictly a testing/debugging tool.

B.13 — BENCHMARK(count, expr)

What it does: Executes an expression a specified number of times and reports how long it took. The result is always 0 — you look at the execution time to measure performance. Useful for comparing the speed of different functions.

Measure Expression Speed

-- Run MD5 hash one million times
SELECT BENCHMARK(1000000, MD5('Hello World')) AS result;
-- Result: 0  (check execution time: ~0.45 sec)

result	Execution Time
0	0.45 sec

Compare Two Functions

-- Test SHA2 (slower, more secure)
SELECT BENCHMARK(1000000, SHA2('test', 256)) AS sha2_result;
-- Execution time: ~1.20 sec

-- Test MD5 (faster, less secure)
SELECT BENCHMARK(1000000, MD5('test')) AS md5_result;
-- Execution time: ~0.42 sec

Function	Iterations	Approx. Time
MD5('test')	1,000,000	~0.42 sec
SHA2('test', 256)	1,000,000	~1.20 sec

Note: BENCHMARK() always returns 0. The value you care about is the query execution time shown by your MySQL client.

Insert Variations

C.1 — REPLACE INTO

What it does: Works like INSERT, but if a row with the same PRIMARY KEY or UNIQUE key already exists, it deletes the old row first and then inserts the new one. It is a "delete + insert" in one statement.

Before: Current products table

SELECT id, name, price, stock FROM products WHERE id IN (1, 2);

id	name	price	stock
1	Wireless Mouse	29.99	150
2	Mechanical Keyboard	89.50	75

REPLACE: Update Existing + Insert New

-- id=1 exists -> delete old row, insert new one
REPLACE INTO products (id, name, category, price, stock, description)
VALUES (1, 'Wireless Mouse Pro', 'Electronics', 39.99, 200,
        'Upgraded wireless mouse with Bluetooth');

-- id=6 does not exist -> normal insert
REPLACE INTO products (id, name, category, price, stock, description)
VALUES (6, 'Webcam HD', 'Electronics', 54.99, 60,
        '1080p HD webcam with microphone');

After: Check the results

SELECT id, name, price, stock FROM products WHERE id IN (1, 2, 6);

id	name	price	stock
1	Wireless Mouse Pro	39.99	200
2	Mechanical Keyboard	89.50	75
6	Webcam HD	54.99	60

Warning: REPLACE deletes the old row before inserting. This means any columns you do not specify will get default values (not the old values). It also triggers DELETE triggers and ON DELETE CASCADE. Use with caution.

C.2 — INSERT ... ON DUPLICATE KEY UPDATE

What it does: Tries to INSERT a row. If a duplicate key conflict occurs, instead of failing, it runs an UPDATE on the existing row. Unlike REPLACE, it does not delete the old row — it modifies it in place. This is one of the most useful MySQL features for "upsert" operations.

Before: Current products table

SELECT id, name, price, stock FROM products WHERE id IN (1, 2);

id	name	price	stock
1	Wireless Mouse Pro	39.99	200
2	Mechanical Keyboard	89.50	75

Upsert: Update if exists, Insert if new

-- id=2 exists -> UPDATE the price and stock
INSERT INTO products (id, name, category, price, stock, description)
VALUES (2, 'Mechanical Keyboard', 'Electronics', 79.99, 100,
        'RGB mechanical keyboard with blue switches')
ON DUPLICATE KEY UPDATE
    price = VALUES(price),
    stock = VALUES(stock);

-- id=7 does not exist -> normal INSERT
INSERT INTO products (id, name, category, price, stock, description)
VALUES (7, 'Monitor Stand', 'Furniture', 29.99, 45,
        'Adjustable monitor riser')
ON DUPLICATE KEY UPDATE
    price = VALUES(price),
    stock = VALUES(stock);

After: Check the results

SELECT id, name, price, stock FROM products WHERE id IN (1, 2, 7);

id	name	price	stock
1	Wireless Mouse Pro	39.99	200
2	Mechanical Keyboard	79.99	100
7	Monitor Stand	29.99	45

REPLACE vs ON DUPLICATE KEY UPDATE

REPLACE: DELETE old + INSERT new

ODKU: UPDATE existing row in place

Practical: Increment Stock on Duplicate

-- If product already exists, ADD to existing stock instead of replacing
INSERT INTO products (id, name, category, price, stock, description)
VALUES (2, 'Mechanical Keyboard', 'Electronics', 79.99, 50,
        'RGB mechanical keyboard')
ON DUPLICATE KEY UPDATE
    stock = stock + VALUES(stock);  -- 100 + 50 = 150

SELECT id, name, stock FROM products WHERE id = 2;

id	name	stock
2	Mechanical Keyboard	150

Real-World Use Cases

1. Inventory sync: Import a product feed daily — new items get inserted, existing items get their price/stock updated.
2. View counters: INSERT a page view record or UPDATE the count if the page already has one.
3. User preferences: Save a preference; if it already exists, overwrite it.

C.3 — INSERT IGNORE

What it does: Tries to INSERT a row. If a duplicate key conflict occurs, it silently skips that row instead of throwing an error. The existing row stays unchanged. Useful when importing data where some records may already exist.

Attempt to Insert Duplicates

-- alice@example.com already exists (customer id=1)
INSERT IGNORE INTO customers (name, email, city, country) VALUES
('Alice Johnson', 'alice@example.com', 'New York', 'USA'),      -- duplicate! skipped
('Frank Lopez',   'frank@example.com', 'Berlin',   'Germany'),  -- new! inserted
('Bob Smith',     'bob@example.com',   'London',   'UK');       -- duplicate! skipped

Query Result
Query OK, 1 row affected, 2 warnings

Verify: Only New Row Was Inserted

SELECT id, name, email FROM customers ORDER BY id;

id	name	email
1	Alice Johnson	alice@example.com
2	Bob Smith	bob@example.com
3	Carlos Rivera	carlos@example.com
4	Diana Chen	diana@example.com
5	Eve Parker	eve@example.com
6	Frank Lopez	frank@example.com

Note: INSERT IGNORE also silently converts invalid data (like truncating a too-long string) instead of erroring. Be careful — it can hide data issues. Always check the warning count after using it.

C.4 — VALUES() / VALUE() in ON DUPLICATE KEY UPDATE

What it does: Inside an ON DUPLICATE KEY UPDATE clause, VALUES(column_name) refers to the value that would have been inserted. This lets you reference the new values you tried to insert and decide how to use them in the update.

Using VALUES() to Reference Attempted Insert Values

INSERT INTO products (id, name, category, price, stock, description)
VALUES (3, 'Premium Notebook A5', 'Stationery', 7.99, 300,
        'Premium ruled notebook 200 pages')
ON DUPLICATE KEY UPDATE
    name  = VALUES(name),         -- use the new name
    price = VALUES(price),        -- use the new price (7.99)
    stock = stock + VALUES(stock); -- ADD new stock to existing

Column	Before	VALUES() returns	After
name	Notebook A5	Premium Notebook A5	Premium Notebook A5
price	4.99	7.99	7.99
stock	500	300	800 (500 + 300)

MySQL 8.0.20+ Update: VALUES() in ON DUPLICATE KEY UPDATE is deprecated. The new syntax uses row aliases:

New Syntax (MySQL 8.0.20+)

INSERT INTO products (id, name, category, price, stock, description)
VALUES (3, 'Premium Notebook A5', 'Stationery', 7.99, 300,
        'Premium ruled notebook')
    AS new_row                    -- alias for the new row
ON DUPLICATE KEY UPDATE
    name  = new_row.name,         -- cleaner syntax
    price = new_row.price,
    stock = stock + new_row.stock;

Summary: When to Use Each Insert Variation

INSERT IGNORE — Skip duplicates silently (data import where you don't care about existing rows)
REPLACE INTO — Delete old + insert new (when you want to completely overwrite, but beware of side effects)
ON DUPLICATE KEY UPDATE — Update existing in place (best for most "upsert" scenarios)

Full-Text Search

What is full-text search? Normal WHERE name LIKE '%keyword%' searches are slow on large tables because MySQL has to scan every row. Full-text search creates a special index of all the words in your text columns, making word-based searches extremely fast. Think of it like the index at the back of a textbook.

D.1 — CREATE FULLTEXT INDEX

What it does: Creates a special index that breaks text columns into individual words and stores them for fast searching. You must create a FULLTEXT index before using MATCH...AGAINST.

Create Full-Text Indexes

-- Index on a single column
CREATE FULLTEXT INDEX ft_articles_body
ON articles(body);

-- Index on multiple columns (searches across both)
CREATE FULLTEXT INDEX ft_articles_title_body
ON articles(title, body);

-- Index on product descriptions
CREATE FULLTEXT INDEX ft_products_desc
ON products(name, description);

Alternative: Add at table creation

CREATE TABLE blog_posts (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    body  TEXT,
    FULLTEXT(title, body)   -- defined inline
);

Note: Full-text indexes work on CHAR, VARCHAR, and TEXT columns. In InnoDB (the default engine in MySQL 8.0), full-text search is fully supported.

D.2 — MATCH() AGAINST() — Natural Language Mode

What it does: Searches for words in the indexed columns and ranks results by relevance. More relevant matches (the search word appears more times, or in shorter documents) get higher scores. This is the default mode.

Basic Natural Language Search

SELECT
    id,
    title,
    MATCH(title, body) AGAINST('database') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database')
ORDER BY relevance DESC;

id	title	relevance
3	Introduction to NoSQL Databases	0.6834
1	Getting Started with MySQL	0.3127
2	Database Performance Tuning	0.2851
5	Building Scalable Web Applications	0.1543

Search for Multiple Words

SELECT
    id,
    title,
    ROUND(MATCH(title, body) AGAINST('MySQL query optimization'), 4) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL query optimization')
ORDER BY score DESC;

id	title	score
2	Database Performance Tuning	0.9412
1	Getting Started with MySQL	0.7823
4	MySQL Full-Text Search Guide	0.6241

Note: Natural language mode ignores words shorter than the minimum word length (default: 3 for InnoDB). Common words that appear in more than 50% of rows (called "stopwords") are also ignored.

D.3 — MATCH() AGAINST() — Boolean Mode

What it does: Gives you precise control over your search using special operators. You can require words, exclude words, use wildcards, and search for exact phrases. Think of it like advanced Google search operators.

Boolean Mode Operators

Operator	Meaning	Example
`+`	Word MUST be present	`+database`
`-`	Word must NOT be present	`-NoSQL`
`*`	Wildcard (word prefix)	`optim*` matches optimize, optimization
`"..."`	Exact phrase	`"full-text search"`
`>`	Increase relevance	`>important`
`<`	Decrease relevance	`<trivial`
`~`	Negate relevance (still match)	`~unwanted`
(none)	Optional — nice to have	`database`

+Required Word

-- Must contain 'MySQL'
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL' IN BOOLEAN MODE);

id	title
1	Getting Started with MySQL
4	MySQL Full-Text Search Guide
5	Building Scalable Web Applications

-Excluded Word

-- Must contain 'database', must NOT contain 'NoSQL'
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+database -NoSQL' IN BOOLEAN MODE);

id	title
1	Getting Started with MySQL
2	Database Performance Tuning
5	Building Scalable Web Applications

*Wildcard (Prefix Search)

-- Any word starting with 'optim' (optimize, optimization, optimizing...)
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('optim*' IN BOOLEAN MODE);

id	title
2	Database Performance Tuning

"Exact Phrase"

-- Search for the exact phrase "full-text search"
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('"full-text search"' IN BOOLEAN MODE);

id	title
4	MySQL Full-Text Search Guide

Combined Boolean Operators

-- Must have 'MySQL', should have 'performance', must NOT have 'NoSQL'
SELECT
    id, title,
    ROUND(MATCH(title, body)
        AGAINST('+MySQL performance -NoSQL' IN BOOLEAN MODE), 4) AS score
FROM articles
WHERE MATCH(title, body)
    AGAINST('+MySQL performance -NoSQL' IN BOOLEAN MODE)
ORDER BY score DESC;

id	title	score
4	MySQL Full-Text Search Guide	1.2105
1	Getting Started with MySQL	0.6834
5	Building Scalable Web Applications	0.3127

D.4 — MATCH() AGAINST() WITH QUERY EXPANSION

What it does: Performs the search twice. First, it finds rows matching your search term. Then, it looks at the most relevant words in those results and searches again using those words too. This finds related documents that may not contain your exact search term. Think of it as "find what I mean, not just what I typed."

Without Query Expansion

-- Only finds articles that literally contain 'scaling'
SELECT id, title,
    ROUND(MATCH(title, body) AGAINST('scaling'), 4) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('scaling');

id	title	score
3	Introduction to NoSQL Databases	0.4521

With Query Expansion (broader results)

-- First finds 'scaling', then expands using related words
SELECT id, title,
    ROUND(MATCH(title, body)
        AGAINST('scaling' WITH QUERY EXPANSION), 4) AS score
FROM articles
WHERE MATCH(title, body)
    AGAINST('scaling' WITH QUERY EXPANSION)
ORDER BY score DESC;

id	title	score
3	Introduction to NoSQL Databases	3.8412
5	Building Scalable Web Applications	2.5637
2	Database Performance Tuning	1.1254
1	Getting Started with MySQL	0.6312

Note: Query expansion can return unexpected results since it broadens the search significantly. It works best when the initial search returns too few results and you want to "cast a wider net."

When to Use Each Full-Text Mode

Natural Language Mode: Default, good for simple searches — "find articles about databases."
Boolean Mode: When users need precise control — must include, must exclude, exact phrases.
Query Expansion: When you want related results, like a recommendation engine or "did you mean?" feature.

D.5 — Practical: Full-Text Search on Products

Putting it all together: Here is a real-world example of searching a product catalog using full-text search.

Search Products by Description

-- Find products related to 'USB wireless'
SELECT
    id,
    name,
    price,
    ROUND(MATCH(name, description)
        AGAINST('USB wireless'), 4) AS relevance
FROM products
WHERE MATCH(name, description) AGAINST('USB wireless')
ORDER BY relevance DESC;

id	name	price	relevance
1	Wireless Mouse Pro	39.99	1.5234
4	USB-C Hub	45.00	0.8721

Boolean: Products with 'LED' but not 'USB'

SELECT id, name, price
FROM products
WHERE MATCH(name, description) AGAINST('+LED -USB' IN BOOLEAN MODE);

id	name	price
5	Desk Lamp	34.75

Part 8A Key Takeaways

CAST/CONVERT — Convert between data types; use CAST(x AS type) or CONVERT(x, type)
BINARY — Forces case-sensitive comparison (deprecated as operator; use CAST instead)
Info functions — DATABASE(), USER(), VERSION() reveal server state; LAST_INSERT_ID() is essential for related inserts
INSERT IGNORE — Skips duplicate rows silently
REPLACE INTO — Deletes old row + inserts new one (careful with side effects)
ON DUPLICATE KEY UPDATE — The preferred "upsert" pattern; updates in place
Full-text search — Create a FULLTEXT index, then use MATCH...AGAINST for fast word-based searches
Boolean mode — Use +required, -excluded, *wildcard, "exact phrase" for precise control