-- 早期MySQL与mSQL的语法对比
-- mSQL语法（受限）
SELECT * FROM users WHERE name = 'John'
-- MySQL早期语法（更强大）
SELECT * FROM users 
WHERE name = 'John' 
  AND age > 25 
  AND created_date > '1995-01-01'

-- 4.0引入的关键特性示例
-- 1. 联合查询
SELECT u.name, o.order_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 2. 查询缓存（已废弃）
SELECT SQL_CACHE * FROM products WHERE category = 'electronics';

-- 4.1引入的子查询和字符集支持
-- 子查询示例
SELECT name FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- 字符集设置
CREATE TABLE users (
    id INT,
    name VARCHAR(100) CHARACTER SET utf8,
    email VARCHAR(100)
) DEFAULT CHARSET=utf8;

-- 5.0引入的企业级特性
-- 1. 存储过程
DELIMITER $$
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
    SELECT * FROM orders WHERE user_id = userId;
END$$
DELIMITER ;
-- 2. 视图
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';
-- 3. 触发器
CREATE TRIGGER before_user_update 
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = NOW();

// Sun时代，MySQL与Java生态的深度集成
// JDBC连接示例
public class MySQLSunEraExample {
    public static void main(String[] args) {
        try {
            // Sun推荐的连接方式
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test", 
                "username", "password"
            );
            
            // 使用Sun提供的连接池
            com.sun.jdbc.ConnectionPoolDataSource ds = 
                new com.sun.jdbc.ConnectionPoolDataSource();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

-- 5.1引入的分区和事件调度器
-- 1. 表分区
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2008 VALUES LESS THAN (2009),
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2011)
);
-- 2. 事件调度器
CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
DO
  CALL backup_database();

-- 5.5的InnoDB改进和复制增强
-- 1. 默认存储引擎改为InnoDB
SELECT @@default_storage_engine;  -- 返回: InnoDB
-- 2. 半同步复制
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
-- 3. 性能Schema
SELECT * FROM performance_schema.events_waits_current;

-- 5.6的重大改进示例
-- 1. 在线DDL操作
ALTER TABLE users 
ADD COLUMN last_login_time DATETIME,
ALGORITHM=INPLACE, 
LOCK=NONE;
-- 2. 优化器改进
EXPLAIN FORMAT=JSON 
SELECT * FROM orders 
WHERE customer_id = 123 
  AND order_date > '2013-01-01';
-- 3. NoSQL API（Memcached）
-- 通过Memcached协议直接访问InnoDB

-- 5.7的现代化特性
-- 1. JSON支持
CREATE TABLE products (
    id INT PRIMARY KEY,
    details JSON,
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products VALUES (1, '{"name": "Laptop", "specs": {"cpu": "i7", "ram": "16GB"}}');
-- 2. 生成列
CREATE TABLE employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(100) AS (CONCAT(first_name, ' ', last_name))
);
-- 3. 多源复制
CHANGE MASTER TO 
  MASTER_HOST='source1',
  MASTER_USER='repl',
  MASTER_PASSWORD='password'
FOR CHANNEL 'source1_channel';

-- 8.0的革命性特性
-- 1. 窗口函数
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- 2. 通用表表达式（CTE）
WITH recursive org_chart AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;
-- 3. 角色管理
CREATE ROLE developer;
GRANT SELECT, INSERT, UPDATE ON app_db.* TO developer;
GRANT developer TO 'user1'@'localhost';

// 不同时代的Java连接MySQL方式对比
// 1. 早期方式（2000年代初）
Class.forName("org.gjt.mm.mysql.Driver");
// 2. 标准方式（2000年代中期）
Class.forName("com.mysql.jdbc.Driver");
// 3. 现代方式（MySQL 8.0+）
// 自动驱动加载，无需Class.forName
String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
Connection conn = DriverManager.getConnection(url, "user", "password");
// 4. 最新推荐（MySQL Connector/J 8.0+）
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
Connection conn = DriverManager.getConnection(url, "user", "password");

# 不同时代的Spring配置MySQL
# 1. Spring 2.x时代 (2006-2009)
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/test"/>
</bean>
# 2. Spring Boot 1.x (2014-2018)
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useSSL=false
    username: root
    password: password
    driver-class-name: com.mysql.jdbc.Driver
# 3. Spring Boot 2.x+ (2018至今)
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver