Mysql存储过程学习笔记
1. 存储过程简介
常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
2.关于MySQL的存储过程
存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
3. MySQL存储过程的创建
(1). 格式
MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体
DELIMITER
CREATE PROCEDURE proc1(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM testSeq;
END
DELIMITER
|
|
这里先举个例子：
注：
（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识。
这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。
4.存储过程参数：
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
小例子：
存储过程为：
| |
| |
DROP PROCEDURE IF EXISTS TestParameter;
CREATE PROCEDURE TestParameter(IN a INT,
INOUT b INT,
OUT c INT)
BEGIN
SET a = a + 10;
SET b = b + 10;
SET c = b + 10;
END;
|
|
SET @a = 5;
SET @b = 6;
SET @c = 7;
CALL TestParameter(@a,@b,@c);
SELECT @a;
SELECT @b;
SELECT @c;
|
|
调用存储过程：
5.变量
(1).变量定义：
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
(2).变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
(3).变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。
| |
| |
DROP PROCEDURE IF EXISTS proc3
CREATE PROCEDURE proc3()
BEGIN
DECLARE x1 VARCHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 VARCHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END;
|
|
6.定义条件和处理程序
定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。
(1).定义条件
MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code
其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。
【示例】 下面定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find。可以用两种不同的方法来定义，代码如下：
//方法一：使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ;
//方法二：使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146 ;
(2).定义处理程序
MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：
DECLARE handler_type HANDLER FOR
condition_value[,...] sp_statement
handler_type:
CONTINUE | EXIT | UNDO
condition_value:
SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING
| NOT FOUND | SQLEXCEPTION | mysql_error_code
其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。
注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。
因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。
condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。
condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的
sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函
数的执行语句。
【示例】 下面是定义处理程序的几种方式。代码如下：
//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND';
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';
//方法三：先定义条件，然后调用
DECLARE can_not_find CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR can_not_find SET
@info='CAN NOT FIND';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
上述代码是6种定义处理程序的方法。
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。
第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。
第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。
第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。
7.MySQL存储过程的查询
我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。
我们可以用
select name from mysql.proc where db=’数据库名’;
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';
进行查询。
如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？
答案是：我们可以查看存储过程的详细，但是需要用另一种方法：
SHOW CREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。
8.MySQL存储过程的删除
删除一个存储过程比较简单，和删除表一样：DROP PROCEDURE从MySQL的表格中删除一个或多个存储过程。
9.条件语句
CREATE PROCEDURE proc2(IN parameter int)
begin
declare var int;
set var=parameter+1;
if var=0 then
insert into t values(17);
end if;
if parameter=0 then
update t set s1=s1+1;
else
update t set s1=s1+2;
end if;
end;
|
|
(1). if-then -else语句
CREATE PROCEDURE proc3 (in parameter int)
begin
declare var int;
set var=parameter+1;
case var
when 0 then
insert into t values(17);
when 1 then
insert into t values(18);
else
insert into t values(19);
end case;
end;
|
|
(2). case语句：
10.循环语句
CREATE PROCEDUER proc4()
BEIGIN
DECLARE VAR INT;
SET VAR = 0;
WHILE VAR < 6 DO
INSERT INTO T VLUES(VAR);
SET VAR = VAR+1;
END WHILE;
END;
|
|
(1).WHILE...END WHILE
(2).
CREATE PROCEDURE proc5()
BEIGIN
DECLARE v int;
set v = 0;
REPEAT
INSERT INTO t VALUES(V);
SET v = v+1;
UNTIL V >=5
END REPEAT;
END
|
|
REPEAT...END REPEAT
(3).LOOP...END LOOP:
CREATE PROCEDURE proc6()
BEIGIN
DECLARE v int;
SET v = 0;
LOOP_LABLE:loop
SET v = v+1;
IF v >= 5 THEN
LEAVE LOOP_LABLE;
END IF;
END LOOP;
END
|
|
LOOP 循环不需要初始条件，这点和WHILE循环相似，同时和REPEAT循环一样不需要结束条件，LEAVE 语句的意思是离开循环
11.ITERATE迭代
(1).ITERATE:
通过引用符合语句的标号，来重新开始符合语句
CREATE PROCEDUER proc10()
BEIGIN
DECLARE v INT;
SET v = 0;
LOOP_LABLE:LOOP
IF v = 3 THEN
SET v = v+1;
ITERATE LOOP_LABLE;
END IF;
INSERT INTO t VALUES(v);
SET v = v+1;
if v>=5 then
leave LOOP_LABLE;
END IF;
END LOOP;
END;
|
|
12.存储过程的基本函数
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
|
|
(1).字符串类：
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
|
|
（2).数学类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
|
|
(3).日期时间类
/*MySql 的注释*/
SELECT /*可以在行中间注释*/UTC_TIMESTAMP() a;
SELECT
/*也可以
多行
注释*/
UTC_TIMESTAMP() b;
#这个是单行注释 一直到行尾
-- 这个也是单行注释 一直到行尾
-- 注意“--”后必须有一个空格
|
|
13.关于注释
DROP PROCEDURE IF EXISTS zhuTestProCursor;
CREATE PROCEDURE zhuTestProCursor()
BEGIN
-- 定义变量 存放从游标读取的数据
DECLARE AtempID INT;
DECLARE AtempContent VARCHAR(200);
-- 该变量记录游标中是否还有数据可以读取
DECLARE no_more_data INT DEFAULT 0;
DECLARE myCursor1 CURSOR FOR SELECT ID,CONTENT FROM zhucwTestTable;
-- 如果没有数据返回时程序继续往下执行 并将 变量no_more_data 的值置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data=1;
-- 这条语句必须放到定义游标的语句下面
-- 打开游标
OPEN myCursor1;
lblRepeat:REPEAT
-- 读取游标内容 将ID和CONTENT值赋给对应暂存变量
FETCH myCursor1 INTO AtempID,AtempContent;
-- 没有数据时跳出REPEAT 循环
IF no_more_data = 1 THEN
LEAVE lblRepeat;
END IF;
-- 将取到的数据插入到zhucwTest
INSERT INTO zhucwTest2 (tempID,tempContent,tempDate) values (AtempID,AtempContent,UTC_TIMESTAMP());
-- 当变量 no_more_data 的值为0的时候跳出REPEAT循环
UNTIL no_more_data
END REPEAT lblRepeat;
-- 关闭游标
CLOSE myCursor1;
END
|
|
14学习游标
DROP PROCEDURE IF EXISTS myProTestUseCursorTempTable;
CREATE PROCEDURE myProTestUseCursorTempTable()
/*
使用游标查询表中数据，并将查询的数据插入到临时表中查询出来
*/
BEGIN
-- 定义暂存从游标读取内容的变量
DECLARE tempID INT;
DECLARE tempContent VARCHAR(50);
DECLARE tempDate DATETIME DEFAULT UTC_TIMESTAMP();
-- 记录游标是否还有内容的变量
DECLARE no_more_data INT DEFAULT 0;
-- 定义游标
DECLARE myCursor Cursor FOR SELECT ID,CONTENT FROM zhucwTestTable;
-- 定义执行
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;
-- 定义临时表
CREATE TEMPORARY TABLE IF NOT EXISTS tbl_myProTestUseCursorTempTable0(
fsID INT,
fsContent VARCHAR(50),
fsDate DATETIME,
PRIMARY KEY(fsID)
);
-- 删除临时表中数据
DELETE FROM tbl_myProTestUseCursorTempTable0;
-- 打开游标
OPEN myCursor;
-- 执行REPEAT 循环
lblRepeat:REPEAT
-- 读取游标信息，将信息给暂存变量
FETCH myCursor INTO tempID,tempContent;
-- 将暂存变量的值插入到临时表中
INSERT INTO tbl_myProTestUseCursorTempTable0 (fsID, fsContent , fsDate)
VALUES (tempID ,tempContent ,tempDate);
-- 如果游标没有数据 跳出循环体
IF no_more_data THEN
LEAVE lblRepeat;
END IF;
UNTIL no_more_data
END REPEAT lblRepeat;
SELECT * from tbl_myProTestUseCursorTempTable0;
END
|
|
16.学习临时表
17.实现mysql序列
| |
| |
/* 实现mysql序列 */
-- 1.创建一个含有一个自增长字段的表
DROP TABLE IF EXISTS testSeq;
CREATE TABLE testSeq(
seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY
)
-- 2.查询最后一个插入的ID值
SELECT LAST_INSERT_ID();
-- 3. 向表中插入一个空值
INSERT INTO testSeq VALUES (NULL)
-- 4.查询最后一个插入的ID值
SELECT LAST_INSERT_ID();
-- 5.向表中插入多个空值
INSERT INTO testSeq VALUES (NULL);
INSERT INTO testSeq VALUES (NULL);
INSERT INTO testSeq VALUES (NULL);
INSERT INTO testSeq VALUES (NULL);
-- 6.查询最后一个插入的ID值
SELECT LAST_INSERT_ID();
-- 7.查询seq的最大值
SELECT MAX(seq) FROM testSeq;
-- 8.具体也可用存储过程实现更为负责的序列
/*
总结：如果有表想实现像ORACLE那样的序列
只需
1.每次插入该表时再插入testSel一个NULL值
2.查询最后一个插入的ID值SELEC LAST_INSERT_ID
或直接查询MAX(seq)
*/
|
|
18.学习函数
| |
|
| |
/*学习函数 实现1+2+3+...N的值*/
-- 如果函数名存在 则删除该函数
DROP FUNCTION IF EXISTS zhuTestSum;
-- 创建函数参数为INT类型 返回值也为INT类型
CREATE FUNCTION zhuTestSum(gdate INT) RETURNS INT
BEGIN
-- 定义返回变量 默认值为0
DECLARE result INT DEFAULT 0;
-- 定义循环变量 默认值为0
DECLARE ii INT DEFAULT 0;
-- 如果参数值为负数则返回 -1
IF gdate < 0 THEN
SET result = -1;
-- 如果参数值为0则返回0
ELSEIF gdate = 0 THEN
set result = 0;
-- 如果参数值大于0则执行循环
ELSE
lblRepeat:REPEAT
-- 如果循环变量大于参数 跳出循环
SET result = ii+result;
SET ii = ii + 1;
UNTIL ii > gdate
END REPEAT lblRepeat;
END IF;
RETURN result;
END
|
|
| |
| |
DROP FUNCTION IF EXISTS zhuTestgetdate;
/*格式化日期*/
CREATE FUNCTION zhuTestgetdate(gdate datetime) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x= date_format(gdate,'%Y-%m-%d %h:%i:%s');
RETURN x;
END;
|
|
19.关于replace into
-- 测试replace into 函数
-- ！！！注意使用此函数必须有主见或唯一标识
-- 此函数的作用是如果数据一样不更新 否则更新该记录
-- 新建一个测试表
DROP TABLE IF EXISTS zhucwTest0412;
create TABLE zhucwTest0412(
id int,
content1 varchar(10),
content2 varchar(10),
PRIMARY KEY(id)
)
-- 插入测试数据
insert into zhucwTest0412(id,content1,content2)
values(1,"content1","content2");
SELECT * FROM zhucwTest0412;
insert into zhucwTest0412(id,content1,content2)
values(1,"content1","content2");
SELECT * FROM zhucwTest0412;
replace into zhucwTest0412(id,content1,content2)
values(1,"content11","content2");
SELECT * FROM zhucwTest0412;
|
|
-- 学习PREPARE 20160418
SET @SQL1 = "SELECT * FROM tbluser LIMIT ?";-- 定义SQL语句
SET @p = 11; -- 定义变量
-- 定义预处理语句
PREPARE executeSQL FROM @SQL1 ;
-- 执行预处理语句 将变量@p赋给占位符？
EXECUTE executeSQL USING @p;
-- 删除定义 释放资源
DEALLOCATE PREPARE executeSql;
/*
注意事项
A：PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。
B： 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。
C： 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。
D： PREPARE stmt_name 的作用域是当前客户端连接会话可见。
E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。
F： EXECUTE stmt_name 句法中，如果 stmt_name 不存在，将会引发一个错误。
G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。
H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。
I： PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！
*/
|
|
22. 学习prepare
20.时间戳
| |
| |
-- 将参数内的日期 转换成时间戳 如果参数为空取当前时间的时间戳
SELECT UNIX_TIMESTAMP('2016/4/18 5:37:36');
-- 将timestamp 形式整数 转化为 date类型
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP())
|
|
fsContent LONGTEXT
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
|
|
21. 长字符串支持表情符号
22.存储过程使用dbForge Studio 进行Debug调试
(1) 为存储过程，生成调试信息：右击要调试的过程--"Debug"--"Compile for Debugging', 操作如下
(2)为存储过程设置断点：打开存储过程的代码, 在你要设置断点的行上双击便可
(3)调试存储过程，单步执行，并查看每个变量的值。
右击存储过程---"Debug"--"Step Into", 如下图
先择"Stop Into"后，如果你的存储过程有参数，则为弹出窗体提示输入参数值，如果没有，则不直接运行;
存储过程会从"begin"开始执行，然后点又上角的"step over"(F10), 单步执行。
查看变量值：选中变量，点右键，选择"Add Wath", 这个变量就会在"Watches"这个视图区出现，如果你单步运行到这个变量值，则可以看见了，这样就可以调试，变量值是否正确，有错误没，循环次数等。
23.条件处理
(1) DECLARE…HANDLER语句
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action:
CONTINUE
| EXIT
| UNDO
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
|
|
语法如下
a、condition_value [,condition_value]，这个的话说明可以包括多种情况(方括弧表示可选的)，也就是一个handler可以定义成针对多种情况进行相应的操作；另外condition_value可以包括的值有上面列出来的6种：
1、mysql_error_code，这个表示MySQL的错误代码，错误代码是一个数字，完成是由mysql自己定义的，这个值可以参考mysql数据库错误代码及信息。
2、SQLSTATE [VALUE] sqlstate_value，这个同错误代码类似形成一一对应的关系，它是一个5个字符组成的字符串，关键的地方是它从ANSI SQL和ODBC这些标准中引用过来的，因此更加标准化，而不像上面的error_code完全是mysql自己定义给自己用的，这个和第一个类似也可以参考mysql数据库错误代码及信息。
3、condtion_name，这个是条件名称，它使用DECLARE...CONDITION语句来定义，这个后面我们会介绍如何定义自己的condition_name。
4、SQLWARNING，表示SQLTATE中的字符串以‘01’起始的那些错误，比如Error: 1311 SQLSTATE:01000 (ER_SP_UNINIT_VAR)
5、NOT FOUND，表示SQLTATE中的字符串以‘02’起始的那些错误，比如Error: 1329 SQLSTATE: 02000(ER_SP_FETCH_NO_DATA)，其实这个错误就是用在我们介绍游标的那个问题所出现的情况，也就是没有fetch到记录，也就是我们游标到记录的尾巴了的情况。
6、SQLEXCEPTION，表示SQLSTATE中的字符串不是以'00'、'01'、'02' 起始的那些错误，这里'00'起始的SQLSTATE其实表示的是成功执行而不是错误，另外两个就是上面的4和5的两种情况。
上面的6种情况其实可以分为两类，一类就是比较明确的处理，就是对指定的错误情况进行处理，包括1、2、3这三种方式；另一类是对对应类型的错误的处理，就是对某一群错误的处理，包括4、5、6这三种方式。这个是介绍了condition_value。另外还要注意的一个内容是MySQL在默认情况下(也就是我们没有定义处理错误的方法-handler)自己的错误处理机制：1、对于SQLWARNING和NOT FOUND的处理方法就是无视错误继续执行，所以在游标的例子里面如果我们没有对repeat的条件判断的那个值做个no_more_products=1的handler来处理，那么循环就会一直下去。2、对于SQLEXCEPTION的话，其默认的处理方法是在出现错误的地方就终止掉了。
b、statement，这个比较简单就是当出现某种条件/错误时，我们要执行的语句，可以是简单的如 SET var = value这样的简单的语句，也可以是复杂的多行的语句，多行的话可以使用BEGIN ..... END这里把语句包括在里面(这个好比delphi里面的情况，注意到我们的存储过程也是多行的，所以也要BEGIN .... END)。
c、handler_action，这个表示当执行完上面的statement后，希望执行怎样的动作，这里包括CONTINUE、EXIT、UNDO，表示继续、退出、撤销(暂时不支持)。这边就是两种动作，其实这两种动作在上面也说过了，CONTINUE就是一个是SQLWARNING和NOT FOUND的默认处理方法，而EXIT就是SQLEXCEPTION的默认处理方法。
来看个简单的例子，这里创建一个对SQLSTATE的代码为'23000'的错误(重复的主键)进行处理的HANDLER，每次发生时我们对变量@x进行增加1：
| |
| |
SET @x = 0;
DROP PROCEDURE IF EXISTS ConditionProc
CREATE PROCEDURE ConditionProc()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = @x+1;
INSERT INTO products values(1,default,default,default,default,default);
INSERT INTO products values(1,default,default,default,default,default);
SELECT @x;
END
|
|
通过结果我们知道出现了两次插入的记录同原有的记录出现主键重复的情况。当然这个是由下面这个代码触发的。
INSERT INTO products values(1,default,default,default,default,default);
(2) DECLARE...CONDITION语句
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
-- body of handler
END;
|
|
这个语句其实是为了让我们的错误条件更加的清晰明了化的，对于上面的情况，像SQLSTATE '23000'这种表示是一种很不直观的方法，要通过相应的文档去对应，阅读起来比较不方便。而DECLARE....CONDITION可以对条件定义相对应的名称，看个例子就清楚了：
当然在上面的例子我们没有用到BEGIN....END，我们只有一行SET @x=@x+1；这里我们用duplicate_key这个条件名称来对应到SQLSTATE '23000'上面，这样查看起来更加的直观。这你duplicate_key就是我们上面介绍DECLARE....HANDLER时候的那个condition_name。
24.INSERT IGNORE INTO 与 INSERT INTO
INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE INTO会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
MyISAM 和InnoDB 讲解
InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。
以下是一些细节和具体实现的差别：
◆1.InnoDB不支持FULLTEXT类型的索引。
◆2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁。而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。
作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，如果数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是首选。
原因如下：
1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。
2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
3、经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。
4、从接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。
5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。
7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。
当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。
26.存储过程调试
可以使用mysql自带的日志表 mysql.general_log的记录 查询出存储过程的调用语句，执行。可以使用debug也可使用select语句进行跟踪调试
| |
| |
-- 查看mysql的整个调用信息
SELECT * FROM mysql.general_log a WHERE a.argument LIKE 'CALL%' ORDER BY event_time desc LIMIT 10；
|
|
27.存储过程实现递归
| |
| |
DROP PROCEDURE IF EXISTS css.testRecursion;
CREATE PROCEDURE css.testRecursion(INOUT i INT,INOUT result INT)
lblTop:BEGIN
/*
存储过程实现递归
*/
IF i <= 0 THEN
LEAVE lblTop;
END IF;
IF result = 0 OR result = NULL THEN
SET result = 0;
END IF;
# 递归调用不能嵌套超过10层
IF i > 11 THEN
LEAVE lblTop;
END IF;
IF i >= 1 THEN
SET result = result + (i);
SET i = i - 1;
CALL testRecursion( i,result);
ELSE
LEAVE lblTop;
END IF;
END
|
|
28.PREPARE 预备语句
-- 定义预备语句
PREPARE stmt_name FROM preparable_stmt;
-- 执行预备语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
-- 解除预备语句
{DEALLOCATE | DROP} PREPARE stmt_name;
|
|
（1）语法
（2） 例子
PREPARE myPre FROM "SELECT * FROM zhucwTest0421 LIMIT ?,?";
-- 占位符只能由用户变量取代
SET @a = 2;
SET @b = 4;
EXECUTE myPre USING @a,@b;
DEALLOCATE myPre;
|
|
（3）使用PREPARE 需要注意
① PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。
② 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。
③ 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。
④ PREPARE stmt_name 的作用域是当前客户端连接会话可见。
⑤ 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。
⑥ EXECUTE stmt_name 句法中，如果
stmt_name 不存在，将会引发一个错误。
⑦ 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。
⑧在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE,
SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。
⑨ PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！
⑩ PREPARE 语句里的占位符参数值只能有用户变量提供，USING子句必须准确地指明用户变量，用户变量的数目与语句中的参数制造符的数量一样多。
DROP PROCEDURE IF EXISTS css.testCycle;
CREATE PROCEDURE testCycle(IN i INT,IN cycle VARCHAR(50))
BEGIN
DECLARE sContent1 VARCHAR(50) DEFAULT 'content1';
DECLARE sContent2 VARCHAR(50) DEFAULT 'content2';
-- 预备一个语句
PREPARE myPre FROM "INSERT INTO css.zhucwTest0421(content1,content2)
VALUES (CONCAT(?,?),CONCAT(?,?))";
# CASE 语句
CASE WHEN cycle = "WHILE"
THEN
# WHILE 循环
WHILE i > 0 DO
SET sContent1 = "WHILE1";
SET sContent2 = "WHILE2";
SET @pContent1 = sContent1;
SET @pContent2 = sContent2;
SET @pI = i;
-- 执行预备的语句
EXECUTE myPre USING @pContent1,@pI,@pContent2,@pI;
SET i = i - 1;
END WHILE;
WHEN
cycle = "REPEAT"
THEN
# REPEAT循环
lblRepeat:REPEAT
IF i = 0 THEN
LEAVE lblRepeat;
END IF;
SET sContent1 = "REPEAT1";
SET sContent2 = "REPEAT2";
SET @pContent1 = sContent1;
SET @pContent2 = sContent2;
SET @pI = i;
EXECUTE myPre USING @pContent1,@pI,@pContent2,@pI;
SET i = i - 1;
UNTIL i = 0
END REPEAT;
|
|
29.CASE…WHEN…ELSE…END CASE与各项循环结合例子
WHEN
cycle = "LOOP"
THEN
# LOOP循环
lblLoop:LOOP
IF i = 0 THEN
LEAVE lblLoop;
END IF;
SET sContent1 = "LOOP1";
SET sContent2 = "LOOP2";;
SET @pContent1 = sContent1;
SET @pContent2 = sContent2;
SET @pI = i;
EXECUTE myPre USING @pContent1,@pI,@pContent2,@pI;
SET i = i - 1;
END LOOP;
WHEN cycle = "ITERATE"
THEN
# ITERATE
lblLoop2:LOOP
SET sContent1 = "ITERATE1";
SET sContent2 = "ITERATE2";
SET @pContent1 = sContent1;
SET @pContent2 = sContent2;
SET @pI = i;
EXECUTE myPre USING @pContent1,@pI,@pContent2,@pI;
SET i = i - 1;
IF i > 0 THEN
ITERATE lblLoop2;
ELSE
LEAVE lblLoop2;
END IF;
END LOOP;
ELSE
# OTHER
lblRepeat3 : REPEAT
SET sContent1 = "Other1";
SET sContent2 = "Other2";
SET @pContent1 = sContent1;
SET @pContent2 = sContent2;
SET @pI = i;
EXECUTE mypre USING @pContent1,@pContent2,@pI;
SET i = i - 1;
UNTIL i <= 0
END REPEAT;
END CASE;
DEALLOCATE PREPARE myPre;
END;
|
|
