How to check collation and charset in mysql.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> 'utf8mb4_0900_ai_ci'
  AND TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

SELECT TABLE_SCHEMA, TABLE_NAME, CHARACTER_SET_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_COLLATION <> 'utf8mb4_0900_ai_ci'
AND TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_SCHEMA, TABLE_NAME;

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE DEFAULT_COLLATION_NAME <> 'utf8mb4_0900_ai_ci'
ORDER BY SCHEMA_NAME;

AI and CI

utf8mb4_general_ci

• "ci" = case-insensitive.
• "A" = "a".
• "café" = "cafe" (accent-insensitive).

utf8mb4_bin

• “bin” = binary.
• "A" ≠ "a".
• Every byte matters — strictest.

utf8mb4_0900_ai_ci (newer MySQL 8 default)

• “ai” = accent-insensitive.
• "ci" = case-insensitive.
• "é" = "e", "A" = "a".

utf8mb3_unicode_ci

• Legacy 3-byte UTF-8 character set.
• Unicode collation (case-insensitive, accent-insensitive).

Limitations

• Can’t store 4-byte characters like emoji.
• Collation rules are older than the modern utf8mb4_0900_ai_ci.

Setting a Variable w/Collation

Need to do "COLLATE" and whatever one it is complaining about if you get this error message: Error: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

set @div = 'MA50' COLLATE utf8mb4_0900_ai_ci