Tour of the WordPress Database

Posted on Feb 20, 2015 by Iain #

Here at Delicious Brains we eat, drink and sleep databases. However, we believe that anyone working with WordPress should have some level of knowledge about the database that sits behind a site. So I have put together a description of all the tables and their columns present in the WordPress database, and in doing so learnt a couple of new things myself.

In this tour I will focus on the tables for a standard single site install of WordPress and all of the tables will be referred to using the default prefix of ‘wp_’. However, for security reasons it is recommended to use a different prefix when installing your WordPress sites.

The Tables

• wp_posts
• wp_postmeta
• wp_comments
• wp_commentmeta
• wp_terms
• wp_term_taxonomy
• wp_term_relationships
• wp_users
• wp_usermeta
• wp_options
• wp_links

wp_posts

The posts table is arguably the most important table in the database. Its name sometimes throws people who believe it purely contains their blog posts. However, albeit badly named, it is an extremely powerful table that stores various types of content including posts, pages, menu items, media attachments and any custom post types that a site uses.

The table’s flexible content nature is provided by the ‘post_type’ column which denotes if the row is a post, page, attachment, nav_menu_item or another type. But this flexibility also makes it hard to discuss and describe. Essentially the table contains rows of content objects with different types, but for ease of reading, I will refer to the rows as “posts” throughout this article.

• ID – unique number assigned to each post.
• post_author – the user ID who created it. (Reference to the wp_users table.)
• post_date – time and date of creation.
• post_date_gmt – GMT time and date of creation. The GMT time and date is stored so there is no dependency on a site’s timezone in the future.
• post_content – holds all the content for the post, including HTML, shortcodes and other content.
• post_title – title of the post.
• post_excerpt – custom intro or short version of the content.
• post_status – status of the post, e.g. ‘draft’, ‘pending’, ‘private’, ‘publish’. Also a great WordPress news site.
• comment_status – if comments are allowed.
• ping_status – if the post allows ping and trackbacks.
• post_password – optional password used to view the post.
• post_name – URL friendly slug of the post title.
• to_ping – a list of URLs WordPress should send pingbacks to when updated.
• pinged – a list of URLs WordPress has sent pingbacks to when updated.
• post_modified – time and date of last modification.
• post_modified_gmt – GMT time and date of last modification.
• post_content_filtered – used by plugins to cache a version of post_content typically passed through the ‘the_content’ filter. Not used by WordPress core itself.
• post_parent – used to create a relationship between this post and another when this post is a revision, attachment or another type.
• guid – Global Unique Identifier, the permanent URL to the post, not the permalink version.
• menu_order – holds the display number for pages and other non-post types.
• post_type – the content type identifier.
• post_mime_type – only used for attachments, the MIME type of the uploaded file.
• comment_count – total number of comments, pingbacks and trackbacks.

wp_postmeta

This table holds any extra information about individual posts. It is a vertical table using key/value pairs to store its data, a technique WordPress employs on a number of tables throughout the database allowing WordPress core, plugins and themes to store unlimited data.

• meta_id – unique number assigned to each row of the table.
• post_id – the ID of the post the data relates to. (Reference to the wp_posts table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_comments

Any post that allows discussion can have comments posted to it. This table stores those comments and some specific data about them. Further information can be stored in wp_commentmeta.

• comment_ID – unique number assigned to each comment.
• comment_post_ID – ID of the post this comment relates to. (Reference to the wp_posts table.)
• comment_author – Name of the comment author.
• comment_author_email – Email of the comment author.
• comment_author_url – URL for the comment author.
• comment_author_IP – IP Address of the comment author.
• comment_date – Time and data the comment was posted.
• comment_date_gmt – GMT time and data the comment was posted.
• comment_content – the actual comment text.
• comment_karma – unused by WordPress core, can be used by plugins to help manage comments.
• comment_approved – if the comment has been approved.
• comment_agent – where the comment was posted from, eg. browser, operating system etc.
• comment_type – type of comment: comment, pingback or trackback.
• comment_parent – refers to another comment when this comment is a reply.
• user_id – ID of the comment author if they are a registered user on the site. (Reference to the wp_users table.)

wp_commentmeta

This table stores any further information related to a comment.

• meta_id – unique number assigned to each row of the table.
• comment_id – the ID of the post the data relates to. (Reference to the wp_comments table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_terms

Terms are items of a taxonomy used to classify objects. Taxonomy what? WordPress allows items like posts and custom post types to be classified in various ways. For example, when creating a post in WordPress, by default you can add a category and some tags to it. Both ‘Category’ and ‘Tag’ are examples of a taxonomy, basically a way to group things together.

To classify this post (how meta of me) I would give it a category of ‘Guide’ and tags of ‘database’ and ‘mysql’. The category and tags are terms that would be contained in this table.

• term_id – unique number assigned to each term.
• name – the name of the term.
• slug – the URL friendly slug of the name.
• term_group – ability for themes or plugins to group terms together to use aliases. Not populated by WordPress core itself.

wp_term_taxonomy

Following the wp_terms example above, the terms ‘Guide’, ‘database’ and ‘mysql’ that are stored in wp_terms don’t exist yet as a ‘Category’ and as ‘Tags’ unless they are given context. Each term is assigned a taxonomy using this table.

The structure of this table allows you to use the same term across different taxonomies. For example ‘Database’ could be used as a category for posts and as a term of a custom taxonomy for a custom post type (think portfolio_category for portfolio items). The term of Database would exist once in wp_terms, but there would be two rows in wp_term_taxonomy for each taxonomy.

• term_taxonomy_id – unique number assigned to each row of the table.
• term_id – the ID of the related term. (Reference to the wp_terms table.)
• taxonomy – the slug of the taxonomy. This can be the built in taxonomies or any taxonomy registered using register_taxonomy().
• description – description of the term in this taxonomy.
• parent – ID of a parent term. Used for hierarchical taxonomies like Categories.
• count – number of post objects assigned the term for this taxonomy.

wp_term_relationships

So far we have seen how terms and their taxonomies are stored in the database, but have yet to see how WordPress stores the critical data when it comes to using taxonomies. This post exists in wp_posts and when we actually assign the category and tags through the WordPress dashboard this is the junction table that records that information. Each row defines a relationship between a post (object) in wp_posts and a term of a certain taxonomy in wp_term_taxonomy.

• object_id – the ID of the post object. (Reference to the wp_posts table.)
• term_taxonomy_id – the ID of the term / taxonomy pair. (Reference to the wp_term_taxonomy table.)
• term_order – allow ordering of terms for an object, not fully used.

wp_users

WordPress’ user management is one of its strongest features and one that makes it great as an application framework. This table is the driving force behind it.

• ID – unique number assigned to each user.
• user_login – unique username for the user.
• user_pass – hash of the user’s password.
• user_nicename – display name for the user.
• user_email – email address of the user.
• user_url – URL of the user, e.g. website address.
• user_registered – time and date the user registered.
• user_activation_key – used for resetting passwords.
• user_status – was used in Multisite pre WordPress 3.0 to indicate a spam user.
• display_name – desired name to be used publicly in the site, can be user_login, user_nicename, first name or last name defined in wp_usermeta.

wp_usermeta

This table stores any further information related to the users. You will see other user profile fields for a user in the dashboard that are stored here.

• umeta_id – unique number assigned to each row of the table.
• user_id – ID of the related user. (Reference to the wp_users table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_options

The options table is the place where all of the site’s configuration is stored, including data about the theme, active plugins, widgets, and temporary cached data. It is typically where other plugins and themes store their settings.

The table is another example of a vertical key/value pair table to allow it to store all sorts of data for a variety of purposes.

• option_id – unique number assigned to each row of the table.
• option_name – an identifying key for the piece of data.
• option_value – the actual piece of data. The data is often serialized so must be handled carefully.
• autoload – controls if the option is automatically loaded by the function wp_load_alloptions() (puts options into object cache on each page load).

Did you know that when performing migrations of databases using WP Migrate DB Pro you can tell the plugin to preserve specific options in the target database using the ‘wpmdb_preserved_options’ filter?

wp_links

During the rise of popularity of blogging having a blogroll (links to other sites) on your site was very much in fashion. This table holds all those links for you.

Nowadays blogrolls are used less and less and as of WordPress 3.5 the administration of links was removed from the admin UI. The table remains in the database for backwards compatibility and you can use the old link manager UI using this plugin.

• link_id – unique number assigned to each row of the table.
• link_url – URL of the link.
• link_name – name of the link.
• link_image – URL of an image related to the link.
• link_target – the target frame for the link. e.g. _blank, _top, _none.
• link_description – description of the link.
• link_visible – control if the link is public or private.
• link_owner – ID of user who created the link. (Reference to the wp_users table.)
• link_rating – add a rating between 0-10 for the link.
• link_updated – time and date of link update.
• link_rel – relationship of link.
• link_notes – notes about the link.
• link_rss – RSS address for the link.

Someone has produced a helpful entity relationship diagram to explain the relationships between all the tables and posted it on the WordPress codex. This was created at version 3.8 but the structure is still current:

WordPress is great at doing all the heavy lifting for you when it comes to reading and writing to the database, so even though we might know where the data is stored and how to get it, we always recommend using the WordPress APIs wherever possible.

I hope this tour has been helpful and informative. If you are looking for a more detailed description of the database scheme then check out the table details here. Watch out for a follow-up tour of the multisite tables soon!

 

[출처] https://deliciousbrains.com/tour-wordpress-database/