2分钟搞懂Mysql常用函数

1、SUBSTRING 截取
函数从特定位置开始的字符串返回一个给定长度的子字符串。

SUBSTRING(string,position);

有两个参数：
string参数是要提取子字符串的字符串。
position参数是一个整数，用于指定子串的起始字符，position可以是正或负整数。
例：

select substring（content,5） as abstract from my_content_t

如果要指定要从字符串中提取的子字符串的长度，可以使用以下形式的SUBSTRING函数：

SUBSTRING(string,position,length);

有三个参数：
string参数是要提取子字符串的字符串。
position参数是一个整数，用于指定子串的起始字符，position可以是正或负整数。
length参数是一个正整数，用于指定子字符串的字符数
例：

select substring（content,5,200） as abstract from my_content_t

2、left 左截取
从左开始截取字符串
left（str, length）

有两个参数：
string参数是要提取子字符串的字符串。
length参数是一个整数，用于指定子字符串的字符数。
例：

select left（content,200） as abstract from my_content_t 

3、right 右截取
从右开始截取字符串
right（str, length）

有两个参数：
string参数是要提取子字符串的字符串。
length参数是一个整数，用于指定子字符串的字符数。
例：

select right（content,200） as abstract from my_content_t 

4、substring_index 截取
根据关键字截取字符串到指定的出现次数处
substring_index（str,delim,count）

有三个参数：
string参数是被截取字段。
delim参数是关键字。
count参数是关键字出现的次数。
例：

select substring_index（"blog.jb51.net"，"."，2） as abstract from my_content_t

注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束

5、JSON_EXTRACT
返回JSON文档中的数据，该文档从path 参数匹配的文档部分中选择。返回NULL如果任何参数 NULL或没有路径找到文档中的一个值。如果json_doc参数不是有效的JSON文档或任何path参数不是有效的路径表达式，则会发生错误 。
返回值由path参数匹配的所有值组成 。如果这些参数可能返回多个值，则匹配的值将按照与生成它们的路径对应的顺序自动包装为数组。否则，返回值是单个匹配的值。
JSON_EXTRACT(json_doc, path[, path] …)
有两个参数：
json_doc参数是json文档。
path参数是匹配的参数。
例：

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');

用法解析:

参数	含义
‘$.*’	返回全部json
‘$.title’	返回key=”title”的数据
‘$**.text’	返回所有最底层key=”text”的数据
‘$.content[].item1[]’	返回key=content的list的key=item1的list的所有内容

6、REPLACE 替换
字符串替换
REPLACE(str,from_str,to_str)

有三个参数：
str参数是要进行替换的字符串。
from_str参数是被替换的参数。
to_str参数是准备替换成的参数
例：

SELECT REPLACE('www.mysql.com', 'w', 'Ww');

7、LOCATE 出现位置
返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0：
LOCATE(substr,str)

同于：
POSITION(substr IN str)

有两个参数：
substr参数是要查找在str中出现的子串。
str参数是被查找的字符串。
例：

SELECT LOCATE('bar', ‘foobarbar');

LOCATE(substr,str,pos)
返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0 ：
有三个参数：
substr参数是要查找在str中出现的子串。
str参数是被查找的字符串。
pos参数是开始计数的位置
例：

SELECT LOCATE('bar', ‘foobarbar',5); 

8、LENGTH(str) 和 CHAR_LENGTH(str)
length返回字符串str的长度，以字节为单位。 多字节字符计为多个字节。

SELECT length('aaa');           # 3
SELECT length('ɠɠɠ');           # 6
SELECT length('我我我');         # 9

char_length返回字符串str的长度，以字符为单位。 多字节字符算作单个字符。

SELECT char_length('aaa');      # 3
SELECT char_length('ɠɠɠ');      # 3
SELECT char_length('我我我');    # 3

9、having与where的异同
having与where类似，可筛选数据，where后的表达式怎么写，having后面就怎么写
where针对表中的列发挥作用，查询数据，而having针对查询结果中的列发挥作用，筛选数据
having后的判断字段必须是聚合函数返回的结果

where版：select goods_id,goods_name,market_price -shop_price as sheng goods where market_price -shop_price>200;
having版：select goods_id,goods_name,market_price -shop_price as sheng from goods having sheng >200;

10、TIME_TO_SEC将时间转变成秒
返回 time 参数，转换成秒。可以直接在mysql执行

SELECT TIME_TO_SEC('22:23:00'); 结果为 80580

UNIX_TIMESTAMP把时间转换成时间戳格式

SELECT STR_TO_DATE('1494362128'); 结果为 1494362128

11、CONCAT(str1,str2,…) 结果连接
CONCAT(str1,str2,…) ：返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

select CONCAT(product_id,skc) from product; 结果为：6476SKC455656

12、ELT(N,str1,str2,str3,…)返回指定位置参数
ELT(N,str1,str2,str3,…)：返回第一个参数后面的第N个参数。

select ELT(2,65,6,7,7,878); 结果为：6

13、INSTR指定字符串出现位置
INSTR(str,substr)：返回字符串 str 中子字符串substr的第一个出现位置。

select INSTR('str','r'); 结果为：3

14、INSERT(str,pos,len,newstr)字符串替换
INSERT(str,pos,len,newstr)：字符串 str的pos到len长的位置被newstr替换。