MySQL 条件判断和常用的函数

MySQL条件判断

IF条件函数

1
IF(expr1,expr2,expr3)

expr1 为真时,返回 expr2,否则返回 expr3

IFNULL函数

1
IFNULL(expr1,expr2)

expr1 不为 NULL 时返回 expr1,否则返回 expr2

CASE语句

1
2
3
4
5
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END

例如

1
2
3
4
5
6
7
8
SELECT 
(
CASE age
WHEN < 60 THEN '60岁以下'
WHEN < 80 THEN '80岁以下'
ELSE '80岁及以上'
END
) as age_range FROM table_a

查询年龄,当age < 60显示是60岁以下age < 80显示是80岁以下,剩下的是80岁及以上

常用系统函数

VERSION():查询版本信息

1
2
SELECT VERSION();
// 5.7.25-log

CONNECTION_ID():返回MySql服务器当前链接的次数,每个链接都有各自唯一的ID

1
2
SELECT CONNECTION_ID();
// 15301000

SHOW PROCESSLIST:输出显示有哪些线程在运行,且各自的运行状态,若是root用户则显示所有的用户状态,若是普通用户则只显示自己的状态

1
SHOW PROCESSLIST;

SHOW PROCESSLIST最多显示100条,若想全部显示,则用下面这条命令:

1
SHOW FULL PROCESSLIST;
字段名称 字段含义
Id 登录MySQL时,系统分配的connection_id
User 当前用户,如果不是root,这个命令则只显示用户权限范围内的语句
Host 显示这个语句是从哪个ip的哪个端口上发出的,可以用来追踪出现问题的用户
db 显示这个进程目前连接的是哪个数据库
Command 显示当前链接数据库执行的命令,一般取值为休眠(Sleep),查询(Query),和连接(Connect)
Time 显示这个状态持续的时间,单位是秒
State 显示使用当前连接的sql语句状态,很重要的列后续会有所有状态的描述,State只是语句执行中的某一个状态,一条sql,以查询为例,可能要经过Copying to tmp table,Sorting result,Sending data等状态才能完成
Info 显示这个sql语句,是判断问题语句的一个重要依据

LAST_INSERT_ID():自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值,意思就是,如果现在有id为1,2的数据,然后一条insert插入3调数据,返回的id是3而不是其他数值

1
SELECT LAST_INSERT_ID();

加密函数

PASSWORD(str):该函数在MySQL服务器鉴定系统中使用,不应将它用在个人的应用中,它的加密是单向的

1
2
SELECT PASSWORD('123');
// *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

MD5(str):参见其他编程语言md5

1
2
SELECT MD5('123');
// 202cb962ac59075b964b07152d234b70

ENCODE(str,pass_str):使用pass_str作为密码加密str

加密后可能是乱码,不建议使用

DECODE(crypt_str,pass_str):使用pass_str作为密码,解密crypt_str,crypt_str是ENCODE()返回的结果

转换函数

CONV(N,from_base,to_base):不同进制转换

INET_ATON(expr):IP地址转换为数字

1
2
SELECT INET_ATON('192.168.0.1');
// 3232235521

INET_NTOA(expr):数字转换为IP地址

1
2
SELECT INET_NTOA(3232235521);
// 192.168.0.1

加锁函数与解锁函数

GET_LOCK(str, timeout) 设法使用字符串 str 给定的名字得到一个锁,超时为 timeout 秒。若成功得到锁,返回1,若操作超时返回0,若发生错误,返回null。假如有一个用 GET_LOCK()得到的锁,当执行RELEASE_LOCK()或断开连接(正常或非正常)时,这个锁就会解除。
RELEASE_LOCK(str)解开被 GET_LOCK()获取的,用字符串 str 所命令的锁。若锁被解开,则返回1;若该线程尚未创建锁,则返回0(此时锁没有被解开);若命名的锁不存在,则返回NULL。若该锁从未被GET_LOCK()的调用获取,或已经被提前解开,则该锁不存在。
IS_FREE_LOCK(str)检查名为str的锁是否可以使用。若可以使用返回1,若正在被使用返回0,出现错误返回0.
IS_USED_LOCK(str)检查名为str的锁是否正在被使用(被封锁),若正在被使用则返回使用该锁的连接表示(connectionId),否则返回NULL

改变数据类型的函数

CAST(expr as type),convert(expr, type),可以用来截取字符串

例如:

1
2
SELECT cast(`title` AS char(4)) AS `title` FROM `order`; 
SELECT convert(`title`, char(4)) AS `title` FROM `order`
坚持原创技术分享,您的支持将鼓励我继续创作!