MySQL 内置函数
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
+------------------------+
| CHAR_LENGTH('klvchen') |
+------------------------+
| 7 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT CHAR_LENGTH('你');
+--------------------+
| CHAR_LENGTH('你') |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
CONCAT(str1,str2,...)
字符串拼接, 如有任何一个参数为NULL ，则返回值为 NULL。
mysql> SELECT CONCAT('kl','v','chen');
+-------------------------+
| CONCAT('kl','v','chen') |
+-------------------------+
| klvchen |
+-------------------------+
1 row in set (0.00 sec)
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）
mysql> SELECT CONCAT_WS('_','chen','wj');
+----------------------------+
| CONCAT_WS('_','chen','wj') |
+----------------------------+
| chen_wj |
+----------------------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT_WS('_','chen',' wj ', NULL, 'hehe');
+--------------------------------------------+
| CONCAT_WS('_','chen',' wj ', NULL, 'hehe') |
+--------------------------------------------+
| chen_ wj _hehe |
+--------------------------------------------+
1 row in set (0.00 sec)
CONV(N,from_base,to_base)
进制转换
mysql> SELECT CONV('a',16,2);
+----------------+
| CONV('a',16,2) |
+----------------+
| 1010 |
+----------------+
1 row in set (0.00 sec)
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
mysql> SELECT FORMAT(1.23,4);
+----------------+
| FORMAT(1.23,4) |
+----------------+
| 1.2300 |
+----------------+
1 row in set (0.00 sec)
INSERT(str,pos,len,newstr)
str 原字符串; pos 要替换位置其实位置(从1开始); len：替换的长度；newstr：需要替换的符串
简洁概况为：先从 pos 处删除 len 长度的字符串，再由新的字符串代替
mysql> SELECT INSERT('klvchen', 1, 1, 'j');
+------------------------------+
| INSERT('klvchen', 1, 1, 'j') |
+------------------------------+
| jlvchen |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT INSERT('klvchen', 1, 1, 'ja');
+-------------------------------+
| INSERT('klvchen', 1, 1, 'ja') |
+-------------------------------+
| jalvchen |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT INSERT('klvchen', 1, 2, 'a');
+------------------------------+
| INSERT('klvchen', 1, 2, 'a') |
+------------------------------+
| avchen |
+------------------------------+
1 row in set (0.00 sec)
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
mysql> SELECT INSTR('klvchen','l');
+----------------------+
| INSTR('klvchen','l') |
+----------------------+
| 2 |
+----------------------+
1 row in set (0.00 sec)
LOCATE(substr,str,pos)
获取子序列索引位置
mysql> SELECT LOCATE('l','klvchen',1);
+-------------------------+
| LOCATE('l','klvchen',1) |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.01 sec)
LEFT(str,len)
返回字符串 str 从开始的 len 位置的子序列字符。
mysql> SELECT LEFT('klvchen',2);
+-------------------+
| LEFT('klvchen',2) |
+-------------------+
| kl |
+-------------------+
1 row in set (0.00 sec)
RIGHT(str,len)
从字符串 str 开始，返回从后边开始 len个字符组成的子序列
mysql> SELECT RIGHT('klvchen', 2);
+---------------------+
| RIGHT('klvchen', 2) |
+---------------------+
| en |
+---------------------+
1 row in set (0.00 sec)
LOWER(str)
字符串转化为小写
mysql> SELECT LOWER('KLVCHEN');
+------------------+
| LOWER('KLVCHEN') |
+------------------+
| klvchen |
+------------------+
1 row in set (0.00 sec)
UPPER(str)
字符串转化为大写
mysql> SELECT UPPER('klvchen');
+------------------+
| UPPER('klvchen') |
+------------------+
| KLVCHEN |
+------------------+
1 row in set (0.00 sec)
LTRIM(str)
返回字符串 str ，左边的空格字符被删除。
mysql> SELECT LTRIM(' klvchen');
+--------------------+
| LTRIM(' klvchen') |
+--------------------+
| klvchen |
+--------------------+
1 row in set (0.00 sec)
RTRIM(str)
返回字符串 str ，结尾空格字符被被删除。
mysql> SELECT RTRIM('klvchen ');
+----------------------+
| RTRIM('klvchen ') |
+----------------------+
| klvchen |
+----------------------+
1 row in set (0.00 sec)
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。
mysql> SELECT TRIM(' kl ');
+------------------+
| TRIM(' kl ') |
+------------------+
| kl |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(LEADING 'x' FROM 'xxklxx');
+----------------------------------+
| TRIM(LEADING 'x' FROM 'xxklxx') |
+----------------------------------+
| klxx |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(BOTH 'x' FROM 'xxklxx');
+-------------------------------+
| TRIM(BOTH 'x' FROM 'xxklxx') |
+-------------------------------+
| kl |
+-------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(trailing 'x' FROM 'xxklxx');
+-----------------------------------+
| TRIM(trailing 'x' FROM 'xxklxx') |
+-----------------------------------+
| xxkl |
+-----------------------------------+
1 row in set (0.00 sec)
REPEAT(str,count)
返回一个由重复的字符串 str 组成的字符串，字符串str重复的次数等于count的值 。
若 count <= 0,则返回一个空字符串；若str 或 count 为 NULL，则返回 NULL 。
mysql> SELECT REPEAT('ha',3);
+----------------+
| REPEAT('ha',3) |
+----------------+
| hahaha |
+----------------+
1 row in set (0.00 sec)
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
mysql> SELECT REPLACE('klvchel', 'l', 'i');
+------------------------------+
| REPLACE('klvchel', 'l', 'i') |
+------------------------------+
| kivchei |
+------------------------------+
1 row in set (0.00 sec)
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
mysql> SELECT REVERSE('klvchen');
+--------------------+
| REVERSE('klvchen') |
+--------------------+
| nehcvlk |
+--------------------+
1 row in set (0.00 sec)
SUBSTRING(str,pos,len)
获取字符串子序列
mysql> SELECT SUBSTRING('klvchen', 1, 3);
+----------------------------+
| SUBSTRING('klvchen', 1, 3) |
+----------------------------+
| klv |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('klvchen', 3);
+-------------------------+
| SUBSTRING('klvchen', 3) |
+-------------------------+
| vchen |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('klvchen' FROM 3);
+-----------------------------+
| SUBSTRING('klvchen' FROM 3) |
+-----------------------------+
| vchen |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('klvchen', -3);
+--------------------------+
| SUBSTRING('klvchen', -3) |
+--------------------------+
| hen |
+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('klvchen', -3, 2);
+-----------------------------+
| SUBSTRING('klvchen', -3, 2) |
+-----------------------------+
| he |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING('klvchen' FROM -3 FOR 2);
+------------------------------------+
| SUBSTRING('klvchen' FROM -3 FOR 2) |
+------------------------------------+
| he |
+------------------------------------+
1 row in set (0.01 sec)
