第18章：MySQL 进阶提升与规范

MySQL 学习到一定阶段后，需要关注代码规范、版本差异、高级特性以及与后端语言的协同。本章将介绍这些进阶内容，帮助你成为更专业的 MySQL 开发者。

18.1 MySQL 代码规范

18.1.1 命名规范

1. 数据库命名

小写字母，使用下划线分隔
见名知意，简短明了
避免使用保留字
示例：user_management、ecommerce

2. 表命名

小写字母，使用下划线分隔
复数形式（表示多条记录）
前缀统一（可选，如 sys_ 表示系统表）
示例：users、orders、product_categories

3. 字段命名

小写字母，使用下划线分隔
见名知意，避免缩写
主键：id
外键：关联表名_id，如 user_id、product_id
时间字段：created_at、updated_at
示例：user_name、email_address、order_total

4. 索引命名

前缀：idx_
字段名组合
示例：idx_name_age、idx_product_id

5. 存储过程/函数命名

前缀：sp_（存储过程）、fn_（函数）
动词+名词
示例：sp_get_user、fn_calculate_total

18.1.2 SQL格式规范

1. 关键字大写

SELECT、FROM、WHERE、JOIN 等关键字大写
字段名、表名小写

2. 换行缩进

每个关键字换行
适当缩进（4个空格）
复杂查询合理分行

3. 注释规范

单行注释：-- 注释内容
多行注释：/* 注释内容 */
关键逻辑添加注释
复杂SQL添加说明

4. 示例

sql

-- 良好的 SQL 格式
SELECT
    u.id,
    u.name,
    o.order_no,
    o.amount
FROM
    users u
INNER JOIN
    orders o ON u.id = o.user_id
WHERE
    u.status = 1
    AND o.created_at >= '2023-01-01'
ORDER BY
    o.created_at DESC
LIMIT 10;

-- 存储过程示例
DELIMITER //
CREATE PROCEDURE sp_get_user(IN user_id INT)
BEGIN
    -- 查询用户信息
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

18.1.3 代码风格检查

1. 工具推荐

SQLFluff：SQL 代码格式化工具
sqlfmt：SQL 格式化工具
MySQL Workbench：内置格式化功能

2. 检查项

命名规范
格式一致性
注释完整性
逻辑清晰度

18.2 MySQL 版本差异

18.2.1 主要版本特性

版本	发布时间	主要特性	注意事项
5.5	2010	支持 InnoDB 作为默认引擎	较旧版本，建议升级
5.6	2013	全文索引、在线 DDL	仍有使用，功能基本满足
5.7	2015	JSON 支持、虚拟列	稳定版本，推荐使用
8.0	2018	窗口函数、CTE、角色管理	最新版本，功能丰富

18.2.2 语法差异

1. 密码哈希

sql

-- MySQL 5.7 及以下
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');

-- MySQL 8.0+
ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';

2. JSON 支持

sql

-- MySQL 5.7+
CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
);

-- JSON 操作
SELECT data->'$.name' FROM users;

3. 窗口函数

sql

-- MySQL 8.0+
SELECT
    id,
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as ranking
FROM students;

4. 通用表表达式 (CTE)

sql

-- MySQL 8.0+
WITH cte AS (
    SELECT * FROM users WHERE status = 1
)
SELECT * FROM cte WHERE age > 18;

18.2.3 兼容性处理

1. 检查版本

sql

SELECT VERSION();

2. 条件语句

sql

-- 兼容不同版本的语法
SET @version = (SELECT VERSION());
IF @version LIKE '8.%' THEN
    -- MySQL 8.0 语法
ELSE
    -- 旧版本语法
END IF;

3. 应用层处理

检测 MySQL 版本
根据版本使用不同的 SQL 语句
使用 ORM 框架处理兼容性

18.3 高级特性（可选，进阶学习）

18.3.1 存储过程

1. 存储过程的优势

封装复杂逻辑
提高性能（预编译）
减少网络传输
增强安全性

2. 存储过程示例

sql

DELIMITER //
CREATE PROCEDURE sp_get_user_orders(IN user_id INT)
BEGIN
    -- 查询用户信息
    SELECT * FROM users WHERE id = user_id;
    
    -- 查询用户订单
    SELECT * FROM orders WHERE user_id = user_id ORDER BY created_at DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL sp_get_user_orders(1);

3. 存储过程管理

sql

-- 查看存储过程
SHOW PROCEDURE STATUS;

-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_get_user_orders;

-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_get_user_orders;

18.3.2 触发器

1. 触发器的作用

自动执行操作
数据完整性检查
日志记录
业务规则实现

2. 触发器类型

BEFORE INSERT：插入前触发
AFTER INSERT：插入后触发
BEFORE UPDATE：更新前触发
AFTER UPDATE：更新后触发
BEFORE DELETE：删除前触发
AFTER DELETE：删除后触发

3. 触发器示例

sql

-- 创建触发器：记录用户更新日志
DELIMITER //
CREATE TRIGGER trg_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (
        user_id,
        action,
        old_data,
        new_data,
        created_at
    ) VALUES (
        OLD.id,
        'update',
        CONCAT('name:', OLD.name, ', age:', OLD.age),
        CONCAT('name:', NEW.name, ', age:', NEW.age),
        NOW()
    );
END //
DELIMITER ;

4. 触发器管理

sql

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trg_user_update;

18.3.3 视图

1. 视图的优势

简化复杂查询
提高安全性（限制数据访问）
数据抽象
一致性查询接口

2. 视图示例

sql

-- 创建视图：用户订单统计
CREATE VIEW user_order_stats AS
SELECT
    u.id as user_id,
    u.name as user_name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.name;

-- 使用视图
SELECT * FROM user_order_stats WHERE order_count > 5;

3. 视图管理

sql

-- 查看视图
SHOW CREATE VIEW user_order_stats;

-- 删除视图
DROP VIEW IF EXISTS user_order_stats;

-- 更新视图
CREATE OR REPLACE VIEW user_order_stats AS
SELECT
    u.id as user_id,
    u.name as user_name,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.name, u.email;

18.4 MySQL 与后端语言协同

18.4.1 PHP 连接 MySQL

1. 使用 PDO

php

<?php
// 连接数据库
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';
$username = 'root';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch (PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}

// 预处理语句
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();

// 插入数据
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['张三', 'zhangsan@example.com']);
?>

2. 使用 MySQLi

php

<?php
// 连接数据库
$mysqli = new mysqli('localhost', 'root', 'password', 'test');

if ($mysqli->connect_error) {
    die("连接失败: " . $mysqli->connect_error);
}

// 设置编码
$mysqli->set_charset('utf8mb4');

// 预处理语句
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// 插入数据
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$name = '张三';
$email = 'zhangsan@example.com';
$stmt->execute();

$mysqli->close();
?>

18.4.2 Java 连接 MySQL

1. 使用 JDBC

java

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            System.out.println("连接成功");
            
            // 查询数据
            String sql = "SELECT * FROM users WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setInt(1, 1);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        System.out.println("ID: " + rs.getInt("id"));
                        System.out.println("Name: " + rs.getString("name"));
                    }
                }
            }
            
            // 插入数据
            String insertSql = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                stmt.setString(1, "张三");
                stmt.setString(2, "zhangsan@example.com");
                int rows = stmt.executeUpdate();
                System.out.println("插入了 " + rows + " 行");
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. 使用框架

MyBatis：轻量级 ORM 框架
Spring JDBC：Spring 提供的 JDBC 模板
JPA/Hibernate：完整的 ORM 框架

18.4.3 Python 连接 MySQL

1. 使用 mysql-connector-python

python

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="test"
)

print("连接成功")

# 查询数据
cursor = conn.cursor(dictionary=True)
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (1,))
user = cursor.fetchone()
print(user)

# 插入数据
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("张三", "zhangsan@example.com")
cursor.execute(sql, values)
conn.commit()
print(f"插入了 {cursor.rowcount} 行")

cursor.close()
conn.close()

2. 使用 SQLAlchemy

python

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建引擎
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 定义模型
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(100))

# 查询数据
user = session.query(User).filter_by(id=1).first()
print(f"ID: {user.id}, Name: {user.name}")

# 插入数据
new_user = User(name="张三", email="zhangsan@example.com")
session.add(new_user)
session.commit()
print(f"插入了用户 ID: {new_user.id}")

session.close()

18.5 本章小结

主题	内容	重要性
代码规范	命名规范、SQL格式、注释规范	高
版本差异	主要版本特性、语法差异、兼容性	中
高级特性	存储过程、触发器、视图	中
后端协同	PHP、Java、Python 连接	高

进阶学习建议：

规范先行：养成良好的代码规范习惯
版本兼容：了解不同版本的特性和差异
按需学习：根据项目需求学习高级特性
实践结合：将 MySQL 与后端语言结合使用
持续学习：关注 MySQL 最新特性和最佳实践

专业的 MySQL 开发者不仅要掌握基础语法，还要了解规范、版本差异和高级特性，这样才能构建更加高效、安全、可维护的数据库系统。

第18章：MySQL 进阶提升与规范 ​

18.1 MySQL 代码规范 ​

18.1.1 命名规范 ​

18.1.2 SQL格式规范 ​

18.1.3 代码风格检查 ​

18.2 MySQL 版本差异 ​

18.2.1 主要版本特性 ​

18.2.2 语法差异 ​

18.2.3 兼容性处理 ​

18.3 高级特性（可选，进阶学习） ​

18.3.1 存储过程 ​

18.3.2 触发器 ​

18.3.3 视图 ​

18.4 MySQL 与后端语言协同 ​

18.4.1 PHP 连接 MySQL ​

18.4.2 Java 连接 MySQL ​

18.4.3 Python 连接 MySQL ​

18.5 本章小结 ​