首页

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) 函数.

MySQL

mysql> select  power(cos(cos(cos(cos(cos(cos(cos(5))))))),2)*2;
+-------------------------------------------------+
| power(cos(cos(cos(cos(cos(cos(cos(5))))))),2)*2 |
+-------------------------------------------------+
|                              0.9999952547970006 |
+-------------------------------------------------+
1 row in set (0.00 sec)

SQLite

sqlite> select  power(cos(cos(cos(cos(cos(cos(cos(5))))))),2)*2;
power(cos(cos(cos(cos(cos(cos(cos(5))))))),2)*2
-----------------------------------------------
0.999995254797001
sqlite>

字符串函数

字符串函数

字符串长度函数

• 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                             |
+--------------------------------------+

数学函数列表

数学函数列表

下面列出的是MySQL 8.4 中的数学函数(Mathematical Functions).

将下面的信息存储到数据表 MathFunctions(Name, Description).

CREATE TABLE MathFunctions(
Name VARCHAR(10),
Description VARCHAR(255),
PRIMARY KEY(Name)
);

mysql> select * from mathfunctions;
+------------+----------------------------------------------------------------+
| Name       | Description                                                    |
+------------+----------------------------------------------------------------+
| ABS()      | Return the absolute value                                      |
| ACOS()     | Return the arc cosine                                          |
| ASIN()     | Return the arc sine                                            |
| ATAN(x)    | Return the arc tangent                                         |
| ATAN(y,x)  | Return the arc tangent of the two arguments x and y            |
| ATAN2(y,x) | Return the arc tangent of the two arguments x and y            |
| CEIL()     | Return the smallest integer value not less than the argument   |
| CEILING()  | Return the smallest integer value not less than the argument   |
| CONV()     | Convert numbers between different number bases                 |
| COS()      | Return the cosine                                              |
| COT()      | Return the cotangent                                           |
| CRC32()    | Compute a cyclic redundancy check value                        |
| DEGREES()  | Convert radians to degrees                                     |
| EXP()      | Raise to the power of                                          |
| FLOOR()    | Return the largest integer value not greater than the argument |
| LN()       | Return the natural logarithm of the argument                   |
| LOG()      | Return the natural logarithm of the first argument             |
| LOG10()    | Return the base-10 logarithm of the argument                   |
| LOG2()     | Return the base-2 logarithm of the argument                    |
| MOD()      | Return the remainder                                           |
| PI()       | Return the value of pi                                         |
| POW()      | Return the argument raised to the specified power              |
| POWER()    | Return the argument raised to the specified power              |
| RADIANS()  | Return argument converted to radians                           |
| RAND()     | Return a random floating-point value                           |
| ROUND()    | Round the argument                                             |
| SIGN()     | Return the sign of the argument                                |
| SIN()      | Return the sine of the argument                                |
| SQRT()     | Return the square root of the argument                         |
| TAN()      | Return the tangent of the argument                             |
| TRUNCATE() | Truncate to specified number of decimal places                 |
+------------+----------------------------------------------------------------+
31 rows in set (0.00 sec)

Name	Description
ABS()	Return the absolute value
ACOS()	Return the arc cosine
ASIN()	Return the arc sine
ATAN()	Return the arc tangent
ATAN2(), ATAN()	Return the arc tangent of the two arguments
CEIL()	Return the smallest integer value not less than the argument
CEILING()	Return the smallest integer value not less than the argument
CONV()	Convert numbers between different number bases
COS()	Return the cosine
COT()	Return the cotangent
CRC32()	Compute a cyclic redundancy check value
DEGREES()	Convert radians to degrees
EXP()	Raise to the power of
FLOOR()	Return the largest integer value not greater than the argument
LN()	Return the natural logarithm of the argument
LOG()	Return the natural logarithm of the first argument
LOG10()	Return the base-10 logarithm of the argument
LOG2()	Return the base-2 logarithm of the argument
MOD()	Return the remainder
PI()	Return the value of pi
POW()	Return the argument raised to the specified power
POWER()	Return the argument raised to the specified power
RADIANS()	Return argument converted to radians
RAND()	Return a random floating-point value
ROUND()	Round the argument
SIGN()	Return the sign of the argument
SIN()	Return the sine of the argument
SQRT()	Return the square root of the argument
TAN()	Return the tangent of the argument
TRUNCATE()	Truncate to specified number of decimal places

问题

问题

将 1,2,3,4,5,...,2004,2005 组成一个长整数 x, \[ x=12345\cdots 2002200320042005 \] 求 x 除以 p 后的余数. (即x模p后的值). 这里 p=3, 7, 9, 23.

实验

我们建立一个函数, 名为 modp(), 参数为 p, 返回模 p 的值.

p=3 或 9 时

当 p=3 或 9 时, 由于 $10^k\equiv 1\pmod 9$, 故对于十进制正整数 $n=a_1 a_2\cdots a_m$, $9|n$ 当且仅当 $9|\sum\limits_{i=1}^{m}a_i$.

并且, 若 $n=\overline{a_s b_t}$, 这里 $a_s$ 是 $s$ 位正整数, $b_t$ 是 $t$ 位正整数, 则 $n=a_s\times 10^t+b_t$. 于是 \[ 9|\overline{a_s b_t}\quad\Leftrightarrow\quad 9|(a_s+b_t). \]

delimiter //
CREATE FUNCTION modp(p INT)
  RETURNS INT
BEGIN
  DECLARE n INT DEFAULT 1;
  DECLARE sum INT DEFAULT 0;
L:LOOP
  IF n>2005 THEN LEAVE L;
  END IF;
  SET sum=sum+n;
  SET n=n+1;
  END LOOP;
  RETURN sum % p;
END//

mysql> delimiter //
mysql> CREATE FUNCTION modp(p INT)
    ->   RETURNS INT
    -> BEGIN
    ->   DECLARE n INT DEFAULT 1;
    ->   DECLARE sum INT DEFAULT 0;
    -> L:LOOP
    ->   IF n>2005 THEN LEAVE L;
    ->   END IF;
    ->   SET sum=sum+n;
    ->   SET n=n+1;
    ->   END LOOP;
    ->   RETURN sum % p;
    -> END//
Query OK, 0 rows affected (0.04 sec)

mysql> select modp(9);
    -> //
+---------+
| modp(9) |
+---------+
|       1 |
+---------+

p=7时

p=11时

p=23时

End

Thanks very much!