MySQL determines string comparisons based on the character set and collation defined for specific columns or tables. While default collations (such as `utf8mb4_0900_ai_ci`) are often case-insensitive, data integrity issues or specific security requirements may necessitate strict case-sensitive matching. Understanding how to control this behavior is essential for accurate data retrieval.

Enforcing Strict Case Sensitivity

To force a query to respect the exact casing of input values, regardless of the table's default collation, the `BINARY` operator can be applied. This cast ensures that the comparison is performed byte-by-byte.

-- Schema setup
CREATE TABLE system_access (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    access_key VARCHAR(64)
);

INSERT INTO system_access (access_key) VALUES ('AdminRoot');

-- Standard query (may be case-insensitive depending on collation)
-- To ensure it is strictly case-sensitive, use BINARY:
SELECT * FROM system_access 
WHERE BINARY access_key = 'adminroot';

-- Result: Empty set (no match found due to case mismatch)

Normalizing Case for Insensitive Searches

Conversely, when a column uses a case-sensitive collation (e.g., `utf8mb4_bin` or `latin1_bin`) but the application requires a case-insensitive search, the `LOWER()` or `UPPER()` functions can normalize the data on both sides of the comparison.

-- Table with a case-sensitive collation
CREATE TABLE secure_credentials (
    credential_id INT PRIMARY KEY,
    username VARCHAR(50) COLLATE utf8mb4_bin
);

INSERT INTO secure_credentials (credential_id, username) VALUES (101, 'SystemUser');

-- Standard query fails due to collation
SELECT * FROM secure_credentials WHERE username = 'systemuser';

-- Solution: Normalize to lowercase
SELECT * FROM secure_credentials 
WHERE LOWER(username) = LOWER('systemuser');

-- Result: 101 | 'SystemUser'

Alternatively, one can override the collation directly in the `WHERE` clause using the `COLLATE` keyword to switch to a case-insensitive rule for that specific query execution.