DedeCMS 批量处理 SQL 实战指南：dede:sql 调用、JOIN 替换、分批 UPDATE 与时间字段同步

DedeCMS 数据上万后批量改标题、改正文、改发布时间必须用 SQL。本文给出 dede:sql 模板调用语法、archives 与 addonarticle 跨表 JOIN 替换、大表分批 UPDATE 防锁表、pubdate/senddate/sortrank 三时间字段同步策略、缩略图自动从正文取首图等实战场景，并补全备份回滚与 MySQL 8 兼容方案。

更新 32 分钟阅读 2,742 阅读

DedeCMS 数据量上万篇之后，所有「批量改标题、批量替换关键词、批量重置发布时间、批量修复 URL」的需求都不可能用后台 UI 完成——必须直接写 SQL。织梦的 dede:sql 标签让模板里就能跑查询，后台「系统-SQL 命令行工具」让你能直接执行任意语句。但很多老教程的代码用的是 dede_archives 这种已经过时的字段（DedeCMS 5.7 之后部分字段拆到 dede_addonarticle 了），跑下去会拿不到数据或者只改一半。本文按场景重新整理：dede:sql 标签的现代用法、批量改正文与标题的 JOIN 写法、备份与回滚、避免锁表的分批 UPDATE、跨字段一致性、SQL 注入防御、与现代 MySQL 8 的兼容。

执行 SQL 前的强制流程

三件必做的事

所有批量 SQL 在生产库上执行前必须完成：

  1. 完整备份数据库：mysqldump 整库 + gzip，保存到非站点目录与异地。具体命令：mysqldump --single-transaction --hex-blob -u root -p dedecms_db | gzip > /backup/dedecms_$(date +%Y%m%d_%H%M%S).sql.gz。
  2. 本地副本演练：把备份恢复到一台测试机，先在测试机跑一遍要执行的 SQL，确认结果符合预期。
  3. 开事务封装：能用 BEGIN/COMMIT 封装的尽量包起来。BEGIN; UPDATE ...; -- 检查影响行数 -- COMMIT 或 ROLLBACK。注意 DDL（ALTER TABLE）不能回滚，只能 mysqldump 兜底。

SQL 命令行工具的位置

DedeCMS 后台「系统-系统设置-SQL 命令行工具」是默认入口。某些二开版本会禁用这个功能（管理员权限不足），可以直接登录服务器用 mysql CLI：

mysql -u dedecms_user -p dedecms_db

命令行执行历史会落到 ~/.mysql_history，事后能溯源。

dede:sql 标签的语法与现代用法

基础语法

{dede:sql sql="SELECT id, title FROM dede_archives WHERE channel=1 ORDER BY id DESC LIMIT 10"}
<a href="/plus/view.php?aid=[field:id /]" target="_blank">[field:title /]</a>
{/dede:sql}

三个关键点：

  • SQL 必须放在 sql="" 的引号内，内容里不能再有相同引号（用 &quot; 或者反引号代替）。
  • 字段必须在 SELECT 列表里出现，模板才能用 [field:xxx /] 引用。
  • WHERE 条件能写但不能用变量，sql 标签不接受动态参数（绕过方法见下）。

动态参数的限制与绕过

原生 dede:sql 不支持模板变量做参数，比如下面这种写法 不工作：

{dede:sql sql="SELECT * FROM dede_archives WHERE typeid={$typeid}"}

绕过方法是改 PHP 模板里直接用 $dsql 对象：

<?php
$typeid = intval($_GET['tid']);
$dsql->SetQuery("SELECT * FROM dede_archives WHERE typeid=$typeid LIMIT 10");
$dsql->Execute();
while ($row = $dsql->GetArray()) {
    echo "<a href='/plus/view.php?aid={$row['id']}'>{$row['title']}</a>";
}
?>

务必 intval 强转参数防止 SQL 注入。

常用查询模板

调用某会员发布的文章

{dede:sql sql="SELECT id, title, pubdate FROM dede_archives WHERE mid=1 AND arcrank=0 ORDER BY id DESC LIMIT 10"}
<li>
    <a href="/plus/view.php?aid=[field:id /]">[field:title /]</a>
    <span>[field:pubdate function="MyDate('Y-m-d', @me)" /]</span>
</li>
{/dede:sql}

多了 arcrank=0 过滤未审核文章，pubdate 字段做日期格式化。

调用最新评论

{dede:sql sql="SELECT f.id, f.aid, f.username, f.msg, a.title FROM dede_feedback f LEFT JOIN dede_archives a ON a.id = f.aid WHERE f.ischeck=1 ORDER BY f.id DESC LIMIT 8"}
<li>
    <a href="/plus/view.php?aid=[field:aid /]">[field:title /]</a>
    <span>[field:username /] : [field:msg function="cn_substr(@me, 30)" /]</span>
</li>
{/dede:sql}

会员积分排行

{dede:sql sql="SELECT mid, userid, uname, scores FROM dede_member WHERE matt=0 ORDER BY scores DESC LIMIT 10"}
<dd>
    <a href="/member/?[field:userid/]">[field:uname /]</a>
    积分：[field:scores /]
</dd>
{/dede:sql}

分类统计

{dede:sql sql="SELECT typeid, typename, COUNT(*) AS cnt FROM dede_archives a INNER JOIN dede_arctype t ON t.id=a.typeid WHERE arcrank=0 GROUP BY typeid ORDER BY cnt DESC LIMIT 5"}
<li>[field:typename /] ([field:cnt /] 篇)</li>
{/dede:sql}

常用统计聚合

{dede:sql sql="SELECT COUNT(*) AS c FROM dede_archives WHERE channel=1 AND arcrank=0"}已发表文章：[field:c /] 篇{/dede:sql}
{dede:sql sql="SELECT COUNT(*) AS c FROM dede_archives WHERE channel=2 AND arcrank=0"}图集：[field:c /] 个{/dede:sql}
{dede:sql sql="SELECT COUNT(*) AS c FROM dede_feedback WHERE ischeck=1"}已审评论：[field:c /] 条{/dede:sql}
{dede:sql sql="SELECT COUNT(*) AS c FROM dede_member"}注册会员：[field:c /] 名{/dede:sql}
{dede:sql sql="SELECT SUM(click) AS c FROM dede_archives"}文章总点击：[field:c /] 次{/dede:sql}
{dede:sql sql="SELECT COUNT(*) AS c FROM dede_archives WHERE pubdate > UNIX_TIMESTAMP(CURDATE())"}今日新增：[field:c /] 篇{/dede:sql}

批量修改类 SQL：标题、正文、描述

DedeCMS 数据存储的拆分模型

DedeCMS 5.7 把文章拆成两张表：

  • dede_archives：标题、关键词、描述、栏目、发布时间、点击量等元信息。
  • dede_addonarticle：正文 body、redirecturl 等大字段。

批量改正文要操作 addonarticle，批量改标题要操作 archives。两表通过 archives.id = addonarticle.aid 关联。

批量替换标题中的关键词

UPDATE dede_archives
SET title = REPLACE(title, '保哥笔记', '张文保博客')
WHERE title LIKE '%保哥笔记%';

WHERE 子句里加 LIKE 限定能减少不必要的全表扫描，特别是 title 没建索引时。

批量替换正文中的关键词

UPDATE dede_addonarticle
SET body = REPLACE(body, '原关键词', '新关键词')
WHERE body LIKE '%原关键词%';

注意 body 是 mediumtext 类型，1600 万字符上限。如果你的替换会让正文长度大幅增加（比如把 5 字短词替换成 50 字长句），先估算字段是否会溢出。

批量替换描述与摘要

UPDATE dede_archives
SET description = REPLACE(description, '原词', '新词')
WHERE description LIKE '%原词%';

跨表条件批量改

「替换正文里包含 SEO 博客的所有文章的标题」需要 JOIN：

UPDATE dede_archives a
INNER JOIN dede_addonarticle ad ON ad.aid = a.id
SET a.title = REPLACE(a.title, '保哥笔记', '张文保博客')
WHERE ad.body LIKE '%SEO博客%';

大表分批 UPDATE 避免锁表

百万行级别的 archives 表上跑 UPDATE 会触发表锁，业务请求几分钟内 502。分批写法：

-- 每批 1000 行，循环执行直到没有匹配
UPDATE dede_archives
SET keywords = ''
WHERE keywords LIKE '%旧词%'
LIMIT 1000;

SQL 命令行工具不能写循环，需要在命令行 mysql 里写 shell 脚本：

while true; do
    AFFECTED=$(mysql -u user -p"pass" dedecms_db -N -e "
        UPDATE dede_archives SET keywords='' WHERE keywords LIKE '%旧词%' LIMIT 1000;
        SELECT ROW_COUNT();
    ")
    if [ "$AFFECTED" = "0" ]; then break; fi
    echo "Updated $AFFECTED rows, sleeping..."
    sleep 1
done

每批之间 sleep 1 秒让其它查询有机会插队，避免长时间持锁。

时间字段的批量重置

三个时间字段的关系

dede_archives 有三个时间字段：

  • pubdate：发布时间，前端按这个排序。
  • senddate：入库时间，作者发文章的实际时间。
  • sortrank：自定义排序权重，多数情况等于 pubdate。

SEO 优化中常需要把老文章的 pubdate 拉新让搜索引擎重新抓取。但要保持 senddate 真实记录历史。

把指定 typeid 文章的 pubdate 重置为今天

UPDATE dede_archives
SET pubdate = UNIX_TIMESTAMP(NOW()),
    sortrank = UNIX_TIMESTAMP(NOW())
WHERE typeid = 5
  AND pubdate < UNIX_TIMESTAMP('2020-01-01');

不要动 senddate，作为历史溯源依据。

批量按比例重新分布发布时间

采集来的文章如果 pubdate 都堆在同一天，前台分页会很丑。用随机时间打散：

UPDATE dede_archives
SET pubdate = UNIX_TIMESTAMP(NOW()) - FLOOR(RAND() * 86400 * 30),
    sortrank = pubdate
WHERE typeid = 5;

这条把 typeid=5 的所有文章 pubdate 随机分布到过去 30 天内。86400 是一天的秒数。

栏目相关批量操作

修改栏目动静态

-- 全部改为静态
UPDATE dede_arctype SET isdefault = 1;

-- 全部改为动态
UPDATE dede_arctype SET isdefault = -1;

-- 仅改某父栏目下的子栏目
UPDATE dede_arctype SET isdefault = -1 WHERE reid = 10;

动态栏目（isdefault=-1）每次访问实时查 DB；静态栏目（isdefault=1）需要在后台「生成-栏目 HTML」后才更新。SEO 角度静态更友好（缓存命中率高），但维护成本高（每次发新文章要重生成）。

批量移动文章到新栏目

-- 把 typeid=10 的所有文章移到 typeid=20
UPDATE dede_archives SET typeid = 20 WHERE typeid = 10;

-- 同时更新 dede_arctiny 这张缓存表
UPDATE dede_arctiny SET typeid = 20 WHERE typeid = 10;

dede_arctiny 是 archives 的精简缓存版（只保留 id、typeid、channel、arcrank 等），生成静态页时用。改 archives 不改 arctiny 会造成「栏目页能看到文章但点进去找不到」。

批量删除空栏目

DELETE FROM dede_arctype
WHERE id NOT IN (SELECT DISTINCT typeid FROM dede_archives)
  AND id NOT IN (SELECT DISTINCT reid FROM dede_arctype WHERE reid > 0);

避免删到当前是其它栏目父级的栏目。

批量处理图片与缩略图

批量替换图片域名

站点搬家或者 CDN 切换后，正文里可能写死了旧域名的图片 URL：

-- 替换 archives 表 litpic（缩略图）字段
UPDATE dede_archives
SET litpic = REPLACE(litpic, 'http://oldcdn.example.com/', 'https://newcdn.example.com/')
WHERE litpic LIKE 'http://oldcdn.example.com/%';

-- 替换 addonarticle 表 body（正文）字段中的图片 URL
UPDATE dede_addonarticle
SET body = REPLACE(body, 'http://oldcdn.example.com/', 'https://newcdn.example.com/')
WHERE body LIKE '%oldcdn.example.com%';

批量给文章配缩略图（自动从正文取首图）

很多老文章 litpic 字段是空的，前台缩略图位显示为占位符。从正文里抓首张 img 地址：

UPDATE dede_archives a
INNER JOIN dede_addonarticle ad ON ad.aid = a.id
SET a.litpic = SUBSTRING_INDEX(SUBSTRING_INDEX(ad.body, 'src="', -1), '"', 1)
WHERE a.litpic = ''
  AND ad.body LIKE '%<img src="%';

SUBSTRING_INDEX 取字符串中 src=" 之后的部分到下一个 " 之前的内容，等于「正文第一张图的 URL」。

评论与垃圾内容批量清理

删除指定 IP 的评论

DELETE FROM dede_feedback WHERE ip = '192.168.1.100';

删除一段时间内的所有未审核评论

DELETE FROM dede_feedback
WHERE ischeck = 0
  AND dtime > UNIX_TIMESTAMP('2023-01-01')
  AND dtime < UNIX_TIMESTAMP('2023-12-31');

清理评论中的常见垃圾词

DELETE FROM dede_feedback
WHERE ischeck = 0
  AND (msg LIKE '%viagra%'
       OR msg LIKE '%cialis%'
       OR msg LIKE '%casino%'
       OR msg LIKE '%http://%'
       OR msg LIKE '%https://%');

未审核评论里出现外链 URL 的，99% 是 spam。

会员相关批量操作

批量删除未激活的注册

DELETE FROM dede_member
WHERE matt = 10  -- 未审核
  AND jointime < UNIX_TIMESTAMP() - 86400 * 30;  -- 注册超过 30 天

批量重置会员密码

不到万不得已不要操作，会让所有用户必须找回密码。如果是清测试库可以：

UPDATE dede_member SET pwd = MD5('reset123');

DedeCMS 密码哈希是直接 MD5(password) 不带 salt，所以可以这么写。

会员积分批量调整

-- 给所有会员加 100 积分
UPDATE dede_member SET scores = scores + 100;

-- 给注册超过 1 年的老用户加 500 积分
UPDATE dede_member
SET scores = scores + 500
WHERE jointime < UNIX_TIMESTAMP() - 86400 * 365;

自定义字段与扩展属性

给文章添加自定义属性

INSERT INTO dede_arcatt (sortid, att, attname) VALUES (9, 'd', '评论');
ALTER TABLE dede_archives MODIFY flag SET('c','h','p','f','s','j','a','b','d') DEFAULT NULL;

这个改动让你能在文章编辑页勾选「评论」属性。

批量给老文章打属性

-- 给点击量超过 10000 的文章打「热门 h」属性
UPDATE dede_archives
SET flag = CONCAT_WS(',', NULLIF(flag, ''), 'h')
WHERE click > 10000
  AND (flag IS NULL OR flag NOT LIKE '%h%');

-- 给最近 7 天文章打「最新 f」属性
UPDATE dede_archives
SET flag = CONCAT_WS(',', NULLIF(flag, ''), 'f')
WHERE pubdate > UNIX_TIMESTAMP() - 86400 * 7
  AND (flag IS NULL OR flag NOT LIKE '%f%');

批量同步与索引重建

修复 dede_arctiny 与 dede_archives 不一致

arctiny 是 archives 的索引缓存，长期使用后可能丢失同步。重建：

TRUNCATE TABLE dede_arctiny;

INSERT INTO dede_arctiny (id, typeid, typeid2, arcrank, channel, senddate, sortrank, mid)
SELECT id, typeid, typeid2, arcrank, channel, senddate, sortrank, mid
FROM dede_archives;

重建栏目文章数缓存

栏目页显示「本栏共 N 篇」如果不准，更新栏目计数：

UPDATE dede_arctype t
LEFT JOIN (
    SELECT typeid, COUNT(*) AS cnt FROM dede_archives WHERE arcrank=0 GROUP BY typeid
) AS s ON s.typeid = t.id
SET t.smalltypes = IFNULL(s.cnt, 0);

SQL 注入防御与安全实践

dede:sql 标签自带的限制

DedeCMS 的 SQL 命令行工具默认禁用了一些危险动作（DROP DATABASE、TRUNCATE 部分系统表）。但 UPDATE / DELETE 是不限制的，所有破坏性命令都能跑。

权限分离

给 DedeCMS 的 MySQL 用户分配最小权限，不要用 root：

GRANT SELECT, INSERT, UPDATE, DELETE ON dedecms_db.* TO 'dedecms_user'@'localhost' IDENTIFIED BY 'strong_password';
REVOKE DROP, ALTER, GRANT OPTION ON dedecms_db.* FROM 'dedecms_user'@'localhost';

这样即便有人 SQL 注入也 DROP 不了表。需要 ALTER TABLE 时切到 root 临时执行再切回去。

审计日志

MySQL 8.0+ 启用 general log 记录所有 SQL：

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

事后排查时能精确知道哪个时间点执行了什么 SQL。生产环境注意 general log 会让性能下降 20%，仅在调查问题时短期开启。

MySQL 8 兼容性

保留字变化

MySQL 8 新增了几十个保留字，如 RANK、ROW、GROUPS。如果你的字段名跟这些撞了，要加反引号：

SELECT `rank` FROM dede_archives;

默认认证插件

MySQL 8 默认 caching_sha2_password，老 PHP 客户端连不上。两种方案：升级 PHP 到 7.4+ 用新的 mysqlnd；或者在 MySQL 8 配置里改回 mysql_native_password：

ALTER USER 'dedecms_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';

UTF8MB4 全面替代 UTF8

MySQL 8 推荐 utf8mb4，老 DedeCMS 表是 utf8。可能在插入 emoji 字符时插不进去。批量转换：

ALTER DATABASE dedecms_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE dede_archives CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 对每张表执行同样的 CONVERT

常见故障

故障 1：UPDATE 后受影响行数为 0

多数是 WHERE 条件没匹配。先用 SELECT 验证：SELECT COUNT(*) FROM dede_archives WHERE 你的条件，看返回数字。

故障 2：UPDATE 报「Lock wait timeout exceeded」

另一个事务正在锁这些行。看进程：SHOW PROCESSLIST; 找到长时间运行的事务，KILL <id> 终止。或者增大 innodb_lock_wait_timeout。

故障 3：执行后前台没生效

DedeCMS 有「文档 HTML」与「栏目 HTML」两层缓存。改完 SQL 必须在后台「生成」菜单里点「更新文档 HTML」与「更新主页 HTML」让缓存重生成。

故障 4：REPLACE 替换后多出乱码

字符集不一致导致。SET NAMES utf8mb4 之后再执行 REPLACE。

故障 5：dede:sql 标签输出空白

三个排查：SQL 在命令行直接跑能否拿到数据；模板里 [field:xxx /] 字段名是不是与 SELECT 列表完全一致；arcrank 过滤是不是把所有文章都筛掉了。

故障 6：批量改完发布时间排序乱了

没同步更新 sortrank。dedeCMS 的排序优先级是 sortrank > pubdate。改 pubdate 时务必同步改 sortrank：UPDATE dede_archives SET pubdate=X, sortrank=X。

常见问题解答

SQL 命令行工具被禁用了怎么办？

多数二开版本会出于安全考虑禁用 SQL 命令行。直接用 phpMyAdmin 或者 SSH 进服务器跑 mysql CLI 都能替代。如果你必须从后台跑，找到 dede/sys_sql_query.php 检查权限校验代码是否被篡改。

批量替换时如何只改正文不改标题？

用具体的 UPDATE 表名+字段：UPDATE dede_addonarticle SET body = REPLACE(body, 'a', 'b') 只改 addonarticle 表的 body 字段。其它字段不会被改。

UPDATE 跑了一半中断了，怎么知道哪些已经改了？

没有简单方法。这就是为什么必须先 mysqldump 备份。如果中断了，先 mysqldump 现状，再 diff 与原始备份，能看出哪些已改。复杂场景建议加自定义 status 字段标记：ALTER TABLE dede_archives ADD COLUMN _migrated TINYINT DEFAULT 0; UPDATE dede_archives SET title=..., _migrated=1 WHERE _migrated=0 LIMIT 1000;

大批量 SQL 执行后数据库变得很慢？

UPDATE 大表后 InnoDB 索引可能碎片化。OPTIMIZE TABLE dede_archives 重建索引。生产库的 OPTIMIZE 会锁表几分钟，建议低峰期执行。

数据库被填满怎么办？

多数是 dede_search_keywords 表（搜索关键词记录）疯涨。SELECT TABLE_NAME, DATA_LENGTH/1024/1024 AS MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dedecms_db' ORDER BY DATA_LENGTH DESC LIMIT 10; 看哪张表占空间。TRUNCATE TABLE dede_search_keywords 清掉历史记录。

能否用 SQL 跨字段批量推送到搜索引擎？

不能直接 SQL 推。但 SQL 可以筛出待推送 URL 清单导出 csv，再用 Python 调百度推送 API：SELECT CONCAT('https://example.com/article/', id, '.html') FROM dede_archives WHERE pubdate > UNIX_TIMESTAMP() - 86400 INTO OUTFILE '/tmp/urls.csv'。

UPDATE 一次改太多行触发 binlog 撑爆磁盘？

分批 UPDATE 是更优方案。或者临时关 binlog：SET sql_log_bin = 0; UPDATE ...; SET sql_log_bin = 1;。注意关闭 binlog 后改的内容不会同步到 slave 与备份系统。

已备份的 mysqldump 怎么验证完整性？

把它恢复到一个空的测试库：mysql -u test_user -p test_db < backup.sql。如果导入过程没报错就是完整的。

SQL 命令行工具支持注释吗？

支持。MySQL 三种注释语法都可以：-- xxx、# xxx、/* xxx */。注释在 SQL 命令行历史中方便事后回看自己的意图。

能不能写存储过程批量处理？

可以。但 DedeCMS 的 SQL 命令行工具多数禁用了 DELIMITER 关键字（必须用它来定义存储过程）。改用命令行 mysql CLI 或者把存储过程定义放到外部 .sql 文件 source 进来。

分享到
QQ 微博 X Facebook LinkedIn Telegram Reddit
标签
版权声明

本文标题：《DedeCMS 批量处理 SQL 实战指南：dede:sql 调用、JOIN 替换、分批 UPDATE 与时间字段同步》

本文链接：https://zhangwenbao.com/dedecms-commonly-used-batch-sql-statements.html

版权声明：本文原创，转载请注明出处和链接。许可协议： CC BY-NC-SA 4.0

继续阅读

← 上一篇

DedeCMS 后台验证码错误终极排查指南：从 GD 库到 sessions_xxx 升级残留全覆盖

下一篇 →

WordPress 分类目录 SEO 自定义 TDK 现代化方案：termmeta API + REST 字段 + Yoast 共存策略
发表评论
分享到微信 或在下方手动填写
支持 Ctrl + Enter 提交
相关文章
更多文章 →