项目开发实战——Day 02

        上一篇文章讲解了关于对话框的一些问题，本篇文章将主要介绍MySQL数据库相关的一些问题。

一、MySQL数据库与MFC应用程序的连接。

        关于这部分的内容，在我的另一篇博客vs2013下C/C++连接mysql数据库相关问题中已经有了比较详细的介绍，这里不再阐述。

二、数据库的设计

        由于本项目的数据量不小，而且分批次进行，每个批次都有一定数量的测试样品，而且需要针对每个批次统计相关信息，打印报表等。因此，需要在数据库中建一张表，用于存储批次信息。在软件中录入批次信息点击确定入库的同时，在该数据库下以该批次号为数据表名新建一张表。

        为实现上述目的，想到了可以通过MySQL的触发器和存储过程来实现需求。但是，触发器中不能包含DDL指令，在触发器中添加创建数据表的SQL语句在执行时会报错：不支持隐式提交的指令。在查阅资料后，我们发现存储过程可以实现动态建表，而且MFC的CRecordset类支持调用存储过程。因此，果断使用存储过程来实现以上需求。

1、触发器

       既然提到了触发器，我们就简单介绍下触发器。

        触发器是MySQL编程中很重要的内容，主要用于数据同步，保证数据的一致性，主要用于在增删改查这些基本操作发生时，触发特定行为。

        创建触发器

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

注意：不能同时在一个表上建立2个相同类型的触发器

INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发

查看触发器：

  (1)show triggers

  (2)select * from INFORMATION_SCHEMA.TRIGGERS;

下面是触发器的一个简单示例：

DELIMITER ..
DROP TRIGGER IF EXISTS TR..
CREATE TRIGGER TR AFTER INSERT ON histab
FOR EACH ROW
BEGIN
DECLARE c varchar;//声明了一个varchar类型的变量c
SET c='10';//变量赋值
CALL create_on_insert(c);//这里调用了存储过程
CALL create_on_insert(new.批号);//这里调用了存储过程
END..
DELIMITER ;

关于new 与 old

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： 
NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用

2、存储过程

存储过程相当于一个函数，里面包含了多条SQL语句，是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程具有如下优点：

(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

注意：MySQL 5.0以前并不支持存储过程，5.0以后才开始支持

        创建存储过程

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

   简单实例：

//只有传入参数
DELIMITER ..
DROP PROCEDURE IF EXISTS create_on_insert..
CREATE PROCEDURE create_on_insert(in tabn varchar(20))
BEGIN 
SET @create_table_sql=CONCAT('CREATE TABLE tb_',tabn,'(id int not null auto_increment PRIMARY KEY,压力 float,体积 float,状态 int,备注 int);');
PREPARE create_table_sql FROM @create_table_sql;
EXECUTE create_table_sql;
END..
DELIMITER ;
//只有传出参数
DELIMITER ..
DROP PROCEDURE IF EXISTS create_on_insert..
CREATE PROCEDURE create_on_insert(in tabn varchar(20))
BEGIN 
SELECT COUNT(*) FROM histab into tabn;
END..
DELIMITER ;
//既有传入参数又有传出参数
DELIMITER ..
DROP PROCEDURE IF EXISTS create_on_insert..
CREATE PROCEDURE create_on_insert(in tabn varchar(20)，out usernums int)
BEGIN 
SET @create_table_sql=CONCAT('CREATE TABLE tb_',tabn,'(id int not null auto_increment PRIMARY KEY,压力 float,体积 float,状态 int,备注 int);');
SELECT COUNT(*) FROM histab INTO usernums;
PREPARE create_table_sql FROM @create_table_sql;//用户变量一般以@开头
EXECUTE create_table_sql;
END..
DELIMITER ;

        存储过程的调用

        CALL 过程名（参数）

        示例：call create_on_insert('test');
存储过程的查看

#查询存储过程
SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
SHOW PROCEDURE STATUS WHERE db='数据库名';
​​​​​​​#查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;

注意事项：

存储过程中，不允许将传入的参数直接作为数据表名去创建表，可以通过拼接（CONCAT）一些固定的字符实现。