一、字符串类。
注：mysql在处理字符串时，字符下标从1开始。
1、concat(string1, string2, ......); //连接字符串
mysql> select concat('leng', 'xue', 'gang') as name;
+-------------+
| name |
+-------------+
| lengxuegang |
+-------------+
1 row in set (0.00 sec)
2、instr(string, substring); //返回substring首次在string中出现的位置，不存在返回0
mysql> select instr('lengxuegang', 'xue');
+-----------------------------+
| instr('lengxuegang', 'xue') |
+-----------------------------+
| 5 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select instr('lengxuegang', 'none');
+------------------------------+
| instr('lengxuegang', 'none') |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
3、lcase(string); //转换为小写
mysql> select lcase('LengxueGang');
+----------------------+
| lcase('LengxueGang') |
+----------------------+
| lengxuegang |
+----------------------+
1 row in set (0.00 sec)
4、left(string, length); //从string左边起取length个字符
mysql> select left('lengxuegang', 4);
+------------------------+
| left('lengxuegang', 4) |
+------------------------+
| leng |
+------------------------+
1 row in set (0.01 sec)
5、length(string); //返回string的长度
mysql> select length('lengxuegang');
+-----------------------+
| length('lengxuegang') |
+-----------------------+
| 11 |
+-----------------------+
1 row in set (0.25 sec)
6、locate(substring, string, [start_position]); //从start_position出开始查找，返回substring在string中首次出现的位置。其功能与instr类似，不过注意string与substring的位置是不一样的。
mysql> select locate('leng', 'lengxueganglengxuegang', 4);
+---------------------------------------------+
| locate('leng', 'lengxueganglengxuegang', 4) |
+---------------------------------------------+
| 12 |
+---------------------------------------------+
1 row in set (0.00 sec)
7、ltrim(string); //去除左边的空格
mysql> select ltrim(' leng');
+------------------+
| ltrim(' leng') |
+------------------+
| leng |
+------------------+
1 row in set (0.00 sec)
8、repeat(string, count); //重复string count次
mysql> select repeat('leng', 4);
+-------------------+
| repeat('leng', 4) |
+-------------------+
| lenglenglengleng |
+-------------------+
1 row in set (0.00 sec)
9、replace(string, search_str, replace_str); //在string中将search_str替换为replace_str
mysql> select replace('lengxueganglengxuegang', 'leng', 'cheng');
+----------------------------------------------------+
| replace('lengxueganglengxuegang', 'leng', 'cheng') |
+----------------------------------------------------+
| chengxuegangchengxuegang |
+----------------------------------------------------+
1 row in set (0.05 sec)
10、rtrim(string); //去除右端空格
mysql> select rtrim('leng ');
+--------------------+
| rtrim('leng ') |
+--------------------+
| leng |
+--------------------+
1 row in set (0.00 sec)
11、strcmp(string1， string2); //比较两个字符串大小，按大小关系分别返回1、0、-1
mysql> select strcmp('leng', 'cheng');
+-------------------------+
| strcmp('leng', 'cheng') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.04 sec)
mysql> select strcmp('cheng', 'leng');
+-------------------------+
| strcmp('cheng', 'leng') |
+-------------------------+
| -1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select strcmp('leng', 'leng');
+------------------------+
| strcmp('leng', 'leng') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
12、substring(string, start_pos, length); //从string的start_pos开始，取length个字符
mysql> select substring('lengxuegang', 5, 3);
+--------------------------------+
| substring('lengxuegang', 5, 3) |
+--------------------------------+
| xue |
+--------------------------------+
1 row in set (0.00 sec)
13、trim(); //去除字符串两端空格
mysql> select trim(' leng ');
+-------------------+
| trim(' leng ') |
+-------------------+
| leng |
+-------------------+
1 row in set (0.00 sec)
14、ucase(string); //转换为大写
mysql> select ucase('lengxuegang');
+----------------------+
| ucase('lengxuegang') |
+----------------------+
| LENGXUEGANG |
+----------------------+
1 row in set (0.00 sec)
15、right(string, length); //取string右边length个字符
mysql> select right('lengxuegang', 4);
+-------------------------+
| right('lengxuegang', 4) |
+-------------------------+
| gang |
+-------------------------+
1 row in set (0.00 sec)
16、space(count); //生成count个空格
mysql> select space(5);
+----------+
| space(5) |
+----------+
| |
+----------+
1 row in set (0.00 sec)
17、lpad(string, length, pad); //在string的左端填充pad，直到其长度达到length
mysql> select lpad('leng', 10, 'dacb');
+--------------------------+
| lpad('leng', 10, 'dacb') |
+--------------------------+
| dacbdaleng |
+--------------------------+
1 row in set (0.00 sec)
18、rpad(); //在string的右端填充pad，直到其长度达到length
mysql> select rpad('leng', 10, 'dacb');
+--------------------------+
| rpad('leng', 10, 'dacb') |
+--------------------------+
| lengdacbda |
+--------------------------+
1 row in set (0.00 sec)
19、coalesce(value1, value2, ...) 返回第一个非null值，如果全为null，则返回null
mysql> select coalesce(null, 1, 2);
+----------------------+
| coalesce(null, 1, 2) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.03 sec)
二、数学类
1、abs(num); //返回绝对值
mysql> select abs(-3.5);
+-----------+
| abs(-3.5) |
+-----------+
| 3.5 |
+-----------+
1 row in set (0.03 sec)
2、bin(decimal_num); //十进制转二进制
mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100 |
+---------+
1 row in set (0.05 sec)
3、ceiling(num); //向上取整
mysql> select ceiling(3.4);
+--------------+
| ceiling(3.4) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> select ceiling(-3.4);
+---------------+
| ceiling(-3.4) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
4、conv(num, from_base,