Mysql第九天内部存储代码、绑定变量

矫情吗；* 2022-08-02 13:45 150阅读 0赞

## 内部存储代码 ##

主要有，存储过程、函数、时间、触发器  
优点：

*  方便代码重用
 *  离数据进，执行起来可以节省带宽和网络延迟
 *  mysql可以缓存存储过程的执行计划，这样可以降低生成执行计划的消耗  
    缺点：
 *  存储过程开发起来没有好的调试工具
 *  没有很多可用的库，因此复杂逻辑实现起来麻烦
 *  不安全，不能控制存储过程的资源消耗，也不好监测，会直接把程序拖死。  
    这里具体的写法就不说了，只是说一下用法，以及一些优缺点。个人因为存储过程的水平实在是差，基本上没写过，所以潜意识有抵触，非必要情况下都是能不用就不用。

### 存储过程和函数 ###

在代码中用Call调用。  
比较适合做统计，而不适合日常的插入和查询，感觉跟事件配合起来做定时任务来 计算一些归档类的，统计类的内容比较合适

### 触发器 ###

在行操作的时候，提供一些触发点，可以定义被触发后执行sql语句。一般可以有一下的用法，或者需要注意的地方：

*  做缓存或者统计表的更新操作。 但是因为现在分布式key-value缓存的流行，及触发器本身的行操作机制，不适合做批量的更新等操作，因此比较少用
 *  自己实现外键，因为外键维护需要性能，可以再应用程序张维护外键关系。还可以使用触发器的BEFROE INSERT做外键检查，但是要注意，如果触发器中没有使用SELECT FOR UPDATE就有可能导致数据不一致。又因为INNODB触发器是在同一个事务中，所以数据不一致的概率比较低
 *  在一些特定的复制场景有用，比如用户各自在自己的库上进行操作，然后这个操作同步到主库，再把所有操作分发给所有用户。 这样就可以使用触发器，在用户在本地库插入数据的时候同步主库，然后利用主题的复制功能  
    大体的做法是：

// 先做一DB_LINK   ,link_name
    CREATE OR REPLACE TRIGGER tg_name BEFORE insert ON t_sb_info REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN insert into t_table1@link_name select *from t_table1 where year=:new.year and month=:new.month;
       insert into t_table2@link_name select *from t_table2 where year=:new.year and month=:new.month;
    END;

### 事件 ###

*  基本使用就是定时执行存储过程
 *  跟存储过程一样，不适合复制，而仅仅是执行结果之后，复制结果
 *  可以在表INFOMATION\_SCHEMA.EVENTS中看到各个事件状态。
 *  可能面临，一个任务没执行完，下一个已经开始的问题。可以用GET\_LOCK()来确保当前总是只有一个事件在被执行。
 *  事件不会用到连接线程，会有自己的事件线程，执行完之后销毁而不会缓存。通过设置 SET CLOBAL event\_scheduler :=1;来开启。

## 绑定变量 ##

这个就是Java里面的PreparedStatement了。

PreparedStatement pstmt = con.prepareStatement("UPDATE table4 SET m = ? WHERE x = ?");
    pstmt.setString(1, "Hi");
    for (int i = 0; i < 10; i++) { 
    pstmt.setInt(2, i);
    int rowCount = pstmt.executeUpdate();
    }

这样一个更新的sql语句分成了两部，第一步是把带?的sql语句发送到服务器做预编译。第二步就是设置参数并且执行了。

这样做会有如下的好处：

*  解析一次sql
 *  执行计划会有部分缓存
 *  二进制的方式只发送参数和句柄减少通信量  
    还有一种预发可以SQL接口绑定变量，这个不常用不说了

### 限制 ###

*  会话级别的
 *  如果只执行一次的SQL，使用绑定变量会多一次通讯的开销
 *  不要忘记释放使用绑定变量的资源，因为mysql对这个有一个上线

### SQL注入 ###

使用绑定变量还能够额外获得一个好处，即能够防止SQL注入攻击。  
比如登陆的时候我们的代码是这样的：

String sql = "SELECT COUNT(*) FROM user WHERE username='"+ username+"' AND password ='"+ password +"';"
    // 如果password 传入 ' OR '1'='1这个时候sql就变成了：
    // SELECT COUNT(*) FROM user WHERE username='wzj' AND password = '' OR '1'='1' 这个时候就是查询所有，就相当于永远为true了。

使用绑定变量就可以有效的避免这个问题。