mysql的函数与储存过程与pymysql的配合使用

函数与储存过程与pymysql的配合使用"/>

mysql的函数与储存过程与pymysql的配合使用

现在mysql上定义一个函数，一个储存过程

函数：

 delimiter \\CREATE FUNCTION f2 ( num2 INT, num1 INT ) RETURNS INT BEGINDECLAREa DEFAULT INT 1;SET a = num1 * num2;RETURN ( a ) ;END  \\delimiter;

 函数式是计算两个值相乘的

 在mysql的调用

select f2(9,5); -- 45

 同时函数可以是配合select 函数 from 来使用的；函数里面不能写select * from 这样的语言。

 储存过程（或者是事物）

 定义

delimiter \\
CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(inout check_num tinyint,
inout num int)
begindeclare exit handler for SQLEXCEPTIONbegin set check_num=1;rollback;
end;start transaction ;select * from class;set num = num *check_num;insert into class(caption)values('高三九班'),('拒水小学wu年级九班');commit; select * from class;set check_num=2;
end \\delimiter ;

 在mysql上调用事物：

set @a=6;
set @b=5;
call p6(@a,@b);
select @a,@b;

 在mysql必须要定义一个全局的变量。然后传值，在查看值得变化，来判断事物是否执行成功，在事物的内部，会显示出来，但是如果有错误，便会对值不会进行修改，而且修改的值必须要进行commit才能修改或者上传成功。

我去，意外发现：

在mysql上定义事物p7

 1 delimiter \\
 2 
 3 CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(inout check_num tinyint,
 4 inout num int)
 5 begin
 6     declare exit handler for SQLEXCEPTION
 7     begin 
 8     set check_num=1;
 9     rollback;
10     end;
11     
12     start transaction ;
13     select * from class;
14     set num = num *check_num;
15     insert into class(caption)values('p7高三九班'),('p7拒水小学wu年级九班');
16     commit; 
17     select * from class;
18     insert into class(caption)values('p7高十八班','p7wu年级九班');
19     commit;
20     set check_num=2;
21     end \\
22     delimiter ;

 你看一下定义的事物，你会发现18行是错误的写法，也就是说mysql会报错，然后我再去调用这个事物

set @a=6;
set @b=5;
call p7(@a,@b);
select @a,@b;

 发现@a=1 @b=30 就是说在12到17行的代码全部都调用了，而且因为里面有个commit所以说表class里面确实是添加('p7高三九班'),('p7拒水小学wu年级九班')这两个变量。

结论：事物的本质是commit的使用，所以需要将检测的数字和commit放在最后，因为事物的代码是会执行的，执行到了错误地地方，才会走报错的地方，因此在写事物的时候，一定要注意commit的位置。

在pymysql里面调用函数和储存过程（以及事物）

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='review60',charset='utf8')
cursor=conn.cursor()#得到信息是以字典的形式
sql ='select f2(3,4)'
cursor.execute(sql)
a=cursor.fetchall()
print('f2(3,4)的返回值：',a)
num1=3
num2=4
cursor.callproc('p6',(num1,num2))#num1=@_p6_0,num2=@_p6_1
print('第一次的p6返回值：',cursor.fetchall())
print('num1 num2:',num1,num2)
cursor.execute('select @_p6_0')
num1=cursor.fetchall()#成功返回2，失败返回1
print('查看返回值num1：',num1)
cursor.execute('select @_p6_1')
num2=cursor.fetchall()#返回num1 x num2
print('查看返回值num2：',num2)
cursor.close()
conn.close()

 结果：

f2(3,4)的返回值： ((12,),)
第一次的p6返回值： 一个很长的列表
num1 num2: 3 4
查看返回值num1： ((2,),)
查看返回值num2： ((12,),)

 mysql的内置函数：

CHAR_LENGTH(str)返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。CONCAT(str1,str2,...)字符串拼接如有任何一个参数为NULL ，则返回值为 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定义连接符）CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。CONV(N,from_base,to_base)进制转换例如：SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示FORMAT(X,D)将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替换位置其实位置len：替换的长度newstr：新字符串特别的：如果pos超过原字符串长度，则返回原字符串如果len超过原字符串长度，则由新字符串完全替换INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。LEFT(str,len)返回字符串str 从开始的len位置的子序列字符。LOWER(str)变小写UPPER(str)变大写LTRIM(str)返回字符串 str ，其引导空格字符被删除。RTRIM(str)返回字符串 str ，结尾空格字符被删去。SUBSTRING(str,pos,len)获取字符串子序列LOCATE(substr,str,pos)获取子序列索引位置REPEAT(str,count)返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)返回字符串 str ，顺序和字符顺序相反。RIGHT(str,len)从字符串str 开始，返回从后边开始len个字符组成的子序列SPACE(N)返回一个由N空格组成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。mysql> SELECT TRIM('  bar   ');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx'

这是一些mysql的内置函数，更多函数请点击官方和中文。

 需要commit的操作的代码，在增删改是，一般要commit才会生效，不然就回滚了。下列的操作需要commmit，

1.在pymysql中操作 execute和callpro都需要的操作，即使储存对象里面有commit。

2.在mysql的储存过程，事物中需要此操作

3.但是在由表中却不需要此操作。

。

转载于:.html