WordPress Database and Table Structure Analysis

The default WordPress installation includes the following 11 tables. The table prefix "wp_" is added by default.

1. wp_commentmeta: Stores metadata for comments.
2. wp_comments: Stores comments.
3. wp_links: Stores blogroll links.
4. wp_options: Stores WordPress system options, plugin, and theme configurations.
5. wp_postmeta: Stores metadata for posts, including pages, uploaded files, and revisions.
6. wp_posts: Stores posts, including pages, uploaded files, and revisions.
7. wp_terms: Stores categories and tags.
8. wp_term_relationships: Stores relationships between posts, links, and corresponding categories.
9. wp_term_taxonomy: Stores the taxonomy (category or tag) for each category or tag.
10. wp_usermeta: Stores metadata for users.
11. wp_users: Stores users.

In the WordPress database structure, the wp_options table, which stores system options and plugin configurations, is a relatively independent structure. It uses a key-value pattern for storage, which allows for easy expansion. Each plugin can easily store its own configuration here.

For the post, comment, and user tables, they are a combination of three basic tables and extension tables. Taking wp_users as an example, wp_users already stores basic information for each user, such as login_name, display_name, password, email, etc. However, if we need to store some less frequently used data, the best practice is not to add a column to the table and disrupt the default table structure. Instead, we can store the data in wp_usermeta. This extension table has a similar structure to wp_options, and we can store each user's QQ number, phone number, theme options for logging in to the WordPress backend, and so on.

The term, which includes wp_terms, wp_term_relationships, and wp_term_taxonomy, can be a bit difficult to understand. In the WordPress system, we commonly have categories for posts and links. In fact, there is also tags, which is a special category. We can even create our own category methods. WordPress records all categories, category methods, and corresponding structures in these three tables. wp_terms records the name and basic information of each category, such as "WordPress Development" and "WPCEO Plugin" on this site. Here, categories refer to the broad sense of categories, so each tag is also a "category". wp_term_taxonomy records the category method to which each category belongs, such as "WordPress Development" and "WPCEO Plugin" being article categories (category), while the "My Friends" and "My Colleagues" categories of the friendship link belong to the friendship link category (link_category). wp_term_relationships records the category method corresponding to each post (or link).

Fortunately, regarding the use of terms, the usage of related functions in WordPress is relatively clear, so we don't need to worry too much about their construction.

In the previous section, we have introduced the functions of each table in the WordPress database. This article will continue to introduce the functions of each column in each table. The WordPress official documentation already has a detailed table. This article only introduces commonly used data.

wp_commentmeta

• meta_id: Auto-increment unique ID.
• comment_id: Corresponding comment ID.
• meta_key: Key name.
• meta_value: Key value.

wp_comments

• comment_ID: Auto-increment unique ID.
• comment_post_ID: Corresponding post ID.
• comment_author: Comment author.
• comment_author_email: Comment author's email.
• comment_author_url: Comment author's website.
• comment_author_IP: Comment author's IP.
• comment_date: Comment date.
• comment_date_gmt: Comment date (GMT+0 time).
• comment_content: Comment content.
• comment_karma: Unknown.
• comment_approved: Whether the comment is approved.
• comment_agent: Comment author's user agent.
• comment_type: Comment type (pingback/normal).
• comment_parent: Parent comment ID.
• user_id: Comment author's user ID (may not exist).

wp_links

• link_id: Auto-increment unique ID.
• link_url: Link URL.
• link_name: Link title.
• link_image: Link image.
• link_target: Link open method.
• link_description: Link description.
• link_visible: Whether it is visible (Y/N).
• link_owner: Adding user's ID.
• link_rating: Rating level.
• link_updated: Unknown.
• link_rel: XFN relationship.
• link_notes: XFN notes.
• link_rss: Link RSS address.

wp_options

• option_id: Auto-increment unique ID.
• blog_id: Blog ID, used for multi-user blogs, default is 0.
• option_name: Key name.
• option_value: Key value.
• autoload: Automatically load on WordPress load (yes/no).

wp_postmeta

• meta_id: Auto-increment unique ID.
• post_id: Corresponding post ID.
• meta_key: Key name.
• meta_value: Key value.

wp_posts

• ID: Auto-increment unique ID.
• post_author: Corresponding author ID.
• post_date: Publication date.
• post_date_gmt: Publication date (GMT+0 time).
• post_content: Content.
• post_title: Title.
• post_excerpt: Excerpt.
• post_status: Post status (publish/auto-draft/inherit, etc.).
• comment_status: Comment status (open/closed).
• ping_status: PING status (open/closed).
• post_password: Post password.
• post_name: Post slug.
• to_ping: Unknown.
• pinged: Links that have been PINGed.
• post_modified: Modification time.
• post_modified_gmt: Modification time (GMT+0 time).
• post_content_filtered: Unknown.
• post_parent: Parent post, mainly used for pages.
• guid: Unknown.
• menu_order: Sorting ID.
• post_type: Post type (post/page, etc.).
• post_mime_type: MIME type.
• comment_count: Total number of comments.

wp_terms

• term_id: Category ID.
• name: Category name.
• slug: Slug.
• term_group: Unknown.

wp_term_relationships

• object_id: Corresponding post ID/link ID.
• term_taxonomy_id: Corresponding category method ID.
• term_order: Sorting order.

wp_term_taxonomy

• term_taxonomy_id: Category method ID.
• term_id: taxonomy: Category method (category/post_tag).
• description: Unknown.
• parent: Parent category method ID.
• count: Article count statistics.

wp_usermeta

• umeta_id: Auto-increment unique ID.
• user_id: Corresponding user ID.
• meta_key: Key name.
• meta_value: Key value.

wp_users

• ID: Auto-increment unique ID.
• user_login: Login name.
• user_pass: Password.
• user_nicename: Nickname.
• user_email: Email.
• user_url: Website.
• user_registered: Registration time.
• user_activation_key: Activation code.
• user_status: User status.
• display_name: Display name.

Original article from: WordPress数据库及各表结构分析 - PHP飞鸟666. All rights reserved to the original author. If there is any infringement, please contact QQ: 337003006 for deletion.