XZ

Xiao Z's blog

Learn, build, share

WordPress Database Structure and Table Schema Analysis

wordpress database structurewp_posts table schemawp_users table fieldswordpress table relationshipswp_options key-value
Published·Modified·

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

  • wp_commentmeta: Stores metadata for comments.
  • wp_comments: Stores comments.
  • wp_links: Stores blogroll (friendship links).
  • wp_options: Stores WordPress system options and plugin/theme configurations.
  • wp_postmeta: Stores metadata for posts (including pages, attachments, and revisions).
  • wp_posts: Stores posts (including pages, attachments, 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 (classification method) 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, has a relatively independent structure. It uses a key-value storage model, which offers the advantage of easy extensibility, allowing various plugins to 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 end of the table and 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 for the WordPress backend.

The most difficult concept to understand is term, which involves wp_terms, wp_term_relationships, and wp_term_taxonomy. In the WordPress system, common categories include post categories and link categories, but there is also TAG, which is a special classification method. We can even create our own classification methods. WordPress records all categories, classification methods, and their corresponding structures in these three tables. wp_terms records the name and basic information of each category (e.g., "WordPress Development", "WPCEO Plugin"). Here, "category" refers to a broad sense, so each TAG is also considered a "category". wp_term_taxonomy records the classification method to which each category 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 related functions for term in WordPress is relatively clear, so there is no need to dwell on its construction.

Having introduced the functions of each table in the WordPress database above, this article continues to explain the function 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: Comment author.
  • comment_author_email: Comment author's email.
  • comment_author_url: Comment author's URL.
  • comment_author_IP: Comment author's IP.
  • comment_date: Comment date.
  • comment_date_gmt: Comment date (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, used for multi-user blogs (default 0).
  • option_name: Key name.
  • option_value: Key value.
  • autoload: Automatically loaded 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 date.
  • post_date_gmt: Publish date (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 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 taxonomy ID.
  • term_order: Sort order.

wp_term_taxonomy

  • term_taxonomy_id: Taxonomy ID.
  • term_id: Taxonomy (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 Structure and Table Schema Analysis - PHP Bird 666. All rights reserved by the original author.