异想天开

What's the true meaning of light, Could you tell me why

开源CMS系统wordpress数据表结构分析

日期:2015-05-17 16:00:30
  
最后更新日期:2016-12-09 13:22:05
本站原先为wordpress,现在只用到了wordpress里面与文章和菜单有关的数据表。这里记录一些权当备忘,如有必要也可以分析其他表。

在后台列出所有wordpress表。
[code lang="cpp"]
mysql> show tables ;
+-------------------------+
| Tables_in_zjwdb_110668 |
+-------------------------+
| blog_commentmeta |
| blog_comments |
| blog_links |
| blog_mobilepress |
| blog_options |
| blog_postmeta |
| blog_posts |
| blog_term_relationships |
| blog_term_taxonomy |
| blog_terms |
| blog_usermeta |
| blog_users |
+-------------------------+
12 rows in set (0.00 sec)
[/code]
某些表是插件创建的,并不是wordpress核心表,比如表blog_mobilepress,是mobilepress主题创建的表。这里仅列举一些关键表的关键字段,可以顾名思义其他表和字段。
1.blog_posts表:
该表为wordpress核心,文章,页面,菜单项等都是存放blog_posts表,用post_type来区分表项。
[code lang="cpp"]
字段值 含义
post 文章
page 页面
revision 文章的自动保存版本
nav_menu_item 菜单的表项
attachment 附加,比如记录上传的图片
optionsframework 框架选项
[/code]
2.blog_term_taxonomy和blog_terms:
wordpress菜单是用两张表表示的,blog_term_taxonomy和blog_terms。blog_terms记录了表项的名字。
[code lang="cpp"]
CREATE TABLE `blog_terms` (
`term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL DEFAULT '',
`slug` varchar(200) NOT NULL DEFAULT '',
`term_group` bigint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`term_id`),
UNIQUE KEY `slug` (`slug`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/code]
slug 缩略名
name 分类名

blog_term_taxonomy表
[code lang="cpp"]
CREATE TABLE `blog_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`taxonomy` varchar(32) NOT NULL DEFAULT '',
`description` longtext NOT NULL,
`parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/code]
字段taxonomy表示分类类型,category(目录),nav_menu(菜单),post_tag(文章标签)

3.blog_term_relationships表
blog_term_taxonomy以及blog_terms通过blog_term_relationships表来与blog_posts串联,可以查找菜单对应的菜单项,某个目录下面的文章,某个tag下的文章。
[code lang="cpp"]
CREATE TABLE `blog_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_order` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
[/code]

用下面的SQL可以列出所有发布的文章:
[code lang="cpp"]
SELECT id,post_title as title,term.name,t.taxonomy as type,r.term_taxonomy_id as tax FROM blog_posts as p LEFT JOIN blog_term_relationships as r ON p.id=r.object_id LEFT JOIN blog_term_taxonomy t ON r.term_taxonomy_id=t.term_taxonomy_id LEFT JOIN blog_terms term ON t.term_id=term.term_id WHERE p.post_status='publish' and p.post_type='post'
[/code]

删除无用的post_tag:
[code lang="cpp"]
1.创建临时表
CREATE TEMPORARY TABLE tmp_table (
id int(11) NOT NULL
)
2.导入id
INSERT INTO tmp_table(id) SELECT r.term_taxonomy_id as id FROM blog_term_taxonomy t LEFT JOIN blog_terms term ON t.term_id=term.term_id LEFT JOIN blog_term_relationships as r ON r.term_taxonomy_id=t.term_taxonomy_id LEFT JOIN blog_posts p ON p.id = r.object_id WHERE ( p.post_status='publish' or p.post_status is null ) and ( p.post_type='post' or p.post_type is null ) and p.post_type is null order by id asc ;
3.删除id
DELETE FROM blog_term_taxonomy WHERE term_taxonomy_id IN (SELECT id FROM tmp_table);
[/code]
其他表的关系简单,忽略。