MySQL 条件判断和常用的函数

MySQL条件判断

IF条件函数

1	IF(expr1,expr2,expr3)

当 expr1 为真时，返回 expr2，否则返回 expr3

IFNULL函数

1	IFNULL(expr1,expr2)

当 expr1 不为 NULL 时返回 expr1，否则返回 expr2

CASE语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END

例如

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`