-- 創(chuàng)建測試表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) NOT NULL,
    username VARCHAR(50),
    created_at DATETIME,
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

-- 插入100萬條測試數(shù)據(jù)
INSERT INTO users (phone, username, created_at) 
SELECT 
    CONCAT('138', LPAD(FLOOR(RAND() * 100000000), 8, '0')),
    CONCAT('user_', UUID()),
    NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
    -- ... 繼續(xù)交叉連接生成數(shù)據(jù)

-- 查詢測試
-- 查詢1：字符串類型（走索引）
EXPLAIN SELECT * FROM users WHERE phone = '13812345678';
-- type: ref, key: idx_phone, rows: 1

-- 查詢2：數(shù)字類型（不走索引?。?
EXPLAIN SELECT * FROM users WHERE phone = 13812345678;
-- type: ALL, key: NULL, rows: 1000000

-- 性能對比
-- 查詢1：0.001秒
-- 查詢2：0.832秒（慢了800多倍?。?/pre>

-- 假設(shè) user_id 是 varchar 類型，有索引
CREATE INDEX idx_user_id ON users (user_id);

-- 問題查詢：隱式轉(zhuǎn)換導(dǎo)致全表掃描
SELECT * FROM users WHERE user_id = 123;  -- '123' 轉(zhuǎn)數(shù)字，索引失效
EXPLAIN SELECT * FROM users WHERE user_id = 123;
-- 輸出：type: ALL (全表掃描)

-- 顯式轉(zhuǎn)換字符串
SELECT * FROM users WHERE user_id = '123';
EXPLAIN SELECT * FROM users WHERE user_id = '123';
-- 輸出：type: ref (使用索引)

CREATE INDEX idx_order_date ON orders (order_date);

-- 問題查詢：字符串轉(zhuǎn)日期，索引失效
SELECT * FROM orders WHERE order_date = '2025-01-01';
EXPLAIN SELECT * FROM orders WHERE order_date = '2025-01-01';
-- 輸出：type: ALL

-- 確保格式匹配
SELECT * FROM orders WHERE order_date = STR_TO_DATE('2025-01-01', '%Y-%m-%d');
-- 或使用參數(shù)化查詢
PREPARE stmt FROM 'SELECT * FROM orders WHERE order_date = ?';
SET @date = '2025-01-01';
EXECUTE stmt USING @date;

CREATE INDEX idx_status ON orders (status);

-- 問題查詢：NULL 不走索引
SELECT * FROM orders WHERE status IS NULL;
EXPLAIN SELECT * FROM orders WHERE status IS NULL;
-- 輸出：type: ALL

-- 使用 IS NOT NULL 或默認(rèn)值
SELECT * FROM orders WHERE status IS NOT NULL;
-- 或修改表結(jié)構(gòu)，使用默認(rèn)值
ALTER TABLE orders MODIFY status VARCHAR(10) NOT NULL DEFAULT 'active';

-- 問題場景：手機(jī)號存儲為VARCHAR，但查詢時(shí)使用數(shù)字
CREATE TABLE user_info (
    id INT PRIMARY KEY,
    mobile VARCHAR(11),
    INDEX idx_mobile (mobile)
);

-- 錯(cuò)誤寫法（觸發(fā)隱式轉(zhuǎn)換）
SELECT * FROM user_info WHERE mobile = 13812345678;
-- MySQL會將mobile字段的每一行都轉(zhuǎn)換為數(shù)字再比較
-- 相當(dāng)于：WHERE CAST(mobile AS UNSIGNED) = 13812345678

-- 正確寫法
SELECT * FROM user_info WHERE mobile = '13812345678';

-- 更嚴(yán)重的問題：前導(dǎo)零
INSERT INTO user_info VALUES (1, '01234567890');
SELECT * FROM user_info WHERE mobile = 01234567890;  -- 查不到！
-- 因?yàn)?01234567890 會被解析為八進(jìn)制數(shù)

-- 性能測試對比
-- 100萬數(shù)據(jù)量下：
-- 錯(cuò)誤寫法：全表掃描，耗時(shí) 0.8秒
-- 正確寫法：索引掃描，耗時(shí) 0.001秒

-- 時(shí)間字段的坑
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_time DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_time (order_time)
);

-- 案例2.1：字符串與DATETIME比較
-- 這個(gè)會走索引（字符串被轉(zhuǎn)換為DATETIME）
SELECT * FROM orders WHERE order_time = '2024-01-15 10:30:00';

-- 案例2.2：數(shù)字與DATETIME比較
-- 不走索引！數(shù)字被當(dāng)作時(shí)間戳
SELECT * FROM orders WHERE order_time = 20240115103000;

-- 案例2.3：函數(shù)導(dǎo)致的隱式轉(zhuǎn)換
-- 不走索引！因?yàn)閷λ饕侄问褂昧撕瘮?shù)
SELECT * FROM orders WHERE DATE(order_time) = '2024-01-15';

-- 正確的范圍查詢
SELECT * FROM orders 
WHERE order_time >= '2024-01-15 00:00:00' 
  AND order_time < '2024-01-16 00:00:00';

-- 兩個(gè)表的關(guān)聯(lián)字段類型不一致
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id VARCHAR(20),  -- 注意：這里是VARCHAR！
    amount DECIMAL(10,2),
    INDEX idx_user_id (user_id)
);

-- 問題查詢
SELECT u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id  -- 類型不匹配！
GROUP BY u.user_id;

-- 執(zhí)行計(jì)劃顯示orders表進(jìn)行了全表掃描
-- 因?yàn)樾枰獙.user_id轉(zhuǎn)換為INT類型

-- 解決方案1：修改表結(jié)構(gòu)（推薦）
ALTER TABLE orders MODIFY COLUMN user_id INT;

-- 解決方案2：顯式轉(zhuǎn)換（臨時(shí)方案）
SELECT u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON CAST(u.user_id AS CHAR) = o.user_id
GROUP BY u.user_id;

# Python代碼中的常見錯(cuò)誤
class INQueryPitfall:
    def __init__(self, db_connection):
        self.db = db_connection
    
    def wrong_way(self, user_ids):
        """錯(cuò)誤的方式：直接拼接數(shù)字"""
        # user_ids = [1, 2, 3, 4, 5]
        sql = f"SELECT * FROM users WHERE user_id IN ({','.join(map(str, user_ids))})"
        # 生成：WHERE user_id IN (1,2,3,4,5)
        
        # 如果user_id是VARCHAR類型，會觸發(fā)隱式轉(zhuǎn)換！
        return self.db.execute(sql)
    
    def correct_way(self, user_ids):
        """正確的方式：使用參數(shù)化查詢"""
        placeholders = ','.join(['%s'] * len(user_ids))
        sql = f"SELECT * FROM users WHERE user_id IN ({placeholders})"
        
        # 讓數(shù)據(jù)庫驅(qū)動處理類型轉(zhuǎn)換
        return self.db.execute(sql, user_ids)
    
    def performance_comparison(self):
        """性能對比測試"""
        import time
        
        # 準(zhǔn)備測試數(shù)據(jù)
        test_ids = list(range(1, 1001))
        
        # 測試錯(cuò)誤方式
        start = time.time()
        self.wrong_way(test_ids)
        wrong_time = time.time() - start
        
        # 測試正確方式
        start = time.time()
        self.correct_way(test_ids)
        correct_time = time.time() - start
        
        print(f"錯(cuò)誤方式耗時(shí)：{wrong_time:.3f}秒")
        print(f"正確方式耗時(shí)：{correct_time:.3f}秒")
        print(f"性能提升：{wrong_time/correct_time:.1f}倍")

-- 創(chuàng)建診斷存儲過程
DELIMITER $$
CREATE PROCEDURE diagnose_query_performance(IN query_sql TEXT)
BEGIN
    -- 執(zhí)行EXPLAIN
    SET @sql = CONCAT('EXPLAIN ', query_sql);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 顯示警告信息（可能包含類型轉(zhuǎn)換提示）
    SHOW WARNINGS;
END$$
DELIMITER ;

-- 使用示例
CALL diagnose_query_performance('SELECT * FROM users WHERE phone = 13812345678');

class TypeConversionPrevention:
    def __init__(self):
        self.best_practices = {
            "設(shè)計(jì)階段": [
                "統(tǒng)一使用INT作為主鍵和外鍵",
                "手機(jī)號、身份證號等使用VARCHAR存儲",
                "金額使用DECIMAL而不是FLOAT",
                "時(shí)間字段統(tǒng)一使用DATETIME或TIMESTAMP"
            ],
            
            "開發(fā)階段": [
                "使用ORM時(shí)注意字段映射類型",
                "SQL語句使用參數(shù)化查詢",
                "避免在WHERE子句中對字段使用函數(shù)",
                "JOIN操作確保關(guān)聯(lián)字段類型一致"
            ],
            
            "測試階段": [
                "所有SQL都要經(jīng)過EXPLAIN分析",
                "關(guān)注type列是否為ALL（全表掃描）",
                "檢查key列是否使用了預(yù)期的索引",
                "注意rows列的掃描行數(shù)"
            ],
            
            "運(yùn)維階段": [
                "定期分析慢查詢?nèi)罩?,
                "監(jiān)控索引使用率",
                "使用pt-query-digest等工具分析",
                "建立SQL審核機(jī)制"
            ]
        }
    
    def generate_code_review_checklist(self):
        """生成代碼審查清單"""
        checklist = """
        ## SQL代碼審查清單
        
        ### 1. 類型匹配檢查
        - [ ] WHERE條件中的字段類型與傳入值類型是否一致？
        - [ ] JOIN條件兩邊的字段類型是否相同？
        - [ ] IN查詢中的值類型是否與字段類型匹配？
        
        ### 2. 索引使用檢查
        - [ ] EXPLAIN結(jié)果中type是否為ref/range/index？
        - [ ] key列是否顯示了預(yù)期的索引？
        - [ ] Extra列是否出現(xiàn)Using filesort/Using temporary？
        
        ### 3. 函數(shù)使用檢查
        - [ ] 是否在索引字段上使用了函數(shù)？
        - [ ] 是否可以改寫為范圍查詢？
        - [ ] 是否可以使用覆蓋索引？
        
        ### 4. 數(shù)據(jù)類型設(shè)計(jì)
        - [ ] 數(shù)值型ID是否統(tǒng)一使用INT/BIGINT？
        - [ ] 字符型編碼是否統(tǒng)一使用VARCHAR？
        - [ ] 時(shí)間字段是否統(tǒng)一使用DATETIME？
        """
        return checklist

-- 優(yōu)化前：一個(gè)真實(shí)的電商訂單查詢
-- 原始表結(jié)構(gòu)
CREATE TABLE orders_bad (
    order_no VARCHAR(32) PRIMARY KEY,  -- 訂單號
    user_id VARCHAR(20),               -- 用戶ID
    create_time VARCHAR(20),           -- 創(chuàng)建時(shí)間
    total_amount VARCHAR(20),          -- 總金額
    status CHAR(1),                    -- 狀態(tài)
    INDEX idx_user (user_id),
    INDEX idx_time (create_time)
);

-- 問題查詢（多個(gè)隱式轉(zhuǎn)換）
SELECT * FROM orders_bad 
WHERE user_id = 12345                          -- 類型不匹配
  AND create