Wordpress 的 Table 分析

由於有想要自己用SC設計一個取代(相容) WORDPRESS 的 CMS架站系統，所以，需要研究一下 WordPress 的資料庫結構。

按照功能大致分為五類：
用戶資訊 : wp_users , wp_usermeta
鏈接資訊 : wp_links
文章及評論 : wp_posts , wp_postmeta , wp_comments
分類，鏈接分類，標籤管理 : wp_term , wp_term_relationships , wp_term_taxonomy
全局設置 : wp_options

wp_commentmeta：評論的其他資訊
wp_comments：留言回覆
wp_links：友情鏈接（Blogroll）
wp_options：儲存 WordPress 系統選項和插件、主題配置
wp_postmeta： 存儲文章（包括頁面、上傳文件、修訂）的其他資訊
wp_posts：儲存文章（包括頁面、上傳文件、修訂）
wp_terms：儲存每個目錄、標籤
wp_term_relationships：儲存每個文章、鏈接和對應分類的關係
wp_term_taxonomy：儲存每個目錄、標籤所對應的分類
wp_usermeta：儲存用戶的其他資訊
wp_users：用戶資訊

https://codex.wordpress.org/Database_Description

The following is an outline and description of the database tables created during the standard installation of WordPress. The database structure and the diagram below were last updated in version 4.4.

The only database supported by WordPress is MySQL version 5.0.15 or greater, or any version of MariaDB.

Also see prior versions of Database Descriptions for WordPress 1.5, WordPress 2.0, WordPress 2.2, WordPress 2.3, WordPress 2.5, WordPress 2.7, WordPress 2.8, WordPress 2.9, and WordPress 3.3.

Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're Writing a Plugin however, you may be interested in learning how WordPress stores its data and relationships. If you have already attempted to use the existing WordPress API to access the data you need but have determined it's not possible without accessing the database directly, WordPress provides the wpdb class to make this task easy.

Database Diagram

The diagram below provides a visual overview of the WordPress database and the relations between the tables created during the WordPress standard installation. The Table Overview below includes additional details on the tables and columns.

(WP 4.4.2 Database diagram)

Please note that within the standard installation of WordPress no integrity between the tables is enforced e.g. between posts and comments. If you are creating a plugin or extension that manipulates the WordPress database, your code should do the housekeeping so that no orphan records remain in the tables e.g. by removing records in other tables with a set of SQL commands when foreign keys are deleted (Don't forget to remind users to backup before such operations).

Table Overview

This section is the overview of all the tables created during the WordPress standard installation. It is followed by specific information of what is in each table.

WordPress 4.4 Tables (12)
Table Name	Description	Relevant Area(s) of WordPress User Interface
wp_commentmeta	Each comment features information called the meta data and it is stored in the wp_commentmeta.	Administration > Comments > Comments
wp_comments	The comments within WordPress are stored in the wp_comments table.	Administration > Comments > Comments
wp_links	The wp_links holds information related to the links entered into the Links feature of WordPress. (This feature has been deprecated, but can be re-enabled with the Links Manager plugin.)	Administration > Links > Add New Administration > Links > Links
wp_options	The Options set under the Administration > Settings panel are stored in the wp_options table. See Option Reference for option_name and default values.	Administration > Settings > General Administration > Settings > Writing Administration > Settings > Reading Administration > Settings > Discussion Administration > Settings > Privacy Administration > Settings > Permalinks Administration > Appearance > Widgets
wp_postmeta	Each post features information called the meta data and it is stored in the wp_postmeta. Some plugins may add their own information to this table.	Administration > Posts > Add New Administration > Pages > Add New
wp_posts	The core of the WordPress data is the posts. It is stored in the wp_posts table. Also Pages and navigation menu items are stored in this table.	Administration > Posts > Add New Administration > Posts > Posts Administration > Pages > Add New Administration > Pages > Pages Administration > Media > Add New Administration > Media > Library Administration > Appearance > Menus
wp_terms	The categories for both posts and links and the tags for posts are found within the wp_terms table.	Administration > Posts > Post Tags Administration > Posts > Categories Administration > Links > Link Categories Administration > Posts > Add New Administration > Posts > Posts Administration > Pages > Add New Administration > Page > Pages
wp_termmeta	Each term features information called the meta data and it is stored in wp_termmeta.
wp_term_relationships	Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships table. The association of links to their respective categories are also kept in this table.
wp_term_taxonomy	This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table.
wp_usermeta	Each user features information called the meta data and it is stored in wp_usermeta.	Administration > Users
wp_users	The list of users is maintained in table wp_users.	Administration > Users

Table Details

The following are the specific fields in each of the tables created during the standard WordPress installation.

Table: wp_commentmeta

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned		PRI		auto_increment
comment_id	bigint(20) unsigned		IND	0
meta_key	varchar(255)	YES	IND	NULL
meta_value	longtext	YES		NULL

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	meta_id
comment_id	INDEX	comment_id
meta_key	INDEX	meta_key

Table: wp_comments

Field	Type	Null	Key	Default	Extra
comment_ID	bigint(20) unsigned		PRI		auto_increment
comment_post_ID	bigint(20) unsigned		IND	0
comment_author	tinytext
comment_author_email	varchar(100)		IND
comment_author_url	varchar(200)
comment_author_IP	varchar(100)
comment_date	datetime			0000-00-00 00:00:00
comment_date_gmt	datetime		IND & IND Pt2	0000-00-00 00:00:00
comment_content	text
comment_karma	int(11)			0
comment_approved	varchar(20)		IND Pt1	1
comment_agent	varchar(255)
comment_type	varchar(20)
comment_parent	bigint(20) unsigned		IND	0
user_id	bigint(20) unsigned			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	comment_ID
comment_post_ID	INDEX	comment_post_ID
comment_approved_date_gmt	INDEX	comment_approved comment_date_gmt
comment_date_gmt	INDEX	comment_date_gmt
comment_parent	INDEX	comment_parent
comment_author_email	INDEX	comment_author_email

Table: wp_links

Field	Type	Null	Key	Default	Extra
link_id	bigint(20) unsigned		PRI		auto_increment
link_url	varchar(255)
link_name	varchar(255)
link_image	varchar(255)
link_target	varchar(25)
link_description	varchar(255)
link_visible	varchar(20)		IND	Y
link_owner	bigint(20) unsigned			1
link_rating	int(11)			0
link_updated	datetime			0000-00-00 00:00:00
link_rel	varchar(255)
link_notes	mediumtext
link_rss	varchar(255)

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	link_id
link_visible	INDEX	link_visible

Table: wp_options

Field	Type	Null	Key	Default	Extra
option_id	bigint(20) unsigned		PRI		auto_increment
option_name	varchar(64)		UNI
option_value	longtext
autoload	varchar(20)		IND	yes

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	option_id
option_name	UNIQUE	option_name
autoload	INDEX	autoload

Table: wp_postmeta

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned		PRI		auto_increment
post_id	bigint(20) unsigned		IND	0
meta_key	varchar(255)	YES	IND	NULL
meta_value	longtext	YES		NULL

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	meta_id
post_id	INDEX	post_id
meta_key	INDEX	meta_key

Table: wp_posts

Field	Type	Null	Key	Default	Extra
ID	bigint(20) unsigned		PRI & IND Pt4		auto_increment
post_author	bigint(20) unsigned		IND	0
post_date	datetime		IND Pt3	0000-00-00 00:00:00
post_date_gmt	datetime			0000-00-00 00:00:00
post_content	longtext
post_title	text
post_excerpt	text
post_status	varchar(20)		IND PT2	publish
comment_status	varchar(20)			open
ping_status	varchar(20)			open
post_password	varchar(20)
post_name	varchar(200)		IND
to_ping	text
pinged	text
post_modified	datetime			0000-00-00 00:00:00
post_modified_gmt	datetime			0000-00-00 00:00:00
post_content_filtered	longtext
post_parent	bigint(20) unsigned		IND	0
guid	varchar(255)
menu_order	int(11)			0
post_type	varchar(20)		IND Pt1	post
post_mime_type	varchar(100)
comment_count	bigint(20)			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	ID
post_name	INDEX	post_name
type_status_date	INDEX	post_type post_status post_date ID
post_parent	INDEX	post_parent
post_author	INDEX	post_author

Table: wp_terms

Field	Type	Null	Key	Default	Extra
term_id	bigint(20) unsigned		PRI		auto_increment
name	varchar(200)		IND
slug	varchar(200)		MUL
term_group	bigint(10)			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	term_id
slug	UNIQUE	slug
name	INDEX	name

Table: wp_termmeta

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned		PRI		auto_increment
term_id	bigint(20) unsigned		IND	0
meta_key	varchar(255)	YES	IND	NULL
meta_value	longtext	YES		NULL

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	meta_id
term_id	INDEX	term_id
meta_key	INDEX	meta_key

Table: wp_term_relationships

Field	Type	Null	Key	Default	Extra
object_id	bigint(20) unsigned		PRI Pt1	0
term_taxonomy_id	bigint(20) unsigned		PRI Pt2 & IND	0
term_order	int(11)			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	object_id term_taxonomy_id
term_taxonomy_id	INDEX	term_taxonomy_id

Table: wp_term_taxonomy

Field	Type	Null	Key	Default	Extra
term_taxonomy_id	bigint(20) unsigned		PRI		auto_increment
term_id	bigint(20) unsigned		UNI Pt1	0
taxonomy	varchar(32)		UNI Pt2 & IND
description	longtext
parent	bigint(20) unsigned			0
count	bigint(20)			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	term_taxonomy_id
term_id_taxonomy	UNIQUE	term_id taxonomy
taxonomy	INDEX	taxonomy

Table: wp_usermeta

Field	Type	Null	Key	Default	Extra
umeta_id	bigint(20) unsigned		PRI		auto_increment
user_id	bigint(20) unsigned		IND	0
meta_key	varchar(255)	Yes	IND	NULL
meta_value	longtext	Yes		NULL

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	umeta_id
user_id	INDEX	user_id
meta_key	INDEX	meta_key

Table: wp_users

Field	Type	Null	Key	Default	Extra
ID	bigint(20) unsigned		PRI		auto_increment
user_login	varchar(60)		IND
user_pass	varchar(64)
user_nicename	varchar(50)		IND
user_email	varchar(100)
user_url	varchar(100)
user_registered	datetime			0000-00-00 00:00:00
user_activation_key	varchar(60)
user_status	int(11)			0
display_name	varchar(250)

NOTE: Enabling Multisite feature of WordPress adds two fields in wp_users table: spam and deleted. Refer the Multisite version.

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	ID
user_login_key	INDEX	user_login
user_nicename	INDEX	user_nicename

Multisite Table Overview

This section is the overview of the tables created for use with the Multisite feature of WordPress. These tables are created via the processes under Administration > Tools > Network.

These tables are considered the multisite global tables.

WordPress 3.0 Multisite Tables
Table Name	Description	Relevant Area(s) of WordPress User Interface
wp_blogs	Each site created is stored in the table, wp_blogs.	Administration > Tools > Network Administration > Super Admin > Sites
wp_blog_versions	The current database version status of each site is maintained in the wp_blogs_versions table and is updated as each site is upgraded.	Administration > Super Admin > Update
wp_registration_log	The wp_registration_log records the admin user created when each new site is created.	Administration > Super Admin > Sites
wp_signups	This table holds the user that have registered for a site via the login registration process. User registration is enable in Administration > Super Admin > Options.	Administration > Settings > General
wp_site	The wp_site table contains the main site address.	Administration > Tools > Network
wp_sitemeta	Each site features information called the site data and it is stored in wp_sitemeta. Various option information, including the site admin is kept in this table.	Administration > Super Admin > Options
wp_users	The list of all users is maintained in table wp_users. Multisite add two fields not in the stand-alone version.	Administration > Users Administration > Super Admin > Users Administration > Super Admin > Sites - Edit Site
wp_usermeta	This table is not re-created for multisite, but meta data of users for each site are stored in wp_usermeta.	Administration > Users Administration > Super Admin > Users Administration > Super Admin > Sites - Edit Site
Site Specific Tables	The data of the main site are stored in existing unnumbered tables. The data of additional sites are stored in new numbered tables.	Administration > Super Admin > Sites - Add Site

Multisite Table Details

The following describe the tables and fields created during the network installation. Note that a global set of tables is created upon creation of the network, and site-specific tables are established as each site is created.

Table: wp_blogs

Field	Type	Null	Key	Default	Extra
blog_id	bigint(20)		PRI		auto_increment
site_id	bigint(20)			0
domain	varchar(200)		IND Pt1
path	varchar(100)		IND Pt2
registered	datetime			0000-00-00 00:00:00
last_updated	datetime			0000-00-00 00:00:00
public	tinyint(2)			1
archived	tinyint(2)			0
mature	tinyint(2)			0
spam	tinyint(2)			0
deleted	tinyint(2)			0
lang_id	int(11)		IND	0

NOTE: The blog_id identifies the site and the site_id identifies the network. It is the trace of the past when the 'site' was called as 'blog' in WordPress 2.x. If you add your site in the network, the new record will be inserted to this table wp_blogs with incremented blog_id and the same site_id.

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	blog_id
domain	INDEX	domain path
lang_id	INDEX	lang_id

Table: wp_blog_versions

Field	Type	Null	Key	Default	Extra
blog_id	bigint(20)		PRI	0
db_version	varchar(20)		IND
last_updated	datetime			0000-00-00 00:00:00

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	blog_id
db_version	INDEX	db_version

Table: wp_registration_log

Field	Type	Null	Key	Default	Extra
ID	bigint(20)		PRI		auto_increment
email	varchar(255)
IP	varchar(30)		IND
blog_id	bigint(20)			0
date_registered	datetime			0000-00-00 00:00:00

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	ID
IP	INDEX	IP

Table: wp_signups

Field	Type	Null	Key	Default	Extra
signup_id	bigint(20)		PRI		auto_increment
domain	varchar(200)		IND Pt1
path	varchar(100)		IND Pt2
title	longtext
user_login	varchar(60)		IND Pt1
user_email	varchar(100)		IND & IND Pt2
registered	datetime			0000-00-00 00:00:00
activated	datetime			0000-00-00 00:00:00
active	tinyint(1)
activation_key	varchar(50)		IND
meta	longtext	Yes		NULL

Indexes

Keyname	Type	Field
signup_id	PRIMARY	signup_id
activation_key	INDEX	activation_key
user_email	INDEX	user_email
user_login_email	INDEX	user_login user_email
domain_path	INDEX	domain path

Table: wp_site

Field	Type	Null	Key	Default	Extra
id	bigint(20)		PRI		auto_increment
domain	varchar(200)		IND Pt1
path	varchar(100)		IND Pt2

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	id
domain	INDEX	domain path

Table: wp_sitemeta

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20)		PRI		auto_increment
site_id	bigint(20)			0
meta_key	varchar(255)	Yes	IND	NULL
meta_value	longtext	Yes	IND	NULL

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	meta_id
meta_key	INDEX	meta_key
site_id	INDEX	site_id

Table: wp_users

Field	Type	Null	Key	Default	Extra
ID	bigint(20) unsigned		PRI		auto_increment
user_login	varchar(60)		IND
user_pass	varchar(64)
user_nicename	varchar(50)		IND
user_email	varchar(100)
user_url	varchar(100)
user_registered	datetime			0000-00-00 00:00:00
user_activation_key	varchar(60)
user_status	int(11)			0
display_name	varchar(250)
spam	tinyint(2)			0
deleted	tinyint(2)			0

Indexes

Keyname	Type	Field
PRIMARY	PRIMARY	ID
user_login_key	INDEX	user_login
user_nicename	INDEX	user_nicename

Site Specific Tables

When a new additional site is created, the site-specific tables, similar to the stand-alone tables above, are created. Each set of tables for a site are created with the site ID (blog_id) as part of the table name. These are the tables that would be created for site ID 2 and table_prefix wp_:

• wp_2_commentmeta
• wp_2_comments
• wp_2_links
• wp_2_options
• wp_2_postmeta
• wp_2_posts
• wp_2_terms
• wp_2_term_relationships
• wp_2_term_taxonomy
• wp_2_term_termmeta

The data of the main site are stored in unnumbered tables.

wp_posts

• ID: bigint(20)
• post_author: bigint(20)
• post_date: datetime
• post_content: longtext
• post_title: text
• post_excerpt: text
• post_status: varchar(20)
• comment_status: varchar(20)
• post_password: varchar(255)
• post_name: varchar(200)
• post_modified: datetime
• post_parent: bigint(20)
• guid: varchar(255)
• menu_order: int(11)
• post_type: varchar(20)
• post_mime_type: varchar(100)
• comment_count: bigint(20)

post_status:

1. Publish: Viewable by any site visitor.
2. Future: Scheduled to be published in a future date.
3. Draft: This is an incomplete post that's not ready for publication.
4. Pending: Awaiting a user with higher permissions to publish.
5. Private: Viewable only to WordPress users at the Administrator level.
6. Trash: These posts are waiting for deletion.
7. Auto-Draft: Revisions that WordPress saves automatically while you are editing.
8. Inherit: This allows a child post (such as Attachments and Revisions) to automatically adopt the same status as its parent post.

WordPress Revisions system：修訂版本系統

Auto-Draft: 編輯時，配合修訂版本系統的自動儲存

wp-config.php:

define( 'WP_POST_REVISIONS', 3 );

WP_POST_REVISIONS: 

• true (default), -1: store every revision 
• false, 0: do not store any revisions (except the one autosave per post) 
• (int) > 0: store that many revisions (+1 autosave) per post. Old revisions are automatically deleted.

Post Types：

There are several default Post Types readily available to users or internally used by the WordPress installation. The most common are:

• Post (Post Type: ‘post’)
• Page (Post Type: ‘page’)
• Attachment (Post Type: ‘attachment’)
• Revision (Post Type: ‘revision’)
• Navigation menu (Post Type: ‘nav_menu_item’)
• Block templates (Post Type: ‘wp_template’)
• Template parts (Post Type: ‘wp_template_part’)
• forum: 討論區
• topic: 討論區 -> 主題
• reply: 討論區 -> 主題-> 回覆
• nav_menu_item: 選單

Post:

Posts are used in blogs. They are:

• displayed in reverse sequential order by time, with the newest post first
• have a date and time stamp
• may have the default taxonomies of categories and tags applied
• are used for creating feeds

The template files that display the Post post type are:

• single and single-post
• category and all its iterations
• tag and all its iterations
• taxonomy and all its iterations
• archive and all its iterations
• author and all its iterations
• date and all its iterations
• search
• home
• index

Page：

Pages are a static Post Type, outside of the normal blog stream/feed. Their features are:

• non-time dependent and without a time stamp
• are not organized using the categories and/or tags taxonomies
• can be organized in a hierarchical structure — i.e. pages can be parents/children of other pages

The template files that display the Page post type are:

• page and all its iterations
• front-page
• search
• index

Attachments：

Attachments are commonly used to display images or media in content, and may also be used to link to relevant files. Their features are:

• contain information (such as name or description) about files uploaded through the media upload system
• for images, this includes metadata information stored in the wp_postmeta table (including size, thumbnails, location, etc)

The template files that display the Attachment post type are:

• MIME_type
• attachment
• single-attachment
• single
• index