MySQL高级

触发器¶

理论¶

组成：

• 触发事件：指定触发器在哪种操作下触发。可以是 INSERT​、UPDATE​、DELETE​ 操作。
• 触发时间：指定触发器在操作之前或之后执行，使用 BEFORE​ 或 AFTER​。
• 触发动作：触发器的具体 SQL 代码，即在触发时要执行的操作。

限制：

• 每个表最多只能为 INSERT​、UPDATE​、DELETE​ 操作各定义一个 BEFORE​ 和 AFTER​ 触发器。
• 触发器不能直接调用事务控制语句（如 COMMIT​ 和 ROLLBACK​），因为触发器在事务内部执行。
• MySQL 不支持表上级联触发（递归触发），即触发器中不应再对该表进行操作，以免形成循环触发。

作用：

• 自动化数据验证和清理。
• 数据库事件记录（如日志表记录）。
• 实现复杂的数据一致性和约束检查。

操作¶

创建¶

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名
FOR EACH ROW
触发器执行的操作SQL;

​​OLD​​：获取触发操作之前的数据（用于 UPDATE​ 和 DELETE​）。

​​NEW​​：获取触发操作之后的数据（用于 INSERT​ 和 UPDATE​）。

删除¶

DROP TRIGGER IF EXISTS 表名.触发器名;

查看¶

SHOW TRIGGERS;

索引¶

索引（Index）是数据库中用于加速数据查询的结构，它在指定字段上创建类似“目录”的数据结构，使数据库可以快速查找到所需数据。虽然索引能够提高查询速度，但它们会占用额外的存储空间，并且在进行数据插入、更新、删除等操作时会带来额外的维护开销。因此，索引的创建和使用需要平衡查询性能和存储、维护开销。

类型¶

1. 普通索引（Normal Index） ：最常用的索引类型，用于加速查询，允许重复和 NULL​ 值。
2. 唯一索引（Unique Index） ：在索引列上不允许出现重复的值，适合唯一性要求的字段，例如身份证号、手机号等。
3. 主键索引（Primary Key Index） ：是唯一索引的一种，只允许一列或多列的组合设置为主键，且值不允许为 NULL​。一个表只能有一个主键索引。
4. 全文索引（Full-Text Index） ：主要用于查找文本数据中的关键词，例如对大段文本进行搜索。MySQL 中 FULLTEXT​ 只支持 CHAR​、VARCHAR​ 和 TEXT​ 列。
5. 组合索引（Composite Index） ：在多个列上创建的索引，用于高效地处理多条件查询，但组合索引的顺序影响使用效果（最左前缀匹配）。
6. 空间索引（Spatial Index） ：用于地理数据类型的索引，例如 POINT​、LINESTRING​、POLYGON​ 等。通常用于 GIS（地理信息系统）应用。

操作¶

创建¶

-- 直接
CREATE INDEX|UNIQUE|PRIMARY KEY|FULLTEXT 索引名 ON 表名(列名1[, 列名2 ...]);

-- 创建表
CREATE TABLE 表名 (
    列名1 数据类型,
    ...
    INDEX [索引名] (列名1[, 列名2 ...]),
    UNIQUE [索引名] (列名1[, 列名2 ...]),
    PRIMARY KEY [索引名] (列名1[, 列名2 ...]),
    FULLTEXT [索引名] (列名1[, 列名2 ...])
);

-- 修改表
ALTER TABLE 表名 ADD INDEX|UNIQUE|PRIMARY KEY|FULLTEXT 索引名 (列名1[, 列名2 ...]);

删除¶

-- 直接
DROP INDEX 索引名 ON 表名;

-- 修改表
ALTER TABLE 表名 DROP INDEX 索引名;

-- 主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

查看¶

SHOW INDEX|KEYS FROM `表名`;

使用 EXPLAIN​ 分析查询执行计划可以检查是否用到了索引。

视图¶

理论¶

视图（View）是一个虚拟表，表示一组通过查询获得的数据结果。它不存储实际数据，而是存储 SQL 查询。视图的使用可以简化复杂查询、隐藏数据细节、提供数据安全以及在数据展示中提升灵活性。

优点：

1. 简化复杂查询：将复杂查询封装为视图，方便重复使用，减少代码冗余。
2. 数据安全：视图可以限制用户访问特定列或数据行，实现数据隔离。
3. 增强灵活性：视图的定义可以改变，而不影响依赖视图的查询。
4. 数据抽象：提供数据的抽象层，隐藏表结构的复杂性。

缺点：

1. 性能问题：视图没有实际存储数据，每次查询会重新执行定义的 SQL 语句。如果视图定义过于复杂，可能会降低查询性能。
2. 不支持索引：视图本身不支持索引，无法在视图中直接使用索引提升性能。
3. 数据一致性：视图依赖的表结构发生变化（如列名更改），视图需要重新定义。

操作¶

创建¶

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

选参说明：

• ​OR REPLACE​：如果视图已存在，则替换旧视图的定义，而无需先删除该视图。

• ​ALGORITHM​：指定视图的处理算法。

  • ​UNDEFINED​（默认）：让 MySQL 自动选择最适合的算法。
  • ​MERGE​：将视图的查询直接合并到实际查询中，适用于简单视图，可以提高查询效率。
  • ​TEMPTABLE​：将视图的结果放到一个临时表中处理，适用于复杂的视图，但会增加系统开销，并且无法更新。

• ​DEFINER​：指定创建视图的用户，即视图的“定义者”。该用户定义了视图的访问权限。

  • ​user​：指定的用户具有创建视图的权限（如 'root'@'localhost'​）。
  • ​CURRENT_USER​：使用当前登录的用户为定义者。

• ​SQL SECURITY​：设置视图的权限执行模式。

  • ​DEFINER​：使用视图定义者的权限执行 SQL 语句（默认）。
  • ​INVOKER​：使用调用者的权限执行 SQL 语句。

• ​(column_list)​：视图的列名列表，可以指定每一列的名称；如果省略，则使用 SELECT​ 语句的结果列名称。

• ​WITH ... CHECK OPTION​：确保通过视图插入或更新的数据符合视图定义的条件。

  • ​CASCADED​（默认）：检查视图及所有依赖视图的定义条件。
  • ​LOCAL​：只检查视图本身的条件。

修改¶

ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];

或在创建语句中使用OR REPLACE​参数可进行覆盖修改。

删除¶

DROP VIEW 视图名;

更新¶

视图可以更新，但必须满足一些条件才能进行数据修改操作：

1. 视图基于单个表创建。
2. 视图中不包含聚合函数（如 SUM​、COUNT​ 等）、DISTINCT​、GROUP BY​、HAVING​ 等。
3. 视图的定义中没有子查询或连接操作。

存储过程¶

理论¶

存储过程（Stored Procedure）是一组在数据库中存储的 SQL 语句集合，可以执行一系列的操作。通过调用存储过程，开发人员能够减少网络交互次数，并封装复杂逻辑，使代码重用性和效率提高。

优点：

1. 减少网络通信：客户端调用存储过程，只需一次请求即可执行多个 SQL 语句，减少了网络交互的开销。
2. 提高性能：存储过程编译后存储在数据库中，调用时无需重新编译，执行效率较高。
3. 增强安全性：存储过程可以将复杂逻辑封装在数据库中，限制直接对表的访问。
4. 代码重用：封装通用逻辑，便于重用，提高开发效率。

操作¶

创建¶

CREATE PROCEDURE 存储过程名 ([IN | OUT | INOUT] 参数名 数据类型, ...)
BEGIN
    -- 存储过程的 SQL 语句；
END;

• ​IN​：输入参数，传入存储过程，存储过程内部可以使用该值，但不能修改。
• ​OUT​：输出参数，传出存储过程，用于返回处理结果。
• ​INOUT​：既可以作为输入参数传入，也可以在存储过程中修改，并返回修改后的值。

流程控制¶

条件语句：

• ​IF​：用于在存储过程中进行条件判断。

IF condition THEN
    -- statements;
ELSEIF condition THEN
    -- statements;
ELSE
    -- statements;
END IF;

• ​CASE​：CASE​ 语句是一种更灵活的条件控制结构，类似于 IF​ 语句，但便于多个条件判断。

CASE
    WHEN condition THEN
        -- statements;
    WHEN condition THEN
        -- statements;
    ELSE
        -- statements;
END CASE;

循环语句：

• ​LOOP​：LOOP​ 是基本的循环结构，适合在循环次数不确定的情况下使用。使用 LEAVE​ 退出循环。

[loop_label:] LOOP
    -- statements;
    IF condition THEN
        LEAVE loop_label;
    END IF;
END LOOP;

使用 LEAVE​ 退出循环。

• ​WHILE​：WHILE​ 循环在满足条件时执行语句组，当条件不满足时结束循环。

[loop_label:] WHILE condition DO
    -- statements;
END WHILE;

• ​REPEAT​：REPEAT​ 循环和 WHILE​ 类似，但它会至少执行一次循环体。

[loop_label:] REPEAT
    -- statements;
UNTIL condition END REPEAT;

• ​FOR​：没有直接的 FOR​ 循环结构，但可以通过 WHILE​ 或 REPEAT​ 循环结合一个计数器变量来实现 FOR​ 循环的效果。

DELIMITER //

CREATE PROCEDURE for_example()
BEGIN
    DECLARE i INT DEFAULT 1;
  
    WHILE i <= 10 DO
        -- 执行的操作
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

跳转语句：

• ​LEAVE​：用于在循环中提前退出循环（break）。
• ​ITERATE​：用于重新开始循环（continue）。

错误处理语句：

• ​DECLARE ... HANDLER​：用于定义错误处理器，以便在发生特定错误或异常时执行特定的操作。可以定义 CONTINUE​ 或 EXIT​ 处理类型。

  • ​CONTINUE​：当异常发生时，继续执行后续代码。
  • ​EXIT​：当异常发生时，退出当前块。

DECLARE handler_type HANDLER FOR condition_value_list statement;

调用¶

CALL 存储过程名([IN | OUT | INOUT] 参数名 数据类型, ...);

删除¶

DROP PROCEDURE 存储过程名;

查看¶

-- 查看所有
SHOW PROCEDURE STATUS WHERE DB = '数据库名';

-- 查看指定
SHOW CREATE PROCEDURE 存储过程名;

游标¶

游标是用于逐行处理查询结果集的结构，适合需要对查询结果逐行操作的情况。以下是存储过程内游标的相关内容和使用方法。

主要步骤：

1. 声明游标：定义游标及其对应的查询语句。

DECLARE cursor_name CURSOR FOR select_statement;

2. 声明结束处理器：定义一个处理程序，用来控制游标遍历结束后的行为。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

3. 打开游标：初始化游标，准备提取数据。

OPEN cursor_name;

4. 提取数据：使用 FETCH​ 语句逐行获取数据。

FETCH cursor_name INTO variable_name [, variable_name] ...;

5. 关闭游标：释放游标占用的资源。

CLOSE cursor_name;

示例：

-- 假设有一个 employees 表，包含 id 和 salary 列。下面的存储过程会逐行遍历员工表，将每位员工的薪水提高 10%。

CREATE PROCEDURE increase_salaries()
BEGIN
    -- 声明变量
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE done BOOLEAN DEFAULT FALSE;

    -- 声明游标
    DECLARE emp_cursor CURSOR FOR SELECT id, salary FROM employees;

    -- 声明结束处理器，用于检测游标遍历的结束
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN emp_cursor;

    -- 开始遍历
    read_loop: LOOP
        -- 获取一行数据
        FETCH emp_cursor INTO emp_id, emp_salary;

        -- 检查是否到达结果集的末尾
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 进行所需操作，如加薪 10%
        UPDATE employees SET salary = emp_salary * 1.1 WHERE id = emp_id;
    END LOOP;

    -- 关闭游标
    CLOSE emp_cursor;
END //

DELIMITER ;

​​DECLARE CONTINUE HANDLER FOR NOT FOUND​​：声明一个异常处理器，用于在遍历结束时将 done​ 变量设置为 TRUE​。

​​LOOP​​ 和 ​LEAVE​​：LOOP​ 用于循环处理结果集，而 LEAVE​ 用于在游标到达末尾时退出循环。

​​FETCH​​：每次从游标中获取一行数据，并将数据存入指定的变量。

​​CLOSE​​：在操作完成后，关闭游标以释放资源。

主从同步

1. 配置文件

主从都需要配置。

[mysqld]
# 统一配置
# MySQL 服务器的唯一标识符
server-id=1
# 启用二进制日志功能，并指定日志文件的前缀名称。
log-bin=mysql-bin

# 主服务器配置
# 指定数据库的更改操作会被记录到二进制日志中。
binlog-do-db=dbName
# 指定数据库的更改操作忽略记录到二进制日志中。
binlog-ignore-db=sys,mysql,information_schema,performance_schema

# 从服务器配置
# 指定从服务器只复制指定数据库的更改。
replicate-do-db=dbName
# 指定从服务器忽略指定数据库的更改。
replicate-ignore-db=sys,mysql,information_schema,performance_schema

2. 授权

主服务器授权用户。

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';

-- 或

GRANT REPLICATION SLAVE ON *.* to 'replica_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

​%​ 可以替换为指定 ip。

3. 查看信息

查看主服务器的master信息。

SHOW MASTER STATUS\G;

获取 File​ 和 Position​ 的值，供后续配置。

4. 配置复制

配置从服务器的master。

CHANGE MASTER TO
  MASTER_HOST='master_IP',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;

5. 启动从属复制

START SLAVE;

6. 查看从属状态

SHOW SLAVE STATUS\G;