-- Demonstrate the problem with utf8 (3-byte only)
CREATE TABLE test_utf8 (msg VARCHAR(100) CHARACTER SET utf8);
INSERT INTO test_utf8 VALUES ('Hello ????');   -- emoji is silently dropped or errors
-- Result: 'Hello ' (emoji missing) or ERROR 1366

-- utf8mb4 handles it correctly
CREATE TABLE test_utf8mb4 (msg VARCHAR(100) CHARACTER SET utf8mb4);
INSERT INTO test_utf8mb4 VALUES ('Hello ????');   -- works correctly
SELECT msg FROM test_utf8mb4;
-- Result: 'Hello ????'

[mysqld]
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Change an existing database (new tables inherit this; existing tables unchanged)
ALTER DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE TABLE users (
  id       INT          NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  email    VARCHAR(255) NOT NULL,
  bio      TEXT,
  PRIMARY KEY (id)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Convert an existing table (also converts all string column data)
ALTER TABLE users
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Case-sensitive column for API tokens (must match exactly)
ALTER TABLE users
  ADD COLUMN api_token VARCHAR(64)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- Check charset and collation of all columns in a table
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';

-- Set the client, connection, and results charset in one statement
SET NAMES 'utf8mb4';

-- Equivalent to all three of these:
SET character_set_client     = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results    = utf8mb4;

-- List all available character sets
SHOW CHARACTER SET;

-- List collations for utf8mb4
SHOW COLLATION WHERE Charset = 'utf8mb4';

-- Check current server defaults
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Convert a string expression to a different charset
SELECT CONVERT('Hello World' USING utf8mb4);

-- Cast with explicit charset
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

-- Check the charset and collation of any string expression
SELECT CHARSET('Hello'), COLLATION('Hello');

-- Force a specific collation for a single query
SELECT * FROM users
WHERE username = 'ALICE' COLLATE utf8mb4_bin;   -- case-sensitive for this query only

-- Diagnose: check the actual hex bytes stored
SELECT username, HEX(username) FROM users WHERE id = 1;
-- If you see 'é' where 'é' is expected, that's mojibake

-- Fix approach: tell MySQL the real encoding, then convert
-- Step 1: reinterpret the column as the actual encoding (e.g., latin1)
ALTER TABLE users MODIFY username VARCHAR(100)
  CHARACTER SET latin1;

-- Step 2: convert to utf8mb4 (MySQL re-encodes properly)
ALTER TABLE users MODIFY username VARCHAR(100)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Dump with the original charset declaration
mysqldump --default-character-set=latin1 myapp > dump_latin1.sql

# Replace charset references in the dump
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g;
        s/utf8_general_ci/utf8mb4_unicode_ci/g;
        s/DEFAULT CHARSET=utf8/DEFAULT CHARSET=utf8mb4/g' dump_latin1.sql

# Import into the new (or same) database
mysql --default-character-set=utf8mb4 myapp_utf8 < dump_latin1.sql

-- With _ci collation: returns the row for 'alice' when searching 'ALICE'
SELECT * FROM users WHERE username = 'ALICE';   -- returns alice

-- With _bin collation: returns no rows (case-sensitive)
SELECT * FROM users WHERE username = 'ALICE';   -- returns nothing if stored as 'alice'

-- JOIN on collation mismatch: can cause performance issues or no rows
SELECT * FROM users u
JOIN sessions s ON u.username = s.username;
-- If u.username is utf8mb4_unicode_ci and s.username is utf8mb4_bin,
-- MySQL cannot use an index on the join and may do a full scan

-- Fix collation mismatch in a JOIN for one query
SELECT * FROM users u
JOIN sessions s ON u.username = s.username COLLATE utf8mb4_unicode_ci;

-- Error: "Specified key was too long; max key length is 3072 bytes"
CREATE INDEX idx_long ON articles (body(800));  -- 800 * 4 = 3200 > 3072

-- Fix 1: use a shorter prefix
CREATE INDEX idx_long ON articles (body(767));  -- 767 * 4 = 3068 bytes

-- Fix 2: use a generated column + hash for full-column uniqueness
ALTER TABLE users ADD COLUMN email_hash VARCHAR(64)
  AS (SHA2(email, 256)) STORED;
CREATE UNIQUE INDEX idx_email_hash ON users (email_hash);

-- See exactly how two collations differ on the same strings
SELECT
  'cafe' = 'café' COLLATE utf8mb4_unicode_ci  AS unicode_ci_equal,  -- 1 (accent-insensitive)
  'cafe' = 'café' COLLATE utf8mb4_0900_as_ci  AS as_ci_equal,       -- 0 (accent-sensitive)
  'cafe' = 'café' COLLATE utf8mb4_bin         AS bin_equal;          -- 0 (binary)

-- Sort order differences between collations
SELECT name FROM countries ORDER BY name COLLATE utf8mb4_unicode_ci;
-- Swedish chars (Å, Ä, Ö) sort after Z in unicode_ci (correct for Swedish)

SELECT name FROM countries ORDER BY name COLLATE utf8mb4_general_ci;
-- general_ci sorts some chars differently — less accurate for non-English

// Node.js (mysql2)
const connection = mysql.createConnection({
  host:    'localhost',
  user:    'app',
  password: 'secret',
  database: 'myapp',
  charset: 'utf8mb4'   // sets SET NAMES utf8mb4 automatically
});

// PHP PDO
$pdo = new PDO(
  'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
  'app', 'secret'
);

// Java JDBC (Connector/J)
// jdbc:mysql://localhost/myapp?characterEncoding=utf8mb4

// Python (mysql-connector-python)
cnx = mysql.connector.connect(
  host='localhost', user='app', password='secret',
  database='myapp', charset='utf8mb4'
)

-- LENGTH vs CHAR_LENGTH
SELECT LENGTH('café');       -- 5 (bytes in utf8mb4: c=1, a=1, f=1, é=2)
SELECT CHAR_LENGTH('café');  -- 4 (characters: c, a, f, é)
-- LENGTH counts bytes; CHAR_LENGTH counts characters

-- For utf8mb4 strings, always use CHAR_LENGTH for display purposes
SELECT username, CHAR_LENGTH(username) AS char_count FROM users;

-- UPPER and LOWER respect collation
SELECT UPPER('café');   -- 'CAFÉ'
SELECT LOWER('CAFÉ');   -- 'café'

-- Comparing with explicit collation
SELECT 'résumé' = 'RÉSUMÉ' COLLATE utf8mb4_0900_ai_ci;  -- 1 (accent+case insensitive)
SELECT 'résumé' = 'RÉSUMÉ' COLLATE utf8mb4_bin;          -- 0 (binary comparison)

-- Ligature comparison (unicode_ci vs 0900)
SELECT 'Ⅷ' = 'VIII' COLLATE utf8mb4_unicode_ci;  -- 0 (not equal)
SELECT 'Ⅷ' = 'VIII' COLLATE utf8mb4_0900_ai_ci;   -- 1 (equal in 0900)

-- Emoji search — works with utf8mb4 but not utf8
SELECT * FROM users WHERE bio LIKE '%????%';

-- Check the number of bytes for different characters
SELECT CHAR_LENGTH('A'),    LENGTH('A');    -- 1 char, 1 byte
SELECT CHAR_LENGTH('é'),    LENGTH('é');    -- 1 char, 2 bytes (utf8mb4)
SELECT CHAR_LENGTH('????'), LENGTH('????'); -- 1 char, 4 bytes (4-byte emoji)

-- Check what charset a specific table is using
SHOW CREATE TABLE usersG
-- Look for: DEFAULT CHARSET=utf8mb4

-- Check all tables in a database that are NOT utf8mb4
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%'
ORDER BY TABLE_NAME;

-- Check all columns that are NOT utf8mb4
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME != 'utf8mb4'
ORDER BY TABLE_NAME, COLUMN_NAME;

[mysqld]
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

# These ensure the server -> client path is also utf8mb4
character_set_results   = utf8mb4
character_set_connection = utf8mb4

[client]
# Default connection charset for the mysql CLI client
default-character-set = utf8mb4

-- Verify client-server charset agreement after connecting
SHOW SESSION VARIABLES LIKE 'character_set%';

-- All of these should be utf8mb4 for a correctly configured setup:
-- character_set_client       utf8mb4
-- character_set_connection   utf8mb4
-- character_set_database     utf8mb4
-- character_set_results      utf8mb4
-- character_set_server       utf8mb4