Database Description

来自WordPress中文文档
跳转到： 导航, 搜索

wordpress.org.cn

目录

The following is an outline and description of the database tables created during the standard installation of WordPress Version 3.0. Currently, the only database supported by WordPress is MySQL version 4.1.2 or greater.

Also see prior versions of Database Descriptions for WordPress 2.0, WordPress 2.2, WordPress 2.3, WordPress 2.5, WordPress 2.7, WordPress 2.8, and WordPress 2.9.

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 it's 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.

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.

文件:WP3.0-ERD.png
(WP 3.0 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.

strongWordPress 3.0 Tables (11)/strong
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 ttwp_commentmeta/tt. Typically plugins would be add their own information to this table.
wp_comments The comments within WordPress are stored in the ttwp_comments/tt table. Comments are created by readers as responses to posts. Comments are managed by administrator via Administration Comments Comments
wp_links The ttwp_links/tt holds information related to the links entered into the Links feature of WordPress.
wp_options The Options set under the Administration Settings panel are stored in the ttwp_options/tt table. See Option Reference for codeoption_name/code and default values.
wp_postmeta Each post features information called the meta data and it is stored in the ttwp_postmeta/tt. 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 ttwp_posts/tt 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 ttwp_terms/tt table.
wp_term_relationships Posts are associated with categories and tags from the ttwp_terms/tt table and this association is maintained in the ttwp_term_relationships/tt 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 ttwp_terms/tt table.
wp_usermeta Each user features information called the meta data and it is stored in ttwp_usermeta/tt.
wp_users The list of users is maintained in table ttwp_users/tt.
br /br /

Table Details

The following are the specific fields in each of the tables created during the standard WordPress installation.

Table: wp_commentmeta

Field Type Null Key Default Extra
meta_id bigint(20) unsigned nbsp; PRI NULL auto_increment
comment_id bigint(20) unsigned nbsp; IND 0 FK-gt;wp_comments.comment_id
meta_key varchar(255) YES IND NULL nbsp;
meta_value longtext YES nbsp; NULL nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 0 meta_ID
comment_id INDEX none comment_id
meta_key INDEX none meta_key

Table: wp_comments

Field Type Null Key Default Extra
comment_ID bigint(20) unsigned nbsp; PRI NULL auto_increment
comment_post_ID bigint(20) unsigned nbsp; IND 0 FK-gt;wp_posts.ID
comment_author tinytext nbsp; nbsp; nbsp; nbsp;
comment_author_email varchar(100) nbsp; nbsp; nbsp; nbsp;
comment_author_url varchar(200) nbsp; nbsp; nbsp; nbsp;
comment_author_IP varchar(100) nbsp; nbsp; nbsp; nbsp;
comment_date datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
comment_date_gmt datetime nbsp; IND IND Pt2 0000-00-00 00:00:00 nbsp;
comment_content text nbsp; nbsp; nbsp; nbsp;
comment_karma int(11) nbsp; nbsp; 0 nbsp;
comment_approved varchar(20) nbsp; IND Ind Pt1 1 nbsp;
comment_agent varchar(255) nbsp; nbsp; nbsp; nbsp;
comment_type varchar(20) nbsp; nbsp; nbsp; nbsp;
comment_parent bigint(20) unsigned nbsp; nbsp; 0 FK-gt;wp_comments.ID
user_id bigint(20) unsigned nbsp; nbsp; 0 FK-gt;wp_users.ID

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 comment_ID
comment_approved INDEX None comment_approved
comment_post_ID INDEX None comment_post_ID
comment_approved_date_gmt INDEX None comment_approvedbr /comment_date_gmt
comment_date_gmt INDEX None comment_date_gmt
comment_parent INDEX None comment_parent

Table: wp_links

Field Type Null Key Default Extra
link_id bigint(20) unsigned nbsp; PRI NULL auto_increment
link_url varchar(255) nbsp; nbsp; nbsp; nbsp;
link_name varchar(255) nbsp; nbsp; nbsp; nbsp;
link_image varchar(255) nbsp; nbsp; nbsp; nbsp;
link_target varchar(25) nbsp; nbsp; nbsp; nbsp;
link_description varchar(255) nbsp; nbsp; nbsp; nbsp;
link_visible varchar(20) nbsp; IND Y nbsp;
link_owner bigint(20) unsigned nbsp; nbsp; 1 nbsp;
link_rating int(11) nbsp; nbsp; 0 nbsp;
link_updated datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
link_rel varchar(255) nbsp; nbsp; nbsp; nbsp;
link_notes mediumtext nbsp; nbsp; nbsp; nbsp;
link_rss varchar(255) nbsp; nbsp; nbsp; nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 7 link_ID
link_category INDEX None link_category
link_visible INDEX None link_visible

Table: wp_options

Field Type Null Key Default Extra
option_id bigint(20) unsigned nbsp; PRI Pt1 NULL auto_increment
blog_id int(11) nbsp; PRI Pt2 0 nbsp;
option_name varchar(64) nbsp; PRI Pt3 IND nbsp; nbsp;
option_value longtext nbsp; nbsp; nbsp;
autoload varchar(20) nbsp; nbsp; yes nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 184 option_idbr /blog_idbr /option_name
option_name UNIQUE 184 option_name

Table: wp_postmeta

Field Type Null Key Default Extra
meta_id bigint(20) unsigned nbsp; PRI NULL auto_increment
post_id bigint(20) unsigned nbsp; IND 0 FK-gt;wp_posts.ID
meta_key varchar(255) YES IND NULL nbsp;
meta_value longtext YES nbsp; NULL nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 13 meta_ID
post_id INDEX 15 post_id
meta_key INDEX 7 meta_key

Table: wp_posts

Field Type Null Key Default Extra
ID bigint(20) unsigned nbsp; PRI IND Pt4 nbsp; auto_increment
post_author bigint(20) unsigned nbsp; nbsp; 0 FK-gt;wp_users.ID
post_date datetime nbsp; IND Pt3 0000-00-00 00:00:00 nbsp;
post_date_gmt datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
post_content longtext nbsp; nbsp; nbsp; nbsp;
post_title text nbsp; nbsp; nbsp; nbsp;
post_excerpt text nbsp; nbsp; nbsp; nbsp;
post_status varchar(20) nbsp; IND PT2 publish nbsp;
comment_status varchar(20) nbsp; nbsp; open nbsp;
ping_status varchar(20) nbsp; nbsp; open nbsp;
post_password varchar(20) nbsp; nbsp; nbsp; nbsp;
post_name varchar(200) nbsp; IND nbsp; nbsp;
to_ping text nbsp; nbsp; nbsp; nbsp;
pinged text nbsp; nbsp; nbsp; nbsp;
post_modified datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
post_modified_gmt datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
post_content_filtered text nbsp; nbsp; nbsp;
post_parent bigint(20) unsigned nbsp; nbsp; 0 FK-gt;wp_posts.ID
guid varchar(255) nbsp; nbsp; nbsp; nbsp;
menu_order int(11) nbsp; nbsp; 0 nbsp;
post_type varchar(20) nbsp; IND Pt1 post nbsp;
post_mime_type varchar(100) nbsp; nbsp; nbsp; nbsp;
comment_count bigint(20) nbsp; nbsp; 0 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 ID
post_name INDEX None post_name
type_status_date INDEX None post_typebr /post_statusbr /post_datebr /ID
post_parent INDEX None post_parent
post_author INDEX None post_author

Table: wp_terms

Field Type Null Key Default Extra
term_id bigint(20) unsigned nbsp; PRI nbsp; auto_increment
name varchar(200) nbsp; nbsp; nbsp; nbsp;
slug varchar(200) nbsp; UNI nbsp; nbsp;
term_group bigint(10) nbsp; nbsp; 0 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 term_ID
slug UNIQUE 2 slug
name Index none name

Table: wp_term_relationships

Field Type Null Key Default Extra
object_id bigint(20) unsigned nbsp; PRI Pt1 0 nbsp;
term_taxonomy_id bigint(20) unsigned nbsp; PRI Pt2 IND 0 FK-gt;wp_term_taxonomy.term_taxonomy_id
term_order int(11) nbsp; nbsp; 0 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 8 object_idbr /term_taxonomy_id
term_taxonomy_id INDEX None term_taxonomy_id

Table: wp_term_taxonomy

Field Type Null Key Default Extra
term_taxonomy_id bigint(20) unsigned nbsp; PRI nbsp; auto_increment
term_id bigint(20) unsigned nbsp; UNI Pt1 0 FK-gt;wp_terms.term_id
taxonomy varchar(32) nbsp; UNI Pt2 nbsp; nbsp;
description longtext nbsp; nbsp; nbsp; nbsp;
parent bigint(20) unsigned nbsp; nbsp; 0 nbsp;
count bigint(20) nbsp; nbsp; 0 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 term_taxonomy_id
term_id_taxonomy UNIQUE 2 term_idbr /taxonomy
taxonomy INDEX None taxonomy

Table: wp_usermeta

Field Type Null Key Default Extra
umeta_id bigint(20) unsigned nbsp; PRI NULL auto_increment
user_id bigint(20) unsigned nbsp; nbsp; '0' FK-gt;wp_users.ID
meta_key varchar(255) Yes IND NULL nbsp;
meta_value longtext Yes IND NULL nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 9 umeta_id
user_id INDEX None user_id
meta_key INDEX None meta_key

Table: wp_users

Field Type Null Key Default Extra
ID bigint(20) unsigned nbsp; PRI NULL auto_increment
user_login varchar(60) nbsp; IND nbsp; nbsp;
user_pass varchar(64) nbsp; nbsp; nbsp; nbsp;
user_nicename varchar(50) nbsp; IND nbsp; nbsp;
user_email varchar(100) nbsp; nbsp; nbsp; nbsp;
user_url varchar(100) nbsp; nbsp; nbsp; nbsp;
user_registered datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
user_activation_key varchar(60) nbsp; nbsp; nbsp;
user_status int(11) nbsp; nbsp; 0 nbsp;
display_name varchar(250) nbsp; nbsp; nbsp; nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 ID
user_login_key INDEX None user_login
user_nicename INDEX None user_nicename

Multisite Table Overview

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.

strongWordPress 3.0 Multisite Tables/strong
Table Name Description Relevant Area(s) of WordPress User Interface
wp_blogs Each site created is stored in the table, ttwp_blogs/tt.
wp_blog_versions The current database version status of each site is maintained in the ttwp_blogs_versions/tt table and is updated as each site is upgraded.
wp_registration_log The ttwp_registration_log/tt 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 ttwp_site/tt table contains the main site address.
wp_sitecategories If global terms (global_terms_enabled = true) are enabled for a site the ttwp_sitecategories/tt table holds those terms.
wp_sitemeta Each site features information called the site data and it is stored in ttwp_sitemeta/tt. Various option information, including the site admin is kept in this table.
wp_users The list of all users is maintained in table ttwp_users/tt. Multisite add two fields not in the stand-alone version.
wp_usermeta This table is not re-create for multisite, but meta data of users for each site are stored in ttwp_usermeta/tt.
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.
br /br /

Multisite Table Details

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.

Table: wp_blogs

Field Type Null Key Default Extra
blog_id bigint(20) unsigned nbsp; PRI NULL auto_increment
site_id bigint(20) unsigned nbsp; IND 0
domain varchar(200) NO nbsp; 0 nbsp;
path varchar(100) NO nbsp; nbsp; nbsp;
registered datetime NO nbsp; 0000-00-00 00:00:00 nbsp;
last_updated datetime NO nbsp; 0000-00-00 00:00:00 nbsp;
public tinyint(2) NO nbsp; 0 nbsp;
archived enum('0','1') NO nbsp; 0 nbsp;
mature tinyint(2) NO nbsp; 0 nbsp;
spam tinyint(2) NO nbsp; 0 nbsp;
deleted tinyint(2) NO nbsp; 0 nbsp;
lang_id int(11) NO nbsp; 0 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 blog_id
domain INDEX none domain(50),path(5)
lang_id INDEX none lang_id

Table: wp_blog_versions

Field Type Null Key Default Extra
blog_id bigint(20) unsigned nbsp; PRI 0 FK-wp_blogs.blog_id
db_version varchar(20) NO nbsp; nbsp; nbsp;
last_updated datetime NO nbsp; 0000-00-00 00:00:00 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 2 blog_id
db_version INDEX none db_version

Table: wp_registration_log

Field Type Null Key Default Extra
ID bigint(20) unsigned nbsp; PRI NULL auto_increment
email varchar(255) NO nbsp; nbsp; nbsp;
IP varchar(30) NO nbsp; nbsp; nbsp;
blog_id bigint(20) unsigned nbsp; PRI 0 FK-wp_blogs.blog_id
date_registered datetime NO nbsp; 0000-00-00 00:00:00 nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 ID
IP INDEX none IP

Table: wp_signups

Field Type Null Key Default Extra
domain varchar(200) NO nbsp; 0 nbsp;
path varchar(100) NO nbsp; nbsp; nbsp;
title longtext NO nbsp; nbsp; nbsp;
user_login varchar(60) NO IND nbsp; nbsp;
user_email varchar(100) NO nbsp; nbsp; nbsp;
registered_date datetime NO nbsp; 0000-00-00 00:00:00 nbsp;
activated datetime NO nbsp; 0000-00-00 00:00:00 nbsp;
active tinyint(1) NO nbsp; nbsp;
activation_key varchar(50) NO nbsp; nbsp; nbsp;
meta longtext nbsp; nbsp; nbsp; nbsp;

Indexes

Keyname Type Cardinality Field
activation_key INDEX None activation_key
domain INDEX None domain

Table: wp_site

Field Type Null Key Default Extra
id bigint(20) unsigned nbsp; PRI NULL auto_increment
domain varchar(200) NO nbsp; 0 nbsp;
path varchar(100) NO nbsp; nbsp; nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 id
domain INDEX none domain,path

Table: wp_sitecategories

Field Type Null Key Default Extra
cat_id bigint(20) unsigned NO PRI NULL auto_increment
cat_name carchar(55) NO nbsp; nbsp; nbsp;
category_nicename varchar(200) NO nbsp; nbsp; nbsp;
last_updated timestamp NO nbsp; nbsp; nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 9 cat_id
category_nicename INDEX None category_nicename
last_updated INDEX None last_updated

Table: wp_sitemeta

Field Type Null Key Default Extra
meta_id bigint(20) unsigned nbsp; PRI NULL auto_increment
site_id bigint(20) unsigned nbsp; nbsp; '0' FK-gt;wp_site.site_id
meta_key varchar(255) Yes IND NULL nbsp;
meta_value longtext Yes IND NULL nbsp;

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 9 meta_id
meta_key INDEX None meta_key
site_id INDEX None site_id

Table: wp_users

Field Type Null Key Default Extra
ID bigint(20) unsigned nbsp; PRI NULL auto_increment
user_login varchar(60) nbsp; IND nbsp; nbsp;
user_pass varchar(64) nbsp; nbsp; nbsp; nbsp;
user_nicename varchar(50) nbsp; IND nbsp; nbsp;
user_email varchar(100) nbsp; nbsp; nbsp; nbsp;
user_url varchar(100) nbsp; nbsp; nbsp; nbsp;
user_registered datetime nbsp; nbsp; 0000-00-00 00:00:00 nbsp;
user_activation_key varchar(60) nbsp; nbsp; nbsp;
user_status int(11) nbsp; nbsp; 0 nbsp;
display_name varchar(250) nbsp; nbsp; nbsp; nbsp;
spam tinyint(2) NO nbsp; 0
deleted tinyint(2) NO nbsp; 0

Indexes

Keyname Type Cardinality Field
PRIMARY PRIMARY 1 ID
user_login_key INDEX None user_login
user_nicename INDEX None user_nicename

Site Specific Tables

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 (codeblog_id/code) 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.

Resources

Changelog

  • 2.8 :
    • comments table: Changed codecomment_post_ID/code column from codeint(11)/code to codebigint(20) unsigned/code.
    • links table: Deleted codelink_category/code column. Changed codelink_owner/code column from codeint(11)/code to codebigint(20) unsigned/code.
    • posts table: Deleted codepost_category/code column.
    • term_taxonomy table: Added KEY codetaxonomy/code.
    • Add codeunsigned/code attribute to various codebigint(20)/code fields.
  • 2.7 :
    • term table: Changed codename/code field codevarchar(55)/code - codevarchar(200)/code.
    • Added KEY codename/code to the term table.
    • Added KEY codepost_parent/code to the posts table.
  • 2.3 :
    • Added the terms, term_taxonomy and term_relationships tables. Removed codecategories/code, codelink2cat/code and codepost2cat/code tables.
    • Rremoved 6 fields from the options table. (codeoption_can_override/code, codeoption_type/code, codeoption_width/code, codeoption_height/code, codeoption_description/code, and codeoption_admin_level/code)
    • Added new status code'pending'/code for codepost_status/code field to the posts table.
    • see also: WordPress Taxonomy
来自“http://codex.wordpress.org.cn/index.php?title=Database_Description&oldid=143851”
用户
名字空间
变换
操作
导航
WordPress
工具箱