Mysql 常用函数 - Wangxiaoman/tech-note GitHub Wiki

字符串中包含某字符的数量

select (length(cover_url) - length(REPLACE (cover_url, ',', '')))  from table;

时间处理函数

select FROM_UNIXTIME(1340553600);
select UNIX_TIMESTAMP("2012-6-25 0:00:00");
select DATE_FORMAT(now(),'%Y-%m-%d’);
select DATE_SUB(now(),INTERVAL 1 DAY);

字符串

  • 截取
substring_index(str,delim,n)   
参数说明： str：源字符串，delim：分割符，n：第几个，返回第n个分隔符之前的字符串

select substring_index( substring_index('http://www.ebrun.com/20180301/266035.shtml', '.shtml', 1),'/',-1);
  • 拼接
select GROUP_CONCAT(id) from scene where show_type=1 and plug_site_id !='';

json处理

— string to json
SELECT CAST('[1,2,3]' as JSON) ;  
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

— get value
SELECT JSON_EXTRACT('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}','$.opening');

— e.g
select scene_id,help from (SELECT JSON_EXTRACT(JSON_EXTRACT(CAST( render_json as JSON),'$.articleHelper'),'$.helperPositions') as help,scene_id from scene_render ) as temp where temp.help like '%readingCount%’;

导入导出 （mysqldump）

mysqldump -hlocalhost -uuserName -ppassword -Pport database tableName > table.sql
source /path/s.sql