#!/usr/bin/env bash
# MySQL 隐式转换 / 字符集不一致 / 函数包列 索引失效演示 v1.0（surinch.com · 盈尺）
# 只在测试实例上跑！会新建并重建库 implicit_lab（自造数据：users 50 万行、orders 100 万行，约 15 秒）。
# 测试版本：MySQL 8.0.46。用法：MYSQL="mysql -uroot -p -h127.0.0.1" ./mysql-implicit-conversion-demo-v1.0.sh
set -euo pipefail
MYSQL=${MYSQL:-mysql}
DB=implicit_lab
$MYSQL -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;"
$MYSQL $DB <<'SQL'
CREATE TABLE users (
  id INT PRIMARY KEY, phone VARCHAR(20) NOT NULL, user_code VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL,
  KEY idx_phone (phone), KEY idx_user_code (user_code), KEY idx_created (created_at)) ENGINE=InnoDB;
-- 老表：字符集 utf8mb3，模拟早年建的表
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_code VARCHAR(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  amount DECIMAL(10,2) NOT NULL, KEY idx_user_code (user_code)) ENGINE=InnoDB;
-- 另一张表把手机号存成数字
CREATE TABLE sms_log (id INT PRIMARY KEY, phone BIGINT NOT NULL, KEY idx_phone (phone)) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO users
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 500000)
SELECT n, CONCAT('138', LPAD(n, 8, '0')), CONCAT('U', LPAD(n, 9, '0')),
       '2026-01-01' + INTERVAL (n % 280) DAY + INTERVAL (n % 86400) SECOND FROM s;
INSERT INTO orders
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 1000000)
SELECT n, CONCAT('U', LPAD(1 + (n * 7919) % 500000, 9, '0')), (n % 1000) + 0.99 FROM s;
INSERT INTO sms_log SELECT id, CAST(phone AS UNSIGNED) FROM users WHERE id <= 1000;
ANALYZE TABLE users, orders, sms_log;
SQL
q() { echo; echo "== $1"; $MYSQL $DB -e "$2; SHOW WARNINGS;"; }
q "1 VARCHAR 列用数字比较（看 Warning 1739）" "EXPLAIN ANALYZE SELECT id FROM users WHERE phone = 13800123456"
q "2 同一列用字符串比较" "EXPLAIN ANALYZE SELECT id FROM users WHERE phone = '13800123456'"
q "3 数字比较的语义坑" "SELECT '13800123456abc' = 13800123456 AS eq1, ' 13800123456' = 13800123456 AS eq2"
q "4 JOIN 关联列字符集不一致（utf8mb4 vs utf8mb3）" "EXPLAIN ANALYZE SELECT COUNT(*) FROM users u JOIN orders o ON o.user_code = u.user_code WHERE u.id BETWEEN 1000 AND 1999"
q "5 DATE() 包住索引列" "EXPLAIN ANALYZE SELECT COUNT(*) FROM users WHERE DATE(created_at) = '2026-03-01'"
q "6 改成半开区间" "EXPLAIN ANALYZE SELECT COUNT(*) FROM users WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02'"
echo; echo "== 7 修复：orders.user_code 改成 utf8mb4（INPLACE 会被拒，只能 COPY，会重建表）"
$MYSQL $DB -e "ALTER TABLE orders MODIFY user_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, ALGORITHM=INPLACE, LOCK=NONE" || true
t0=$(date +%s%N)
$MYSQL $DB -e "ALTER TABLE orders MODIFY user_code VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL; ANALYZE TABLE orders;" >/dev/null
echo "alter_copy_ms=$(( ($(date +%s%N)-t0)/1000000 ))"
q "8 修复后同一条 JOIN" "EXPLAIN ANALYZE SELECT COUNT(*) FROM users u JOIN orders o ON o.user_code = u.user_code WHERE u.id BETWEEN 1000 AND 1999"
echo; echo "完成。清理：$MYSQL -e 'DROP DATABASE implicit_lab'"