mysql
# 图片名称 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-1),'.',1) from cv_fastdfs_url_test #时间 -- from_unixtime(timestamp,'%Y-%m-%d %H:%i:%S') -- SELECT SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1) from cv_fastdfs_url_test # 年 SELECT SUBSTR(SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1),1,4) from cv_fastdfs_url_test #月 SELECT SUBSTR(SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1),5,2) from cv_fastdfs_url_test # 日 SELECT SUBSTR(SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1),7,2) from cv_fastdfs_url_test # topic SELECT SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-3)),'/',1) from cv_fastdfs_url_test # 摄像头id SELECT CASE LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1)) WHEN 32 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1) ELSE '' END from cv_fastdfs_url_test;
SELECT SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1) as created_at ,SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-1),'.',1) as pic_name,SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-3)),'/',1) as pic_type, SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-1),'.',1) as pic_name,url_fastdfs,CASE LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1)) WHEN 32 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1) ELSE '' END as camera from cv_fastdfs_url_test;
#从一张表查出来插入到另一张表（mysql 5.7 插入的字段要用 --` `---引起来）
insert into cv_pic_fastdfs_url_dd( `created_at`, `pic_name`, `pic_type`, `url`, `camera` ) SELECT SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-2)),'/',1) as created_at ,SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-1),'.',1) as pic_name,SUBSTRING_INDEX((SUBSTRING_INDEX(url_old,'/',-3)),'/',1) as pic_type, url_fastdfs,CASE LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1)) WHEN 32 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(url_old,'/',-4),'/',-2),'/',-1),'_',1) ELSE '' END as camera from cv_fastdfs_url;