MySQL的系统信息函数和加密解密函数(十四)

我看到了那天的夕阳，美得如此骄艳，我便决定，追寻夕阳，拼尽余生。

上一章简单介绍了 MySQL的条件判断函数(十三),如果没有看过,​​请观看上一章​​

一.一 MySQL的其他函数

MySQL除了常见的，数学函数，字符串函数，日期/时间函数，还有一些其他的函数，如 系统信息函数，加密解密函数等。 现将其他的函数，统一进行讲解。

讲解的函数主要有:

• 系统信息函数
• 加密/解密函数
• 进制转换函数
• ip与数字转换函数
• 类型转换函数
• 格式化函数

其中，系统信息函数有:

函数	作用	举例	举例结果
versison()	查询版本号	select version()	5.7.13
connection_id()	查询连接id	select connection_id()	8
database()	查询目前所使用的数据库	select database()	yuejl
schema()	查询数据库	select schema()	yuejl
user()	查询登录的用户	select user()	root
current_user()	查询登录的用户	select current_user()	root
system_user()	查询登录的用户	select system_user()	root
session_user()	查询登录的用户	select session_user()	root
charset(str)	获取字符串的字符集	select charset(‘abc123’)	gbk
collation(str)	获取字符串的排序方式	select collation(‘abc123’)	gbk_chinese_ci
last_insert_id()	获取最后生成的id值	select last_insert_id()	1

加密解密函数有:

函数	作用	举例	举例结果
password(str)	加密字符串str	select password(‘abc123’)	*6691484EA6B50DDDE1926A220DA01FA9E575C18A
md5(str)	md5方式加密	select md5(‘abc123’)	e99a18c428cb38d5f260853678922e03
encode(str,ps_str)	固定字符串加密	select encode(‘abc123’,‘A’)	<黟‹
decode(str,ps_str)	固定字符串解密	select decode(encode(‘abc123’,‘A’),‘A’)	abc123

其他函数:

函数	作用	举例	举例结果
format(num,n)	格式化数字	select format(1234.234.2)	1,234.23
conv(num,from_base,to_base)	select conv(‘F’,16,10)	15
bin(num)	将十进制转换二进制	bin(7)	111
oct(num)	将十进制转换成八进制	oct(10)	12
hex(num)	将十进制转换成十六进制	hex(15)	F
inet_aton(ip字符串)	将ip地址转换成数字	select inet_aton(‘127.0.0.1’)	2130706433
inet_ntoa(数字)	将数字转换成ip地址	select inet_ntoa(2130706433)	127.0.0.1
convert(str using 新的编码)	转换字符串的编码	select charset(convert(‘abc’ using utf8))	utf8
cast(x as type)	改变数据类型	select cast(10 as char(4))	10
convert(x ,type)	改变数据类型	select convert(10,char(4))	10

二. 系统信息函数

二.一 查看MySQL的版本号 version()

select version();

老蝴蝶所用的版本是 5.7.13版本。

二.二 查询连接id connection_id()

select connection_id();

查询出来的是连接的 id.

每一个用户连接数据库时，都会有一个唯一id,进行相应的区分。 注意，这个id 并不是从1 按照从小到大排列的。 会根据登录的次数，而有不同。

可以通过 processlist 命令来详细的查询。

show processlist;

processlist 命令不仅可以查询当前有哪些线程在运行， 当前的所有的连接数，还可以显示当前的连接的状态，帮助识别有问题的查询语句。

其中，如果是root 管理员用户的话，可以查询全部的用户的信息。 如果是普通的用户的话，只能显示该用户的信息。

如果用户过多的话， 可以使用 show full processlist ; 命令来查询全部。 show processlist 默认只列出前100条记录。

show full processlist;

各个列的含义如下:

1. id 列 用户登录Mysql时，系统自动分配的 connection id, 即 select connection_id() 函数的返回值。
2. user 列。 显示当前登录的用户。 如果是root管理员，就显示root.
   如果不是root,就显示用户权限范围内的sql语句。
3. host 列 显示这条语句是从哪个ip的哪个端口发出的。 按照 ip:端口 的格式展示。 端口，并不是mysql的端口3306.
4. db 列 目前这个进程连接的是哪个数据库。 如果没有使用数据库，显示null
5. Command 列 当前连接执行的命令，取值为 休眠 (Sleep), 查询(Query),连接(Connect)
6. Time 列显示这个状态持续的时间，单位是秒
7. State 列 显示当前连接的sql语句的状态。
8. info 列 显示这个SQL语句，是判断问题语句的一个重要依据。

二.三 查询当前使用的数据库 database() 和 schema()

用

use yuejl();

数据库。

select database();

或者:

select schema();

二.四 获取用户名 user();

可以用以下四个函数

• user()
• current_user()
• system_user()
• session_user()

一般情况下，这四个函数查询出来的值是相同的。

select user(),current_user(),system_user(),session_user();

返回的是，连接服务器的用户名及当前的主机。 root为用户名，localhost为主机。

二.五 获取字符串的字符集 charset(str)

可以获取字符串的字符集， 常见的为 utf8, gbk, 默认的为 latin1。

老蝴蝶默认的是gbk

select charset('岳泽霖'),charset(convert('岳泽霖' using gbk)),charset(convert('岳泽霖' using latin1));

其中， convert(str using 新编码方式) 是 改变字符串的编码方式。

常常在创建数据库时，指定数据库的字符集。

二.六 字符串的排序方式 collation(str)

获取字符串的排列方式。 一般返回 编码方式_general_ci

select collation('两个蝴蝶飞'),collation(convert('两个蝴蝶飞' using utf8)),
  collation(convert('两个蝴蝶飞' using latin1));

常常在创建数据库时，指定数据库的排序方式。

二.七 获取最后一个生成的id 值 last_insert_id()

常常用于插入数据之前，获取最大的自动增长的id() 值。 如MyBatis的插入id值。

1 .为了演示，先创建一个数据表 teacher， 使用yuejl数据库。 是自动增长的。

user yuejl;
  
   create table teacher(
      id int(11) primary key auto_increment,
      name varchar(20)
     );

2 . 先插入第一条数据，再查询

insert into teacher(name) values('两个蝴蝶飞');

插入成功， 查询id

select last_insert_id();

查询出id 值为1.

3 . 再次插入一条数据，然后查询

insert into teacher(name) values('岳泽霖');

再次查询

select last_insert_id();

4 . 批量插入时的增长

insert into teacher(name) values ('a'),('b'),('c');

再次查询

select last_insert_id();

查询后，并不是5， 而是3.

在批量插入之前，下一个值是3， 获取的是插入 name=a 记录时的id.

即如果插入多行记录时，只返回插入第一行时所返回的值。

5 . 修改时 不变。

update teacher set name='修改后的值' where id=1;

再查询

select last_insert_id();

6 . last_insert_id() 的值只与最近查询的那个表有关。 如果此时再有一个自动增长的 user 表，
往teacher表再添加一条记录，那么 last_insert_id() 变成了4.
这时，再往 user() 表插入一条数据，
那么这个时候，last_insert_id() 就与 user 表相关了，变成了1.

三. 加解密函数

三.一 password(str) 加密

是单向加密，且不可逆的。 常常用于MySQL服务器加密。

如果加密的字符串为null,那么将返回 空字符串.

select password('abc123'),password('两个蝴蝶飞'),password(null);

三.二 md5(str) MD5方式加密

采用Md5的方式进行加密

select md5('abc123'),md5('123456');

三.三 encode(str,ps_str) 固定字符串加密

以 ps_str 字符串为密钥，进行加密 str字符串。 可以让用户自定义加密方式。

select encode('abc123','A'),encode('abc123','ABC');

三.四 decode(str,ps_str) 固定字符串解密

以ps_str字符串为密钥，进行解密 str字符串。 与encode(str,ps_str)互为反函数

select decode(encode('abc123','A'),'A'),decode(encode('abc123','ABC'),'ABC');

可以正好返回以前要加密的字符串。

四. 其他函数

四.一 格式化数字 format(num,n)

格式化数字 num, 返回 n位小数， 四舍五入的方式进行获取。

select format(1234.234,2),format(1234.236,2),format(12.23423,1),format(123.22,0),format(123.23,-1);

发现， -1 值不起作用。 即 n>=0.

要想使 -1起作用，可以用 数学函数里面的 round(x,-1) 函数进行处理。

四.二 conv(num,from_base,to_base) 进制转换

其中，最小为二进制。

select conv('F',16,10),conv(7,10,2),conv(9,10,8),conv(1010,2,10);

四.三 bin(num) 将十进制数字转换成二进制

select bin(7),bin(4),bin(10);

四.四 oct(num) 将十进制数字转换成八进制

select oct(7),oct(10),oct(15);

四.五 hex(num) 将十进制数字转换成十六进制

select hex(9),hex(10),hex(15);

四.六 inet_aton(ip字符串) ip地址转换成数字

其中, inet 为internet 的简写。

aton 为 address to num.

select inet_aton('127.0.0.1'),inet_aton('10.1.1.121');

值分别为 2130706433，和 167838073

为 数字结果=第一个值256^3 +第二个值256^2 + 第三个值*256^1+ 第四个值。

四.七 inet_ntoa(数字) 将数字转换成ip地址

与 inet_aton(ip字符串) 互为反函数。

ntoa 为 num to address.

select inet_ntoa(2130706433),inet_ntoa(167838073);

四.八 convert(str using 新的编码) 转换字符串的编码

可以改变字符串的编码

要借助一下 charset(str) 函数进行验证。

select charset('岳泽霖'),charset(convert('岳泽霖' using gbk)),
  charset(convert('岳泽霖' using latin1));

四.九 cast(x as type),convert(x,type) 改变数据类型

可以改变数据类型，但前堤是两个数据类型可以相互转换。

其中，转换成类型 type 可以的取值有:

type取值	表示意义
char(n)	字符串型
date	日期型
time	时间型
datetime	日期和时间型
decimal	float浮点型
signed	int类型

select cast(10 as char(4)),cast('1234' as signed),
  convert(10,char(4)),convert('1234',signed);

谢谢!!!