帝国CMS批量提取新闻内容的第一张图片为标题图片的SQL语句

1. 备份数据库：确保数据安全。
2. 执行SQL语句：
   • 主表情况：

     UPDATE [!db.pre!]ecms_news SET titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(newstext, 'src=', -1), '.gif', 1), '"', ''), '.gif') WHERE newstext LIKE '%.gif%' AND titlepic = '';
     UPDATE [!db.pre!]ecms_news SET titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(newstext, 'src=', -1), '.jpg', 1), '"', ''), '.jpg') WHERE newstext LIKE '%.jpg%' AND titlepic = '';
     UPDATE [!db.pre!]ecms_news SET titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(newstext, 'src=', -1), '.png', 1), '"', ''), '.png') WHERE newstext LIKE '%.png%' AND titlepic = '';

   • 副表情况：

     UPDATE [!db.pre!]ecms_news_data_1 a, [!db.pre!]ecms_news b SET b.titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(a.newstext, 'src=', -1), '.gif', 1), '"', ''), '.gif') WHERE a.newstext LIKE '%.gif%' AND b.titlepic = '' AND a.id = b.id;
     UPDATE [!db.pre!]ecms_news_data_1 a, [!db.pre!]ecms_news b SET b.titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(a.newstext, 'src=', -1), '.jpg', 1), '"', ''), '.jpg') WHERE a.newstext LIKE '%.jpg%' AND b.titlepic = '' AND a.id = b.id;
     UPDATE [!db.pre!]ecms_news_data_1 a, [!db.pre!]ecms_news b SET b.titlepic = CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(a.newstext, 'src=', -1), '.png', 1), '"', ''), '.png') WHERE a.newstext LIKE '%.png%' AND b.titlepic = '' AND a.id = b.id;