This is the companion blog post to my Introduction to the WordPress Database presentation.
When I first started working with database driven websites, I was afraid of the database. One of the biggest things that helped me was when someone described a database like a spreadsheet (and each table is like a tab in the spreadsheet). I’ve created a spreadsheet version of the WordPress Database to help visualize the database.
Below I’ll go through the 12 tables in the WordPress database. You may also want to checkout the Official WordPress Database Description.
WordPress Database Tables
I’ve grouped the database tables by their functionality.
- Options (settings)
- Users
- Posts
- Comments
- Terms and Taxonomies
- No longer used by WordPress core
Note: All of the database tables listed here start with wp_
, which is the standard WordPress database table prefix. This prefix can vary on different installations. You can read more about the WordPress database prefix.
wp_options
option_id
auto-incrementing unique identifieroption_name
key to be used when looking up this optionoption_value
value set for this optionautoload
used as part of the author URL
option_id | option_name | option_value | autoload |
---|---|---|---|
1 | site_url | https://example.test | yes |
2 | home | https://example.test | yes |
3 | blogname | Sal’s Website | yes |
wp_users
ID
auto-incrementing unique identifieruser_login
used when logging into the siteuser_pass
hash of the user’s passworduser_nicename
used as part of the author URL (sanitized version of user_login)user_email
the email address of the useruser_url
the URL of the user (optional)user_registered
when the user registered on the websiteuser_activation_key
used when a password reset is performed (blank the rest of the time)user_status
this field is no longer useddisplay_name
the name to display (e.g. when displaying the author name)
ID | user_login | user_email | user_nicename | user_pass | user_url | user_registered | user_activation_key | user_status | display_name |
---|---|---|---|---|---|---|---|---|---|
1 | s@l | sal@example.com | sl | $P$BM6VNLH7yCai2CoP8/5PW62xjTkETR/ | http://ironco.de | 2019-03-04 15:22:32 | 0 | Sal Ferrarello | |
2 | me@example.com | me@example.com | meexample-com | $P$BgvNn0SMQJW0924bw6kPFLFPuI/JS6. | 2019-03-07 14:03:15 | 0 | Me Lastname |
wp_posts
ID
auto-incrementing unique identifierpost_title
title of the postpost_name
post slug (used in the permalink)post_type
the type of post (e.g.post
,page
,attachment
,nav_menu_item
, a custom post type), see Post Typepost_content
the content body of the postpost_status
the status of the post (e.g.publish
,draft
), see Post Statuspost_mime_type
media type, only used when post_type is ‘attachment’ (e.g.image/jpeg
) see MIME Typespost_author
user ID of the post author (from wp_usersID
)post_date
when the post was published (local time)post_date_gmt
when the post was published (GMT)post_excerpt
short summary of the post (optional) see Excerptcomment_status
are comments allowed on this post (open
orclosed
)ping_status
are pings and trackbacks (remote notifications from other websites) allowed (open
orclosed
)post_password
password for viewing the post (optional) see Password Protected Poststo_ping
a list of URLs WordPress should send pingbacks topinged
a list of URLs WordPress has sent pingbacks topost_modified
when the post was last modified (local time)post_modified_gmt
when the post was last modified (GMT)post_content_filtered
this field is no longer usedpost_parent
the ID of the parent post (e.g. parent page of this page or post associated with this attachment) (optional)guid
global unique identifier string for this post (the permalink originally used when this post was published)menu_order
used to manage display order (e.g. of pages or menu items) (default0
)
wp_comments
comment_ID
auto-incrementing unique identifiercomment_post_ID
the ID of the post this comment is associated with (from wp_posts)comment_content
the content of the commentcomment_author
the name of the comment author (from Userdisplay_name
or as typed in by the commenter)comment_author_email
the email of the comment author (from Useruser_email
or as typed in by the commenter)comment_author_url
the URL of the comment author (from Useruser_url
or as typed in by the commenter)comment_approved
status of the comment ("1"
,"trash"
,"spam"
,"pending"
)comment_parent
the comment_ID of the parent comment (the comment this comment is replying to) (optional)comment_author_IP
the IP address of the computer used to make the commentcomment_date
when the comment was made (local time)comment_date_gmt
when the comment was made (GMT)comment_karma
unused by WordPress corecomment_agent
the user agent of the browser used to make the commentcomment_type
comment type (pingback
,trackback
, or blank to indicate a regular comment)user_id
the user ID of the comment author (from wp_usersID
) or0
for an unregistered user
Meta Information
Sometimes we want to store additional information that doesn’t fit in one of the existing tables. For example, if we want to store:
- the favorite breakfast food of a user
- a subtitle for a post
- a comment author twitter handle for a comment
These are all reasonable ways to want to extend WordPress but the relevant tables do not have appropriate columns for any of these values. Fortunately, WordPress is designed to be extended and provides a way to store values like these. We refer to this extra data we want to store as “meta data”. Favorite breakfast food is meta data for a user. A subtitle is meta data for a post. A comment author twitter handle is meta data for a comment.
WordPress provides the following tables where we can store this meta data (wp_usermeta
, wp_postmeta
, and wp_commentmeta
). Each row in these tables includes a reference to the entry the data is associated with (e.g. the wp_usermeta
table has a column called user_id
that refers to a row in the wp_users
table).
wp_usermeta
umeta_id
auto-incrementing unique identifieruser_id
the user ID from wp_usersmeta_key
the label for the value stored (e.g. favorite_breakfast_food)meta_value
the value stored (e.g. waffles)
umeta_id | user_id | meta_key | meta_value |
---|---|---|---|
1 | 1 | nickname | sal |
2 | 1 | first_name | Sal |
3 | 1 | last_name | Ferrarello |
8 | 1 | admin_color | fresh |
16 | 2 | nickname | me@example.com |
17 | 2 | first_name | Me |
18 | 2 | last_name | Lastname |
23 | 2 | admin_color | fresh |
31 | 1 | favorite_breakfast_food | bacon |
32 | 2 | favorite_breakfast_food | waffles |
wp_postmeta
meta_id
auto-incrementing unique identifierpost_id
the ID of the post this information is associated with (from wp_posts)meta_key
the label for the value stored (e.g. subtitle)meta_value
the value stored (e.g. Hi Mom)
When developing with WordPress post meta comes up a lot. I’ve written another blog post specifically on WordPress post meta and I’ve given a talk, Introduction to Post Meta.
meta_id | post_id | meta_key | meta_value |
---|---|---|---|
1 | 2 | _wp_page_template | default |
2 | 3 | _wp_page_template | default |
3 | 1 | subtitle | Hi Mom |
4 | 3 | subtitle | We respect your privacy |
17 | 9 | _wp_attached_file | 2019/03/thecount.jpg |
21 | 1 | _thumbnail_id | 9 |
wp_commentmeta
meta_id
auto-incrementing unique identifiercomment_id
the ID of the comment this information is associated with (from wp_comments)meta_key
the label for the value stored (e.g. twitter)meta_value
the value stored (e.g. WapuuEU)
meta_id | comment_id | meta_key | meta_value |
---|---|---|---|
1 | 1 | WapuuEU | |
2 | 2 | spam | |
3 | 2 | akismet_pro_tip | discard |
Terms and Taxonomies
WordPress comes with two taxonomies by default, Categories and Tags, both of which apply to Posts. Each value within those taxonomies (e.g. a specific category like Programming
) is called a “term”.
wp_terms
term_id
auto-incrementing unique identifiername
the name of the term (e.g. “Programming”)slug
the slug for the term used in url (e.g. “programming”)term_group
unused by WordPress core (see this WordPress Stackexchange entry on term_group)
term_id | name | slug | term_group |
---|---|---|---|
1 | Spicy | spicy | 0 |
2 | Spicy | spicy | 0 |
3 | Vegetable | vegetable | 0 |
4 | Greeting | greeting | 0 |
5 | Pleasant | pleasant | 0 |
You’ll notice in the table above, the term “Spicy” appears twice, this occurs because “Spicy” is being used in two different taxonomies (which can be determined by looking at the wp_term_taxonomy table below)
wp_term_taxonomy
This table associates a term with a specific taxonomy. In wp_terms the term name Spicy
comes up twice (term_id 1
and 2
) but here you can see the first is in the fe_recipe_tag
taxonomy and the second in the post_tag
taxonomy.
term_taxonomy_id
auto-incrementing unique identifierterm_id
the term_id of the Term from the wp_terms tabletaxonomy
the taxonomy to which the term belongs (e.g. ‘category’, ‘post_tag’, custom taxonomy)description
the description of the termparent
the parent of the term (e.g. the term_id of the parent category from the wp_terms table)count
number of posts assigned this term (because this is programmatically expensive to calculate, WordPress calculates this number and stores it here)
term_taxonomy_id | term_id | taxonomy | description | parent | count |
---|---|---|---|---|---|
1 | 1 | fe_recipe_tag | Spicy food | 0 | 1 |
2 | 2 | post_tag | Spicy views | 0 | 1 |
3 | 3 | fe_recipe_tag | 0 | 1 | |
4 | 4 | category | 0 | 1 | |
5 | 5 | category | 14 | 1 |
wp_term_relationships
This table associates terms (by their term_taxonomy_id from wp_term_taxonomy) with specific posts.
Note: this table does not include a auto-incrementing unique identifier column (this table is exclusively for relating terms to their object/post)
object_id
the object id (almost always post_id from wp_posts) of the object the term is associated withterm_taxonomy_id
the term_taxonomy_id from the wp_term_taxonomy table of the term (for a specific) taxonomy that is associated with the objectterm_order
allows an ordering of terms for an object (unused in WordPress core)
object_id | term_taxonomy_id | term_order |
---|---|---|
1 | 2 | 0 |
1 | 4 | 0 |
1 | 5 | 0 |
4 | 1 | 0 |
4 | 3 | 0 |
For example the first row, indicates that post Hello World (object_id 1
) has the post tag Spicy (term_taxonomy_id 2
).
A cool SQL query to see all your terms, the taxonomy they are part of, and the post they are associated with is
SELECT
t.name as 'Term',
t.term_id,
tt.term_taxonomy_id,
tt.taxonomy,
p.post_title,
p.post_type,
tr.object_id AS 'Post ID'
FROM wp_term_relationships tr
INNER JOIN wp_posts p ON tr.object_id = p.ID
INNER JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_terms t ON t.term_id = tt.term_id
ORDER BY t.term_id;
Thanks to Brad Williams for his original version of this SQL query for WordPress taxonomy relationships.
wp_termmeta
This table is similar to the other meta tables (wp_usermeta, wp_postmeta, and wp_commentmeta). It allows us to store additional information with a term.
meta_id
auto-incrementing unique identifierterm_id
the term_id of the term from the wp_terms tablemeta_key
the label for the value storedmeta_value
the value stored
meta_id | term_id | meta_key | meta_value |
---|---|---|---|
1 | 3 | tagline | Good for you |
wp_links
This table is no longer used by WordPress core. You can read about the Links Manager functionality that has been removed and if you would like to restore this functionality in WordPress you can use the Link Manager plugin.
Photo Credit
Pexels Architecture Black Cave Corridor
Leave a Reply