自定义函数

创建函数

delimiter //
create function `myfunc`(num1 int,num2 int)
returns varchar(20)
begin
    if num1>num2 then
            return "num1 > num2";
        elseif num1<num2 then
            return "num1 < num2";
        else
            return "num1 = num2";
    end if;
    end//
delimiter ;

查看函数信息

mysql> show create function myfunc\G;
*************************** 1. row ***************************
            Function: myfunc
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`%` FUNCTION `myfunc`(num1 int,num2 int) RETURNS varchar(10) CHARSET latin1
begin
    if num1>num2 then
            return "num1 > num2";
        elseif num1<num2 then
            return "num1 < num2";
        else
            return "num1 = num2";
    end if;
    end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

mysql> show function status\G;
*************************** 1. row ***************************
                  Db: test
                Name: myfunc
                Type: FUNCTION
             Definer: root@%
            Modified: 2016-10-27 10:19:46
             Created: 2016-10-27 10:19:46
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

删除函数

drop function `myfunc`;

流程函数

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

mysql> select if(1>2,6,8)
    -> ;
+-------------+
| if(1>2,6,8) |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

mysql> select case 1
    -> when 2 then 'A'
    -> when 1 then 'B'
    -> else 'C'
    -> end;
+-----------------------------------------------------+
| case 1 when 2 then 'A' when 1 then 'B' else 'C' end |
+-----------------------------------------------------+
| B                                                   |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case 
    -> when 1=1 then 'A'
    -> when 2>1 then 'B'
    -> ELSE 'C'
    -> END;
+--------------------------------------------------------+
| case 
when 1=1 then 'A'
when 2>1 then 'B'
ELSE 'C'
END |
+--------------------------------------------------------+
| A                                                      |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql常用函数

字符串函数

对于针对字符串位置的操作，第一个位置被标记为1。

范例

mysql> select concat('111','222','333'),lower('ABCJDKDK'),upper('ADkjkjADD'),length('1223'),insert('abcdefg',2,4,'xy'),length('abc   '),length(trim('abc   ')),replace('abcdefg','abc','xxx'),lpad('abc',10,'xyz'),rpad('abc',10,'xyz'),repeat('abc',5),ascii('abc'),ord('哈哈哈'),length('哈哈哈'),char_length('哈哈哈'),character_length('哈哈哈'),locate('c','abcd'),position('c' in 'abcd'),locate('c','abcde',5),instr('abcde','c'),left('abcde',3),right('abcde',3),substring('abcdef',2,3),substring('abcdef' from 2 for 3),mid('abcdef',2,3),mid('abcdef' from 2 for 3),substring_index('abc,def,ghi.jhi',',',2),substring_index('abc,def,ghi.jhi',',',-2),trim(TRAILING 'xyz' FROM 'barxxyz'),soundex('too'),soundex('to'),length(space(6)),reverse('测试字串'),elt(1,'abc','def'),field('a','b','bac','bg','abc','a'),find_in_set('a','aa,bb'),find_in_set('a','aa,bb,a'),make_set(1|4,'hello','nice','world'),bin(1|4),export_set(5,'Y','N',',',4),bin(5),lcase('ADADasd'),ucase('ASASsdfds'),load_file('/tmp/test.txt')\G;
*************************** 1. row ***************************
                concat('111','222','333'): 111222333
                        lower('ABCJDKDK'): abcjdkdk
                       upper('ADkjkjADD'): ADKJKJADD
                           length('1223'): 4
               insert('abcdefg',2,4,'xy'): axyfg
                         length('abc   '): 6
                   length(trim('abc   ')): 3
           replace('abcdefg','abc','xxx'): xxxdefg
                     lpad('abc',10,'xyz'): xyzxyzxabc
                     rpad('abc',10,'xyz'): abcxyzxyzx
                          repeat('abc',5): abcabcabcabcabc
                             ascii('abc'): 97
                         ord('哈哈哈'): 15045512
                      length('哈哈哈'): 9
                 char_length('哈哈哈'): 3
            character_length('哈哈哈'): 3
                       locate('c','abcd'): 3
                  position('c' in 'abcd'): 3
                    locate('c','abcde',5): 0
                       instr('abcde','c'): 3
                          left('abcde',3): abc
                         right('abcde',3): cde
                  substring('abcdef',2,3): bcd
         substring('abcdef' from 2 for 3): bcd
                        mid('abcdef',2,3): bcd
               mid('abcdef' from 2 for 3): bcd
 substring_index('abc,def,ghi.jhi',',',2): abc,def
substring_index('abc,def,ghi.jhi',',',-2): def,ghi.jhi
      trim(TRAILING 'xyz' FROM 'barxxyz'): barx
                           soundex('too'): T000
                            soundex('to'): T000
                         length(space(6)): 6
                  reverse('测试字串'): 串字试测
                       elt(1,'abc','def'): abc
      field('a','b','bac','bg','abc','a'): 5
                 find_in_set('a','aa,bb'): 0
               find_in_set('a','aa,bb,a'): 3
     make_set(1|4,'hello','nice','world'): hello,world
                                 bin(1|4): 101
              export_set(5,'Y','N',',',4): Y,N,Y,N
                                   bin(5): 101
                         lcase('ADADasd'): adadasd
                       ucase('ASASsdfds'): ASASSDFDS
               load_file('/tmp/test.txt'): 111111111111111111111111111111111111111111111111111111111111111111

数字函数

日期函数

%W 星期名字(Sunday……Saturday)
　　%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
　　%Y 年, 数字, 4 位
　　%y 年, 数字, 2 位
　　%a 缩写的星期名字(Sun……Sat)
　　%d 月份中的天数, 数字(00……31)
　　%e 月份中的天数, 数字(0……31)
　　%m 月, 数字(01……12)
　　%c 月, 数字(1……12)
　　%b 缩写的月份名字(Jan……Dec)
　　%j 一年中的天数(001……366)
　　%H 小时(00……23)
　　%k 小时(0……23)
　　%h 小时(01……12)
　　%I 小时(01……12)
　　%l 小时(1……12)
　　%i 分钟, 数字(00……59)
　　%r 时间,12 小时(hh:mm:ss [AP]M)
　　%T 时间,24 小时(hh:mm:ss)
　　%S 秒(00……59)
　　%s 秒(00……59)
　　%p AM或PM
　　%w 一个星期中的天数(0=Sunday ……6=Saturday ）
　　%U 星期(0……52), 这里星期天是星期的第一天
　　%u 星期(0……52), 这里星期一是星期的第一天
　　%% 一个文字“%”。
所有的其他字符不做解释被复制到结果中。

范例如下

mysql> select dayofweek('2016-10-26'),weekday('2016-10-26'),dayofmonth('2016-10-26'),dayofyear('2016-10-26'),month('2016-10-26'),dayname('2016-10-26'),monthname('2016-10-26'),quarter('2016-10-26'),week(20161026),year(20161026),hour('11:12:13'),minute(111213),second(111213),period_add(1610,2),period_diff(1610,1612),to_days(20161026),from_days(736628),date_format(now(),'%Y'),date_format(now(),'%T'),curdate()+0,current_date,curtime(),current_time+0,now(),now()+0,sysdate(),sysdate()+0,current_timestamp,current_timestamp+0,unix_timestamp(now()),from_unixtime(unix_timestamp()),from_unixtime(unix_timestamp())+0,sec_to_time(61),sec_to_time(61)+0,time_to_sec('00:01:01')\G;
*************************** 1. row ***************************
          dayofweek('2016-10-26'): 4
            weekday('2016-10-26'): 2
         dayofmonth('2016-10-26'): 26
          dayofyear('2016-10-26'): 300
              month('2016-10-26'): 10
            dayname('2016-10-26'): Wednesday
          monthname('2016-10-26'): October
            quarter('2016-10-26'): 4
                   week(20161026): 43
                   year(20161026): 2016
                 hour('11:12:13'): 11
                   minute(111213): 12
                   second(111213): 13
               period_add(1610,2): 201612
           period_diff(1610,1612): -2
                to_days(20161026): 736628
                from_days(736628): 2016-10-26
          date_format(now(),'%Y'): 2016
          date_format(now(),'%T'): 17:11:06
                      curdate()+0: 20161026
                     current_date: 2016-10-26
                        curtime(): 17:11:06
                   current_time+0: 171106
                            now(): 2016-10-26 17:11:06
                          now()+0: 20161026171106
                        sysdate(): 2016-10-26 17:11:06
                      sysdate()+0: 20161026171106
                current_timestamp: 2016-10-26 17:11:06
              current_timestamp+0: 20161026171106
            unix_timestamp(now()): 1477473066
  from_unixtime(unix_timestamp()): 2016-10-26 17:11:06
from_unixtime(unix_timestamp())+0: 20161026171106
                  sec_to_time(61): 00:01:01
                sec_to_time(61)+0: 101
          time_to_sec('00:01:01'): 61

其他函数

例子

mysql> select version(),database(),user(),md5('123456'),inet_aton('192.168.1.62'),inet_ntoa(3232235838),encode('123456','888'),decode(encode('123456','888'),'888')\G;
*************************** 1. row ***************************
                           version(): 5.6.33
                          database(): NULL
                              user(): root@localhost
                       md5('123456'): e10adc3949ba59abbe56e057f20f883e
           inet_aton('192.168.1.62'): 3232235838
               inet_ntoa(3232235838): 192.168.1.62
              encode('123456','888'): ??? ??
decode(encode('123456','888'),'888'): 123456

运算符

基本运算符

注意除以0和null的时候都返回null

mysql> select 1+1,1-1,1*2,1/2,1%2,1/0,1/null,1*0;
+-----+-----+-----+--------+------+------+--------+-----+
| 1+1 | 1-1 | 1*2 | 1/2    | 1%2  | 1/0  | 1/null | 1*0 |
+-----+-----+-----+--------+------+------+--------+-----+
|   2 |   0 |   2 | 0.5000 |    1 | NULL |   NULL |   0 |
+-----+-----+-----+--------+------+------+--------+-----+
1 row in set (0.00 sec)

比较运算符

当表达式中有null时，会返回null,<=>可以用来判断null

mysql> select 1=1,1='1',1!=1,1=null,1<=>null,null=null,1!=2,1<>2,1<=2,1>=2;
+-----+-------+------+--------+----------+-----------+------+------+------+------+
| 1=1 | 1='1' | 1!=1 | 1=null | 1<=>null | null=null | 1!=2 | 1<>2 | 1<=2 | 1>=2 |
+-----+-------+------+--------+----------+-----------+------+------+------+------+
|   1 |     1 |    0 |   NULL |        0 |      NULL |    1 |    1 |    1 |    0 |
+-----+-------+------+--------+----------+-----------+------+------+------+------+
1 row in set (0.00 sec)

空值运算符

mysql> select isnull(null),null is null,1 isnotnull,null is not null;
+--------------+--------------+-----------+------------------+
| isnull(null) | null is null | isnotnull | null is not null |
+--------------+--------------+-----------+------------------+
|            1 |            1 |         1 |                0 |
+--------------+--------------+-----------+------------------+
1 row in set (0.00 sec)

between运算符

mysql> select 2 between 1 and 3,1 between 1 and 3;
+-------------------+-------------------+
| 2 between 1 and 3 | 1 between 1 and 3 |
+-------------------+-------------------+
|                 1 |                 1 |
+-------------------+-------------------+
1 row in set (0.00 sec)

least运算符

mysql> select least(1,2,3,null),least(1,2,3);
+-------------------+--------------+
| least(1,2,3,null) | least(1,2,3) |
+-------------------+--------------+
|              NULL |            1 |
+-------------------+--------------+
1 row in set (0.00 sec)

greatest运算符

mysql> select greatest(1,2,3),greatest(1,2,3,null);
+-----------------+----------------------+
| greatest(1,2,3) | greatest(1,2,3,null) |
+-----------------+----------------------+
|               3 |                 NULL |
+-----------------+----------------------+
1 row in set (0.00 sec)

in和not in运算符

mysql> select 1 in (1,2,3),null in (1,2,3,null),1 not in (1,2,3);
+--------------+----------------------+------------------+
| 1 in (1,2,3) | null in (1,2,3,null) | 1 not in (1,2,3) |
+--------------+----------------------+------------------+
|            1 |                 NULL |                0 |
+--------------+----------------------+------------------+
1 row in set (0.00 sec)

like运算符

%代表任意个字符，_代表单个字符

mysql> select 'abc' like 'abc','abcd' like 'abc','abcde' like 'abc%','abcde' like 'abc_','abcde' like 'abcd_';
+------------------+-------------------+---------------------+---------------------+----------------------+
| 'abc' like 'abc' | 'abcd' like 'abc' | 'abcde' like 'abc%' | 'abcde' like 'abc_' | 'abcde' like 'abcd_' |
+------------------+-------------------+---------------------+---------------------+----------------------+
|                1 |                 0 |                   1 |                   0 |                    1 |
+------------------+-------------------+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)

regexp运算符

正则表达式

mysql> select 'abc' regexp '^a';
+-------------------+
| 'abc' regexp '^a' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

逻辑运算符

mysql> select 1 and 1,1 and 2,1 and 0,1 or 0,!1,!0,!null,1 xor 1,1 xor 0;
+---------+---------+---------+--------+----+----+-------+---------+---------+
| 1 and 1 | 1 and 2 | 1 and 0 | 1 or 0 | !1 | !0 | !null | 1 xor 1 | 1 xor 0 |
+---------+---------+---------+--------+----+----+-------+---------+---------+
|       1 |       1 |       0 |      1 |  0 |  1 |  NULL |       0 |       1 |
+---------+---------+---------+--------+----+----+-------+---------+---------+
1 row in set (0.00 sec)

位或运算符

mysql> select 10|15;
+-------+
| 10|15 |
+-------+
|    15 |
+-------+
1 row in set (0.00 sec)

位与运算符

mysql> select 10&15;
+-------+
| 10&15 |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

位异或运算符

mysql> select 10^15;
+-------+
| 10^15 |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

位左移运算符

mysql> select 10<<2;
+-------+
| 10<<2 |
+-------+
|    40 |
+-------+
1 row in set (0.00 sec)

位右移运算符

mysql> select 10>>2;
+-------+
| 10>>2 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

位取反运算符

mysql> select ~2;
+----------------------+
| ~2                   |
+----------------------+
| 18446744073709551613 |
+----------------------+
1 row in set (0.00 sec)

1. 编辑 php-fpm 配置文件
   最近在在转移一个discuz站点的时候，打开网站一直处在请求状态，服务器不返回任何东西，也不结束连接，因为是测试服务器，机器的负载也非常低，cpu内存什么的基本上没使用，打开一个只有一句phpinfo函数的页面又是正常的，问题也不知道如何排除，然后打开php-fpm的slowlog来追踪是哪里代码出的问题

修改request_slowlog_timeout,例如:

;10代表达到或者超过10秒的脚本
request_slowlog_timeout=10
;日志路径
slowlog = /tmp/slow.log

1. 重启 php-fpm 即可