WordPress数据库结构

近期在学习WordPress插件系统,在此记录以下WordPress的数据库结构。

官方数据库关系图
MariaDB [wp]> show tables;
 +-----------------------+
 | Tables_in_wp          |
 +-----------------------+
 | wp_commentmeta        |
 | wp_comments           |
 | wp_links              |
 | wp_options            |
 | wp_postmeta           |
 | wp_posts              |
 | wp_term_relationships |
 | wp_term_taxonomy      |
 | wp_termmeta           |
 | wp_terms              |
 | wp_usermeta           |
 | wp_users              |
 +-----------------------+
 12 rows in set (0.001 sec)

wp_posts

这个表存放了post、page、revision、attachment、custom_css、customize_changeset、nav_menu_item、wp_block。其中revision、customize_changeset都属于持久化存储(历史记录),可以安全删除。

MariaDB [wp]> desc wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(255)        | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+
23 rows in set (0.004 sec)

wp_postmeta

这张表存储的是所有post的元信息。先看一下表结构:

MariaDB [wp]> desc wp_postmeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

可以看到,这就是一个简单的post-key-value三元存储关系。再看一下都有那些key:

MariaDB [wp]> select distinct meta_key from wp_postmeta;
+-------------------------------------+
| meta_key                            |
+-------------------------------------+
| _wp_page_template                   |
| _edit_lock                          |
| _wp_old_slug                        |
| _menu_item_type                     |
| _menu_item_menu_item_parent         |
| _menu_item_object_id                |
| _menu_item_object                   |
| _menu_item_target                   |
| _menu_item_classes                  |
| _menu_item_xfn                      |
| _menu_item_url                      |
| _wp_old_date                        |
| _wp_attached_file                   |
| _wp_attachment_metadata             |
| _wp_attachment_context              |
| _thumbnail_id                       |
| _wp_attachment_is_custom_background |
| _wp_trash_meta_status               |
| _wp_trash_meta_time                 |
| _edit_last                          |
+-------------------------------------+
20 rows in set (0.001 sec)

可以看到一个_wp_old_slug,这个就是WordPress在改变了permlink后老链接302跳转的根据。在手动删除post后可能会出现孤立的metadata,通过此命令删除:

delete from wp_postmeta where post_id not in (select ID from wp_posts);

wp_terms

这个表存储了wordpress中的tag和categories。

MariaDB [wp]> desc wp_terms;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| term_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(200)        | NO   | MUL |         |                |
| slug       | varchar(200)        | NO   | MUL |         |                |
| term_group | bigint(10)          | NO   |     | 0       |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

wp_termmeta

类似于wp_postmeta

MariaDB [wp]> desc wp_termmeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

wp_term_taxonomy

这个表存储了每一个term的分类(category、nav_menu、post_tag)和父亲以及描述。顺便统计了对应post的个数。对wp_term是多对一。

MariaDB [wp]> desc wp_term_taxonomy;
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_id          | bigint(20) unsigned | NO   | MUL | 0       |                |
| taxonomy         | varchar(32)         | NO   | MUL |         |                |
| description      | longtext            | NO   |     | NULL    |                |
| parent           | bigint(20) unsigned | NO   |     | 0       |                |
| count            | bigint(20)          | NO   |     | 0       |                |
+------------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

wp_term_relationships

这个表存储的是wp_term_taxonomy和wp_posts的多对多关系。

MariaDB [wp]> desc wp_term_relationships;
+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id        | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_order       | int(11)             | NO   |     | 0       |       |
+------------------+---------------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

总结

WordPress作为最负盛名的CMS,其数据库的结构十分精简清晰,具有示范性。对于其中的一些冗余信息如历史记录等,可以通过数据库操作简单删除。

Leave a Reply

Your email address will not be published. Required fields are marked *