mysql-lg

最新推荐文章于 2023-09-27 18:14:51 发布

丁典

最新推荐文章于 2023-09-27 18:14:51 发布

阅读量181

点赞数

分类专栏： IT 文章标签： mysql

本文链接：https://blog.csdn.net/a289237642/article/details/103322574

版权

IT 专栏收录该内容

68 篇文章 0 订阅

订阅专栏

# 图片名称
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;

#从一张表查出来插入到另一张表

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;