Solo  当前访客：5 开始使用
极客说
记录精彩的程序人生

MySQL函数（五）

作者：wangdj | 更新日期: 2024-11-10 00:26 | 浏览数： 0 | 评论数： 0

标签： , , ,

MySQL函数

MySQL 函数简介

  • MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。各类函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

数学函数

  • 数学函数主要用来处理数值数据，主要的数学函数有绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在有错误产生时，数学函数将会返回空值NULL。

绝对值函数ABS(x)和返回圆周率的功能PI()

  1. ABS(x)

    ABS(x)返回X的绝对值。

  2. PI()

    PI()返回圆周率的值。默认的显示小数位数是6位。

平方根函数SQRT(x)和求余函数MOD(x,y)

  1. SQRT(x)

    SQRT(x)返回非负数x的二次方根。当x为负数时，返回结果为NULL。

  2. MOD(x,y)

    MOD(x,y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，返回除法运算后的精确余数。

获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

  1. CEIL(x)和CEILING(x)

    CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

  2. FLOOR(x)

    FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。

获取随机数的函数RAND()和RAND(x)

  • RAND(x)返回一个随机浮点值v，范围在0到1之间 （0 <= v <= 1.0）。若已指定一个整数参数x，则它被用作种子值，用来产生重复序列。
  • 不带参数的RAND() 每次产生的随机数值是不同的。而当RAND(x) 的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。

函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

  1. ROUND(x)

    ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。

  2. ROUND(x,y)

    ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

  3. TRUNCATE(x,y)

    TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y为负数，则截去（归零） x 小数点左起第y位 开始后面所有低位的值。

提示：ROUND(x,y) 函数在截取值的时候会四舍五入，而 TRUNCATE(x,y) 直接截取值，并不进行四舍五入。

符号函数SIGN(x)

  • SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为 -1、0或1。

幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

  1. POW(x,y)和POWER(x,y)

    POW(x,y)或者POWER(x,y)函数返回x的y次乘方结果值。

  2. EXP(x)

    EXP(x)返回e的x次乘方后的值。

对数运算函数LOG(x)和LOG10(x)

  1. LOG(x)

    LOG(x)返回x的自然对数，x相对于基数e的对数。对数定义域不能为负数，若x为负数，则返回结果NULL。

  2. LOG10(x)

    LOG10(x)返回x相对于基数10的对数。

角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

  1. RADIANS(x)

    RADIANS(x)将参数x由角度转化为弧度。

  2. DEGREES(x)

    DEGREES(x)将参数x由弧度转化为角度。

正弦函数SIN(x)和反正弦函数ASIN(x)

  1. SIN(x)

    SIN(x)返回x的正弦值，其中x为弧度值。

  2. ASIN(x)

    ASIN(x)返回x的反正弦，即正弦为x的值。若x不在 -1到1 的范围之内，则返回NULL。

余弦函数COS(x)和反余弦函数ACOS(x)

  1. COS(x)

    COS(x)返回x的余弦值，其中x为弧度值。

  2. ACOS(x)

    ASIN(x)返回x的反余弦，即余弦为x的值。若x不在 -1到1 的范围之内，则返回NULL。

正切函数、反正切函数和余切函数

  1. 正切函数TAN(x)

    TAN(x)返回x的正切，其中x为给定的弧度值。

  2. 反正切函数ATAN(x)

    ATAN(x)返回x的反正切，即正切为x的值。

  3. 余切函数COT(x)

    COT(x)返回x的余切。

字符串函数

  • 字符串函数主要用来处理数据库中的字符串数据，MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

计算字符串字符数和字符串长度的函数

  1. CHAR_LENGTH(str)

    CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

  2. LENGTH(str)

    LENGTH(str)返回值为字符串的字节长度，使用utf8（UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。

合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)

  1. CONCAT(s1,s2,...)

    CONCAT(s1,s2,...)返回结果为连接参数产生的字符串，或许有一个或多个参数。任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

  2. CONCAT_WS(x,s1,s2,...)

    在CONCAT_WS(x,s1,s2,...)中，CONCAT_WS代表CONCAT With Separator，是CONCAT() 的特殊形式；第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。 如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

替换字符串的函数INSERT(s1,x,len,s2)

  • INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，那么返回值为原始字符串。如果len的长度大于原字符串长度，就从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。例如 INSERT('opd',1,2,'abc') 执行后返回字符串 'abcd'。

字母大小写转换函数

  1. LOWER(str)和LCASE(str)

    LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。

  2. UPPER(str)或者UCASE(str)

    UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。

获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

  1. LEFT(s,n)

    LEFT(s,n)返回字符串s开始的最左边n个字符。

  2. RIGHT(s,n)

    RIGHT(s,n)返回字符串s开始的最右边n个字符。

填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

  1. LPAD(s1,len,s2)

    LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度。若s1的长度大于len，则返回值被缩短至len字符长度。

  2. RPAD(s1,len,s2)

    RPAD(s1,len,s2)返回字符串s1，其右边由字符串s2填补到len字符长度。若s1的长度大于len，则返回值被缩短至len字符长度。

删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

  1. LTRIM(s)

    LTRIM(s)返回字符串s，字符串左侧空格字符被删除。

  2. RTRIM(s)

    RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

  3. TRIM(s)

    TRIM(s)删除字符串s两侧的空格。

删除特定字符串的函数TRIM(s1 FROM s)

  • TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。

重复生成字符串的函数REPEAT(s,n)

  • REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n小于等于0，则返回一个空字符串。若s或n为NULL，则返回NULL。

空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

  1. SPACE(n)

    SPACE(n)返回一个由n个空格组成的字符串。

  2. REPLACE(s,s1,s2)

    REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

比较字符串大小的函数STRCMP(s1,s2)

  • 在STRCMP(s1,s2)中，若所有的字符串均相同，则返回0；根据当前分类次序，若第一个参数小于第二个，则返回 -1，其他情况返回1。

获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

  1. SUBSTRING(s,n,len)

    SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串，起始于位置n。也可能对n使用一个负值，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

  2. MID(s,n,len)

    MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

  • 提示：如果对len使用的是一个小于1的值，则结果始终为空字符串。

匹配子串开始位置的函数

  • LOCATE(str1,str)、POSITION(str1 IN str) 和 INSTR(str,str1) 三个函数作用相同，返回子字符串str1在字符串str中的开始位置。若匹配失败则返回结果0。（提示：注意INSTR函数参数位置与前两个函数不同）

字符串逆序的函数REVERSE(s)

  • REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

返回指定位置的字符串的函数

  • 在ELT(N，字符串1，字符串2，字符串3，...，字符串N) 中，若N=1，则返回值为字符串1；若N=2，则返回值为字符串2；以此类推。若N小于1或大于参数的数目，则返回值为NULL。

返回指定字符串位置的函数FIELD(s,s1,s2,...)

  • FIELD(s,s1,s2,...) 返回字符串s在列表（s1，s2，...） 中 第一次 出现的位置，在找不到s的情况下，返回值为0。如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

返回子串位置的函数FIND_IN_SET(s1,s2)

  • FIND_IN_SET(s1,s2) 返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号 ’,‘ 分开的字符串组成的列表。 若 s1不在s2中或s2为空字符串，则返回值为0。若任意一个参数为NULL，则返回值为NULL。第一个参数包含一个逗号 ',' 时这个函数将无法正常运行。

选取字符串的函数MAKE_SET(x,s1,s2...)

  • MAKE_SET(x,s1,s2...) 返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特0001，s2对应比特0010，以此类推。（s1，s2，...）中的NULL值不会被添加到结果中。例如，MAKE_SET(0101,'a','b','c') 的执行结果为 'a,c'。

日期和时间函数

  • 日期和时间函数主要用来处理日期和时间值。一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分。许多日期函数可以同时接受数字和字符串类型的两种参数。

获取当前日期的函数和获取当前时间的函数

  1. CURDATE()和CURRENT_DATE()

    • CURDATE( )和CURRENT_DATE( )函数函数作用相同，将当前日期按照 ‘YYYY-MM-DD’ 或 YYYYMMDD格式的值返回，具体格式根据函数是在字符串还是数字语境中而定。
    • “CURDATE( )+0” 将当前日期值转换为数值型。

  2. CURTIME()和CURRENT_TIME()

    • CURTIME( )和CURRENT_TIME( )函数函数作用相同，将当前日期按照 ‘HH:MM:SS’ 或 HHMMSS 格式的值返回，具体格式根据函数是在字符串还是数字语境中而定。
    • “CURTIME( )+0” 将当前日期值转换为数值型。

获取当前日期和时间的函数

  • CURRENT_TIMESTAMP( )、LOCALTIME( )、NOW( )和SYSDATE( ) 4个函数的作用相同，均返回当前日期和时间值，格式为 ‘YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS，具体格式根据函数是在字符串还是数字语境中而定。

UNIX时间戳函数

  1. UNIX_TIMESTAMP(date)

    UNIX_TIMESTAMP(date) 若无参数调用，则返回一个UNIX时间戳（‘1970-01-01 00:00:00’ GMT之后的秒数）作为无符号整数。其中GMT（Greenwich mean time）为格林尼治标准时间。若用date来调用UNIX_TIMESTAMP( )，则会将参数值以‘1970-01-01 00:00:00’ GMT后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD 格式的数字。

  2. FROM_UNIXTIME(date)

    FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间。输入语句如下：

    FROM_UNIXTIME('1614498353')

返回UTC日期的函数和返回UTC时间的函数

  1. UTC_DATE()

    UTC_DATE( )函数返回当前UTC（世界标准时间）日期值，其格式为 ‘YYYY-MM-DD’ 或 YYYYMMDD，具体格式取决于函数是用在字符串还是数字语境中。

  2. UTC_TIME()

    UTC_TIME( )函数返回当前UTC（世界标准时间）时间值，其格式为 ‘HH:MM:SS’ 或 HHMMSS，具体格式取决于函数是用在字符串还是数字语境中。

获取月份的函数MONTH(date)和MONTHNAME(date)

  1. MONTH(date)

    MONTH(date)函数返回date对应的月份，值为1~12。

  2. MONTHNAME(date)

    MONTHNAME(date)函数返回日期date对应月份的英文全名。

获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

  1. DAYNAME(d)

    DAYNAME(d) 函数返回d对应的工作日的英文名称，例如Sunday、Monday等。

  2. DAYOFWEEK(d)

    DAYOFWEEK(d) 函数返回d对应的一周中的索引（位置）：1表示周日，2表示周一，......，7表示周六。

  3. WEEKDAY(d)

    WEEKDAY(d) 返回d对应的工作日索引：0表示周一，1表示周二，......，6表示周日。

获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

  1. WEEK(d)

    WEEK(d) 计算日期d是一年中的第几周。WEEK(d,Mode) 的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为0~53或1~53。若Mode参数被省略，则使用default_week_format系统自变量的值。MySQL中该值默认为0。WEEK函数中Mode参数取值如下表所示。

    Mode一周的第一天范围Week 1 为第一周
    0周日0~53本年度中有一个周日
    1周一0~53本年度中有3天以上
    2周日1~53本年度中有一个周日
    3周一1~53本年度中有3天以上
    4周日0~53本年度中有3天以上
    5周一0~53本年度中有一个周一
    6周日1~53本年度中有3天以上
    7周一1~53本年度中有一个周一

  2. WEEKOFYEAR(d)

    WEEKOFYEAR(d) 计算某天位于一年中的第几周，范围是1~53，相当于WEEK(d,3)。

获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

  1. DAYOFYEAR(d)

    DAYOFYEAR(d) 函数返回d是一年中的第几天，范围是1~366。

  2. DAYOFMONTH(d)

    DAYOFMONTH(d) 函数返回d是一个月中的第几天，范围是1~31。

获取年份、季度、小时、分钟和秒数的函数

  1. YEAR(date)

    YEAR(date) 返回date对应的年份，范围是1970~2069。

    提示：‘ 0~69 ’ 转换为 ’ 2000~2069 ‘ ，’ 70~99 ‘ 转换为 ’ 1970~1999 ‘。

  2. QUARTER(date)

    QUARTER(date) 返回date对应的一年中的季度值，范围是1~4。

  3. MINUTE(time)

    MINUTE(time) 返回time对应的分钟数，范围是0~59。

  4. SECOND(time)

    SECOND(time) 返回time对应的秒数，范围是0~59。

获取日期的指定值的函数EXTRACT(type FROM date)

  • EXTRACT(type FROM date) 函数所使用的时间间隔类型说明符与DATE_ADD( ) 或DATE_SUB( ) 的相同，但它从日期中提取一部分，而不是执行日期运算。语法格式如下所示。

    EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03')

    上述SQL语句为获取年和月份。执行后返回结果为201107。

时间和秒钟转换的函数

  1. TIME_TO_SEC(time)

    TIME_TO_SEC(time) 返回已转化为秒的time参数。转换公式为：小时x3600+分钟x60+秒。

  2. SEC_TO_TIME(seconds)

    SEC_TO_TIME(seconds) 返回被转化为小时、分钟和秒数的seconds参数值，其格式为 ’HH:MM:SS‘ 或 HHMMSS，具体格式根据该函数是用在字符串还是数字语境中而定。

计算日期和时间的函数

  • 计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME() 和 DATE_DIFF()。

  • 在DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type) 中，date是一个DATETIME或DATE值，用来指定起始时间；expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值，对于负值的时间的间隔，它可以以一个负号 ’-‘ 开头；type为关键词，指定表达式被解释的方式。

  • MySQL中计算日期和时间的格式如下表所示：

    type值预期的expr格式
    MICROSECONDMICROSECONDS
    SECONDSECONDS
    MINUTEMINUTES
    HOURHOURS
    DAYDAYS
    WEEKWEEKS
    MONTHMONTHS
    QUARTERQUARTERS
    YEARYEARS
    SECOND_MICROSECOND'SECONDS.MICROSECONDS'
    MINUTE_MICROSECOND'MINUTES.MICROSECONDS'
    MINUTE_SECOND'MINUTES:SECONDS'
    HOUR_MICROSECOND'HOURS.MICROSECONDS'
    HOUR_SECOND'HOURS:MINUTES:SECONDS'
    HOUR_MINUTE'HOURS:MINUTES'
    DAY_MICROSECOND'DAYS.MICROSECONDS'
    DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
    DAY_MINUTE'DAYS HOURS:MINUTES'
    DAY_HOUR'DAYS HOURS'
    YEAR_MONTH'YEARS-MONTHS'

    若date参数是一个DATE值，计算只会包括YEAR、MONTH和DAY部分（没有时间部分），其结果是一个DATE值；否则，结果将是一个DATETIME值。

提示：DATE_ADD和DATE_SUB在指定修改的时间段，也可以指定负值，负值代表相减，即返回以前的日期和时间。

  • ADDTIME(date,expr) 函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。
  • SUBTIME(date,expr) 函数将date减去expr值，并返回修改后的值。date是一个日期或者日期时间表达式，expr是一个时间表达式。
  • DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time表达式。

将日期和时间格式化的函数

  1. DATE_FORMAT()

    DATE_FORMAT(date,format) 根据format指定的格式显示date值。主要format格式如下表所示。

    说明符说明
    %a工作日的缩写名称（Sun...Sat）
    %b月份的缩写名称（Jan...Dec）
    %c月份，数字形式（0...12）
    %D带有英语后缀的该月日期（0th，1st，2nd，3rd，...）
    %d该月日期，数字形式（00...31）
    %e该月日期，数字形式（0...31）
    %f微秒（000000...999999）
    %H以2位数表示24小时（00...23）
    %h，%I以2位数表示12小时（01...12）
    %i分钟，数字形式（00...59)
    %j一年中的天数（001...366）
    %k以24（0...23）小时表示时间
    %l以12（1...12）小时表示时间
    %M月份名称（January...December）
    %m月份，数字形式（00...12）
    %p上午（AM）或下午（PM）
    %r时间，12小时制（小时hh:分钟mm:秒数ss 后加AM或PM）
    %S，%s以2位数形式表示秒（00...59）
    %T时间，24小时制（小时hh:分钟mm:秒数ss）
    %U周（00...53），其中周日为每周的第一天
    %u周（00...53），其中周一为每周的第一天
    %V周（01...53），其中周日为每周的第一天；和%X同时使用
    %v周（01...53），其中周日为每周的第一天；和%x同时使用
    %W工作日名称（Sunday...Saturday）
    %w一周中的每日（0=周日...6=周六）
    %X该周的年份，其中周日为每周的第一天；数字形式，4位数；和%V同时使用
    %x该周的年份，其中周一为每周的第一天；数字形式，4位数；和%v同时使用
    %Y4位数形式表示年份
    %y2位数形式表示年份
    %%‘%’ 文字字符

  2. TIME_FORMAT()

    TIME_FORMAT(time,format) 根据format字符串安排time值的格式。format字符串可能仅会处理小时、分钟和秒的格式说明符，其他说明符产生一个NULL值或0。若time值包含一个大于23的小时部分，则 %H 和 %k 小时格式说明符会产生一个 大于（0...23） 的通常范围的值。

  3. GET_FORMAT()

    GET_FORMAT(val_type,format_type) 返回日期时间字符串的显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型，包括EUR、INTERVAL、ISO、JIS、USA。GET_FORMAT根据两个值的类型组合返回的字符串显示格式如下表所示。

    值类型格式化类型显示格式字符串
    DATEEUR%d.%m.%Y
    DATEINTERVAL%Y%m%d
    DATEISO%Y-%m-%d
    DATEJIS%Y-%m-%d
    DATEUSA%m.%d.%Y
    TIMEEUR%H.%i.%s
    TIMEINTERVAL%H%i%s
    TIMEISO%H:%i:%s
    TIMEJIS%H:%i:%s
    TIMEUSA%h:%i:%s %p
    DATETIMEEUR%Y-%m-%d %H.%i.%s
    DATETIMEINTERVAL%Y%m%d%H%i%s
    DATETIMEISO%Y-%m-%d %H:%i:%s
    DATETIMEJIS%Y-%m-%d %H:%i:%s
    DATETIMEUSA%Y-%m-%d %H.%i.%s

    GET_FORMAT(DATE,'USA') 返回的显示格式字符串为 %m.%d.%Y。

条件判断函数

  • 条件判断函数也称为控制流程函数，根据满足的条件执行相应的流程。MySQL中进行条件判断的函数有IF、IFNULL和CASE。

IF(expr,v1,v2)函数

  • 在IF(expr,v1,v2) 中，若表达式expr是true（expr<>0 and expr<>NULL），则IF() 的返回值为v1；否则返回值为v2。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

IFNULL(v1,v2)函数

  • 在IFNULL(v1,v2)中，假如v1不为NULL，则IFNULL() 的返回值为v1；否则，返回值为v2。IFNULL() 的返回值是数字值或字符串值，具体情况取决于其所在语境。

提示：若v1或v2只有一个明确是NULL，则IFNULL() 函数的结果类型为非NULL表达式的结果类型。

CASE函数

  1. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

    该函数表示，如果expr值等于某个vi，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn。

  2. CASE WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

    该函数表示，某个vi值为true时，返回对应位置THEN后面的结果，如果所有值都不为true，则返回ELSE后的rn。

提示：一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，具体情况视其所在语境而定；用在字符串语境中，返回结果为字符串；用在数字语境中，返回结果为十进制值、实数值或整数值。

系统信息函数

  • MySQL中的系统信息有数据库的版本号、当前用户名和连接数、系统字符集、最后一个自动生成的ID值等。

获取MySQL版本号、连接数和数据库名的函数

  1. VERSION()

    VERSION() 返回指示MySQL服务器版本的字符串。这个字符串使用utf8字符集。

  2. CONNECTION_ID()

    CONNECTION_ID() 返回MySQL服务器当前用户连接数，每个连接都有各自唯一的ID。

  3. SHOW PROCESSLIST 和 SHOW FULL PROCESSLIST

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

    如果是root账号，就能看到所有用户的当前连接。如果是其他普通账号，则只能看到自己占用的连接。SHOW PROCESSLIST 只列出前100条，如果想全部列出可使用SHOW FULL PROCESSLIST。

    返回结果中，各个列的含义和用途：

    （1）Id列，用户登录MySQL时，系统分配的 “connection id”。

    （2）User列，显示当前用户。如果不是root，这个命令就只显示用户权限范围内的SQL语句。

    （3）Host列，显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出现的问题语句的用户。

    （4）db列，显示这个进程目前连接的是哪个数据库。

    （5）Command列，显示当前连接的执行命令，一般取值为休眠（sleep）、查询（Query），连接（Connect）。

    （6）Time列，显示这个状态持续的时间，单位是秒。

    （7）State列，显示使用当前连接的SQL语句状态，很重要的列，State只是语句执行中的某一个状态。一个SQL语句，以查询为例，可能需要经过Copying to tmp table、Sorting result、Sending data等状态才能完成。

    （8）Info列，显示这个SQL语句，是判断问题语句的一个重要依据。

  4. DATABASE()和SCHEMA()

    DATABASE( )和SCHEMA( ) 函数返回使用utf8字符集的默认（当前）数据库名。

获取用户名的函数

  • USER()、CURRENT_USER()、SYSTEM_USER() 和 SESSION_USER() 这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。一般情况下，这几个函数的返回值是相同的。

获取字符串的字符集和排序方式的函数

  1. CHARSET(str)

    CHARSET(str) 返回字符串str自变量的字符集。

  2. COLLATION(str)

    COLLATION(str) 返回字符串str的字符排列方式。

获取最后一个自动生成的ID值的函数

  • LAST_INSERT_ID() 自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值。

加密函数

  • 加密函数主要用来数据进行加密和界面处理，以保证某些重要数据不被别人获取。这些函数在保证数据库安全时非常有用。

加密函数PASSWORD(str)

  • PASSWORD(str) 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。
  • MySQL将PASSWORD函数加密后的密码保存到用户权限表中。
  • 提示：PASSWORD() 函数在MySQL服务器的鉴定系统中使用；不应将它用在个人的应用程序中。PASSWORD() 加密是单向的（不可逆）。PASSWORD() 执行密码加密与UNIX中密码被加密的方式不同。

加密函数MD5(str)

  • MD5(str) 为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。

加密函数ENCODE(str,pswd_str)

  • ENCODE(str,pswd_str) 使用pswd_str作为密钥，加密str。使用DECODE() 解密结果，结果是一个和str长度相同的二进制字符串。

解密函数DECODE(crypt_str,pswd_str)

  • DECODE(crypt_str,pswd_str) 使用pswd_str作为密钥，解密加密字符串crypt_str。crypt_str是由ENCODE() 返回的字符串。

其他函数

  • 这些函数不能笼统地分为哪一类，但是这些函数也非常有用，例如重复指定操作函数、改变字符集函数、IP地址与数字转换函数等。

格式化函数FORMAT(x,n)

  • FORMAT(x,n) 将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果不含小数部分。

不同进制的数字进行转换的函数

  • CONV(N,from_base,to_base) 函数进行不同进制数间的转换。返回值为数值N的字符串表示，由from_base进制转化为to_base进制。若有任意一个参数为NULL，则返回值为NULL。自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为2，最大基数为36。

IP地址与数字相互转换的函数

  1. INET_ATON()

    • INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4bit或8bit地址。
    • 例如IP地址为 a.b.c.d，计算方法为 a *(256^3) + b *(256^2) + c *(256^1) + d *(256^0)。

  2. INET_NTOA()

    INET_NTOA(expr) 给定一个数字网络地址（4bit或8bit），返回作为字符串的该地址的点地址表示。

加锁函数和解锁函数

  1. GET_LOCK(str,timeout)

    GET_LOCK(str,timeout) 设法使用字符串str给定的名字得到一个锁，超时为timeout秒。若成功得到锁，则返回1；若操作超时，则返回0；若发生错误，则返回NULL。假如有一个用GET_LOCK() 得到的锁，当执行RELEASE_LOCK() 或连接断开（正常或非正常）时，这个锁就会解除。

  2. RELEASE_LOCK(str)

    RELEASE_LOCK(str) 解开被GET_LOCK() 获取的，用字符串str所命名的锁。若锁被解开，则返回1；若线程尚未创建锁，则返回0（此时锁没有被解开）；若命名的锁不存在，则返回NULL。若该锁从未被GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在。

  3. IS_FREE_LOCK(str)

    IS_FREE_LOCK(str) 检查名为str的锁是否可以使用（换言之，没有被封锁）。若锁可以使用，则返回1（没有人在用这个锁）；若这个锁正在被使用，则返回0；出现错误，则返回NULL（诸如不正确的参数）。

  4. IS_USED_LOCK(str)

    IS_USED_LOCK(str) 检查名为str的锁是否正在被使用（换言之，被封锁）。若被封锁，则返回使用该锁的客户端的连接标识符（connection ID）；否则，返回NULL。

重复执行指定操作的函数

  • BENCHMARK(count,expr) 函数重复执行表达式（expr）count次。它可以用于计算MySQL处理表达式的速度。结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）。另一个作用是它可以在MySQL客户端内部报告语句执行的时间。

    提示：BENCHMARK报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。

改变字符集的函数

  • CONVERT(...USING...) 带有USING的CONVERT() 函数被用来在不同的字符集之间转化数据。

改变数据类型的函数

  • CAST(x,AS type) 和 CONVERT(x,type) 函数将一个类型的值转换为另一个类型的值，可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。
标题：MySQL函数（五）
作者：wangdj
地址：https://qntop.com/articles/2024/11/10/1731169500480.html

新一篇：MySQL查询数据（六）
旧一篇：MySQL数据类型和运算符（四）