m (→Table: wp_posts: post_type varchar(100) -> (20), Default:post) |
Stevenlinx (talk | contribs) (→Site Specific Tables: revise wp_term_termmeta to wp_termmeta; sorted order) |
||
(111 intermediate revisions by 41 users not shown) | |||
Line 1: | Line 1: | ||
+ | {{Languages| |
||
+ | {{en|Database Description}} |
||
+ | {{ar|وصف قاعدة البيانات}} |
||
+ | {{ja|データベース概要}} |
||
+ | {{zh-hans|数据库描述}} |
||
+ | {{ko|한국어}} |
||
+ | {{ru|Описание структуры базы данных}} |
||
+ | }} |
||
+ | |||
__TOC__ |
__TOC__ |
||
− | The following is an outline and description of the database tables created during the standard installation of WordPress |
+ | The following is an outline and description of the [[Glossary#Database|database]] tables created during the standard installation of WordPress. The database structure and the diagram below were last updated in [[Version 4.4|version 4.4]]. |
+ | The only database supported by WordPress is [[Glossary#MySQL|MySQL]] version 5.0.15 or greater, or any version of [[Glossary#MariaDB|MariaDB]]. |
||
− | Also see: |
||
− | *[[Database_Description/2.0|description of the database for WordPress 2.0.x]] |
||
− | *[[Database_Description/2.2|description of the database for WordPress 2.2.x]] |
||
+ | Also see prior versions of Database Descriptions for [[Database_Description/1.5|WordPress 1.5]], [[Database_Description/2.0|WordPress 2.0]], [[Database_Description/2.2|WordPress 2.2]], [[Database_Description/2.3|WordPress 2.3]], [[Database_Description/2.5|WordPress 2.5]], [[Database_Description/2.7|WordPress 2.7]], [[Database_Description/2.8|WordPress 2.8]], [[Database_Description/2.9|WordPress 2.9]], and [[Database_Description/3.3|WordPress 3.3]]. |
||
− | Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're [[Writing a Plugin]], however, you may be interested in learning how to interface with the database directly. WordPress provides the [[Function_Reference/wpdb Class|wpdb Class]] to make this task easy. |
||
+ | Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're [[Writing a Plugin]] however, you may be interested in learning how WordPress stores its data and relationships. If you have already attempted to use the existing [[:Category:API|WordPress API]] to access the data you need but have determined it's not possible without accessing the database directly, WordPress provides the [[Class Reference/wpdb|wpdb]] class to make this task easy. |
||
− | = Table Overview = |
||
+ | |||
+ | == Database Diagram == |
||
+ | The diagram below provides a visual overview of the WordPress database and the relations between the tables created during the WordPress standard installation. The Table Overview below includes additional details on the tables and columns. |
||
+ | |||
+ | [[Image:WP4.4.2-ERD.png|500px|thumb|none|(WP 4.4.2 Database diagram)]] |
||
+ | |||
+ | |||
+ | Please note that within the standard installation of WordPress no integrity between the tables is enforced e.g. between posts and comments. If you are creating a plugin or extension that manipulates the WordPress database, your code should do the housekeeping so that no orphan records remain in the tables e.g. by removing records in other tables with a set of SQL commands when foreign keys are deleted (Don't forget to remind users to backup before such operations). |
||
+ | |||
+ | == Table Overview == |
||
This section is the overview of all the tables created during the WordPress standard installation. It is followed by specific information of what is in each table. |
This section is the overview of all the tables created during the WordPress standard installation. It is followed by specific information of what is in each table. |
||
+ | {| class="widefat" |
||
− | {| width="75%" border="1" |
||
+ | |- valign="top" style="background:#464646; color:#d7d7d7;" |
||
+ | ! colspan="3" bordercolor="#FFFFFF" | '''WordPress 4.4 Tables (12)''' |
||
+ | |- valign="top" style="background:#464646; color:#d7d7d7;" |
||
+ | ! width="6%" | Table Name !! width="35%" | Description !! Relevant Area(s) of WordPress User Interface |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_commentmeta|wp_commentmeta]] |
||
− | | colspan="3" bordercolor="#FFFFFF" | <strong>WordPress 2.3 Tables (10)</strong> |
||
+ | | Each '''comment''' features information called the '''meta data''' and it is stored in the <tt>wp_commentmeta</tt>. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Comments|Comments]] > [[Comments Comments SubPanel|Comments]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_comments|wp_comments]] |
||
− | | width="6%" | <strong>Table Name</strong> || width="15%" | <strong>Description</strong> || width="15%" | <strong>Relevant Area(s) of WordPress User Interface</strong> |
||
+ | | The '''comments''' within WordPress are stored in the <tt>wp_comments</tt> table. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Comments|Comments]] > [[Comments Comments SubPanel|Comments]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_links|wp_links]] |
||
− | | [[#Table:_wp_comments|wp_comments]] || The '''comments''' within WordPress are stored in the <tt>wp_comments</tt> table. || Comments are created by readers as responses to posts. Comments are managed by administrator via [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Comments|Comments]] |
||
+ | | The <tt>wp_links</tt> holds information related to the '''links''' entered into the [[Administration Panels#Links - Putting the Inter in the Internet|Links]] feature of WordPress. ''(This feature has been deprecated, but can be re-enabled with the [http://wordpress.org/extend/plugins/link-manager/ Links Manager] plugin.)'' || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Link|Links]] > [[Links Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Edit Links|Links]] > [[Links Links SubPanel|Links]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_options|wp_options]] |
||
− | | [[#Table:_wp_links|wp_links]] || The <tt>wp_links</tt> holds information related to the '''links''' entered into the [[Manage_Blogroll_SubPanel|Blogroll Manager]] feature of WordPress. || [[Administration Panels]] >> [[Administration_Panels#Blogroll_-_Putting_the_Inter_in_the_Internet|Blogroll]] >> [[Administration_Panels#Manage_Blogroll|Manage Blogroll]]<br /> [[Administration Panels]] >> [[Administration_Panels#Blogroll_-_Putting_the_Inter_in_the_Internet|Blogroll]] >> [[Administration_Panels#Add_Link|Add Links]]<br /> [[Administration Panels]] >> [[Administration_Panels#Blogroll_-_Putting_the_Inter_in_the_Internet|Blogroll]] >> [[Administration_Panels#Import_Links|Import Links]] |
||
+ | | The '''[[Administration Panels#Settings|Options]]''' set under the '''Administration > [[Administration Panels#Settings|Settings]]''' panel are stored in the <tt>wp_options</tt> table. See [[Option Reference]] for <code>option_name</code> and default values. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings General SubPanel|General]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings Writing SubPanel|Writing]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings Reading SubPanel|Reading]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings Discussion SubPanel|Discussion]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings Privacy SubPanel|Privacy]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings Permalinks SubPanel|Permalinks]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Widgets|Appearance]] > [[Appearance Widgets SubPanel|Widgets]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_postmeta|wp_postmeta]] |
||
− | | [[#Table:_wp_options|wp_options]] || The '''[[Administration_Panels#Options_-_Configuration_Settings|Options]]''' set under the '''Administration > [[Administration_Panels#Options_-_Configuration_Settings|Options]]''' panel are stored in the <tt>wp_options</tt> table. || [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > > [[Administration_Panels#General|General]]<br /> [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > >[[Administration_Panels#Writing|Writing]]<br /> [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > >[[Administration_Panels#Reading|Reading]]<br /> [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > >[[Administration_Panels#Discussion|Discussion]]<br /> [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > >[[Administration_Panels#Permalinks|Permalinks]]<br /> [[Administration Panels]] >> [[Administration_Panels#Options_-_Configuration_Settings|Options]] > >[[Administration_Panels#Miscellaneous|Miscellaneous]] |
||
+ | | Each '''post''' features information called the '''meta data''' and it is stored in the <tt>wp_postmeta</tt>. Some plugins may add their own information to this table. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Post|Posts]] > [[Posts Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Page|Pages]] > [[Pages Add New SubPanel|Add New]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_posts|wp_posts]] |
||
− | | [[#Table:_wp_postmeta|wp_postmeta]] || Each '''post''' features information called the '''meta data''' and it is stored in the <tt>wp_postmeta</tt>. Some tag plugins, and other plugins may add their own information to this table. || [[Administration Panels]] >> [[Administration_Panels#Write_-_Make_some_content|Write]] >> [[Administration_Panels#Write_Post|Write a Post]] |
||
+ | | The core of the WordPress data is the [[Glossary#Post|'''posts''']]. It is stored in the <tt>wp_posts</tt> table. Also [[Glossary#Page|Pages]] and navigation menu items are stored in this table. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Post|Posts]] > [[Posts Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Posts|Posts]] > [[Posts Posts SubPanel|Posts]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Page|Pages]] > [[Pages Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Pages|Pages]] > [[Pages Pages SubPanel|Pages]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Upload New Media|Media]] > [[Media Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Media Library|Media]] > [[Media Library SubPanel|Library]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Appearance|Appearance]] > [[Appearance Menus SubPanel|Menus]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_terms|wp_terms]] |
||
− | | [[#Table:_wp_posts|wp_posts]] || The core of the WordPress data is the '''posts'''. It is stored in the <tt>wp_posts</tt> table. || [[Administration Panels]] >> [[Administration_Panels#Write_-_Make_some_content|Write]] >> [[Administration_Panels#Write_Post|Write a Post]] |
||
+ | | The '''categories''' for both posts and links and the '''tags''' for posts are found within the <tt>wp_terms</tt> table. || rowspan="3" | |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Post Tags|Posts]] > [[Posts Post Tags SubPanel|Post Tags]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Categories|Posts]] > [[Posts Categories SubPanel|Categories]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Link Categories|Links]] > [[Links Link Categories SubPanel|Link Categories]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Post|Posts]] > [[Posts Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Posts|Posts]] > [[Posts Posts SubPanel|Posts]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Add New Page|Pages]] > [[Pages Add New SubPanel|Add New]] |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Pages|Page]] > [[Pages Pages SubPanel|Pages]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_termmeta|wp_termmeta]] |
||
− | | [[#Table:_wp_terms|wp_terms]] || The '''categories''' for both posts and links and the '''tags''' for posts are found within the <tt>wp_terms</tt> table. || [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Categories|Categories]]<br />[[Administration Panels]] >> [[Administration_Panels#Write_-_Make_some_content|Write]] >> [[Administration_Panels#Write_Post|Write Post]]<br /> [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Posts|Posts]] >> [[Administration_Panels#Write_Post|Edit]] |
||
+ | | Each '''term''' features information called the '''meta data''' and it is stored in <tt>wp_termmeta</tt>. |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_term_relationships|wp_term_relationships]] |
||
− | | [[#Table:_wp_term_relationships|wp_term_relationships]] || '''Posts''' are associated with categories and tags from the <tt>wp_terms</tt> table and this association is maintained in the <tt>wp_term_relationships</tt> table. The association of '''links''' to their respective categories are also kept in this table.|| [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Categories|Categories]]<br />[[Administration Panels]] >> [[Administration_Panels#Write_-_Make_some_content|Write]] >> [[Administration_Panels#Write_Post|Write Post]]<br /> [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Posts|Posts]] >> [[Administration_Panels#Write_Post|Edit]] |
||
+ | | '''Posts''' are associated with categories and tags from the <tt>wp_terms</tt> table and this association is maintained in the <tt>wp_term_relationships</tt> table. The association of '''links''' to their respective categories are also kept in this table. |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_term_taxonomy|wp_term_taxonomy]] |
||
− | | [[#Table:_wp_term_taxonomy|wp_term_taxonomy]] || This table describes the taxonomy ('''category''', '''link''', or '''tag''') for the entries in the <tt>wp_terms</tt> table. || [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Categories|Categories]]<br />[[Administration Panels]] >> [[Administration_Panels#Write_-_Make_some_content|Write]] >> [[Administration_Panels#Write_Post|Write Post]]<br /> [[Administration Panels]] >> [[Administration_Panels#Manage_-_Change_your_content|Manage]] >> [[Administration_Panels#Posts|Posts]] >> [[Administration_Panels#Write_Post|Edit]] |
||
+ | | This table describes the taxonomy ('''category''', '''link''', or '''tag''') for the entries in the <tt>wp_terms</tt> table. |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_usermeta|wp_usermeta]] |
||
− | | [[#Table:_wp_usermeta|wp_usermeta]] || Each '''user''' features information called the '''meta data''' and it is stored in <tt>wp_usermeta</tt>. || [[Administration Panels]] >> [[Administration_Panels#Users_-_Your_Blogging_Family|Users]] |
||
+ | | Each '''user''' features information called the '''meta data''' and it is stored in <tt>wp_usermeta</tt>. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Users|Users]] |
||
|- valign="top" |
|- valign="top" |
||
+ | ! [[#Table:_wp_users|wp_users]] |
||
− | | height="55" | [[#Table:_wp_users|wp_users]] || The list of '''users''' is maintained in table <tt>wp_users</tt>. || [[Administration Panels]] >> [[Administration_Panels#Users_-_Your_Blogging_Family|Users]] |
||
+ | | The list of '''users''' is maintained in table <tt>wp_users</tt>. || |
||
− | |}<br /><br /> |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Users|Users]] |
||
+ | |} |
||
− | =Table Details= |
+ | ==Table Details== |
The following are the specific fields in each of the tables created during the standard WordPress installation. |
The following are the specific fields in each of the tables created during the standard WordPress installation. |
||
− | ==Table: |
+ | ===Table: wp_commentmeta=== |
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | | |
+ | |meta_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | | |
+ | |comment_id||bigint(20) unsigned|| ||IND||0|| |
+ | |- |
||
+ | |meta_key||varchar(255)||YES||IND||NULL|| |
||
+ | |- |
||
+ | |meta_value||longtext||YES|| ||NULL|| |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | |||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||meta_id |
||
+ | |- |
||
+ | |comment_id||INDEX||comment_id |
||
+ | |- |
||
+ | |meta_key||INDEX||meta_key |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_comments=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |comment_ID||bigint(20) unsigned|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |comment_post_ID||bigint(20) unsigned|| ||IND||0|| |
||
|- |
|- |
||
|comment_author||tinytext|| || || || |
|comment_author||tinytext|| || || || |
||
|- |
|- |
||
− | |comment_author_email||varchar(100)|| || |
+ | |comment_author_email||varchar(100)|| ||IND|| || |
|- |
|- |
||
|comment_author_url||varchar(200)|| || || || |
|comment_author_url||varchar(200)|| || || || |
||
Line 59: | Line 153: | ||
|comment_date||datetime|| || ||0000-00-00 00:00:00|| |
|comment_date||datetime|| || ||0000-00-00 00:00:00|| |
||
|- |
|- |
||
− | |comment_date_gmt||datetime|| ||& |
+ | |comment_date_gmt||datetime|| ||IND & IND Pt2||0000-00-00 00:00:00|| |
|- |
|- |
||
|comment_content||text|| || || || |
|comment_content||text|| || || || |
||
Line 65: | Line 159: | ||
|comment_karma||int(11)|| || ||0|| |
|comment_karma||int(11)|| || ||0|| |
||
|- |
|- |
||
− | |comment_approved|| |
+ | |comment_approved||varchar(20)|| ||IND Pt1||1|| |
|- |
|- |
||
|comment_agent||varchar(255)|| || || || |
|comment_agent||varchar(255)|| || || || |
||
Line 71: | Line 165: | ||
|comment_type||varchar(20)|| || || || |
|comment_type||varchar(20)|| || || || |
||
|- |
|- |
||
− | |comment_parent||bigint(20)|| || |
+ | |comment_parent||bigint(20) unsigned|| ||IND||0|| |
|- |
|- |
||
− | |user_id||bigint(20)|| || ||0|| |
+ | |user_id||bigint(20) unsigned|| || ||0|| |
|} |
|} |
||
− | == |
+ | ====Indexes==== |
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||comment_ID |
||
+ | |- |
||
+ | |comment_post_ID||INDEX||comment_post_ID |
||
+ | |- style="vertical-align:top;" |
||
+ | |comment_approved_date_gmt||INDEX||comment_approved<br />comment_date_gmt |
||
+ | |- |
||
+ | |comment_date_gmt||INDEX||comment_date_gmt |
||
+ | |- |
||
+ | |comment_parent||INDEX||comment_parent |
||
+ | |- |
||
+ | |comment_author_email||INDEX||comment_author_email |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_links=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |link_id||bigint(20)|| ||PRI|| |
+ | |link_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
|link_url||varchar(255)|| || || || |
|link_url||varchar(255)|| || || || |
||
Line 89: | Line 206: | ||
|- |
|- |
||
|link_target||varchar(25)|| || || || |
|link_target||varchar(25)|| || || || |
||
− | |- |
||
− | |link_category||bigint(20)|| ||IND||0||FK->wp_categories.cat_ID |
||
|- |
|- |
||
|link_description||varchar(255)|| || || || |
|link_description||varchar(255)|| || || || |
||
|- |
|- |
||
− | |link_visible|| |
+ | |link_visible||varchar(20)|| ||IND||Y|| |
|- |
|- |
||
− | |link_owner|| |
+ | |link_owner||bigint(20) unsigned|| || ||1|| |
|- |
|- |
||
|link_rating||int(11)|| || ||0|| |
|link_rating||int(11)|| || ||0|| |
||
Line 109: | Line 224: | ||
|} |
|} |
||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
− | ==Table: wp_options== |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
− | {| table width="90%" border=1 |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||link_id |
||
+ | |- |
||
+ | |link_visible||INDEX||link_visible |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_options=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |option_id||bigint(20)|| ||PRI|| |
+ | |option_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | | |
+ | |option_name||varchar(64)|| ||UNI|| || |
− | |- |
||
− | |option_name||varchar(64)|| ||PRI&IND|| || |
||
− | |- |
||
− | |option_can_override||enum('Y','N')|| || ||Y|| |
||
− | |- |
||
− | |option_type||int(11)|| || ||1|| |
||
|- |
|- |
||
|option_value||longtext|| || || || |
|option_value||longtext|| || || || |
||
|- |
|- |
||
− | | |
+ | |autoload||varchar(20)|| ||IND||yes|| |
− | | |
+ | |} |
+ | |||
− | |option_height||int(11)|| || ||8|| |
||
+ | ====Indexes==== |
||
− | |- |
||
+ | |||
− | |option_description||tinytext|| || || || |
||
+ | {| class="dummy-to-narrow" |
||
− | |- |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
− | |option_admin_level||int(11)|| || ||1|| |
||
+ | !Keyname !! Type !! Field |
||
|- |
|- |
||
+ | |PRIMARY||PRIMARY||option_id |
||
− | |autoload||enum('yes','no')|| || ||yes|| |
||
+ | |- |
||
+ | |option_name||UNIQUE||option_name |
||
+ | |- |
||
+ | |autoload||INDEX||autoload |
||
|} |
|} |
||
+ | ===Table: wp_postmeta=== |
||
+ | {| class="widefat" |
||
− | ==Table: wp_postmeta== |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
− | {| table width="90%" border=1 |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |meta_id||bigint(20)|| ||PRI|| |
+ | |meta_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | |post_id||bigint(20)|| ||IND||0|| |
+ | |post_id||bigint(20) unsigned|| ||IND||0|| |
|- |
|- |
||
|meta_key||varchar(255)||YES||IND||NULL|| |
|meta_key||varchar(255)||YES||IND||NULL|| |
||
Line 151: | Line 280: | ||
|} |
|} |
||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
− | ==Table: wp_posts== |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
− | {| table width="90%" border=1 |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||meta_id |
||
+ | |- |
||
+ | |post_id||INDEX||post_id |
||
+ | |- |
||
+ | |meta_key||INDEX||meta_key |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_posts=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |ID||bigint(20) unsigned|| ||PRI|| |
+ | |ID||bigint(20) unsigned|| ||PRI & IND Pt4|| ||auto_increment |
|- |
|- |
||
− | |post_author||bigint(20)|| || |
+ | |post_author||bigint(20) unsigned|| ||IND||0|| |
|- |
|- |
||
− | |post_date||datetime|| || |
+ | |post_date||datetime|| ||IND Pt3||0000-00-00 00:00:00|| |
|- |
|- |
||
|post_date_gmt||datetime|| || ||0000-00-00 00:00:00|| |
|post_date_gmt||datetime|| || ||0000-00-00 00:00:00|| |
||
Line 167: | Line 312: | ||
|- |
|- |
||
|post_title||text|| || || || |
|post_title||text|| || || || |
||
− | |- |
||
− | |post_category||int(4)|| || ||0||FK->wp_categories.cat_ID |
||
|- |
|- |
||
|post_excerpt||text|| || || || |
|post_excerpt||text|| || || || |
||
|- |
|- |
||
− | |post_status|| |
+ | |post_status||varchar(20)|| ||IND PT2||publish|| |
|- |
|- |
||
− | |comment_status|| |
+ | |comment_status||varchar(20)|| || ||open|| |
|- |
|- |
||
− | |ping_status|| |
+ | |ping_status||varchar(20)|| || ||open|| |
|- |
|- |
||
|post_password||varchar(20)|| || || || |
|post_password||varchar(20)|| || || || |
||
Line 190: | Line 333: | ||
|post_modified_gmt||datetime|| || ||0000-00-00 00:00:00|| |
|post_modified_gmt||datetime|| || ||0000-00-00 00:00:00|| |
||
|- |
|- |
||
− | |post_content_filtered|| |
+ | |post_content_filtered||longtext|| || || || |
|- |
|- |
||
− | |post_parent||bigint(20)|| || |
+ | |post_parent||bigint(20) unsigned|| ||IND||0|| |
|- |
|- |
||
|guid||varchar(255)|| || || || |
|guid||varchar(255)|| || || || |
||
Line 198: | Line 341: | ||
|menu_order||int(11)|| || ||0|| |
|menu_order||int(11)|| || ||0|| |
||
|- |
|- |
||
− | |post_type||varchar(20)|| || |
+ | |post_type||varchar(20)|| ||IND Pt1||post|| |
|- |
|- |
||
|post_mime_type||varchar(100)|| || || || |
|post_mime_type||varchar(100)|| || || || |
||
Line 205: | Line 348: | ||
|} |
|} |
||
− | == |
+ | ====Indexes==== |
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||ID |
||
+ | |- |
||
+ | |post_name||INDEX||post_name |
||
+ | |- style="vertical-align:top;" |
||
+ | |type_status_date||INDEX||post_type<br />post_status<br />post_date<br />ID |
||
+ | |- |
||
+ | |post_parent||INDEX||post_parent |
||
+ | |- |
||
+ | |post_author||INDEX||post_author |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_terms=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |term_id||bigint(20)|| ||PRI|| ||auto_increment |
+ | |term_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | |name||varchar( |
+ | |name||varchar(200)|| ||IND|| || |
|- |
|- |
||
− | |slug||varchar(200)|| || |
+ | |slug||varchar(200)|| ||MUL|| || |
|- |
|- |
||
|term_group||bigint(10)|| || ||0|| |
|term_group||bigint(10)|| || ||0|| |
||
|} |
|} |
||
+ | ====Indexes==== |
||
− | ==Table: wp_term_relationships== |
||
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||term_id |
||
+ | |- |
||
+ | |slug||UNIQUE||slug |
||
+ | |- |
||
+ | |name||INDEX||name |
||
+ | |} |
||
+ | |||
+ | ===Table: wp_termmeta=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | | |
+ | |meta_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | | |
+ | |term_id||bigint(20) unsigned|| ||IND||0|| |
+ | |- |
||
+ | |meta_key||varchar(255)||YES||IND||NULL|| |
||
+ | |- |
||
+ | |meta_value||longtext||YES|| ||NULL|| |
||
|} |
|} |
||
+ | ====Indexes==== |
||
− | ==Table: wp_term_taxonomy== |
||
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||meta_id |
||
+ | |- |
||
+ | |term_id||INDEX||term_id |
||
+ | |- |
||
+ | |meta_key||INDEX||meta_key |
||
+ | |} |
||
+ | |||
+ | |||
+ | ===Table: wp_term_relationships=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | | |
+ | |object_id||bigint(20) unsigned|| ||PRI Pt1||0|| |
|- |
|- |
||
− | | |
+ | |term_taxonomy_id||bigint(20) unsigned|| ||PRI Pt2 & IND||0|| |
|- |
|- |
||
− | | |
+ | |term_order||int(11)|| || ||0|| |
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | |||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- style="vertical-align:top;" |
||
+ | |PRIMARY||PRIMARY||object_id<br />term_taxonomy_id |
||
+ | |- |
||
+ | |term_taxonomy_id||INDEX||term_taxonomy_id |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_term_taxonomy=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |term_taxonomy_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |term_id||bigint(20) unsigned|| ||UNI Pt1||0 || |
||
+ | |- |
||
+ | |taxonomy||varchar(32)|| ||UNI Pt2 & IND|| || |
||
|- |
|- |
||
|description||longtext|| || || || |
|description||longtext|| || || || |
||
|- |
|- |
||
− | |parent||bigint(20)|| || ||0|| |
+ | |parent||bigint(20) unsigned|| || ||0|| |
|- |
|- |
||
|count||bigint(20)|| || ||0|| |
|count||bigint(20)|| || ||0|| |
||
Line 245: | Line 470: | ||
|} |
|} |
||
+ | ====Indexes==== |
||
− | ==Table: wp_usermeta== |
||
+ | |||
− | {| table width="90%" border=1 |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||term_taxonomy_id |
||
+ | |- |
||
+ | |term_id_taxonomy||UNIQUE||term_id<br />taxonomy |
||
+ | |- |
||
+ | |taxonomy||INDEX||taxonomy |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_usermeta=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |umeta_id||bigint(20)|| ||PRI|| |
+ | |umeta_id||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | |user_id||bigint(20)|| || |
+ | |user_id||bigint(20) unsigned|| ||IND||0|| |
|- |
|- |
||
− | |meta_key||varchar(255)|| |
+ | |meta_key||varchar(255)||Yes||IND||NULL|| |
|- |
|- |
||
− | |meta_value||longtext|| |
+ | |meta_value||longtext||Yes|| ||NULL|| |
|} |
|} |
||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
− | ==Table: wp_users== |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
− | {| table width="90%" border=1 |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||umeta_id |
||
+ | |- |
||
+ | |user_id||INDEX||user_id |
||
+ | |- |
||
+ | |meta_key||INDEX||meta_key |
||
+ | |} |
||
+ | |||
+ | ===Table: wp_users=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
!Field!!Type!!Null!!Key!!Default!!Extra |
!Field!!Type!!Null!!Key!!Default!!Extra |
||
|- |
|- |
||
− | |ID||bigint(20) unsigned|| ||PRI|| |
+ | |ID||bigint(20) unsigned|| ||PRI|| ||auto_increment |
|- |
|- |
||
− | |user_login||varchar(60)|| || |
+ | |user_login||varchar(60)|| ||IND|| || |
|- |
|- |
||
|user_pass||varchar(64)|| || || || |
|user_pass||varchar(64)|| || || || |
||
|- |
|- |
||
− | |user_nicename||varchar(50)|| || |
+ | |user_nicename||varchar(50)|| ||IND|| || |
|- |
|- |
||
|user_email||varchar(100)|| || || || |
|user_email||varchar(100)|| || || || |
||
Line 277: | Line 533: | ||
|user_registered||datetime|| || ||0000-00-00 00:00:00|| |
|user_registered||datetime|| || ||0000-00-00 00:00:00|| |
||
|- |
|- |
||
− | | |
+ | |user_activation_key||varchar(60)|| || || || |
+ | |- |
||
+ | |user_status||int(11)|| || ||0|| |
||
+ | |- |
||
+ | |display_name||varchar(250)|| || || || |
||
+ | |} |
||
+ | |||
+ | |||
+ | NOTE: Enabling Multisite feature of WordPress adds two fields in wp_users table: '''spam''' and '''deleted'''. Refer [[#Table:_wp_users_2|the Multisite version]]. |
||
+ | |||
+ | ====Indexes==== |
||
+ | |||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||ID |
||
+ | |- |
||
+ | |user_login_key||INDEX||user_login |
||
+ | |- |
||
+ | |user_nicename||INDEX||user_nicename |
||
+ | |} |
||
+ | |||
+ | == Multisite Table Overview == |
||
+ | |||
+ | This section is the overview of the tables created for use with the [[Glossary#Multisite|Multisite]] feature of WordPress. These tables are created via the processes under [[Super Admin Menu|Administration]] > [[Super Admin Menu#Tools|Tools]] > [[Tools Network SubPanel|Network]]. |
||
+ | |||
+ | These tables are considered the multisite global tables. |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- valign="top" style="background:#464646; color:#d7d7d7;" |
||
+ | ! colspan="3" bordercolor="#FFFFFF" | '''WordPress 3.0 Multisite Tables''' |
||
+ | |- valign="top" style="background:#464646; color:#d7d7d7;" |
||
+ | ! width="6%" | Table Name !! width="35%" | Description !! Relevant Area(s) of WordPress User Interface |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_blogs|wp_blogs]] |
||
+ | | Each '''site''' created is stored in the table, <tt>wp_blogs</tt>. || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Tools|Tools]] > [[Tools Network SubPanel|Network]] |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Sites|Super Admin]] > [[Super Admin Sites SubPanel|Sites]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_blog_versions|wp_blog_versions]] |
||
+ | | The current database version status of each '''site''' is maintained in the <tt>wp_blogs_versions</tt> table and is updated as each site is upgraded. || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Update|Super Admin]] > [[Super Admin Update SubPanel|Update]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_registration_log|wp_registration_log]] |
||
+ | | The <tt>wp_registration_log</tt> records the admin user created when each new site is created. || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Sites|Super Admin]] > [[Super Admin Sites SubPanel|Sites]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_signups|wp_signups]] |
||
+ | | This table holds the user that have registered for a site via the login registration process. User registration is enable in [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Options|Super Admin]] > [[Super Admin Options SubPanel|Options]]. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Settings|Settings]] > [[Settings General SubPanel|General]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_site|wp_site]] |
||
+ | | The <tt>wp_site</tt> table contains the main site address. || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Tools|Tools]] > [[Tools Network SubPanel|Network]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_sitemeta|wp_sitemeta]] |
||
+ | | Each '''site''' features information called the '''site data''' and it is stored in <tt>wp_sitemeta</tt>. Various '''option''' information, including the site admin is kept in this table. || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Options|Super Admin]] > [[Super Admin Options SubPanel|Options]] |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_users_2|wp_users]] |
||
+ | | The list of all '''users''' is maintained in table <tt>wp_users</tt>. Multisite add two fields not in [[#Table: wp_users|the stand-alone version]].|| |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Users|Users]] |
||
+ | * [[Super Admin Menu|Administration]] > [[Super Admin Menu#Super Admin|Super Admin]] > [[Super Admin Users SubPanel|Users]] |
||
+ | * [[Super Admin Menu|Administration]] > [[Super Admin Menu#Super Admin|Super Admin]] > [[Super Admin Sites SubPanel|Sites]] - Edit Site |
||
+ | |- valign="top" |
||
+ | ! [[#Table:_wp_usermeta|wp_usermeta]] |
||
+ | | This table is not re-created for multisite, but '''meta data of users''' for each site are stored in <tt>wp_usermeta</tt>. || |
||
+ | * [[Administration Panels|Administration]] > [[Administration Panels#Users|Users]] |
||
+ | * [[Super Admin Menu|Administration]] > [[Super Admin Menu#Super Admin|Super Admin]] > [[Super Admin Users SubPanel|Users]] |
||
+ | * [[Super Admin Menu|Administration]] > [[Super Admin Menu#Super Admin|Super Admin]] > [[Super Admin Sites SubPanel|Sites]] - Edit Site |
||
+ | |- valign="top" |
||
+ | ! [[#Site Specific Tables|Site Specific Tables]] |
||
+ | | The data of the main site are stored in existing unnumbered tables. The data of additional sites are stored in new numbered tables. |
||
+ | || |
||
+ | * [[Super_Admin_Menu|Administration]] > [[Super_Admin_Menu#Sites|Super Admin]] > [[Super Admin Sites SubPanel|Sites]] - Add Site |
||
+ | |}<br /><br /> |
||
+ | |||
+ | ==Multisite Table Details== |
||
+ | |||
+ | The following describe the tables and fields created [[Create A Network#Step 4: Installing a Network|during the network installation]]. Note that a global set of tables is created upon creation of the network, and [[#Site Specific Tables|site-specific tables]] are established as each site is created. |
||
+ | |||
+ | ===Table: wp_blogs=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |blog_id||bigint(20)|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |site_id||bigint(20)|| || ||0|| |
||
+ | |- |
||
+ | |domain||varchar(200)|| ||IND Pt1|| || |
||
+ | |- |
||
+ | |path||varchar(100)|| ||IND Pt2|| || |
||
+ | |- |
||
+ | |registered||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |- |
||
+ | |last_updated||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |- |
||
+ | |public||tinyint(2)|| || ||1|| |
||
+ | |- |
||
+ | |archived||tinyint(2)|| || ||0|| |
||
+ | |- |
||
+ | |mature||tinyint(2)|| || ||0|| |
||
+ | |- |
||
+ | |spam||tinyint(2)|| || ||0|| |
||
+ | |- |
||
+ | |deleted||tinyint(2)|| || ||0|| |
||
+ | |- |
||
+ | |lang_id||int(11)|| ||IND||0|| |
||
+ | |} |
||
+ | |||
+ | |||
+ | NOTE: The '''blog_id''' identifies the site and the '''site_id''' identifies the network. It is the trace of the past when the 'site' was called as 'blog' in WordPress 2.x. If you add your site in the network, the new record will be inserted to this table <tt>wp_blogs</tt> with incremented '''blog_id''' and the same '''site_id'''. |
||
+ | |||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||blog_id |
||
+ | |- |
||
+ | |domain||INDEX||domain<br/>path |
||
+ | |- |
||
+ | |lang_id||INDEX||lang_id |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_blog_versions=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |blog_id||bigint(20)|| ||PRI||0|| |
||
+ | |- |
||
+ | |db_version||varchar(20)|| ||IND|| || |
||
+ | |- |
||
+ | |last_updated||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||blog_id |
||
+ | |- |
||
+ | |db_version||INDEX||db_version |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_registration_log=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |ID||bigint(20)|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |email||varchar(255)|| || || || |
||
+ | |- |
||
+ | |IP||varchar(30)|| ||IND|| || |
||
+ | |- |
||
+ | |blog_id||bigint(20)|| || ||0|| |
||
+ | |- |
||
+ | |date_registered||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||ID |
||
+ | |- |
||
+ | |IP||INDEX||IP |
||
+ | |} |
||
+ | |||
+ | ===Table: wp_signups=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |signup_id||bigint(20)|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |domain||varchar(200)|| ||IND Pt1|| || |
||
+ | |- |
||
+ | |path||varchar(100)|| ||IND Pt2|| || |
||
+ | |- |
||
+ | |title||longtext|| || || || |
||
+ | |- |
||
+ | |user_login||varchar(60)|| ||IND Pt1|| || |
||
+ | |- |
||
+ | |user_email||varchar(100)|| ||IND & IND Pt2|| || |
||
+ | |- |
||
+ | |registered||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |- |
||
+ | |activated||datetime|| || ||0000-00-00 00:00:00|| |
||
+ | |- |
||
+ | |active||tinyint(1)|| || || || |
||
+ | |- |
||
+ | |activation_key||varchar(50)|| ||IND|| || |
||
+ | |- |
||
+ | |meta||longtext||Yes|| ||NULL|| |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |signup_id||PRIMARY||signup_id |
||
+ | |- |
||
+ | |activation_key||INDEX||activation_key |
||
+ | |- |
||
+ | |user_email||INDEX||user_email |
||
+ | |- style="vertical-align:top;" |
||
+ | |user_login_email||INDEX||user_login<br/>user_email |
||
+ | |- |
||
+ | |domain_path||INDEX||domain<br/>path |
||
+ | |} |
||
+ | |||
+ | ===Table: wp_site=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |id||bigint(20)|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |domain||varchar(200)|| ||IND Pt1|| || |
||
+ | |- |
||
+ | |path||varchar(100)|| ||IND Pt2|| || |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||id |
||
+ | |- |
||
+ | |domain||INDEX||domain<br/>path |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_sitemeta=== |
||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |meta_id||bigint(20)|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |site_id||bigint(20)|| || ||0|| |
||
+ | |- |
||
+ | |meta_key||varchar(255)||Yes||IND||NULL|| |
||
+ | |- |
||
+ | |meta_value||longtext||Yes||IND||NULL|| |
||
+ | |} |
||
+ | |||
+ | ====Indexes==== |
||
+ | {| class="dummy-to-narrow" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||meta_id |
||
+ | |- |
||
+ | |meta_key||INDEX||meta_key |
||
+ | |- |
||
+ | |site_id||INDEX||site_id |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | ===Table: wp_users=== |
||
+ | |||
+ | {| class="widefat" |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Field!!Type!!Null!!Key!!Default!!Extra |
||
+ | |- |
||
+ | |ID||bigint(20) unsigned|| ||PRI|| ||auto_increment |
||
+ | |- |
||
+ | |user_login||varchar(60)|| ||IND|| || |
||
+ | |- |
||
+ | |user_pass||varchar(64)|| || || || |
||
+ | |- |
||
+ | |user_nicename||varchar(50)|| ||IND|| || |
||
+ | |- |
||
+ | |user_email||varchar(100)|| || || || |
||
+ | |- |
||
+ | |user_url||varchar(100)|| || || || |
||
+ | |- |
||
+ | |user_registered||datetime|| || ||0000-00-00 00:00:00|| |
||
|- |
|- |
||
|user_activation_key||varchar(60)|| || || || |
|user_activation_key||varchar(60)|| || || || |
||
Line 284: | Line 834: | ||
|- |
|- |
||
|display_name||varchar(250)|| || || || |
|display_name||varchar(250)|| || || || |
||
+ | |- |
||
+ | |spam||tinyint(2)|| || || 0 || |
||
+ | |- |
||
+ | |deleted||tinyint(2)|| || || 0 || |
||
|} |
|} |
||
+ | ====Indexes==== |
||
− | = Resources = |
||
+ | {| class="dummy-to-narrow" |
||
− | *[http://wpbits.wordpress.com/2007/08/08/a-look-inside-the-wordpress-database/ A look inside the WordPress database] - an indepth dicussion of the database tables by Leonid Mamchenkov. |
||
+ | |- style="background:#464646; color:#d7d7d7;" |
||
+ | !Keyname !! Type !! Field |
||
+ | |- |
||
+ | |PRIMARY||PRIMARY||ID |
||
+ | |- |
||
+ | |user_login_key||INDEX||user_login |
||
+ | |- |
||
+ | |user_nicename||INDEX||user_nicename |
||
+ | |} |
||
+ | |||
+ | ===Site Specific Tables=== |
||
+ | When a new additional site is created, the site-specific tables, similar to [[#Table Overview|the stand-alone tables above]], are created. Each set of tables for a site are created with the site ID ([[#Table: wp_blogs|<code>blog_id</code>]]) as part of the table name. These are the tables that would be created for site ID ''2'' and [[Editing wp-config.php#table_prefix|table_prefix]] ''wp_'': |
||
+ | * [[#Table: wp_commentmeta|''wp_2''_commentmeta]] |
||
+ | * [[#Table: wp_comments|''wp_2''_comments]] |
||
+ | * [[#Table: wp_links|''wp_2''_links]] |
||
+ | * [[#Table: wp_options|''wp_2''_options]] |
||
+ | * [[#Table: wp_postmeta|''wp_2''_postmeta]] |
||
+ | * [[#Table: wp_posts|''wp_2''_posts]] |
||
+ | * [[#Table: wp_term_relationships|''wp_2''_term_relationships]] |
||
+ | * [[#Table: wp_term_taxonomy|''wp_2''_term_taxonomy]] |
||
+ | * [[#Table: wp_termmeta|''wp_2''_termmeta]] |
||
+ | * [[#Table: wp_terms|''wp_2''_terms]] |
||
+ | |||
+ | |||
+ | The data of the main site are stored in unnumbered tables. |
||
+ | |||
+ | ==Source File== |
||
+ | |||
+ | The database scheme is defined in {{Trac|wp-admin/includes/schema.php}} |
||
+ | |||
+ | ==Resources== |
||
+ | * [[WordPress Backups]] - performing backups |
||
+ | * [http://wordpress.org/extend/plugins/tags/backup backup related plugins] - plugins with backup tag |
||
+ | * [http://wordpress.org/extend/plugins/tags/database database related plugins] - plugins with database tag |
||
+ | |||
+ | ==Changelog== |
||
+ | * [[Version 4.2.2|4.2.2]] : |
||
+ | ** [[#Table: wp_termmeta|termmeta table]]: New table to house term related data. |
||
+ | ** Database Diagram: Added the new diagram including the wp_termmeta table. |
||
+ | |||
+ | * [[Version 3.4|3.4]] : |
||
+ | ** [[#Table: wp_comments|comments table]]: Drop Index <code>comment_approved</code>. |
||
+ | ** [[#Table: wp_options|options table]]: Drop field <code>blog_id</code>. |
||
+ | ** [[#Table: wp_posts|posts table]]: Change field <code>post_content_filtered</code> from text to longtext. |
||
+ | |||
+ | * [[Version 3.0|3.0]] : |
||
+ | ** [[#Table: wp_comments|comments table]]: Added Index <code>comment_parent</code>. |
||
+ | ** [[#Table: wp_posts|posts table]]: Added Index <code>post_author</code>. |
||
+ | ** When the [[Glossary#Multisite|Multisite]] feature enabled: |
||
+ | *** Add [[#Multisite Table Overview|multisite tables]]. |
||
+ | *** Add the <code>spam</code> field and the <code>deleted</code> field to [[#Table:_wp_users_2|users table]]. |
||
+ | |||
+ | * [[Version 2.9|2.9]] : |
||
+ | ** [[#Table: wp_commentmeta|commentmeta table]]: New table to house comment related data. |
||
+ | ** [[#Table: wp_option|options table]]: Added Index <code>option_name</code> UNIQUE. |
||
+ | ** [http://schemabank.com/p/5Lj5F updated diagram] |
||
+ | |||
+ | * [[Version 2.8|2.8]] : |
||
+ | ** [[#Table: wp_comments|comments table]]: Changed <code>comment_post_ID</code> column from <code>int(11)</code> to <code>bigint(20) unsigned</code>. |
||
+ | ** [[#Table: wp_links|links table]]: Deleted <code>link_category</code> column. Changed <code>link_owner</code> column from <code>int(11)</code> to <code>bigint(20) unsigned</code>. |
||
+ | ** [[#Table: wp_posts|posts table]]: Deleted <code>post_category</code> column. |
||
+ | ** [[#Table: wp_term_taxonomy|term_taxonomy table]]: Added KEY <code>taxonomy</code>. |
||
+ | ** Add <code>unsigned</code> attribute to various <code>bigint(20)</code> fields. |
||
+ | |||
+ | * [[Version 2.7|2.7]] : |
||
+ | ** [[#Table: wp_terms|term table]]: Changed <code>name</code> field <code>varchar(55)</code> -> <code>varchar(200)</code>. |
||
+ | ** Added KEY <code>name</code> to the [[#Table: wp_terms|term table]]. |
||
+ | ** Added KEY <code>post_parent</code> to the [[#Table: wp_posts|posts table]]. |
||
+ | |||
+ | * [[Version 2.5|2.5]] : |
||
+ | ** All of <code>enum</code> type fields -> <code>varchar(20)</code>: |
||
+ | *** [[#Table: wp_comments|comments table]]: <code>comment_approved</code> field |
||
+ | *** [[#Table: wp_links|links table]]: <code>link_visible</code> |
||
+ | *** [[#Table: wp_options|options table]]: <code>autoload</code> |
||
+ | *** [[#Table: wp_posts|posts table]]: <code>post_status</code>, <code>comment_status</code>, <code>ping_status</code> |
||
+ | ** Added KEY <code>comment_approved_date_gmt</code> and <code>comment_date_gmt</code> to the [[#Table: wp_comments|comments table]]. |
||
+ | ** Added <code>term_order</code> field to the [[#Table: term_relationships|term_relationships table]] |
||
+ | ** see also: [[Migrating Plugins and Themes to 2.5#Database changes|Migrating Plugins and Themes to 2.5 - Database changes]] |
||
+ | |||
+ | * [[Version 2.3|2.3]] : |
||
+ | ** Added the [[#Table: wp_terms|terms]], [[#Table: wp_term_taxonomy|term_taxonomy]] and [[#Table: wp_term_relationships|term_relationships]] tables. Removed <code>categories</code>, <code>link2cat</code> and <code>post2cat</code> tables. |
||
+ | ** Rremoved 6 fields from the [[#Table: wp_options|options table]]. (<code>option_can_override</code>, <code>option_type</code>, <code>option_width</code>, <code>option_height</code>, <code>option_description</code>, and <code>option_admin_level</code>) |
||
+ | ** Added new status <code>'pending'</code> for <code>post_status</code> field to the [[#Table: wp_posts|posts table]]. |
||
+ | ** see also: [[WordPress Taxonomy]] |
||
+ | |||
+ | * [[Version 2.1|2.1]] : |
||
+ | ** Removed the <code>linkcategories</code> table. linkcategories data were marged to the <code>categories</code> table, and IDs were reseted. |
||
+ | ** Added <code>post_type</code> field to the [[#Table: wp_posts|posts table]] (in 2.0.x, <code>post_status</code> = <code>'static'</code> for [[Pages]]) |
||
+ | ** Added new status <code>'future'</code> for <code>post_status</code> field to the [[#Table: wp_posts|posts table]], instead of <code>NOW()</code> in 2.0.x. |
||
+ | ** see also: [[Migrating Plugins and Themes to 2.1#Core WordPress Database Changes|Migrating Plugins and Themes to 2.1 - Core WordPress Database Changes]] |
||
[[Category:Getting Started]] |
[[Category:Getting Started]] |
||
[[Category:Advanced Topics]] |
[[Category:Advanced Topics]] |
||
[[Category:WordPress Development]] |
[[Category:WordPress Development]] |
||
+ | [[Category:WordPress Database]] |
||
+ | [[Category:Network]] |
Languages: English • العربية • 日本語 中文(简体) • 한국어 • Русский • (Add your language)
The following is an outline and description of the database tables created during the standard installation of WordPress. The database structure and the diagram below were last updated in version 4.4.
The only database supported by WordPress is MySQL version 5.0.15 or greater, or any version of MariaDB.
Also see prior versions of Database Descriptions for WordPress 1.5, WordPress 2.0, WordPress 2.2, WordPress 2.3, WordPress 2.5, WordPress 2.7, WordPress 2.8, WordPress 2.9, and WordPress 3.3.
Because WordPress interfaces with this database by itself, you as an end user, shouldn't have to worry much about its structure. If you're Writing a Plugin however, you may be interested in learning how WordPress stores its data and relationships. If you have already attempted to use the existing WordPress API to access the data you need but have determined it's not possible without accessing the database directly, WordPress provides the wpdb class to make this task easy.
The diagram below provides a visual overview of the WordPress database and the relations between the tables created during the WordPress standard installation. The Table Overview below includes additional details on the tables and columns.
Please note that within the standard installation of WordPress no integrity between the tables is enforced e.g. between posts and comments. If you are creating a plugin or extension that manipulates the WordPress database, your code should do the housekeeping so that no orphan records remain in the tables e.g. by removing records in other tables with a set of SQL commands when foreign keys are deleted (Don't forget to remind users to backup before such operations).
This section is the overview of all the tables created during the WordPress standard installation. It is followed by specific information of what is in each table.
WordPress 4.4 Tables (12) | ||
---|---|---|
Table Name | Description | Relevant Area(s) of WordPress User Interface |
wp_commentmeta | Each comment features information called the meta data and it is stored in the wp_commentmeta. | |
wp_comments | The comments within WordPress are stored in the wp_comments table. | |
wp_links | The wp_links holds information related to the links entered into the Links feature of WordPress. (This feature has been deprecated, but can be re-enabled with the Links Manager plugin.) |
|
wp_options | The Options set under the Administration > Settings panel are stored in the wp_options table. See Option Reference for option_name and default values. |
|
wp_postmeta | Each post features information called the meta data and it is stored in the wp_postmeta. Some plugins may add their own information to this table. |
|
wp_posts | The core of the WordPress data is the posts. It is stored in the wp_posts table. Also Pages and navigation menu items are stored in this table. |
|
wp_terms | The categories for both posts and links and the tags for posts are found within the wp_terms table. |
|
wp_termmeta | Each term features information called the meta data and it is stored in wp_termmeta. | |
wp_term_relationships | Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships table. The association of links to their respective categories are also kept in this table. | |
wp_term_taxonomy | This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table. | |
wp_usermeta | Each user features information called the meta data and it is stored in wp_usermeta. | |
wp_users | The list of users is maintained in table wp_users. |
The following are the specific fields in each of the tables created during the standard WordPress installation.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
meta_id | bigint(20) unsigned | PRI | auto_increment | ||
comment_id | bigint(20) unsigned | IND | 0 | ||
meta_key | varchar(255) | YES | IND | NULL | |
meta_value | longtext | YES | NULL |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | meta_id |
comment_id | INDEX | comment_id |
meta_key | INDEX | meta_key |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
comment_ID | bigint(20) unsigned | PRI | auto_increment | ||
comment_post_ID | bigint(20) unsigned | IND | 0 | ||
comment_author | tinytext | ||||
comment_author_email | varchar(100) | IND | |||
comment_author_url | varchar(200) | ||||
comment_author_IP | varchar(100) | ||||
comment_date | datetime | 0000-00-00 00:00:00 | |||
comment_date_gmt | datetime | IND & IND Pt2 | 0000-00-00 00:00:00 | ||
comment_content | text | ||||
comment_karma | int(11) | 0 | |||
comment_approved | varchar(20) | IND Pt1 | 1 | ||
comment_agent | varchar(255) | ||||
comment_type | varchar(20) | ||||
comment_parent | bigint(20) unsigned | IND | 0 | ||
user_id | bigint(20) unsigned | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | comment_ID |
comment_post_ID | INDEX | comment_post_ID |
comment_approved_date_gmt | INDEX | comment_approved comment_date_gmt |
comment_date_gmt | INDEX | comment_date_gmt |
comment_parent | INDEX | comment_parent |
comment_author_email | INDEX | comment_author_email |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
link_id | bigint(20) unsigned | PRI | auto_increment | ||
link_url | varchar(255) | ||||
link_name | varchar(255) | ||||
link_image | varchar(255) | ||||
link_target | varchar(25) | ||||
link_description | varchar(255) | ||||
link_visible | varchar(20) | IND | Y | ||
link_owner | bigint(20) unsigned | 1 | |||
link_rating | int(11) | 0 | |||
link_updated | datetime | 0000-00-00 00:00:00 | |||
link_rel | varchar(255) | ||||
link_notes | mediumtext | ||||
link_rss | varchar(255) |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | link_id |
link_visible | INDEX | link_visible |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
option_id | bigint(20) unsigned | PRI | auto_increment | ||
option_name | varchar(64) | UNI | |||
option_value | longtext | ||||
autoload | varchar(20) | IND | yes |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | option_id |
option_name | UNIQUE | option_name |
autoload | INDEX | autoload |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
meta_id | bigint(20) unsigned | PRI | auto_increment | ||
post_id | bigint(20) unsigned | IND | 0 | ||
meta_key | varchar(255) | YES | IND | NULL | |
meta_value | longtext | YES | NULL |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | meta_id |
post_id | INDEX | post_id |
meta_key | INDEX | meta_key |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint(20) unsigned | PRI & IND Pt4 | auto_increment | ||
post_author | bigint(20) unsigned | IND | 0 | ||
post_date | datetime | IND Pt3 | 0000-00-00 00:00:00 | ||
post_date_gmt | datetime | 0000-00-00 00:00:00 | |||
post_content | longtext | ||||
post_title | text | ||||
post_excerpt | text | ||||
post_status | varchar(20) | IND PT2 | publish | ||
comment_status | varchar(20) | open | |||
ping_status | varchar(20) | open | |||
post_password | varchar(20) | ||||
post_name | varchar(200) | IND | |||
to_ping | text | ||||
pinged | text | ||||
post_modified | datetime | 0000-00-00 00:00:00 | |||
post_modified_gmt | datetime | 0000-00-00 00:00:00 | |||
post_content_filtered | longtext | ||||
post_parent | bigint(20) unsigned | IND | 0 | ||
guid | varchar(255) | ||||
menu_order | int(11) | 0 | |||
post_type | varchar(20) | IND Pt1 | post | ||
post_mime_type | varchar(100) | ||||
comment_count | bigint(20) | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | ID |
post_name | INDEX | post_name |
type_status_date | INDEX | post_type post_status post_date ID |
post_parent | INDEX | post_parent |
post_author | INDEX | post_author |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
term_id | bigint(20) unsigned | PRI | auto_increment | ||
name | varchar(200) | IND | |||
slug | varchar(200) | MUL | |||
term_group | bigint(10) | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | term_id |
slug | UNIQUE | slug |
name | INDEX | name |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
meta_id | bigint(20) unsigned | PRI | auto_increment | ||
term_id | bigint(20) unsigned | IND | 0 | ||
meta_key | varchar(255) | YES | IND | NULL | |
meta_value | longtext | YES | NULL |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | meta_id |
term_id | INDEX | term_id |
meta_key | INDEX | meta_key |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
object_id | bigint(20) unsigned | PRI Pt1 | 0 | ||
term_taxonomy_id | bigint(20) unsigned | PRI Pt2 & IND | 0 | ||
term_order | int(11) | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | object_id term_taxonomy_id |
term_taxonomy_id | INDEX | term_taxonomy_id |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
term_taxonomy_id | bigint(20) unsigned | PRI | auto_increment | ||
term_id | bigint(20) unsigned | UNI Pt1 | 0 | ||
taxonomy | varchar(32) | UNI Pt2 & IND | |||
description | longtext | ||||
parent | bigint(20) unsigned | 0 | |||
count | bigint(20) | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | term_taxonomy_id |
term_id_taxonomy | UNIQUE | term_id taxonomy |
taxonomy | INDEX | taxonomy |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
umeta_id | bigint(20) unsigned | PRI | auto_increment | ||
user_id | bigint(20) unsigned | IND | 0 | ||
meta_key | varchar(255) | Yes | IND | NULL | |
meta_value | longtext | Yes | NULL |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | umeta_id |
user_id | INDEX | user_id |
meta_key | INDEX | meta_key |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint(20) unsigned | PRI | auto_increment | ||
user_login | varchar(60) | IND | |||
user_pass | varchar(64) | ||||
user_nicename | varchar(50) | IND | |||
user_email | varchar(100) | ||||
user_url | varchar(100) | ||||
user_registered | datetime | 0000-00-00 00:00:00 | |||
user_activation_key | varchar(60) | ||||
user_status | int(11) | 0 | |||
display_name | varchar(250) |
NOTE: Enabling Multisite feature of WordPress adds two fields in wp_users table: spam and deleted. Refer the Multisite version.
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | ID |
user_login_key | INDEX | user_login |
user_nicename | INDEX | user_nicename |
This section is the overview of the tables created for use with the Multisite feature of WordPress. These tables are created via the processes under Administration > Tools > Network.
These tables are considered the multisite global tables.
WordPress 3.0 Multisite Tables | ||
---|---|---|
Table Name | Description | Relevant Area(s) of WordPress User Interface |
wp_blogs | Each site created is stored in the table, wp_blogs. | |
wp_blog_versions | The current database version status of each site is maintained in the wp_blogs_versions table and is updated as each site is upgraded. | |
wp_registration_log | The wp_registration_log records the admin user created when each new site is created. | |
wp_signups | This table holds the user that have registered for a site via the login registration process. User registration is enable in Administration > Super Admin > Options. | |
wp_site | The wp_site table contains the main site address. | |
wp_sitemeta | Each site features information called the site data and it is stored in wp_sitemeta. Various option information, including the site admin is kept in this table. | |
wp_users | The list of all users is maintained in table wp_users. Multisite add two fields not in the stand-alone version. |
|
wp_usermeta | This table is not re-created for multisite, but meta data of users for each site are stored in wp_usermeta. |
|
Site Specific Tables | The data of the main site are stored in existing unnumbered tables. The data of additional sites are stored in new numbered tables. |
|
The following describe the tables and fields created during the network installation. Note that a global set of tables is created upon creation of the network, and site-specific tables are established as each site is created.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
blog_id | bigint(20) | PRI | auto_increment | ||
site_id | bigint(20) | 0 | |||
domain | varchar(200) | IND Pt1 | |||
path | varchar(100) | IND Pt2 | |||
registered | datetime | 0000-00-00 00:00:00 | |||
last_updated | datetime | 0000-00-00 00:00:00 | |||
public | tinyint(2) | 1 | |||
archived | tinyint(2) | 0 | |||
mature | tinyint(2) | 0 | |||
spam | tinyint(2) | 0 | |||
deleted | tinyint(2) | 0 | |||
lang_id | int(11) | IND | 0 |
NOTE: The blog_id identifies the site and the site_id identifies the network. It is the trace of the past when the 'site' was called as 'blog' in WordPress 2.x. If you add your site in the network, the new record will be inserted to this table wp_blogs with incremented blog_id and the same site_id.
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | blog_id |
domain | INDEX | domain path |
lang_id | INDEX | lang_id |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
blog_id | bigint(20) | PRI | 0 | ||
db_version | varchar(20) | IND | |||
last_updated | datetime | 0000-00-00 00:00:00 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | blog_id |
db_version | INDEX | db_version |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint(20) | PRI | auto_increment | ||
varchar(255) | |||||
IP | varchar(30) | IND | |||
blog_id | bigint(20) | 0 | |||
date_registered | datetime | 0000-00-00 00:00:00 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | ID |
IP | INDEX | IP |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
signup_id | bigint(20) | PRI | auto_increment | ||
domain | varchar(200) | IND Pt1 | |||
path | varchar(100) | IND Pt2 | |||
title | longtext | ||||
user_login | varchar(60) | IND Pt1 | |||
user_email | varchar(100) | IND & IND Pt2 | |||
registered | datetime | 0000-00-00 00:00:00 | |||
activated | datetime | 0000-00-00 00:00:00 | |||
active | tinyint(1) | ||||
activation_key | varchar(50) | IND | |||
meta | longtext | Yes | NULL |
Keyname | Type | Field |
---|---|---|
signup_id | PRIMARY | signup_id |
activation_key | INDEX | activation_key |
user_email | INDEX | user_email |
user_login_email | INDEX | user_login user_email |
domain_path | INDEX | domain path |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
id | bigint(20) | PRI | auto_increment | ||
domain | varchar(200) | IND Pt1 | |||
path | varchar(100) | IND Pt2 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | id |
domain | INDEX | domain path |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
meta_id | bigint(20) | PRI | auto_increment | ||
site_id | bigint(20) | 0 | |||
meta_key | varchar(255) | Yes | IND | NULL | |
meta_value | longtext | Yes | IND | NULL |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | meta_id |
meta_key | INDEX | meta_key |
site_id | INDEX | site_id |
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint(20) unsigned | PRI | auto_increment | ||
user_login | varchar(60) | IND | |||
user_pass | varchar(64) | ||||
user_nicename | varchar(50) | IND | |||
user_email | varchar(100) | ||||
user_url | varchar(100) | ||||
user_registered | datetime | 0000-00-00 00:00:00 | |||
user_activation_key | varchar(60) | ||||
user_status | int(11) | 0 | |||
display_name | varchar(250) | ||||
spam | tinyint(2) | 0 | |||
deleted | tinyint(2) | 0 |
Keyname | Type | Field |
---|---|---|
PRIMARY | PRIMARY | ID |
user_login_key | INDEX | user_login |
user_nicename | INDEX | user_nicename |
When a new additional site is created, the site-specific tables, similar to the stand-alone tables above, are created. Each set of tables for a site are created with the site ID (blog_id
) as part of the table name. These are the tables that would be created for site ID 2 and table_prefix wp_:
The data of the main site are stored in unnumbered tables.
The database scheme is defined in wp-admin/includes/schema.php
comment_approved
.blog_id
.post_content_filtered
from text to longtext.comment_parent
.post_author
.spam
field and the deleted
field to users table.option_name
UNIQUE.comment_post_ID
column from int(11)
to bigint(20) unsigned
.link_category
column. Changed link_owner
column from int(11)
to bigint(20) unsigned
.post_category
column.taxonomy
.unsigned
attribute to various bigint(20)
fields.name
field varchar(55)
-> varchar(200)
.name
to the term table.post_parent
to the posts table.enum
type fields -> varchar(20)
:
comment_approved
fieldlink_visible
autoload
post_status
, comment_status
, ping_status
comment_approved_date_gmt
and comment_date_gmt
to the comments table.term_order
field to the term_relationships tablecategories
, link2cat
and post2cat
tables.option_can_override
, option_type
, option_width
, option_height
, option_description
, and option_admin_level
)'pending'
for post_status
field to the posts table.linkcategories
table. linkcategories data were marged to the categories
table, and IDs were reseted.post_type
field to the posts table (in 2.0.x, post_status
= 'static'
for Pages)'future'
for post_status
field to the posts table, instead of NOW()
in 2.0.x.