MYSQL FUNCTIONS

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysqladmin -uroot -p reload

reload the mysql server

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
可以用户分页查找

mysql> select * from t limit 5 offset 4;
+----+-------+
| id | name  |
+----+-------+
|  5 | hello |
|  6 | hello |
|  7 | hello |
|  8 | hello |
|  9 | hello |
+----+-------+
5 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
求模
mysql> select mod(29,9);
+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

——————————
mysql> select mod(id,3) ,name from t;
+-----------+-------+
| mod(id,3) | name  |
+-----------+-------+
|         1 | tiger |
|         2 | tiger |
|         0 | hello |
|         1 | hello |
|         2 | hello |
。。。。。。。。。。。
|         0 | hello |
|         1 | hello |
|         2 | hello |
|         0 | hello |
|         1 | hello |
|         2 | hello |
|         0 | hello |
|         1 | hello |
+-----------+-------+
19 rows in set (0.01 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

算术运算
mysql> select 1+3*4 ;
+-------+
| 1+3*4 |
+-------+
|    13 |
+-------+
1 row in set (0.00 sec)

括号

mysql> select (1+3)*4;
+---------+
| (1+3)*4 |
+---------+
|      16 |
+---------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

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

mysql> select 0 = 1;
+-------+
| 0 = 1 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> select 1 is true , 0 is false, null is unknown;
+-----------+------------+-----------------+
| 1 is true | 0 is false | null is unknown |
+-----------+------------+-----------------+
|         1 |          1 |               1 |
+-----------+------------+-----------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
+------------------+------------------+---------------------+
| 1 IS NOT UNKNOWN | 0 IS NOT UNKNOWN | NULL IS NOT UNKNOWN |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

mysql> select 'b' between 'a' and 'c';
+-------------------------+
| 'b' between 'a' and 'c' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select coalesce(null,1);
+------------------+
| coalesce(null,1) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> select coalesce(null,0);
+------------------+
| coalesce(null,0) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select coalesce(null,100);
+--------------------+
| coalesce(null,100) |
+--------------------+
|                100 |
+--------------------+
1 row in set (0.00 sec)

mysql> select coalesce(null,null,100);
+-------------------------+
| coalesce(null,null,100) |
+-------------------------+
|                     100 |
+-------------------------+
1 row in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> select * from t;
+----+-------+
| id | name  |
+----+-------+
|  1 | tiger |
|  2 | tiger |
|  3 | NULL  |
|  4 | hello |
|  5 | hello |
|  6 | NULL  |
|  7 | hello |
|  8 | hello |
|  9 | NULL  |
| 10 | hello |
| 11 | hello |
| 12 | NULL  |
| 13 | hello |
| 14 | hello |
| 15 | NULL  |
| 16 | hello |
| 17 | hello |
| 18 | NULL  |
| 19 | hello |
+----+-------+
19 rows in set (0.00 sec)

mysql> select coalesce(name ,id) from t;
+--------------------+
| coalesce(name ,id) |
+--------------------+
| tiger              |
| tiger              |
| 3                  |
| hello              |
| hello              |
| 6                  |
| hello              |
| hello              |
| 9                  |
| hello              |
| hello              |
| 12                 |
| hello              |
| hello              |
| 15                 |
| hello              |
| hello              |
| 18                 |
| hello              |
+--------------------+
19 rows in set (0.00 sec)

mysql>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select greatest(2,3);
+---------------+
| greatest(2,3) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
mysql> select greatest(2,3,33,4);
+--------------------+
| greatest(2,3,33,4) |
+--------------------+
|                 33 |
+--------------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select 2 in(select id from t) ;
+------------------------+
| 2 in(select id from t) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select 2 in (2,3,4,5);
+----------------+
| 2 in (2,3,4,5) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
mysql> select 2 in (1,3,4,5);
+----------------+
| 2 in (1,3,4,5) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> select isnull(1+1);
+-------------+
| isnull(1+1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select isnull(null);
+--------------+
| isnull(null) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select isnull(name) from t;
+--------------+
| isnull(name) |
+--------------+
|            0 |
|            0 |
|            1 |
|            0 |
|            0 |
|            1 |
|            0 |
|            0 |
|            1 |
|            0 |
|            0 |
|            1 |
|            0 |
|            0 |
|            1 |
|            0 |
|            0 |
|            1 |
|            0 |
+--------------+
19 rows in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> select interval(23,1,15,17,30,44,200);
+--------------------------------+
| interval(23,1,15,17,30,44,200) |
+--------------------------------+
|                              3 |
+--------------------------------+
1 row in set (0.00 sec)

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

mysql> select interval(22,23,30,44,200);
+---------------------------+
| interval(22,23,30,44,200) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INTERVAL(N,N1,N2,N3,...)

Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All

arguments are treated as integers. It is required that N1 < N2 < N3 <

... < Nn for this function to work correctly. This is because a binary

search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select greatest(12,23,33);
+--------------------+
| greatest(12,23,33) |
+--------------------+
|                 33 |
+--------------------+
1 row in set (0.00 sec)

mysql> select least('abc','dd','ff');
+------------------------+
| least('abc','dd','ff') |
+------------------------+
| abc                    |
+------------------------+
1 row in set (0.00 sec)
mysql> select least(2,0);
+------------+
| least(2,0) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

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

mysql> select not 0;
+-------+
| not 0 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

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

mysql>
mysql> select !(1+1);
+--------+
| !(1+1) |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> select !1+1;
+------+
| !1+1 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select id && name from t;
+------------+
| id && name |
+------------+
|          0 |
|          0 |
|       NULL |
|          0 |
|          0 |
|       NULL |
|          0 |
|          0 |
|       NULL |
|          0 |
|          0 |
|       NULL |
|          0 |
|          0 |
|       NULL |
|          0 |
|          0 |
|       NULL |
|          0 |
+------------+
19 rows in set (0.00 sec)

mysql>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql> select id || name from t;
+------------+
| id || name |
+------------+
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
|          1 |
+------------+
19 rows in set (0.00 sec)

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

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

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

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 1 xor null;
+------------+
| 1 xor null |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

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

mysql> select case 1
    -> when 1 then 'one'
    -> when 2 then 'two'
    -> else 'more'
    -> end;
+-------------------------------------------------------------+
| case 1
when 1 then 'one'
when 2 then 'two'
else 'more'
end |
+-------------------------------------------------------------+
| one                                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case when 1>0
    -> then 'true'
    -> else 'false'
    -> end;
+----------------------------------------------+
| case when 1>0
then 'true'
else 'false'
end |
+----------------------------------------------+
| true                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select case binary 'b'
    -> when 'a' then 1 when 'b' then 2 end;
+-----------------------------------------------------+
| case binary 'b'
when 'a' then 1 when 'b' then 2 end |
+-----------------------------------------------------+
|                                                   2 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,name,id/3 ,if(id/3>1,name,'小于') from t ;
+----+-------+--------+------------------------+
| id | name  | id/3   | if(id/3>1,name,'小于') |
+----+-------+--------+------------------------+
|  1 | tiger | 0.3333 | 小于                   |
|  2 | tiger | 0.6667 | 小于                   |
|  3 | NULL  | 1.0000 | 小于                   |
|  4 | hello | 1.3333 | hello                  |
|  5 | hello | 1.6667 | hello                  |
|  6 | NULL  | 2.0000 | NULL                   |
|  7 | hello | 2.3333 | hello                  |
|  8 | hello | 2.6667 | hello                  |
|  9 | NULL  | 3.0000 | NULL                   |
| 10 | hello | 3.3333 | hello                  |
| 11 | hello | 3.6667 | hello                  |
| 12 | NULL  | 4.0000 | NULL                   |
| 13 | hello | 4.3333 | hello                  |
| 14 | hello | 4.6667 | hello                  |
| 15 | NULL  | 5.0000 | NULL                   |
| 16 | hello | 5.3333 | hello                  |
| 17 | hello | 5.6667 | hello                  |
| 18 | NULL  | 6.0000 | NULL                   |
| 19 | hello | 6.3333 | hello                  |
+----+-------+--------+------------------------+
19 rows in set (0.00 sec)

mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no                                    |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(STRCMP('test','test'),'no','yes');
+--------------------------------------+
| IF(STRCMP('test','test'),'no','yes') |
+--------------------------------------+
| yes                                  |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select ifnull(1,0);
+-------------+
| ifnull(1,0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,'not null');
+-------------------------+
| ifnull(null,'not null') |
+-------------------------+
| not null                |
+-------------------------+
1 row in set (0.00 sec)

mysql> select ifnull(1/0,10);
+----------------+
| ifnull(1/0,10) |
+----------------+
|        10.0000 |
+----------------+
1 row in set (0.00 sec)

mysql> select nullif(1,1);
+-------------+
| nullif(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select nullif('chang','chang');
+-------------------------+
| nullif('chang','chang') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select nullif('chang','tiger');
+-------------------------+
| nullif('chang','tiger') |
+-------------------------+
| chang                   |
+-------------------------+
1 row in set (0.00 sec)

mysql>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

12.2. Control Flow Functions
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

The first version returns the result where value=compare-value. The

second version returns the result for the first condition that is true.

If there was no matching result value, the result after ELSE is

returned, or NULL if there is no ELSE part.

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

The default return type of a CASE expression is the compatible

aggregated type of all return values, but also depends on the context

in which it is used. If used in a string context, the result is

returned as a string. If used in a numeric context, then the result is

returned as a decimal, real, or integer value.

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns

expr2; otherwise it returns expr3. IF() returns a numeric or string

value, depending on the context in which it is used.

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

If only one of expr2 or expr3 is explicitly NULL, the result type of

the IF() function is the type of the non-NULL expression.

expr1 is evaluated as an integer value, which means that if you are

testing floating-point or string values, you should do so using a

comparison operation.

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

In the first case shown, IF(0.1) returns 0 because 0.1 is converted to

an integer value, resulting in a test of IF(0). This may not be what

you expect. In the second case, the comparison tests the original

floating-point value to see whether it is non-zero. The result of the

comparison is used as an integer.

The default return type of IF() (which may matter when it is stored

into a temporary table) is calculated as follows:

Expression Return Value
expr2 or expr3 returns a string string
expr2 or expr3 returns a floating-point value floating-point
expr2 or expr3 returns an integer integer

If expr2 and expr3 are both strings, the result is case sensitive if

either string is case sensitive.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns

expr2. IFNULL() returns a numeric or string value, depending on the

context in which it is used.

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

The default result value of IFNULL(expr1,expr2) is the more “general”

of the two expressions, in the order STRING, REAL, or INTEGER. Consider

the case of a table based on expressions or where MySQL must internally

store a value returned by IFNULL() in a temporary table:

CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;

In this example, the type of the test column is CHAR(4).

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is

the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

Note that MySQL evaluates expr1 twice if the arguments are not equal.