首页

MySQL 中的函数

— MySQL 初步 —

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

References: 刘增杰、张少军 《MySQL 5.5 从零开始学》

http://forums.mysql.com/

MySQL 5.1 Reference Manual

目录

介绍

介绍

MySQL 提供了众多功能强大的函数. 它们包括

• 数学函数
• 字符串函数
• 日期和时间函数
• 条件判断函数
• 系统信息函数
• 加密函数

数学函数

数学函数

主要的数学函数有

• 绝对值函数 ABS(x)
• 返回圆周率的函数 PI()
• 平方根函数 SQRT(x)
• 求余函数 MOD(x,y), 表示 $x$ 被 $y$ 除后得到的余数, 即 $x\mod y$
• 三角函数
  • 正弦
  • 余弦
  • 正切
  • 余切
• 对数函数
• 随机函数

当有错误发生时, 数学函数将返回 NULL

绝对值函数

绝对值函数

绝对值函数 ABS(x)

mysql> select abs(-3);
+---------+
| abs(-3) |
+---------+
|       3 |
+---------+

圆周率

圆周率

圆周率 PI(), 默认显示 6 位小数.

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

平方根

平方根

平方根 SQRT(x)

mysql> select sqrt(2), sqrt(-2);
+--------------------+----------+
| sqrt(2)            | sqrt(-2) |
+--------------------+----------+
| 1.4142135623730951 |     NULL |
+--------------------+----------+

求余函数

求余函数

求余函数 MOD(x,y), 结果返回 $x$ 被 $y$ 除所得的余数.

mysql> select mod(10,3), mod(-10,3);
+-----------+------------+
| mod(10,3) | mod(-10,3) |
+-----------+------------+
|         1 |         -1 |
+-----------+------------+

mysql> select mod(10.1,3);
+-------------+
| mod(10.1,3) |
+-------------+
|         1.1 |
+-------------+

取整函数

取整函数

取整函数 CEIL(x), CEILING(x), FLOOR(x). 其中 CEIL(x) 和 CEILING(x) 相同.

mysql> select ceil(3.5), ceiling(3.5), floor(3.5);
+-----------+--------------+------------+
| ceil(3.5) | ceiling(3.5) | floor(3.5) |
+-----------+--------------+------------+
|         4 |            4 |          3 |
+-----------+--------------+------------+
mysql> select ceil(-3.2), floor(-3.6);
+------------+-------------+
| ceil(-3.2) | floor(-3.6) |
+------------+-------------+
|         -3 |          -4 |
+------------+-------------+

随机数

随机数

获取随机数 RAND() 和 RAND(x). 这两个函数都返回 [0,1] 中的某个浮点值.

mysql> select rand(), rand(), rand();
+---------------------+---------------------+---------------------+
| rand()              | rand()              | rand()              |
+---------------------+---------------------+---------------------+
| 0.30437831236457297 | 0.43280036322101983 | 0.25086690974502535 |
+---------------------+---------------------+---------------------+

不带参数的 RAND() 函数产生随机数, 而且每次产生的数一般都不同.

mysql> select rand(2), rand(2), rand(3);
+--------------------+--------------------+--------------------+
| rand(2)            | rand(2)            | rand(3)            |
+--------------------+--------------------+--------------------+
| 0.6555866465490187 | 0.6555866465490187 | 0.9057697559760601 |
+--------------------+--------------------+--------------------+

带参数的 RAND(x) 函数将参数 x 作为种子, 产生随机数与 x 有关, 每次都是固定的, 不同的 x 产生不同的随机数.

mysql> select rand(2), rand(2.1), rand(3.2);
+--------------------+--------------------+--------------------+
| rand(2)            | rand(2.1)          | rand(3.2)          |
+--------------------+--------------------+--------------------+
| 0.6555866465490187 | 0.6555866465490187 | 0.9057697559760601 |
+--------------------+--------------------+--------------------+

mysql> select rand(3.2), rand(3.5), rand(3.9), rand(4);
+--------------------+---------------------+---------------------+---------------------+
| rand(3.2)          | rand(3.5)           | rand(3.9)           | rand(4)             |
+--------------------+---------------------+---------------------+---------------------+
| 0.9057697559760601 | 0.15595286540310166 | 0.15595286540310166 | 0.15595286540310166 |
+--------------------+---------------------+---------------------+---------------------+

四舍五入以及截断函数

四舍五入以及截断函数

• ROUND(x), 对 x 进行四舍五入操作.
• ROUND(x,y), 返回最接近 x 的数, 其值保留到小数点后面 y 位.
• TRUNCATE(x,y), 对 x 进行截取, 保留小数点后面 y 位.

mysql> select round(1.23), round(-1.23), round(1.56), round(-1.56);
+-------------+--------------+-------------+--------------+
| round(1.23) | round(-1.23) | round(1.56) | round(-1.56) |
+-------------+--------------+-------------+--------------+
|           1 |           -1 |           2 |           -2 |
+-------------+--------------+-------------+--------------+

mysql> select round(1.23456,3), round(-1.23456,2), round(1.56789,4), round(-1.56789,1);
+------------------+-------------------+------------------+-------------------+
| round(1.23456,3) | round(-1.23456,2) | round(1.56789,4) | round(-1.56789,1) |
+------------------+-------------------+------------------+-------------------+
|            1.235 |             -1.23 |           1.5679 |              -1.6 |
+------------------+-------------------+------------------+-------------------+

mysql> select truncate(1.2345,2), truncate(-1.2345,3);
+--------------------+---------------------+
| truncate(1.2345,2) | truncate(-1.2345,3) |
+--------------------+---------------------+
|               1.23 |              -1.234 |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select truncate(1.5678,2), truncate(-1.5678,3);
+--------------------+---------------------+
| truncate(1.5678,2) | truncate(-1.5678,3) |
+--------------------+---------------------+
|               1.56 |              -1.567 |
+--------------------+---------------------+

符号函数

符号函数

SIGN(x) \[ \text{sign}(x)=\begin{cases} +1, & \text{若}\ x>0,\\ 0, & \text{若}\ x=0,\\ -1, & \text{若}\ x<0,\\ \end{cases} \]

mysql> select sign(-3.5), sign(0), sign(pi());
+------------+---------+------------+
| sign(-3.5) | sign(0) | sign(pi()) |
+------------+---------+------------+
|         -1 |       0 |          1 |
+------------+---------+------------+

mysql> select sign(1+sqrt(-1));
+------------------+
| sign(1+sqrt(-1)) |
+------------------+
|             NULL |
+------------------+

幂运算函数

幂运算函数

• POW(x,y) 或 POWER(x,y), 返回 $x^y$.
• EXP(x), 指数函数, 返回 $e^x$

mysql> select pow(2,3), power(3,2), exp(1);
+----------+------------+-------------------+
| pow(2,3) | power(3,2) | exp(1)            |
+----------+------------+-------------------+
|        8 |          9 | 2.718281828459045 |
+----------+------------+-------------------+

对数运算函数

对数运算函数

• LOG(x), 返回自然对数, 即 $\ln x$.
• LOG10(x), 返回以 10 为底的对数.

mysql> select log(1),log(2.71828), log10(100);
+--------+-------------------+------------+
| log(1) | log(2.71828)      | log10(100) |
+--------+-------------------+------------+
|      0 | 0.999999327347282 |          2 |
+--------+-------------------+------------+
1 row in set (0.00 sec)

mysql> select log(-1),log(0);
+---------+--------+
| log(-1) | log(0) |
+---------+--------+
|    NULL |   NULL |
+---------+--------+

mysql> select log(exp(pi()))=pi();
+---------------------+
| log(exp(pi()))=pi() |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

角度与弧度相互转换的函数

角度与弧度相互转换的函数

• RADIANS(x), 将角度 x 转换为弧度.
• DEGREES(x), 将弧度 x 转换为角度.

mysql> select radians(90), degrees(1), degrees(PI()/2);
+--------------------+-------------------+-----------------+
| radians(90)        | degrees(1)        | degrees(PI()/2) |
+--------------------+-------------------+-----------------+
| 1.5707963267948966 | 57.29577951308232 |              90 |
+--------------------+-------------------+-----------------+

三角函数与反三角函数

三角函数与反三角函数

• SIN(x), ASIN(x)
• COS(x), ACOS(x)
• TAN(x), ATAN(x)
• COT(x)

mysql> select sin(pi()/2), asin(1), degrees(asin(1));
+-------------+--------------------+------------------+
| sin(pi()/2) | asin(1)            | degrees(asin(1)) |
+-------------+--------------------+------------------+
|           1 | 1.5707963267948966 |               90 |
+-------------+--------------------+------------------+

mysql> select cos(pi()), acos(-1), degrees(acos(-1));
+-----------+-------------------+-------------------+
| cos(pi()) | acos(-1)          | degrees(acos(-1)) |
+-----------+-------------------+-------------------+
|        -1 | 3.141592653589793 |               180 |
+-----------+-------------------+-------------------+

mysql> select tan(pi()/4), atan(1), degrees(atan(1));
+--------------------+--------------------+------------------+
| tan(pi()/4)        | atan(1)            | degrees(atan(1)) |
+--------------------+--------------------+------------------+
| 0.9999999999999999 | 0.7853981633974483 |               45 |
+--------------------+--------------------+------------------+

mysql> select cot(pi()/4), cot(pi()/2);
+--------------------+-----------------------+
| cot(pi()/4)        | cot(pi()/2)           |
+--------------------+-----------------------+
| 1.0000000000000002 | 6.123233995736766e-17 |
+--------------------+-----------------------+

mysql> select cot(pi()), tan(pi()/2);
+-----------------------+----------------------+
| cot(pi())             | tan(pi()/2)          |
+-----------------------+----------------------+
| -8.165619676597685e15 | 1.633123935319537e16 |
+-----------------------+----------------------+

没有 ACOT(x) 函数.

字符串函数

字符串函数

字符串长度函数

• CHAR_LENGTH(str), 返回字符串 str 的长度, 即所含有字符的个数.
• LENGTH(str), 返回字符串 str 的字节长度.
  • 使用 utf8 编码的字符集, 一个汉字占 3 个字节(byte), 一个数字或字母占一个字节.

mysql> select char_length("Hello world!");
+-----------------------------+
| char_length("Hello world!") |
+-----------------------------+
|                          12 |
+-----------------------------+

mysql> select length("Hello world!");
+------------------------+
| length("Hello world!") |
+------------------------+
|                     12 |
+------------------------+

mysql> select length("你好, 世界!");
+---------------------------+
| length("你好, 世界!")     |
+---------------------------+
|                        15 |
+---------------------------+

mysql> select char_length("你好, 世界!");
+--------------------------------+
| char_length("你好, 世界!")     |
+--------------------------------+
|                              7 |
+--------------------------------+

字符串合并函数

• CONCAT(s1,s2,...), 返回连接 s1,s2,... 这若干个字符串所得的字符串.
  • 如果某个参数是 NULL, 则返回 NULL.
  • 如果所有参数都是非二进制字符串, 则结果也是非二进制字符串.
  • 只要参数中有一个是二进制字符串, 则结果就是二进制字符串.
• CONCAT_WS(x,s1,s2,...), 是指带有分隔符的连接函数(concat with separator).
  • 如果分隔符是 NULL, 则结果为 NULL.
  • 参数 s1,s2,... 中如果有 NULL, 则会被忽略.

mysql> select concat("hello", "world");
+--------------------------+
| concat("hello", "world") |
+--------------------------+
| helloworld               |
+--------------------------+

mysql> select concat_ws('-',"hello", "world");
+---------------------------------+
| concat_ws('-',"hello", "world") |
+---------------------------------+
| hello-world                     |
+---------------------------------+

mysql> select concat_ws('-*-',"hello", "world");
+-----------------------------------+
| concat_ws('-*-',"hello", "world") |
+-----------------------------------+
| hello-*-world                     |
+-----------------------------------+

mysql> select concat_ws('-',"Hi", NULL, "John!", NULL, "I am Mike.");
+--------------------------------------------------------+
| concat_ws('-',"Hi", NULL, "John!", NULL, "I am Mike.") |
+--------------------------------------------------------+
| Hi-John!-I am Mike.                                    |
+--------------------------------------------------------+

字符串替换函数

• INSERT(s1,x,len,s2), 将字符串 s1 第 x 个开始长度为 len 的子字符串替换为 s2.
  • 如果 x 的位置超出 s1 的范围, 则直接返回原字符串 s1.

mysql> select insert("Hello world!", 6,1,"-");
+---------------------------------+
| insert("Hello world!", 6,1,"-") |
+---------------------------------+
| Hello-world!                    |
+---------------------------------+

mysql> select insert("Hello world!", -1,20,"Chinese people");
+------------------------------------------------+
| insert("Hello world!", -1,20,"Chinese people") |
+------------------------------------------------+
| Hello world!                                   |
+------------------------------------------------+

mysql> select insert("Hello world!", 7,5,"China");
+-------------------------------------+
| insert("Hello world!", 7,5,"China") |
+-------------------------------------+
| Hello China!                        |
+-------------------------------------+

mysql> select insert("Hello world!", 7,5,"Chinese people");
+----------------------------------------------+
| insert("Hello world!", 7,5,"Chinese people") |
+----------------------------------------------+
| Hello Chinese people!                        |
+----------------------------------------------+

mysql> select insert("Hello world!", 7,10,"Chinese people");
+-----------------------------------------------+
| insert("Hello world!", 7,10,"Chinese people") |
+-----------------------------------------------+
| Hello Chinese people                          |
+-----------------------------------------------+

mysql> select insert("Hello world!", 7,20,"Chinese people");
+-----------------------------------------------+
| insert("Hello world!", 7,20,"Chinese people") |
+-----------------------------------------------+
| Hello Chinese people                          |
+-----------------------------------------------+

mysql> select insert("What",3,100,"question");
+---------------------------------+
| insert("What",3,100,"question") |
+---------------------------------+
| Whquestion                      |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select insert("What",3,8,"question");
+-------------------------------+
| insert("What",3,8,"question") |
+-------------------------------+
| Whquestion                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select insert("What",3,2,"question");
+-------------------------------+
| insert("What",3,2,"question") |
+-------------------------------+
| Whquestion                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select insert("What",3,3,"question");
+-------------------------------+
| insert("What",3,3,"question") |
+-------------------------------+
| Whquestion                    |
+-------------------------------+

字母大小写转换函数

• LOWER(str) 或者 LCASE(str), 将字符串 str 中的字母全部转换为小写字母.
• UPPER(str) 或者 UCASE(str), 将字符串 str 中的字母全部转换为大写字母.

mysql> select lower("tPxf0EZhTdM9"), lcase("tPxf0EZhTdM9");
+-----------------------+-----------------------+
| lower("tPxf0EZhTdM9") | lcase("tPxf0EZhTdM9") |
+-----------------------+-----------------------+
| tpxf0ezhtdm9          | tpxf0ezhtdm9          |
+-----------------------+-----------------------+

mysql> select upper("tPxf0EZhTdM9"), ucase("tPxf0EZhTdM9");
+-----------------------+-----------------------+
| upper("tPxf0EZhTdM9") | ucase("tPxf0EZhTdM9") |
+-----------------------+-----------------------+
| TPXF0EZHTDM9          | TPXF0EZHTDM9          |
+-----------------------+-----------------------+

获取指定长度的字符串的函数

• LEFT(str,n), 返回字符串 str 的前 n 个字符的子字符串.
• RIGHT(str,n), 返回字符串 str 的最后 n 个字符的子字符串.

mysql> select left("hello world", 5);
+------------------------+
| left("hello world", 5) |
+------------------------+
| hello                  |
+------------------------+
1 row in set (0.00 sec)

mysql> select right("hello world", 5);
+-------------------------+
| right("hello world", 5) |
+-------------------------+
| world                   |
+-------------------------+

填充字符串的函数

• LPAD(s1,len,s2), 返回字符串 s1, 其左边被字符串 s2 补充直到总长度为 len.
  • 如果 s1 的长度大于 len, 则不填充, 返回从第一个字符开始的长为 len 的子字符串.
• RPAD(s1,len,s2), 返回字符串 s1, 其右边被字符串 s2 补充直到总长度为 len.
  • 如果 s1 的长度大于 len, 则不填充, 返回从第一个字符开始的长为 len 的子字符串. 即被截断了.

mysql> select lpad("hello", 4, '??');
+------------------------+
| lpad("hello", 4, '??') |
+------------------------+
| hell                   |
+------------------------+
1 row in set (0.32 sec)

mysql> select lpad("98-10-30", 10, '19');
+----------------------------+
| lpad("98-10-30", 10, '19') |
+----------------------------+
| 1998-10-30                 |
+----------------------------+

mysql> select rpad("hello", 4, '??');
+------------------------+
| rpad("hello", 4, '??') |
+------------------------+
| hell                   |
+------------------------+
1 row in set (0.00 sec)

mysql> select rpad("hello", 10, '??');
+-------------------------+
| rpad("hello", 10, '??') |
+-------------------------+
| hello?????              |
+-------------------------+

mysql> select rpad("hello", 10, '?#');
+-------------------------+
| rpad("hello", 10, '?#') |
+-------------------------+
| hello?#?#?              |
+-------------------------+

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

• LTRIM(s), 返回字符串 s, 字符串左侧空格字符被删除.
• RTRIM(s), 返回字符串 s, 字符串右侧空格字符被删除.
• TRIM(s), 返回字符串 s, 删除字符串两侧的空格.

mysql> select '(  hello world   )', concat('(',ltrim('  hello world   '),')');
+--------------------+-------------------------------------------+
| (  hello world   ) | concat('(',ltrim('  hello world   '),')') |
+--------------------+-------------------------------------------+
| (  hello world   ) | (hello world   )                          |
+--------------------+-------------------------------------------+

mysql> select '(  hello world   )', concat('(',rtrim('  hello world   '),')');
+--------------------+-------------------------------------------+
| (  hello world   ) | concat('(',rtrim('  hello world   '),')') |
+--------------------+-------------------------------------------+
| (  hello world   ) | (  hello world)                           |
+--------------------+-------------------------------------------+

mysql> select '(  hello world   )', concat('(',trim('  hello world   '),')');
+--------------------+------------------------------------------+
| (  hello world   ) | concat('(',trim('  hello world   '),')') |
+--------------------+------------------------------------------+
| (  hello world   ) | (hello world)                            |
+--------------------+------------------------------------------+

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

• TRIM(s1 FROM s), 删除字符串 s 中两端所有的子字符串 s1.
• s1 可以不填, 缺省状态下删除空格.

mysql> select trim('he' from 'hehe he say hello to Pathehehe');
+--------------------------------------------------+
| trim('he' from 'hehe he say hello to Pathehehe') |
+--------------------------------------------------+
|  he say hello to Pat                             |
+--------------------------------------------------+

注意, 第一个字符是空格.

mysql> select char_length(trim('he' from 'hehe he say hello to Pathehehe'));
+---------------------------------------------------------------+
| char_length(trim('he' from 'hehe he say hello to Pathehehe')) |
+---------------------------------------------------------------+
|                                                            20 |
+---------------------------------------------------------------+

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

REPEAT(s,n), 对于字符串 s, 重复 n 次, 然后返回.

• 若 n<=0, 则返回一个空字符串.
• 若 s 或 n 为 NULL, 则返回 NULL.

mysql> select repeat("I love China!\n",3);
+--------------------------------------------+
| repeat("I love China!\n",3)                |
+--------------------------------------------+
| I love China!
I love China!
I love China!
 |
+--------------------------------------------+

mysql> select repeat('abc',-1);
+------------------+
| repeat('abc',-1) |
+------------------+
|                  |
+------------------+
1 row in set (0.00 sec)

mysql> select char_length(repeat('abc',-1));
+-------------------------------+
| char_length(repeat('abc',-1)) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select repeat('abc',-1)='';
+---------------------+
| repeat('abc',-1)='' |
+---------------------+
|                   1 |
+---------------------+

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

• SPACE(n), 返回由 n 个空格组成的字符串.
• REPLACE(s,s1,s2), 在字符串 s 中, 将所有子字符串 s1 替换为 s2.

mysql> select concat('[',space(5),']');
+--------------------------+
| concat('[',space(5),']') |
+--------------------------+
| [     ]                  |
+--------------------------+

mysql> select space(5)=repeat(' ',5);
+------------------------+
| space(5)=repeat(' ',5) |
+------------------------+
|                      1 |
+------------------------+

mysql> select replace ('www.atzjg.com','com','net');
+---------------------------------------+
| replace ('www.atzjg.com','com','net') |
+---------------------------------------+
| www.atzjg.net                         |
+---------------------------------------+

注意和 INSERT(s1,x,len,s2) 的区别, 后者只替换一次, 本质上还是插入操作.

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

将字符串 s1 和 s2 从第一个字符开始逐个字符比较,

• 如果两者全等, 则返回 0.
• 如果第一次碰到某个字符不等, 并且如果前者小于后者, 则返回 -1.
• 否则, 返回 1.

mysql> select strcmp('hello','HelLo');
+-------------------------+
| strcmp('hello','HelLo') |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('hello2','hello1');
+---------------------------+
| strcmp('hello2','hello1') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('helloA','helloB');
+---------------------------+
| strcmp('helloA','helloB') |
+---------------------------+
|                        -1 |
+---------------------------+

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

SUBSTRING(s,n,len), 返回字符串 s 中从第 n 个字符开始的长度为 len 的子串.

• 其中 len 可以缺省, 此时返回从第 n 个字符开始到末尾的子串.
• n 也可以为负数, 例如: -3, 意思为倒数第 3 个字符开始(但是方向还是往后).

mysql> select substring('hello world', 3, 5);
+--------------------------------+
| substring('hello world', 3, 5) |
+--------------------------------+
| llo w                          |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select substring('hello world', 3);
+-----------------------------+
| substring('hello world', 3) |
+-----------------------------+
| llo world                   |
+-----------------------------+

mysql> select substring('hello world', -5, 3);
+---------------------------------+
| substring('hello world', -5, 3) |
+---------------------------------+
| wor                             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select substring('hello world', -5);
+------------------------------+
| substring('hello world', -5) |
+------------------------------+
| world                        |
+------------------------------+

MID(s,n,len) 与 SUBSTRING(s,n,len) 的功能相同.

匹配子串开始位置的函数

LOCATE(s1,s), POSITION(s1 IN s) 和 INSTR(s,s1) 这三个函数的功能相同, 都是返回子串 s1 在字符串 s 中的开始位置. 只是要注意 INSTR 函数中参数的顺序.

mysql> select locate('wo','hello world');
+----------------------------+
| locate('wo','hello world') |
+----------------------------+
|                          7 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select position('he' in 'the hero');
+------------------------------+
| position('he' in 'the hero') |
+------------------------------+
|                            2 |
+------------------------------+
1 row in set (0.00 sec)

mysql> select instr('the hero', 'ero');
+--------------------------+
| instr('the hero', 'ero') |
+--------------------------+
|                        6 |
+--------------------------+

字符串逆序函数 REVERSE(s)

将字符串 s 中的字符按逆序排列后返回.

mysql> select reverse('atzjg');
+------------------+
| reverse('atzjg') |
+------------------+
| gjzta            |
+------------------+

mysql> select reverse('arxiv');
+------------------+
| reverse('arxiv') |
+------------------+
| vixra            |
+------------------+

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

ELT(k,str_1,str_2,...,str_n)

返回 str_k

• 若 k<1 或大于 n, 则返回 NULL

mysql> select elt(3,'January','February','March','April','May');
+---------------------------------------------------+
| elt(3,'January','February','March','April','May') |
+---------------------------------------------------+
| March                                             |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select elt(6,'January','February','March','April','May');
+---------------------------------------------------+
| elt(6,'January','February','March','April','May') |
+---------------------------------------------------+
| NULL                                              |
+---------------------------------------------------+

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

FIELD(s,s1,s2,...), 返回字符串 s 在列表 s1,s2,... 中第一次出现的位置.

• 如果在列表中找不到 s, 则返回 0.
• 如果 s 为 NULL, 则返回 0. (原因是 NULL 不能与任何值进行同等比较.)

mysql> select field('May','January','February','March','April','May');
+---------------------------------------------------------+
| field('May','January','February','March','April','May') |
+---------------------------------------------------------+
|                                                       5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select field('ary','January','February','March','April','May');
+---------------------------------------------------------+
| field('ary','January','February','March','April','May') |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+

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

FIND_IN_SET(s1,s2) 返回字符串 s1 在字符串列表 s2 中出现的位置, 字符串列表是一个由多个逗号分开的字符串组成的列表.

• 如果 s1 不在列表 s2 中, 或者 s2 为空字符串, 则返回 0.
• 如果任意一个参数为 NULL, 则返回 NULL.
• 当第一个参数包含一个逗号时, 函数将无法正常运行.

mysql> select find_in_set("hi","jf,si,hi,li,Hi,test");
+-----------------------------------------+
| find_in_set("hi","jf,si,hi,li,Hi,test") |
+-----------------------------------------+
|                                       3 |
+-----------------------------------------+

mysql> select find_in_set("hi","jf,si,hi,li,hi,test");
+-----------------------------------------+
| find_in_set("hi","jf,si,hi,li,hi,test") |
+-----------------------------------------+
|                                       3 |
+-----------------------------------------+

mysql> select find_in_set("hi,","jf,si,hi,li,hi,test");
+------------------------------------------+
| find_in_set("hi,","jf,si,hi,li,hi,test") |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

mysql> select find_in_set("hi",NULL);
+------------------------+
| find_in_set("hi",NULL) |
+------------------------+
|                   NULL |
+------------------------+

mysql> select find_in_set("hi,","jf,si,hi,,li,hi,test");
+-------------------------------------------+
| find_in_set("hi,","jf,si,hi,,li,hi,test") |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set("'hi,'","jf,si,'hi,',li,hi,test");
+-----------------------------------------------+
| find_in_set("'hi,'","jf,si,'hi,',li,hi,test") |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

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

MAKE_SET(x,s1,s2,...), 其中第一个参数 x 是指十进制的整数, 但是会转化为二进制. 比如当 x 为 3 时, 就解释为 011, 从右到左第一位和第二位为 1, 就将后面的字符串列表中第一个和第二个取出组成字符串返回, 中间还是用逗号隔开.

• NULL 不会添加到结果中.

MAKE_SET 返回的是一个字符串, 而不是一个集合(SET).

mysql> select make_set(1|4, 'hello','NULL','world');
+---------------------------------------+
| make_set(1|4, 'hello','NULL','world') |
+---------------------------------------+
| hello,world                           |
+---------------------------------------+

mysql> select make_set(1|4, 'hello','NULL',NULL,'world');
+--------------------------------------------+
| make_set(1|4, 'hello','NULL',NULL,'world') |
+--------------------------------------------+
| hello                                      |
+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select make_set(1|4, 'hello','I''m Mike','NULL',NULL,'world');
+--------------------------------------------------------+
| make_set(1|4, 'hello','I''m Mike','NULL',NULL,'world') |
+--------------------------------------------------------+
| hello,NULL                                             |
+--------------------------------------------------------+

第一个参数也可以是十进制数, make_set 函数会将其转化为二进制数. 例如:

mysql> select make_set(5, 'hello', 'nice', 'world') as col2;
+-------------+
| col2        |
+-------------+
| hello,world |
+-------------+

我们可以将make_set生成的集合赋给一个变量 A.

mysql> set @A=make_set(5, 'hello', 'nice', 'world');
Query OK, 0 rows affected (0.00 sec)

mysql> select @A;
+-------------+
| @A          |
+-------------+
| hello,world |
+-------------+

mysql> select length(@A);
+------------+
| length(@A) |
+------------+
|         11 |
+------------+

mysql> select char_length(@A);
+-----------------+
| char_length(@A) |
+-----------------+
|              11 |
+-----------------+

mysql> select strcmp('hello world', @A);
+---------------------------+
| strcmp('hello world', @A) |
+---------------------------+
|                        -1 |
+---------------------------+

日期和时间函数

日期和时间函数

一般的日期函数除了使用 DATE 类型的参数外, 也可以使用 DATETIME 或者 TIMESTAMP 类型的参数, 但会忽略这些值的时间部分.

类似的, 以 TIME 类型值为参数的函数, 可以接受 TIMESTAMP 类型的参数, 但会忽略日期部分.

许多日期函数可以同时接受数和字符串类型的两种参数.

获取当前日期的函数

CURDATE() 与 CURRENT_DATE() 作用相同, 将当前日期按照 'YYYY-MM-DD' 或 'YYYYMMDD' 的格式返回, 具体格式根据函数用在字符串或是数字语境中而定.

CURDATE()+0 将当前日期值转换为数值型.

mysql> select curdate(), current_date(), curdate()+0;
+------------+----------------+-------------+
| curdate()  | current_date() | curdate()+0 |
+------------+----------------+-------------+
| 2013-11-18 | 2013-11-18     |    20131118 |
+------------+----------------+-------------+

获取当前时间的函数

CURTIME() 与 CURRENT_TIME() 作用相同, 将当前日期按照 'HH:MM:SS' 或 'HHMMSS' 的格式返回, 具体格式根据函数用在字符串或是数字语境中而定.

CURTIME()+0 将当前日期值转换为数值型.

mysql> select curtime(), current_time(), curtime()+0;
+-----------+----------------+---------------+
| curtime() | current_time() | curtime()+0   |
+-----------+----------------+---------------+
| 13:48:22  | 13:48:22       | 134822.000000 |
+-----------+----------------+---------------+

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

CURRENT_TIMESTAMP(), LOCALTIME(), NOW() 和 SYSDATE() 这四个函数的作用相同, 均返回当前日期和时间值. 格式为 'YYYY-MM-DD HH:MM:SS' 或 'YYYYMMDDHHMMSS', 具体格式根据函数用在字符串或是数字语境中而定.

mysql> select current_timestamp(), localtime(), now(), sysdate();
+---------------------+---------------------+---------------------+---------------------+
| current_timestamp() | localtime()         | now()               | sysdate()           |
+---------------------+---------------------+---------------------+---------------------+
| 2013-11-18 13:51:41 | 2013-11-18 13:51:41 | 2013-11-18 13:51:41 | 2013-11-18 13:51:41 |
+---------------------+---------------------+---------------------+---------------------+

UNIX 时间戳函数

UNIX_TIMESTAMP(date) 若无参数调用, 则返回一个 Unix 时间戳, 是指 '1970-01-01 00:00:00' GMT 之后的秒数. 这是一个无符号整数.

GMT (Greenwich Mean Time) 格林威治标准时间.

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1384817494 |
+------------------+

mysql> select unix_timestamp(now()), now();
+-----------------------+---------------------+
| unix_timestamp(now()) | now()               |
+-----------------------+---------------------+
|            1384817526 | 2013-11-19 07:32:06 |
+-----------------------+---------------------+

mysql> select 1384817526/3600/24/365;
+------------------------+
| 1384817526/3600/24/365 |
+------------------------+
|        43.912275684932 |
+------------------------+

FROM_UNIXTIME(unixtime) 函数把 UNIX 时间戳转换为普通格式的时间, 与 UNIX_TIMESTAMP(unixtime) 互为反函数. FROM_UNIXTIME(unixtime) 必须要输入参数.

mysql> select from_unixtime(unix_timestamp(now()));
+--------------------------------------+
| from_unixtime(unix_timestamp(now())) |
+--------------------------------------+
| 2013-11-19 07:36:08                  |
+--------------------------------------+

mysql> select from_unixtime(1384817526);
+---------------------------+
| from_unixtime(1384817526) |
+---------------------------+
| 2013-11-19 07:32:06       |
+---------------------------+

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

UTC ([英] Coordinated Universal Time) 世界标准时间.

• UTC_DATE() 返回当前 UTC 日期值, 其格式为 'YYYY-MM-DD' 或 'YYYYMMDD', 具体格式取决于函数是否用在字符串或数字语境中.
• UTC_TIME() 返回当前 UTC 时间值, 其格式为 'HH:MM:SS' 或 'HHMMSS', 具体格式取决于函数是否用在字符串或数字语境中.

mysql> select utc_date(), utc_date()+0;
+------------+--------------+
| utc_date() | utc_date()+0 |
+------------+--------------+
| 2013-11-19 |     20131119 |
+------------+--------------+

mysql> select utc_time(), utc_time()+0;
+------------+--------------+
| utc_time() | utc_time()+0 |
+------------+--------------+
| 00:07:02   |   702.000000 |
+------------+--------------+

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

• MONTH(date) 函数返回 date 对应的月份, 范围值从 1~12.
• MONTHNAME(date) 函数返回 date 对应的月份的英文名称.

mysql> select month('2013-11-19');
+---------------------+
| month('2013-11-19') |
+---------------------+
|                  11 |
+---------------------+

mysql> select monthname('2013-11-19');
+-------------------------+
| monthname('2013-11-19') |
+-------------------------+
| November                |
+-------------------------+

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

• DAYNAME(d) 返回 d 对应的工作日的英文名称.
• DAYOFWEEK(d) 返回 d 对应的一周中的索引位置, 1 表示周日, 2 表示周一,...,7表示周六.
• WEEKDAY(d) 返回日期对应的工作日索引. 0 表示周一, 1 表示周二, ..., 6表示周日.

mysql> select dayname('2013-11-19');
+-----------------------+
| dayname('2013-11-19') |
+-----------------------+
| Tuesday               |
+-----------------------+

mysql> select dayofweek('2013-11-19');
+-------------------------+
| dayofweek('2013-11-19') |
+-------------------------+
|                       3 |
+-------------------------+

mysql> select weekday('2013-11-19');
+-----------------------+
| weekday('2013-11-19') |
+-----------------------+
|                     1 |
+-----------------------+

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

WEEK(d) 计算日期 d 是在一年中的第几周. (等同于下面的 WEEK(d,0).)

但是不同国家和地区, 由于习惯的不同, 每周的第一天并不相同, 因此 WEEK 函数有另一种形式 WEEK(d,mode). 这里 mode 可取 0,1,2,3,4,5,6,7. 分别对应每周的第一天为 周日, 周一,周日,周一,周日,周一,周日,周一. mode 参数被省略时, 则使用 default_week_format 系统自变量的值.

Mode	每周第一天	返回值的范围	Week 1 为第一周...(Week 1 is the first week ...)
0	周日	0~53	有一个周日(with a Sunday in this year)
1	周一	0~53	有三天以上(with more than 3 days this year)
2	周日	1~53	有一个周日(with a Sunday in this year)
3	周一	1~53	有三天以上(with more than 3 days this year)
4	周日	0~53	有三天以上(with more than 3 days this year)
5	周一	0~53	有一个周日(with a Sunday in this year)
6	周日	1~53	有三天以上(with more than 3 days this year)
7	周一	1~53	有一个周日(with a Sunday in this year)

mysql> select week('2013-11-19');
+--------------------+
| week('2013-11-19') |
+--------------------+
|                 46 |
+--------------------+


mysql> select week('2018-09-27');
+--------------------+
| week('2018-09-27') |
+--------------------+
|                 38 |
+--------------------+

试一下 YEARWEEK() 函数.

mysql> SELECT YEARWEEK('2010-3-14');
+-----------------------+
| YEARWEEK('2010-3-14') |
+-----------------------+
|                201011 |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK('2010-3-14');
+-------------------+
| WEEK('2010-3-14') |
+-------------------+
|                11 |
+-------------------+

实验

2018年1月1日是星期一,

mysql> SELECT WEEK('2018-1-1');
+------------------+
| WEEK('2018-1-1') |
+------------------+
|                0 |
+------------------+

mysql> SELECT WEEK('2018-1-7');
+------------------+
| WEEK('2018-1-7') |
+------------------+
|                1 |
+------------------+

这表明, 每周的第一天并不是从周一开始的. 查看系统变量 default_week_format.

mysql> SELECT @@DEFAULT_WEEK_FORMAT;
+-----------------------+
| @@DEFAULT_WEEK_FORMAT |
+-----------------------+
|                     0 |
+-----------------------+

mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+

default_week_format 等于0, 表明每周是从周日开始的. 从而 2018-1-7 是第二周(索引为 1).

mysql> SELECT WEEK('2018-9-28');
+-------------------+
| WEEK('2018-9-28') |
+-------------------+
|                38 |
+-------------------+

因此, 2018-9-28 是2018年的第39周, 如果周日作为每周的第一天的话.

WEEKOFYEAR(d) 计算日期 d 位于一年中的第几周, 范围是 1~53. 相当于 WEEK(d,3).

mysql> select weekofyear('2013-11-19');
+--------------------------+
| weekofyear('2013-11-19') |
+--------------------------+
|                       47 |
+--------------------------+

mysql> select weekofyear(20180928);
+----------------------+
| weekofyear(20180928) |
+----------------------+
|                   39 |
+----------------------+

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

• DAYOFYEAR(d) 返回日期 d 是所在年中的第几天, 范围是 1~366. 每年的1月1日总是第一天.
• DAYOFMONTH(d) 返回日期 d 是所在月中的第几天, 范围是 1~31.

mysql> select dayofyear('2013-11-19');
+-------------------------+
| dayofyear('2013-11-19') |
+-------------------------+
|                     323 |
+-------------------------+

mysql> SELECT DAYOFYEAR('2018-1-1');
+-----------------------+
| DAYOFYEAR('2018-1-1') |
+-----------------------+
|                     1 |
+-----------------------+

2018 年是平年, 而2016 年是闰年.

mysql> SELECT DAYOFYEAR('2018-12-31');
+-------------------------+
| DAYOFYEAR('2018-12-31') |
+-------------------------+
|                     365 |
+-------------------------+
1 row in set (0.00 sec)


mysql> SELECT DAYOFYEAR('2016-12-31');
+-------------------------+
| DAYOFYEAR('2016-12-31') |
+-------------------------+
|                     366 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select dayofyear('2018-9-28');
+------------------------+
| dayofyear('2018-9-28') |
+------------------------+
|                    271 |
+------------------------+

mysql> select 31*5+28+30+30+28;
+------------------+
| 31*5+28+30+30+28 |
+------------------+
|              271 |
+------------------+

如果输入的参数超出范围, 则出现错误, 返回 NULL.

mysql> select dayofyear('2018-9-31');
+------------------------+
| dayofyear('2018-9-31') |
+------------------------+
|                   NULL |
+------------------------+

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

• YEAR(date) 返回日期 date 对应的年份, 范围是 0000~9999. ('00~69' 转换为 '2000~2069', '70~99' 转换为 '1970~1999')
• QUARTER(date) 返回日期 date 对应的季度值, 范围是 1~4.
• MINUTE(time) 返回时间 time 对应的分钟数, 范围是 0~59.
• SECOND(time) 返回时间 time 对应的秒数, 范围是 0~59.

mysql> select year('2013-11-19');
+--------------------+
| year('2013-11-19') |
+--------------------+
|               2013 |
+--------------------+

mysql> select year('2070-11-19');
+--------------------+
| year('2070-11-19') |
+--------------------+
|               2070 |
+--------------------+

mysql> select year('9999-11-19');
+--------------------+
| year('9999-11-19') |
+--------------------+
|               9999 |
+--------------------+

mysql> select year('10000-11-19');
+---------------------+
| year('10000-11-19') |
+---------------------+
|                NULL |
+---------------------+

mysql> select year('0000-11-19');
+--------------------+
| year('0000-11-19') |
+--------------------+
|                  0 |
+--------------------+

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2018 |
+-------------+

mysql> select year(690928);
+--------------+
| year(690928) |
+--------------+
|         2069 |
+--------------+

mysql> select quarter('0001-11-19');
+-----------------------+
| quarter('0001-11-19') |
+-----------------------+
|                     4 |
+-----------------------+

mysql> select now(), minute(now());
+---------------------+---------------+
| now()               | minute(now()) |
+---------------------+---------------+
| 2013-11-19 08:49:21 |            49 |
+---------------------+---------------+

mysql> select now(), second(now());
+---------------------+---------------+
| now()               | second(now()) |
+---------------------+---------------+
| 2013-11-19 08:50:41 |            41 |
+---------------------+---------------+

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

EXTRACT(type FROM date) 所使用的时间间隔类型说明符与 DATE_ADD() 或 DATE_SUB() 的相同. 但它是从日期中提取一部分, 而不是执行日期运算.

• type 为 YEAR 时, 返回年份.
• type 为 YEAR_MONTH 时, 返回年份和月份.
• type 为 DAY_MINUTE 时, 返回日小时分钟.

mysql> select extract(year from now());
+--------------------------+
| extract(year from now()) |
+--------------------------+
|                     2013 |
+--------------------------+

mysql> select extract(year_month from now());
+--------------------------------+
| extract(year_month from now()) |
+--------------------------------+
|                         201311 |
+--------------------------------+


mysql> select extract(day_minute from now());
+--------------------------------+
| extract(day_minute from now()) |
+--------------------------------+
|                         190855 |
+--------------------------------+

Windows 下使用 day_minute 似乎不显示‘日’.

mysql> select now(), extract(day_minute from now());
+---------------------+--------------------------------+
| now()               | extract(day_minute from now()) |
+---------------------+--------------------------------+
| 2018-09-28 06:08:20 |                            608 |
+---------------------+--------------------------------+

mysql> select now(), extract(day from now()), extract(second from now());
+---------------------+-------------------------+----------------------------+
| now()               | extract(day from now()) | extract(second from now()) |
+---------------------+-------------------------+----------------------------+
| 2018-09-28 06:12:18 |                      28 |                         18 |
+---------------------+-------------------------+----------------------------+

时间和秒的转换函数

• TIME_TO_SEC, 将时间（格式为 HH:MM:SS）转换为秒数. 公式为 $3600\times hour+60\times min+sec$
• SEC_TO_TIME, 将秒数转换为时间格式 HH:MM:SS.

mysql> select now(), time_to_sec(now());
+---------------------+--------------------+
| now()               | time_to_sec(now()) |
+---------------------+--------------------+
| 2013-11-22 10:01:41 |              36101 |
+---------------------+--------------------+

mysql> select 10*3600+1*60+41;
+-----------------+
| 10*3600+1*60+41 |
+-----------------+
|           36101 |
+-----------------+

mysql> select sec_to_time(12345);
+--------------------+
| sec_to_time(12345) |
+--------------------+
| 03:25:45           |
+--------------------+

mysql> select 12345 div 3600, (12345-3*3600) div 60 , 12345-3*3600-25*60;
+----------------+-----------------------+--------------------+
| 12345 div 3600 | (12345-3*3600) div 60 | 12345-3*3600-25*60 |
+----------------+-----------------------+--------------------+
|              3 |                    25 |                 45 |
+----------------+-----------------------+--------------------+

mysql> select unix_timestamp(), sec_to_time(unix_timestamp());
+------------------+-------------------------------+
| unix_timestamp() | sec_to_time(unix_timestamp()) |
+------------------+-------------------------------+
|       1385085792 | 838:59:59                     |
+------------------+-------------------------------+

mysql> select 838*3600+59*60+59;
+-------------------+
| 838*3600+59*60+59 |
+-------------------+
|           3020399 |
+-------------------+

mysql> select from_unixtime(1385085792);
+---------------------------+
| from_unixtime(1385085792) |
+---------------------------+
| 2013-11-22 10:03:12       |
+---------------------------+

mysql> select time_to_sec('10:03:12');
+-------------------------+
| time_to_sec('10:03:12') |
+-------------------------+
|                   36192 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select sec_to_time(36192);
+--------------------+
| sec_to_time(36192) |
+--------------------+
| 10:03:12           |
+--------------------+

mysql> select time_to_sec(100312);
+---------------------+
| time_to_sec(100312) |
+---------------------+
|               36192 |
+---------------------+

计算日期和时间的函数

• DATE_ADD(date, INTERVAL expr type)
• ADDDATE(date, INTERVAL expr type)
• DATE_SUB(date, INTERVAL expr type)
• SUBDATE(date, INTERVAL expr type)
• ADDTIME(date,expr)
• SUBTIME(date,expr)
• DATE_DIFF(date1,date2)

DATE_ADD() 和 ADDDATE() 是一样的.
DATE_SUB() 和 SUBDATE() 是一样的.

mysql> select now(), date_add(now(),interval '1:1' minute_second) AS after;
+---------------------+---------------------+
| now()               | after               |
+---------------------+---------------------+
| 2013-11-22 10:23:18 | 2013-11-22 10:24:19 |
+---------------------+---------------------+

mysql> select date_sub('2013-11-22', interval 2 day);
+----------------------------------------+
| date_sub('2013-11-22', interval 2 day) |
+----------------------------------------+
| 2013-11-20                             |
+----------------------------------------+
mysql> select date_sub('2013-11-22', interval -2 day);
+-----------------------------------------+
| date_sub('2013-11-22', interval -2 day) |
+-----------------------------------------+
| 2013-11-24                              |
+-----------------------------------------+

其中 expr, type 的格式及定义如下.

type 值	expr 格式
MICROSECOND	microseconds
SECOND	seconds
MINUTE	minutes
HOUR	hours
DAY	days
WEEK	weeks
MONTH	months
QUARTER	quarters
YEAR	years
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_FORMAT(date,format), 根据 format 指定的格式显示 date 值.

TIME_FORMAT(time,format), 根据 format 指定的格式显示 time 值. 只处理时间值.

GET_FORMAT(val_type,format_type), 返回日期时间字符串的显示格式. 其中 val_type 表示日期类型, 可取 DATE, DATETIME, TIME. format_type 表示格式化显示类型, 包括 EUR, INTERVAL, ISO, JIS, USA

mysql> select date_format(now(),get_format(date,'usa'));
+-------------------------------------------+
| date_format(now(),get_format(date,'usa')) |
+-------------------------------------------+
| 11.22.2013                                |
+-------------------------------------------+

mysql> select get_format(date, 'usa');
+-------------------------+
| get_format(date, 'usa') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+

mysql> select get_format(date, 'EUR');
+-------------------------+
| get_format(date, 'EUR') |
+-------------------------+
| %d.%m.%Y                |
+-------------------------+

ISO 和 JIS 格式是一样的.

mysql> select date_format(now(),get_format(date, 'iso')), date_format(now(),get_format(date, 'jis'));
+--------------------------------------------+--------------------------------------------+
| date_format(now(),get_format(date, 'iso')) | date_format(now(),get_format(date, 'jis')) |
+--------------------------------------------+--------------------------------------------+
| 2018-09-28                                 | 2018-09-28                                 |
+--------------------------------------------+--------------------------------------------+

条件判断函数

条件判断函数

IF(expr, v1, v2)

IF(expr, v1, v2). 若表达式 expr 是 TRUE (即 expr<>0 and expr<>NULL), 则返回表达式 v1 的值, 否则返回表达式 v2 的值.

mysql> select if(1>2, 1+1,1+2);
+------------------+
| if(1>2, 1+1,1+2) |
+------------------+
|                3 |
+------------------+

mysql> select strcmp('hello','hi'), if(strcmp('hello','hi'), 'y','n');
+----------------------+-----------------------------------+
| strcmp('hello','hi') | if(strcmp('hello','hi'), 'y','n') |
+----------------------+-----------------------------------+
|                   -1 | y                                 |
+----------------------+-----------------------------------+

上面这个例子, 仅当两个字符串相等时, strcmp 才会变为 FALSE.

mysql> select if(isnull(NULL), 1,2);
+-----------------------+
| if(isnull(NULL), 1,2) |
+-----------------------+
|                     1 |
+-----------------------+

IFNULL(v1,v2)

IFNULL(v1,v2) 用于判断第一个表达式 v1 是否为 NULL, 如果是则返回表达式 v2 的值, 否则返回第一个表达式 v1 的值.

mysql> select ifnull(1/0,'error');
+---------------------+
| ifnull(1/0,'error') |
+---------------------+
| error               |
+---------------------+

mysql> select ifnull(2+3,'error');
+---------------------+
| ifnull(2+3,'error') |
+---------------------+
| 5                   |
+---------------------+

mysql> select ifnull(2+3,NULL);
+------------------+
| ifnull(2+3,NULL) |
+------------------+
|                5 |
+------------------+

ifnull(v1,v2) 等价于 if(isNull(v1),v2,v1);

CASE 函数

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

当表达式 expr 的值为 vk 时, 就返回值 rk.

mysql> select 
    -> case 3<2  
    -> when true then '3 is less than 2' 
    -> when false then '3 is greater than or equal to 2' 
    -> end;
+---------------------------------------------------------------------------------------+
| case 3<2  when true then '3 is less than 2' when false then '3 is greater than 2' end |
+---------------------------------------------------------------------------------------+
| 3 is greater than or equal to 2                                                                   |
+---------------------------------------------------------------------------------------+

系统信息函数

系统信息函数

MySQL 的系统信息有

• 数据库的版本号
• 当前用户名
• 当前连接数
• 系统字符集
• 最后一个自动生成的 ID 值

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.28-log |
+------------+

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

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               8 |
+-----------------+

返回值根据登录的次数会有不同. 比如我退出当前的 MySQL, 用另一账户 root 登录. 再次运行

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               9 |
+-----------------+

显示当前运行的线程

SHOW PROCESSLIST 和 SHOW FULL PROCESSLIST 显示有哪些线程在运行. 不仅可以查看当前所有的连接数, 还可以查看当前的连接状态, 帮助识别出有问题的查询语句等.

• 如果是 root 账号, 则可以看到所有用户的当前连接.
• 如果是普通账号, 则只能看到自己占用的连接.
• SHOW PROCESSLIST 只列出前 100 条.
• SHOW FULL PROCESSLIST 可以列出全部的信息.

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 10 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

这时, root 不退出, 使用另一普通用户登录 MySQL. 利用 root 账户查看连接信息, 则会看到.

mysql> show processlist;
+----+---------+-----------+------+---------+------+-------+------------------+
| Id | User    | Host      | db   | Command | Time | State | Info             |
+----+---------+-----------+------+---------+------+-------+------------------+
| 10 | root    | localhost | NULL | Query   |    0 | NULL  | show processlist |
| 11 | haifeng | localhost | NULL | Sleep   |    5 |       | NULL             |
+----+---------+-----------+------+---------+------+-------+------------------+

获取当前数据库的名字

DATEBASE() 和 SCHEMA() 返回使用 utf8 字符集的当前数据库名.

mysql> use test_bar;
Database changed
mysql> select database(), schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| test_bar   | test_bar |
+------------+----------+

获取用户名的函数

USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() 这几个函数返回当前被 MySQL 服务器验证的用户名和主机名组合.

• 一般情况下, 这几个函数的返回值是相同的. 记得加().
• CURRENT_USER()可以写为CURRENT_USER

mysql> select USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER() ;
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+

返回当前帐户连接服务器时的用户名以及所连接的客户主机. 这里 root 是当前登录用户, localhost 是登录的主机名.

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

CHARSET(str) 返回字符串 str 的字符集.

mysql> SELECT CHARSET('hello'), CHARSET(CONVERT('hello' USING latin1)),
    -> CHARSET(VERSION());
+------------------+----------------------------------------+--------------------+
| CHARSET('hello') | CHARSET(CONVERT('hello' USING latin1)) | CHARSET(VERSION()) |
+------------------+----------------------------------------+--------------------+
| gbk              | latin1                                 | utf8               |
+------------------+----------------------------------------+--------------------+
1 row in set (0.00 sec)

注意这里第一个表明 MySQL 的结果字符集是 gbk. 这可以通过 SHOW VARIABLES LIKE '%character_set%' 查询. 注意到 character_set_results 的值是 gbk.

mysql> SHOW VARIABLES LIKE '%character_set%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | gbk                                           |
| character_set_connection | gbk                                           |
| character_set_database   | latin1                                        |
| character_set_filesystem | binary                                        |
| character_set_results    | gbk                                           |
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |
| character_sets_dir       | c:\wamp\bin\mysql\mysql5.6.17\share\charsets\ |
+--------------------------+-----------------------------------------------+

当然也可以直接查询系统变量 character_set_results.

mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| gbk                     |
+-------------------------+

SHOW CHARACTER SET; 列出系统中可用的字符集. SHOW COLLATION; 列出系统中可用的校对规则.

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

当一个属性(通常是 ID)设置为 AUTO_INCREMENT 后, LAST_INSERT_ID() 将自动返回最后一个 INSERT 或 UPDATE 成功运行后对应的 ID 值.

首先建立一张名为 worker 的表.

mysql> CREATE TABLE worker(
    -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> Name VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> DESCRIBE worker;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

其中属性 id 已经设置为 PRIMARY KEY, 因此不必再写 NOT NULL.

mysql> create table worker2(
    -> id INT auto_increment primary key,
    -> name varchar(30)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc worker2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> drop table worker2;

分别单独向表 worker 插入两条记录:

mysql> INSERT INTO worker VALUES ( NULL, 'Jimy');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO worker VALUES ( NULL, 'Tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from worker;
+----+------+
| id | Name |
+----+------+
|  1 | Jimy |
|  2 | Tom  |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

注意到这里我们也可以直接写

mysql> INSERT INTO worker (Name) VALUES ('Kate');
Query OK, 1 row affected (0.01 sec)

mysql> select * from worker;
+----+------+
| id | Name |
+----+------+
|  1 | Jimy |
|  2 | Tom  |
|  3 | Kate |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

如果一次同时插入多条记录:

mysql> INSERT INTO worker (Name) VALUES ('Kavin'), ('Michael'), ('Nick');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from worker;
+----+---------+
| id | Name    |
+----+---------+
|  1 | Jimy    |
|  2 | Tom     |
|  3 | Kate    |
|  4 | Kavin   |
|  5 | Michael |
|  6 | Nick    |
+----+---------+
6 rows in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

注意这次 LAST_INSERT_ID() 不是 6, 而是 4.

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

mysql> select inet_aton('8.8.8.8');
+----------------------+
| inet_aton('8.8.8.8') |
+----------------------+
|            134744072 |
+----------------------+
1 row in set (0.00 sec)

mysql> select inet_ntoa(134744072);
+----------------------+
| inet_ntoa(134744072) |
+----------------------+
| 8.8.8.8              |
+----------------------+
1 row in set (0.00 sec)

mysql> select 8*pow(256,3)+8*pow(256,2)+8*256+8;
+-----------------------------------+
| 8*pow(256,3)+8*pow(256,2)+8*256+8 |
+-----------------------------------+
|                         134744072 |
+-----------------------------------+

mysql> select password('hello');
+-------------------------------------------+
| password('hello')                         |
+-------------------------------------------+
| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select password('hello');
+-------------------------------------------+
| password('hello')                         |
+-------------------------------------------+
| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select password('hello2');
+-------------------------------------------+
| password('hello2')                        |
+-------------------------------------------+
| *D3C231EDE4DC9971821205B03B5A702EC6F12986 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select benchmark(50000,password('hello2'));
+-------------------------------------+
| benchmark(50000,password('hello2')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (0.01 sec)

mysql> select benchmark(500000,password('hello2'));
+--------------------------------------+
| benchmark(500000,password('hello2')) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.12 sec)

mysql> select benchmark(5000000,password('hello2'));
+---------------------------------------+
| benchmark(5000000,password('hello2')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.88 sec)

mysql> select charset('string');
+-------------------+
| charset('string') |
+-------------------+
| gbk               |
+-------------------+
1 row in set (0.00 sec)

mysql> select charset(convert('string' using latin1));
+-----------------------------------------+
| charset(convert('string' using latin1)) |
+-----------------------------------------+
| latin1                                  |
+-----------------------------------------+
1 row in set (0.00 sec)

更改数据类型

CAST(x, AS type) 和 CONVERT(x, type)

mysql> select cast(100 as char(2));
+----------------------+
| cast(100 as char(2)) |
+----------------------+
| 10                   |
+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect CHAR(2) value: '100' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

使用 convert

mysql> select convert(100, char(2));
+-----------------------+
| convert(100, char(2)) |
+-----------------------+
| 10                    |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect CHAR(2) value: '100' |
+---------+------+------------------------------------------+

转换时间格式

mysql> select convert('2018-09-28', time);
+-----------------------------+
| convert('2018-09-28', time) |
+-----------------------------+
| 00:20:18                    |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2018-09-28' |
+---------+------+----------------------------------------------+

mysql> select convert('2018-09-28 15:57:45', time);
+--------------------------------------+
| convert('2018-09-28 15:57:45', time) |
+--------------------------------------+
| 15:57:45                             |
+--------------------------------------+

End

Thanks very much!