MySQL之存储过程与函数用法及说明_Mysql

简单的说,存储过程就是一条或者多条sql语句的集合,可以理解为脚本,但是起作用不仅限于批处理,下面我们将重点学习如何使用创建存储函数和过程,变量的调用查看等,存储过程是mysql的一个重点内容.

存储程序可以分为存储过程和函数,mysql中创建存储过程和函数使用的语句分别是:create procedure和create function.使用call语句来调用存储过程,只能用输出变量返回值.函数可以从语句外调用(即通过引用函数名),也能返回标量值,存储过程也可以调用其他存储过程.

创建存储过程

创建存储过程,需要使用create procedure语句,其基本语法格式如下:

create procedure proc_name ([proc_parameter])
	[characteristics ...] routine_body

如上,create procedure为用来创建存储函数的关键字,proc_name为存储过程名称,proc_parameter为指定存储过程的参数列表,列表的形式如下所示:

[ in | out | inout ] param_name type

其中,in表示输入的参数,out表示输出的参数,inout表示即可以输入也可以输出,param_name表示参数名称,type表示参数的类型,该类型可以使mysql数据库中任意的类型.

创建简单存储过程: 创建一个存储过程proc(),实现查询lyshark数据表的所有字段.

mariadb [lyshark]> delimiter //
mariadb [lyshark]> create procedure proc()
    -> begin
    -> select * from lyshark;
    -> end //
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> delimiter ;
mariadb [lyshark]> call proc;

以上存储过程,只是执行了查询语句的操作,delimiter语句的作用是方式结束符与冒号相冲突,当我们写完存储过程时,应该使用相同的闭合语句恢复.

创建带参存储过程: 创建一个存储过程,查询lyshark表中记录,过程名称为countproc,并使用count(*)计算后把结果放入参数param1中,sql语句如下:

mariadb [lyshark]> delimiter //
mariadb [lyshark]> create procedure countproc(out param1 int)
    -> begin
    -> select count(*) into param1 from lyshark;
    -> end //
query ok, 0 rows affected (0.01 sec)
mariadb [lyshark]> delimiter ;

当我们想要调用上面的存储过程是,需要指定一个变量名来接收返回结果,此处指定为temp.

mariadb [lyshark]> call countproc(@temp);
query ok, 1 row affected (0.00 sec)
mariadb [lyshark]> select @temp;
+-------+
| @temp |
+-------+
|    17 |
+-------+
1 row in set (0.00 sec)

创建带参存储过程: 创建一个存储过程countproc1,然后调用这个过程,定义sql语句如下:

mariadb [lyshark]> delimiter //
mariadb [lyshark]> create procedure countproc1(in id int,out temp int)
    -> begin
    -> select count(*) into temp from lyshark where gid=id;
    -> end //
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> delimiter ;

接着我们调用countproc1这个存储过程,并给它传递相应的参数.

mariadb [lyshark]> select count(*) from lyshark where gid=101;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
mariadb [lyshark]> call countproc1(101,@num);
query ok, 1 row affected (0.00 sec)
mariadb [lyshark]> select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

创建存储函数

存储函数的使用方法与mysql内部函数的使用方法是一样的,在mysql中,用户自己定义的存储函数与mysql内部函数是一个性质的.区别在于,存储函数是用户自己定义的,而内部函数则是开发者编写的.

创建存储函数: 创建储存函数countproc2,然后调用这个函数,sql语句如下:

mariadb [lyshark]> delimiter //
mariadb [lyshark]> create function countproc2(myid int)
    -> returns int
    -> begin
    -> return(select count(*) from lyshark where gid=myid);
    -> end //
query ok, 0 rows affected (0.02 sec)
mariadb [lyshark]> delimiter ;

接着我们来调用这个存储函数,sql语句如下:

mariadb [lyshark]> select countproc2(101);
+-----------------+
| countproc2(101) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

以上可以看出,返回结果,虽然存储函数和存储过程的定义稍有不同,但是可以实现相同功能.

创建存储函数: 创建名称为namezip,该函数返回select语句查询结果,数值类型为字符串,sql语句如下:

mariadb [lyshark]> delimiter //
mariadb [lyshark]> create function namezip()
    -> returns char(50)
    -> return (select s_name from suppliers where s_call='4521');
    -> //
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> delimiter ;

调用一下看看效果,sql语句如下.

mariadb [lyshark]> select s_name from suppliers where s_call ='4521';
+------------+
| s_name     |
+------------+
| fastfruit. |
+------------+
1 row in set (0.00 sec)
mariadb [lyshark]> select namezip();
+------------+
| namezip()  |
+------------+
| fastfruit. |
+------------+
1 row in set (0.00 sec)

如果在存储函数中的return语句返回一个类型不同于函数的型的值,返回值将被强制为恰当的类型.

提示:指定参数为in,out或者inout只对procedure是合法的.function中总是默认为in参数.returns子句只能对function做指定,对函数而言这是强制的.它用来指定函数的返回类型,而且函数体必须包含一个return value语句.

声明使用变量

变量可以在子程序中声明并使用,这些变量的作用范围实在begin...end程序中,本小姐将介绍定义和赋值一个变量,定义变量的语句如下:

declare var_name[,varname]...date_type [default value];

如上,var_name为局部变量名称,default value子句给变量提供一个默认值,值除了可以被声明为一个常数之外,还可以被指定为一个表达式,如果没有default子句,初始值为null.

定义变量: 定义名称为myparam的变量,类型为int,默认值设置为100,只能在过程中使用.

declare myparam int default 100;
declare charsss char default 'hello';

变量赋值: 定义3个变量,分别为var1,var2,var3,数据类型为int,使用set为变量赋值,代码如下:

declare var1,var2,var3 int;
mariadb [lyshark]> set @var1=10,@var2=30;
query ok, 0 rows affected (0.01 sec)
mariadb [lyshark]> set @var3=@var1+@var2;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> select @var3;
+-------+
| @var3 |
+-------+
|    40 |
+-------+
1 row in set (0.00 sec)

使用数据光标

查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和函数中使用光标来逐条读取查询结果集中的记录,光标必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明.

声明光标: 声明名称为cursor_lyshark的光标,sql代码如下:

declare cursor_lyshark cursor for select name,price from lyshark;

打开光标: 打开名称为cursor_lyshark的光标,sql代码如下:

open cursor_lyshark

使用光标: 使用名称为cursor_lyshark的光标,将查询出的数据存入lyshark_name和lyshark_price这两个变量中.

fetch cursor_lyshark into lyshark_name,lyshark_price;

关闭光标: 关闭名称为cursor_lyshark的光标文件.

close cursor_lyshark

使用流程控制

流程控制语句用来根据条件控制语句的执行,mysql中用来构造控制流程的语句有:if,case,loop,leave,iterate,repeat,while等,每个流程中可能包含一个单独的语句,或者是使用begin...end构造的复合语句,构造可以被嵌套.

if-then-else条件语句: if语句用来判断条件分支

首先传递一个数值,接收到parameter变量里,然后自增+1,并判断,如果var=0则返回系统时间,var=1则返回系统日期.

create procedure proc_1(in parameter int)
begin
    declare var int;
    set var=parameter+1;
		if var=0
		then
				select current_time();
		elseif var=1
		then
				select current_date();
		end if;
  end

接着我们编译这段过程,并测试依次传入temp=-1和temp=1.

mariadb [lyshark]> set @temp=0;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> call proc_1(@temp);
+----------------+
| current_date() |
+----------------+
| 2018-12-28     |
+----------------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> set @temp=-1;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> call proc_1(@temp);
+----------------+
| current_time() |
+----------------+
| 07:23:04       |
+----------------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)

case-when-then-else语句: 另一个进行条件判断的语句,条件判断分支结构

create procedure proc_2 (in parameter int)
    begin
      declare var int;
      set var=parameter+1;
      case var
        when 0 then
          select "这个数值是0呀";
        when 1 then
          select "这个数值是1呀";
        else
          select "这个数值是其他呀";
      end case ;
    end ;

mariadb [lyshark]> set @temp=0;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> call proc_2(@temp);
+---------------------+
| 这个数值是1呀       |
+---------------------+
| 这个数值是1呀       |
+---------------------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> set @temp=-1;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> call proc_2(@temp);
+---------------------+
| 这个数值是0呀       |
+---------------------+
| 这个数值是0呀       |
+---------------------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> set @temp=-9999;
query ok, 0 rows affected (0.00 sec)
mariadb [lyshark]> call proc_2(@temp);
+--------------------------+
| 这个数值是其他呀         |
+--------------------------+
| 这个数值是其他呀         |
+--------------------------+
1 row in set (0.00 sec)
query ok, 0 rows affected (0.00 sec)

loop语句: 用于循环执行重复语句,loop只会创建一个循环过程,并不会判断.

首先使用loop语句进行循环操作,id值小于等于5之前,将重复执行循环过程,代码如下:

create procedure proc_4()
	begin
      declare id int;
      set id=0;
      loop_lable:loop
		select "这里开始计数";
        set id=id+1;
        if id >=5 then
          leave loop_lable;  #退出循环
        end if;
      end loop;
	end;

whwhile-do…end-while语句: 流程循环语句.

create procedure proc_4()
    begin
      declare var int;
      set var=0;
      while var<6 do
        select "循环判断";
        set var=var+1;
      end while ;
    end;

iterate语句: 通过引用复合语句的标号,来从新开始复合语句

create procedure proc_5()
  begin
    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;

repeat语句: 此语句的特点是执行操作后检查结果

create procedure proc_6 ()
    begin
      declare v int;
      set v=0;
      repeat
        insert into t values(v);
        set v=v+1;
        until v>=5
      end repeat;
    end;

查看删除过程

mysql中,用户可以使用show status语句或show create语句来查看存储过程和函数,也可以直接从系统的information_schema数据库中查询

show status查看存储过程:

mariadb [lyshark]> show procedure status like 'p%' \g
*************************** 1. row ************************
                  db: lyshark
                name: proc_1
                type: procedure
             definer: lyshark@%
            modified: 2018-12-28 21:16:26
             created: 2018-12-28 21:16:26
       security_type: definer
             comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  database collation: latin1_swedish_ci
12 rows in set (0.00 sec)

show create查看过程与函数:

mariadb [lyshark]> show create function lyshark.namezip;
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| function | sql_mode | create function                                                                                                                                  | character_set_client | collation_connection | database collation |
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| namezip  |          | create definer=`root`@`localhost` function `namezip`() returns char(50) charset latin1
return (select s_name from suppliers where s_call='4521') | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

select 查询存储过程:

mariadb [lyshark]> select name,created from mysql.proc where db="lyshark" and type="procedure";
+------------+---------------------+
| name       | created             |
+------------+---------------------+
| countproc  | 2018-12-28 04:19:58 |
| countproc1 | 2018-12-28 04:31:10 |
| proc       | 2018-12-28 03:47:30 |
| proc11111  | 2018-12-28 21:24:19 |
| proc3      | 2018-12-28 05:34:18 |
| proc4      | 2018-12-28 05:37:10 |
| proc8      | 2018-12-28 21:27:12 |
| proc_1     | 2018-12-28 07:20:08 |
| proc_111   | 2018-12-28 21:14:01 |
| proc_1111  | 2018-12-28 21:14:16 |
| proc_2     | 2018-12-28 07:27:32 |
| proc_3     | 2018-12-28 07:35:11 |
| proc_4     | 2018-12-28 21:15:40 |
| proc_444   | 2018-12-28 21:16:26 |
| wang       | 2018-12-28 04:19:07 |
| wang1      | 2018-12-28 04:25:30 |
+------------+---------------------+
16 rows in set (0.00 sec)

select 查询存储函数:

mariadb [lyshark]> select name,created from mysql.proc where db="lyshark" and type="function";
+------------+---------------------+
| name       | created             |
+------------+---------------------+
| countproc2 | 2018-12-28 05:04:28 |
| namezip    | 2018-12-28 05:16:35 |
+------------+---------------------+
2 rows in set (0.00 sec)

show语句的其他用法:

mariadb [lyshark]> show procedure status;
mariadb [lyshark]> show function status;

删除过程与函数:

drop procedure lyshark;
drop function countproc;

常用内置函数

函数表示对输入参数值返回一个具有特定关系的值,mysql提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数.通过对数据的处理,数据库功能可以变得更加强大,更加灵活地满足不同用户的需求.各类函数从功能方面主要分为以下几类：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等.下面我们就来介绍一些基础函数的使用.

数学函数

绝对值函数:abx(x)

mariadb [lyshark]> select abs(2) , abs(-10.5) ,abs(-100);
+--------+------------+-----------+
| abs(2) | abs(-10.5) | abs(-100) |
+--------+------------+-----------+
|      2 |       10.5 |       100 |
+--------+------------+-----------+
1 row in set (0.00 sec)

平方根函数:sqrt(x)

mariadb [lyshark]> select sqrt(10) , sqrt(50) , sqrt(-10);
+--------------------+--------------------+-----------+
| sqrt(10)           | sqrt(50)           | sqrt(-10) |
+--------------------+--------------------+-----------+
| 3.1622776601683795 | 7.0710678118654755 |      null |
+--------------------+--------------------+-----------+
1 row in set (0.00 sec)

获取整数的函数:ceil(x),ceiling(x),floor(x)

mariadb [lyshark]> select ceil(-3.35) , ceiling(3.35) , floor(3.35);
+-------------+---------------+-------------+
| ceil(-3.35) | ceiling(3.35) | floor(3.35) |
+-------------+---------------+-------------+
|          -3 |             4 |           3 |
+-------------+---------------+-------------+
1 row in set (0.00 sec)

获取随机数函数:rand(),rand(x)

mariadb [lyshark]> select rand() , rand(10);
+--------------------+--------------------+
| rand()             | rand(10)           |
+--------------------+--------------------+
| 0.5317976954689227 | 0.6570515219653505 |
+--------------------+--------------------+
1 row in set (0.00 sec)

其他函数:round(x),round(x,y),runcate(x,y)

mariadb [lyshark]> select round(-1.14) , round(1.14) , round(1.66);        #对数据进行四舍五入
+--------------+-------------+-------------+
| round(-1.14) | round(1.14) | round(1.66) |
+--------------+-------------+-------------+
|           -1 |           1 |           2 |
+--------------+-------------+-------------+
1 row in set (0.00 sec)
mariadb [lyshark]> select round(1.38,1) , round(232.38989,3);              #对指定小数点后几位进行四舍五入
+---------------+--------------------+
| round(1.38,1) | round(232.38989,3) |
+---------------+--------------------+
|           1.4 |            232.390 |
+---------------+--------------------+
1 row in set (0.00 sec)
mariadb [lyshark]> select truncate(1.31,1) , truncate(20.9999,2);           #截断操作，y参数为保留小数点后几位
+------------------+---------------------+
| truncate(1.31,1) | truncate(20.9999,2) |
+------------------+---------------------+
|              1.3 |               20.99 |
+------------------+---------------------+
1 row in set (0.00 sec)

字符串函数

计算字符串长度:char_length,字节长度:length(str)

mariadb [lyshark]> select char_length('date'),char_length('egg');
+---------------------+--------------------+
| char_length('date') | char_length('egg') |
+---------------------+--------------------+
|                   4 |                  3 |
+---------------------+--------------------+
1 row in set (0.00 sec)
mariadb [lyshark]> select length('date'),length('egg');
+----------------+---------------+
| length('date') | length('egg') |
+----------------+---------------+
|              4 |             3 |
+----------------+---------------+
1 row in set (0.00 sec)
mariadb [lyshark]>

合并字符串函数:concat()

mariadb [lyshark]> select concat('hello','lyshark'),concat('my',null,'sql');
+---------------------------+-------------------------+
| concat('hello','lyshark') | concat('my',null,'sql') |
+---------------------------+-------------------------+
| hellolyshark              | null                    |
+---------------------------+-------------------------+
1 row in set (0.00 sec)

字符串替换:insert(s1,len,s2) 将从quest替换,从第二个字符开始,一直替换三个.

mariadb [lyshark]> select insert('quest',2,3,'what') ;
+----------------------------+
| insert('quest',2,3,'what') |
+----------------------------+
| qwhatt                     |
+----------------------------+
1 row in set (0.00 sec)

大小写转换:lower(str),lcase(str) 将大写转换成小写

mariadb [lyshark]> select lower('lyshark'),lcase('well');
+------------------+---------------+
| lower('lyshark') | lcase('well') |
+------------------+---------------+
| lyshark          | well          |
+------------------+---------------+
1 row in set (0.00 sec)

大小写转换:upper(str),ucase(str) 将小写转换成大写

mariadb [lyshark]> select upper('black'),ucase('mkdirs');
+----------------+-----------------+
| upper('black') | ucase('mkdirs') |
+----------------+-----------------+
| black          | mkdirs          |
+----------------+-----------------+
1 row in set (0.00 sec)

比较字符串大小:strcmp(s1,s2) 相同返回0,s1<s2返回-1,其他返回1

mariadb [lyshark]> select strcmp('lyshark','lyshark'),strcmp('txt','ttxt'),strcmp('ttxt','txt');
+-----------------------------+----------------------+----------------------+
| strcmp('lyshark','lyshark') | strcmp('txt','ttxt') | strcmp('ttxt','txt') |
+-----------------------------+----------------------+----------------------+
|                           0 |                    1 |                   -1 |
+-----------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

字符串逆序:reverse(str)

mariadb [lyshark]> select reverse('lyshark');
+--------------------+
| reverse('lyshark') |
+--------------------+
| krahsyl            |
+--------------------+
1 row in set (0.00 sec)

日期时间函数

获取当前日期:curdate()

mariadb [lyshark]> select curdate(),current_date(),curdate()+1;
+------------+----------------+-------------+
| curdate()  | current_date() | curdate()+1 |
+------------+----------------+-------------+
| 2018-12-24 | 2018-12-24     |    20181225 |
+------------+----------------+-------------+
1 row in set (0.00 sec)

获取当前时间:curtime()

mariadb [lyshark]> select curtime(),current_time(),curtime()+1;
+-----------+----------------+-------------+
| curtime() | current_time() | curtime()+1 |
+-----------+----------------+-------------+
| 20:49:04  | 20:49:04       |      204905 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

获取日期与时间:all

mariadb [lyshark]> select current_timestamp(),localtime(),now(),sysdate();
+---------------------+---------------------+---------------------+---------------------+
| current_timestamp() | localtime()         | now()               | sysdate()           |
+---------------------+---------------------+---------------------+---------------------+
| 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 | 2018-12-24 20:50:19 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

获取unix时间戳:unix_timestamp()

mariadb [lyshark]> select unix_timestamp(),unix_timestamp(now()),now();
+------------------+-----------------------+---------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               |
+------------------+-----------------------+---------------------+
|       1545702698 |            1545702698 | 2018-12-24 20:51:38 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

获取年份:year()

mariadb [lyshark]> select year('18-10-10');
+------------------+
| year('18-10-10') |
+------------------+
|             2018 |
+------------------+
1 row in set (0.00 sec)

获取月份:month()

mariadb [lyshark]> select month('2018-10-05');
+---------------------+
| month('2018-10-05') |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

获取星期:dayname()

mariadb [lyshark]> select dayname('2018-10-25');
+-----------------------+
| dayname('2018-10-25') |
+-----------------------+
| thursday              |
+-----------------------+
1 row in set (0.00 sec)

获取天:dayofyear()

mariadb [lyshark]> select dayofyear('2018-10-10');
+-------------------------+
| dayofyear('2018-10-10') |
+-------------------------+
|                     283 |
+-------------------------+
1 row in set (0.00 sec)

条件判断函数

if(expr,v1,v2): 如果表达式expr是true(expr<>0 and expr<> null),则if()函数返回为v1,否则返回v2,if()语句返回值为数字或字符串,具体情况视其所在语境而定.

mariadb [lyshark]> select if(1>2,2,3),
    -> if(1<2,'yes','no'),
    -> if(strcmp('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| if(1>2,2,3) | if(1<2,'yes','no') | if(strcmp('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
|           3 | yes                | no                                    |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)

ifnull(v1,v2): 假如v1不为null,则ifnull()返回值为v1,否则其返回值为v2.

mariadb [lyshark]> select ifnull(1,2) , ifnull(null,10) , ifnull(1/0,'wrong');
+-------------+-----------------+---------------------+
| ifnull(1,2) | ifnull(null,10) | ifnull(1/0,'wrong') |
+-------------+-----------------+---------------------+
|           1 |              10 | wrong               |
+-------------+-----------------+---------------------+
1 row in set (0.00 sec)

case expr when v1 then r1 [when v2 then r2][else rn] end: 如果expr等价于某个vn,则返回对应位置then后面的结果.如果所用值都不相等,则返回else后面的rn.

mariadb [lyshark]> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mariadb [lyshark]> select case when 1<0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1<0 then 'true' else 'false' end |
+--------------------------------------------+
| false                                      |
+--------------------------------------------+
1 row in set (0.00 sec)

系统信息函数

获取版本:version()

mariadb [lyshark]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-mariadb |
+----------------+
1 row in set (0.04 sec)

查看当前用户连接数:connection_id()

mariadb [lyshark]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

输出当前用户的连接信息:show processlist

mariadb [lyshark]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| id | user | host      | db      | command | time | state | info             | progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
|  2 | root | localhost | lyshark | query   |    0 | null  | show processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
1 row in set (0.01 sec)
mariadb [lyshark]>
mariadb [lyshark]> show full processlist;
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
| id | user | host      | db      | command | time | state | info                  | progress |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
|  2 | root | localhost | lyshark | query   |    0 | null  | show full processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+-----------------------+----------+
1 row in set (0.00 sec)

返回当前使用的数据库:schema()

mariadb [lyshark]> select database(),schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| lyshark    | lyshark  |
+------------+----------+
1 row in set (0.00 sec)

加密解密函数

加密函数:password(str)

mariadb [lyshark]> select password('newpass');
+-------------------------------------------+
| password('newpass')                       |
+-------------------------------------------+
| *d8decec305209eefec43008e1d420e1aa06b19e0 |
+-------------------------------------------+
1 row in set (0.00 sec)

加密函数:md5(str)

mariadb [lyshark]> select md5('mypass');
+----------------------------------+
| md5('mypass')                    |
+----------------------------------+
| a029d0df84eb5549c641e04a9ef389e5 |
+----------------------------------+
1 row in set (0.00 sec)

加密函数:encode(str,pswd_str)

mariadb [lyshark]> select encode('secret','cry'),length(encode('secret','cry'));
+------------------------+--------------------------------+
| encode('secret','cry') | length(encode('secret','cry')) |
+------------------------+--------------------------------+
| ▒h▒ ▒                  |                              6 |
+------------------------+--------------------------------+
1 row in set (0.00 sec)

解密函数:decode(crypt_str,pswd_str)

mariadb [lyshark]> select decode(encode('secret','cry'),'cry');
+--------------------------------------+
| decode(encode('secret','cry'),'cry') |
+--------------------------------------+
| secret                               |
+--------------------------------------+
1 row in set (0.00 sec)

其他通用函数

格式化函数:ormat(x,n)

mariadb [lyshark]> select format(123.123,2);
+-------------------+
| format(123.123,2) |
+-------------------+
| 123.12            |
+-------------------+
1 row in set (0.00 sec)

ip地址转为数字:inet_aton(ipaddr)

mariadb [lyshark]> select inet_aton("192.168.1.1");
+--------------------------+
| inet_aton("192.168.1.1") |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

数字转为ip地址:inet_ntoa

mariadb [lyshark]> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)