XZ

Xiao Z's blog

Learn, build, share

WordPress Database and Table Structure Analysis

wordpress database structurewp_posts table schemawp_users table columnswordpress meta tableswp_options key-value
Published·Modified·

By default, WordPress includes the following 11 tables, prefixed with wp_:

  • wp_commentmeta: Stores metadata for comments.
  • wp_comments: Stores comments.
  • wp_links: Stores blogroll links.
  • wp_options: Stores WordPress system options, plugin settings, and theme configurations.
  • wp_postmeta: Stores metadata for posts (including pages, uploaded files, and revisions).
  • wp_posts: Stores posts (including pages, uploaded files, and revisions).
  • wp_terms: Stores categories and tags.
  • wp_term_relationships: Stores relationships between posts, links, and their corresponding categories.
  • wp_term_taxonomy: Stores the taxonomy for each category and tag.
  • wp_usermeta: Stores user metadata.
  • wp_users: Stores user accounts.

In the WordPress database structure, the wp_options table, which stores system options and plugin configurations, is relatively independent. As mentioned later, it uses a key-value storage model. The benefit of this approach is extensibility; plugins can easily store their own configurations here.

The post, comment, and user tables represent a combination of basic tables and extended tables. Taking wp_users as an example, it stores basic information used by each user, such as login_name, display_name, password, email, etc. However, if we need to store less common data, the best practice is not to add a new column to the table, which would disrupt the default structure, but to store the data in wp_usermeta. This extended table has a structure similar to wp_options, allowing us to store data like each user's QQ number, mobile phone number, or theme options in the WordPress backend.

The most difficult concept to understand is term, involving wp_terms, wp_term_relationships, and wp_term_taxonomy. In the WordPress system, common categories include post categories and link categories, but there are also TAGs, which are a special type of classification. We can even create our own classification methods. WordPress records all classifications, classification methods, and their corresponding structures in these three tables. wp_terms records the name and basic information of each classification (e.g., "WordPress Development", "WPCEO Plugin"). Here, "classification" refers to a broad sense, so each TAG is also considered a "classification". wp_term_taxonomy records the classification method to which each classification belongs (e.g., "WordPress Development" and "WPCEO Plugin" are post categories, while "My Friends" and "My Colleagues" for links belong to link categories). wp_term_relationships records the classification method corresponding to each post (or link).

Fortunately, the usage of functions related to term in WordPress is relatively clear, so there is no need to dwell on its construction.

In the previous section, we introduced the functions of each table in the WordPress database. This article continues to explain the role of each column in every table. While the official WordPress documentation provides detailed tables, this article focuses on 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: Commenter.
  • comment_author_email: Commenter's email.
  • comment_author_url: Commenter's URL.
  • comment_author_IP: Commenter's IP.
  • comment_date: Comment time.
  • comment_date_gmt: Comment time (GMT+0).
  • comment_content: Comment content.
  • comment_karma: Unknown.
  • comment_approved: Whether the comment is approved.
  • comment_agent: Commenter's User Agent.
  • comment_type: Comment type (pingback/normal).
  • comment_parent: Parent comment ID.
  • user_id: Commenter'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 opening method.
  • link_description: Link description.
  • link_visible: Visibility (Y/N).
  • link_owner: User ID of the adder.
  • 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 (for multisite, default 0).
  • option_name: Key name.
  • option_value: Key value.
  • autoload: Auto-load when WordPress loads (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: Publish time.
  • post_date_gmt: Publish time (GMT+0).
  • 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).
  • post_content_filtered: Unknown.
  • post_parent: Parent post, mainly used for pages.
  • guid: Unknown.
  • menu_order: Sort ID.
  • post_type: Post type (post/page, etc.).
  • post_mime_type: MIME type.
  • comment_count: Total comment count.

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 taxonomy ID.
  • term_order: Sort order.

wp_term_taxonomy

  • term_taxonomy_id: Taxonomy ID.
  • term_id: ID of the term.
  • taxonomy: Taxonomy type (category/post_tag).
  • description: Unknown.
  • parent: Parent taxonomy ID.
  • count: Post 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: URL.
  • user_registered: Registration time.
  • user_activation_key: Activation key.
  • user_status: User status.
  • display_name: Display name.

Original source: WordPress Database and Table Structure Analysis - PHP Bird 666. All rights reserved by the original author. If there is any infringement, please contact QQ: 337003006 for deletion.