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

内部存储代码

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

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

存储过程和函数

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

触发器

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

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

// 先做一DB_LINK   ,link_nameCREATE OR REPLACE TRIGGER tg_nameBEFORE insertON t_sb_infoREFERENCING OLD AS OLD NEW AS NEWFOR 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了。

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