mysql做批量插入时如果存在就更新不存就插入_自学MySQL第七天

什么是store procedure，简单来说存储代码的地方。它具有方便调用，增加数据库安全性、稳定性等等优点。

首先，使用CREATE PROCEDURE + 名字+括号（）来创建一个存储过程（STORE PROCEDURE），使用BEGIN和END告诉MySQL起点和终点，在创建过程中需要注意的是，我们要用DELIMITER来改变默认的分隔符，这样才不会报错。如图：

也就是说，将BEGIN和END中间的主体代码给存储到get_invoices_with_balance这个存储过程里去了，下次需要用到这段代码时直接调用即可。需要调用时，使用代码CALL + 过程名+（）即可。

另外，还有一种更加简便的方法可以创建存储过程，也就是直接在控制面板的这个地方：

单机鼠标右键点create store procedure，直接创建存储过程，这样就不用改分隔符，专注于主体代码即可。

想要删除存储过程，使用DROP PROCEDURE IF EXISTS +过程名即可。

在创建过程时，我们还可以在过程名后的括号中加入指定的参数，之后当我们调用过程中，需要输入相应的参数值才可以正确的调出结果，输入参数时需要指定格式。如图：

我们还可以指定如果在参数为空情况下的默认值，可以通过IF-SET-END IF来实现，也可以通过上面说过的内置函数IFNULL()来实现

存储过程可以用来更新、删除数据，并且要是输入的参数不符合要求，还能做出错误提示等功能，SQLSTATE ERRORS可以上网查看：

图中创建了一个有三个参数的存储程序，并且可以检验输入数据的有效性，如果在payment_amount参数中输入了负数，则会报错。

在MySQL中还可以设置变量，这里介绍了两种变量，第一种时User or session variables，另一种是Local variables。区别在于前者可以看作全局变量，一直存在直至对象被垃圾回收，后者是存储程序中的临时变量，他们在内存中的call stack上，程序结束后消失。

接下来介绍自创函数，类似其它统计软件，在mysql中，直接右键点击数据库下面的function就可以找到create function按键。

在图中，我定义了一个名为 get_risk_factor_for_client的函数，在RETUENS INT后有一个READS SQL DATA，是指这个函数具有这个attribute，另外还有DETERMINISTIC和MODIFY SQL DATA这两个attribute。

1. READS SQL DATA 是指此函数根据数据库的数据来进行计算操作，结果随着数据变动而变动。

2. DETERMINISTIC 是指当给此函数相同的参数时将不会改变结果，不会随着数据库数据的变化而变化。

3. MODIFY SQL DATA 是指此函数可以改变数据库数据。

创建函数时必须指定至少一个上述attribute。

接下来定义函数中需要用到的变量risk_factor，在这里它就是个local variables，仅存在此方法中。用SELECT-INTO来给用到的变量赋值，最后用SET来指定公式。一个函数就创建完毕了。

接下来要学习的是创建一个TRIGGER，TRIGGER是指一段会自动运行的代码，经常用在我们更新数据后的自动操作。

在图中，我创建了一个当在payments表中插入新数据时自动运行的TRIGGER，它会自动更新invoices表里的数据。首先要在CREATE TRIGGER后加一个名字，在下面一行写上AFTER INSERT ON + 表名，告诉MySQL这个TRIGGER要在插入操作之后发生，再加一个FOR EACH ROW表明在每插入一行后都会运行。在BEGIN 和END中写入主要的code，这样就可以完成啦。

创建完成后可以发现，我们看不到创建的TRIGGER，这时可以用SHOW TRIGGERS 来查看所有创建的TRIGGERS，删除TRIGGER 可以用DROP TRIGGER IF EXISTS。

TRIGGERS一个非常有用的用法就是可以记录所有操作，类似于生成一个操作LOG。如图：

这个TRIGGER就是在日记表payment_audit中更新操作日志，可以看到每插入一个新数据，都会在日志中记录下新数据的内容和操作类型以及操作时间。

接下来是EVENTS，EVENTS也是一段代码，它会根据使用者的要求自动运行，比如每天中午运行一次等等。

图中显示了一段简单的创建EVENT的代码，首先是CREATE EVENT，下面接 ON SCHEDULE，可以加在什么时间点，或者从什么时候开始到什么时候结束（STARTS-ENDS），接着要用DO BEGIN-END，在中间写上主体code即可。

想要看到有哪些EVENT，可以用SHOW EVENTS来查看，删除EVENT可以用DROP EVENTS。更方便的是用ALTER EVENT，这样就不用删掉再重新创建。后面还可以加ENABLE和DISABLE来使EVENT生效和失效。

接下来要了解的是事物（transaction）的概念，事物是一个最小的不能再分的工作单元，通常一个事务对应一个完整的业务（例如银行账户转账业务，该业务就是一个最小的工作单元），一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成

事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。简单来说，事务的存在保证了我们在对数据库的多个内容同时进行操作时的正确性。

在图中创建了一个事务，主要关注最后一个ROLLBACK，还可以选择使用COMMIT。当MySQL看到COMMIT时，才会将所有的DML写进数据库，如果其中有一个内容出错，将会撤销所有的操作。如果写的是ROLL BACK，就会撤销所有的事务，用于检查错误。