WordPress table structure

WordPress only uses 10 tables: wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms, wp_usermeta, wp_users

There are roughly five categories by function
User information: wp_users and wp_usermeta
Link info: wp_links
Article and comment: wp_posts, wp_postmeta, wp_comments
Classification, link classification, tag management: wp_term, wp_term_relationships, wp_term_taxonomy
Global settings: wp_options

Wp_posts
This wp_posts table is the place where the blog posts "articles" are stored. In addition to common articles, this table also contains information about attachments and pages. The post_type field is used to differentiate document types. If post_type is 'post', It is an article. If it is 'page', It is a page. If it is 'attachment ', it is an attachment.

Wp_postmeta
This table is very simple. Only meta_id, post_id, meta_key, and meta_value fields are supported. Post_id is the ID of the related "article. Meta_value is of the longtext type. It is only used to store values. When writing an article, there is a custom fields option under the edit box. We can add post meta information here.

Wp_comments
The two most important fields are comment_post_id and comment_approved. The first one is used to indicate which article the comment belongs to and the other one is used to record the review status. Another interesting thing is the commnet_agent field. You can use this field to calculate the type of your browser.

Wp_users
User Account table. The storage username and password also contain some basic user information.

Wp_usermeta
Similar to the preceding wp_postmeta, it stores some other user information.

Wp_options
Used to record some WordPress settings and options. There is a blog_id field, which should be used in the Mu version to mark different blogs. The autoload field is used to control whether the options are always imported and cached by WordPress or plug-ins, or whether the options are imported only when required.

Wp_links
Used to store links in blogroll.

Wp_terms
It stores the basic information of a term. Name is the name of the term, and slug is used to make the URL friendly. Term_group is used to aggregate similar terms. Term_id is the unique ID of a term.

Wp_term_taxonomy
CATEGORY information supplements the relationship information of wp_terms information, including the category, link_category, tag, and detailed description of the number of articles (LINKS) owned by wp_terms.

Wp_term_relationships
Associate posts and links objects with the relational table term_taxonomy_id in the term_taxonomy table. object_id is associated with different objects, such as IDS in wp_posts (link_id in wp_links, term_taxonomy_id is the term_taxonomy_id associated with wp_term_taxonomy.

WordPress uses the MySQL database. As a developer, We need to master the basic structure of the WordPress database and use them in our own plug-ins or themes.

As of wordpress3.0, WordPress has the following 11 tables. The default table prefix WP _ is added here _.

Wp_commentmeta: stores the metadata of comments.
Wp_comments: store comments
Wp_links: blogroll)
Wp_options: stores WordPress System Options, plug-ins, and topic configurations.
Wp_postmeta: stores the metadata of an article (including pages, uploaded files, and revisions ).
Wp_posts: stores articles (including pages, uploaded files, and revisions)
Wp_terms: stores each directory and tag
Wp_term_relationships: stores the relationship between each article, link, and corresponding category.
Wp_term_taxonomy: stores the categories corresponding to each directory and tag.
Wp_usermeta: stores user metadata.
Wp_users: storage user

In the WordPress database structure, the wp_options table in the storage system options and plug-in configuration is relatively independent. As mentioned later, it adopts the key-value mode for storage, the advantage of doing so is that it is easy to expand, and various plug-ins can easily store their own configurations here.

Post, comment, and user are the combination of three basic tables and extended tables. Taking wp_users as an example, wp_users has stored the basic information that each user will use, such as common information such as login_name, display_name, password, and email. However, if we want to store some infrequently used data, the best practice is not to add a column after the table to destroy the default table structure, but to store data in wp_usermeta. The wp_usermeta extension table has a similar structure as the wp_options table. We can store QQ numbers, mobile phone numbers, and theme options for logging on to the WordPress background for each user.

It is hard to understand the term, that is, wp_terms, wp_term_relationships, and wp_term_taxonomy. In Wordpress systems, we often classify articles, links, and tags. It is also a special classification method, we can even create our own classification methods. WordPress records all categories, classification methods, and corresponding structures in these three tables. Wp_terms records the names and basic information of each category. For example, this site is divided into "WordPress development" and "wpceo plug-in". The category here refers to the classification in a broad sense, therefore, each tag is also a "classification ". Wp_term_taxonomy records the classification methods of each category. For example, "WordPress development" and "wpceo plug-in" are document categories ), the "my friends" and "my colleagues" categories that place links belong to the link category (link_category ). Wp_term_relationships records the classification method corresponding to each article (or link.

Fortunately, regarding the use of term, the use of related functions in WordPress is clear and clear, so we don't have to worry about its construction.

 

 

In the above section, we have introduced the functions of each table in the WordPress database. This article will continue to introduce the functions of each column in each table. WordPress official documentation has a detailed table. This article only introduces common data.

Wp_commentmeta
Meta_id: Unique auto-increment ID
Comment_id: Comment ID
Meta_key: key name
Meta_value: Key Value

 
Wp_comments
Comment_id: auto-increment unique ID
Comment_post_id: Document ID
Comment_author: reviewer
Comment_author_email: reviewer's email
Comment_author_url: reviewer URL
Comment_author_ip: comment_author_ip
Comment_date: Comment time
Comment_date_gmt: Comment time (GMT + 0 time)
Comment_content: Comment body
Comment_karma: Unknown
Comment_approved: whether the comment is approved
Comment_agent: reviewer's user agent
Comment_type: Comment type (pingback/normal)
Comment_parent: parent comment ID
User_id: reviewer user ID (not necessarily exists)

Wp_links
Link_id: auto-increment unique ID
Link_url: link URL
Link_name: link title
Link_image: link Image
Link_target: Link Opening Method
Link_description: Link Description
Link_visible: visible (y/N)
Link_owner: the user ID of the adder.
Link_rating: Rating
Link_updated: Unknown
Link_rel: xfn relationship
Link_notes: xfn Annotation
Link_rss: link RSS address

 

Wp_options
Option_id: auto-increment unique ID
Blog_id: blog ID, used for multi-user blogs. The default value is 0.
Option_name: key name
Option_value: Key Value
Autoload: automatically loaded during WordPress loading (Yes/No)

 

Wp_postmeta
Meta_id: Unique auto-increment ID
Post_id: Document ID
Meta_key: key name
Meta_value: Key Value

 

Wp_posts
ID: auto-increment unique ID
Post_author: Corresponding author ID
Post_date: release time
Post_date_gmt: release time (GMT + 0)
Post_content: Body
Post_title: Title
Post_excerpt: Excerpt
Post_status: Article status (publish/auto-Draft/inherit)
Comment_status: Comment status (Open/closed)
Ping_status: Ping status (Open/closed)
Post_password: password of the document
Post_name: name of the article
To_ping: Unknown
Pinged: The Ping link.
Post_modified: modification time
Post_modified_gmt: modification time (GMT + 0 time)
Post_content_filtered: Unknown
Post_parent: parent document, mainly used for page
Guid: Unknown
Menu_order: Sorting ID
Post_type: Document Type (post/Page, etc)
Post_mime_type: MIME type
Comment_count: Total number of comments

 

Wp_terms
Term_id: Category ID
Name: category name
Slug: Scaling name
Term_group: Unknown
Wp_term_relationships
Object_id: Document ID/link ID
Term_taxonomy_id: ID of the corresponding classification method
Term_order: Sorting
Wp_term_taxonomy
Term_taxonomy_id: Category method ID
Term_id: taxonomy: Classification Method (category/post_tag)
Description: Unknown
Parent: parent category method ID
Count: number of articles

 

Wp_usermeta
Umeta_id: Unique id of auto-Increment
User_id: corresponding user ID
Meta_key: key name
Meta_value: Key Value

 

Wp_users
ID: auto-increment unique ID
User_login: Login Name
User_pass: Password
User_nicename: nickname
User_email: Email
User_url: URL
User_registered: Registration Time
User_activation_key: activation code
User_status: User status
Display_name: Display name

Tables, fields, types, and descriptions in the WordPress Database

The fields of each table In Wordpress are essential for Wordpress ~

Wp_categories:The table used to save classification-related information. Five fields are included:

• Cat_id-Unique id of each category, which is a bigint (20) value and has an additional Property auto_increment.
• Cat_name-name of a category, which is a varchar (55) value.
• Category_nicename-an easy-to-remember name specified for the classification, that is, the so-called slug, which is a varchar (200) value.
• Category_description-a detailed description of a category, which is a longtext value.
• Category_parent-the upper-level category of the category, which is an int (4) value, corresponding to the cat_id in the current table, that is, wp_categories.cat_id. If there is no upper-level classification, this value is 0.

Wp_comments:The table used to save comment information. Including 15 fields:

• Comment_id-the unique ID of each comment, which is a bigint (20) value. With the additional property auto_increment.
• Comment_post_id-ID of the article corresponding to each comment, INT (11) value, equivalent to wp_posts.id.
• Comment_author-the comments name and tinytext value for each comment.
• Comment_author_email-the email address of the reviewer for each comment, varchar (100) value.
• Comment_author_url-the comments URL for each comment, varchar (200) value.
• Comment_author_ip-IP address of each comment reviewer, varchar (100) value.
• Comment_date-the time when each comment is published, datetime value (the value after the time zone offset is added ).
• Comment_date_gmt-the time when each comment is published, datetime value (Standard Greenwich Mean Time ).
• Comment_content-the text value of each comment.
• Comment_karma-unknown, INT (11) value. The default value is 0.
• Comment_approved-the current status of each comment, which is an Enum ('0', '1', 'spam'), 0 is pending for review, 1 is allowed to publish, spam is a spam comment. The default value is 1.
• Comment_agent-client information of each comment reviewer, varchar (255) value, mainly including the type and version of the browser and operating system.
• Comment_type-unknown, varchar (20) value.
• Comment_parent-the comment of a comment. The value of INT (11) corresponds to wp_comment.id. The default value is 0, indicating that no comment is sent.
• User_id-the user ID corresponding to a comment. It is generated only after the user is registered. The int (11) value corresponds to wp_users.id. For unregistered users, that is, external reviewers, the value of this ID is 0.

Wp_linkcategories:The table that stores the information about the link added in the WP background. Including 13 fields:

• Cat_id-the unique ID of each link category. The value of bigint (20) is an auto-increment auto_increment.
• Cat_name-name of each link category, tinytext value.
• Auto_toggle-this field contains a special attribute. If it is Y, when a new link is added to the category, other links become invisible. It is an Enum ('y', 'n') of the enumerated type. The default value is N.
• Show_images-this field is also the enum ('y', 'n') enumerated value. The default value is Y. You can specify whether to allow image links to be displayed in the Link category.
• Show_description-this field specifies the links under the corresponding link category. Do you want to [wrap] them? This is an Enum ('y', 'n') enumerated value '), the default value is N, indicating that the description is not displayed (but the description is displayed in the title attribute ).
• Show_rating-displays the level of The Link under this category. It is also an Enum ('y', 'n') enumerated value. The default value is Y. In this case, you can sort the links under the link category by link level.
• Show_updated-specifies whether the link category is updated and displayed. The Enum ('y', 'n') is the enumerated value. The default value is Y.
• Sort_order-specifies the sort basis of links in the Link category, varchar (64) value. Generally, the name (name, namely wp_links.link_name) or ID (ID, that is, wp_links.link_id) of the link is used ).
• Sort_desc-specify the sorting method of link classification. The Enum ('y', 'n') is used as the enumerated value. The default value is N, which is descending.
• Text_before_link-the front HTML text and varchar (128) value of each link under the link category. The default value is 'list start tag '.
• Text_after_link-the HTML text between the link and the description text (wp_links.link_description) in each link under the link category. varchar (128) value. The default value is 'line feed tag '.
• Text_after_all-the post-HTML text and varchar (128) value of each link under the link category. The default value is 'list end label '.
• List_limit-used to specify the information displayed in a link category (configurable ?) The number of links. INT (11) value. The default value is-1, which means there is no limit on the number of links under the link category.

Wp_links:The table used to save the Link (via link manager) that the user inputs to Wordpress. 14 fields in total:

• Link_id-Unique id of each link, which is a bigint (20) value. The additional attribute is auto_increment.
• Link_url-the URL address of each link, varchar (255) value, in the form of an address starting with http.
• Link_name-name of a single link, varchar (255) value.
• Link_image-link can be defined as an image link. This field is used to save the image address, which is a varchar (255) value.
• Link_target-there are three ways to open a link. _ blank is opened in a new window, _ top is opened in this window and is at the top level. None is not selected, will open in this window. This field is the value of varchar (25.
• Link_category-the link category corresponding to a link, which is the INT (11) value. It is equivalent to wp_linkcategories.cat_id.
• Link_description-Description of the link. You can choose whether to display the link at the bottom or in the title attribute. Varchar (255) value.
• Link_visible-whether the link is available. Enum ('y', 'n') values are enumerated. The default value is Y, which is visible.
• Link_owner-the creator of a link, which is an int (11) value. The default value is 1. (The corresponding wp_users.id should be used)
• Link_rating-link level, INT (11) value. The default value is 0.
• Link_updated-the time when the link is defined and modified, datetime value.
• Link_rel-relationship between the link and the definer, set by xfn creator and varchar (255) value.
• Link_notes-link details, mediumtext value.
• Link_rss-the RSS address of the link. The value of varchar (255.

Wp_options:The table used to save WordPress settings and parameters. There are 11 fields in total. The most important is the option_value field, which contains a large amount of important information.

• Option_id-option ID, bigint (20) value, appended auto_increment attribute.
• Blog_id-Unknown. It may not be important to use the Wordpress version of a single user. It may be a value set for the blog of different users. INT (11) value. The default value is 0, that is, the current blog.
• Option_name-option name, varchar (64) value.
• Option_can_override-whether this option can be overwritten or updated. Enum ('y', 'n') values can be enumerated. The default value is Y, which can be overwritten or updated.
• Option_type-the type of the option. The function is unknown. INT (11) value. The default value is 1.
• Option_value-the value of the option, longtext value, the content of this field is more important. During WordPress initialization, about 70 default values are set.
• Option_width-the width (?) of the option (?), The role is unknown. INT (11) value. The default value is 20.
• Option_height-the height of the option (?), The role is unknown. INT (11) value. The default value is 8.
• Option_description-specifies the tinytext value for an option.
• Option_admin_level-sets the user level that can be manipulated by an option (for details, see my related articles). The value of INT (11) is 1 by default.
• Whether the autoload-option is automatically loaded every time. The Enum ('yes', 'no') value is enumerated. The default value is yes.

Wp_post2cat:A table that stores the relationship between an article (posts) and a category. There are only three fields:

• Rel_id-correlation ID, bigint (20) value, is an auto increment with the auto_increment attribute.
• Post_id-the Article ID, bigint (20) value, equivalent to wp_posts.id.
• Category_id-category ID, which is also a bigint (20) value, which is equivalent to wp_categories.id.

The relationship between the article and classification is formed as follows: rel_id is an increasing auto increment used to identify each different post. Post_id can be repeated (when it corresponds to multiple categories), because it can be identified by rel_id, so there will be no confusion. When each post_id can correspond to multiple category_id, a combination of rel_id and post_id can recognize a specific category. Therefore, the classification of each article can be different. Using this table, you can quickly and efficiently find out which categories A post corresponds to, and vice versa.

Wp_postmeta:The table used to save the meta information of an article. There are four fields:

• Meta_id-Metadata ID, which is a bigint (20) value. The additional attribute is auto_increment.
• Post_id-Article ID, bigint (20) value, equivalent to wp_posts.id.
• Meta_key-Keyword of meta information, varchar (255) value.
• Meta_value-the value of metadata, text value.

The content is mainly set under "Add a new custom field to this post (page):" On the article and page editing page (write post, write page. Meta_key corresponds to the item in the drop-down list named "key", and the value is filled by the user (in some cases, WP is automatically added, such as the audio media in the article ).

Wp_posts:It is very important to store tables related to all your articles (posts. Generally, it stores the most data. A total of 21 fields are included.
ID-the unique ID of each article, which is a bigint (20) value and an additional Property auto_increment.

• Post_author-the number of the author of each article. The value of INT (4) corresponds to wp_users.id.
• Post_date-the time when each article was published, datetime value. It is the result of the GMT time plus the time zone offset.
• Post_date_gmt-The GMT (Greenwich Mean Time) and datetime value when each article is published.
• Post_content-the specific content of each article, longtext value. All the content you write on the Background Article editing page is placed here.
• Post_title-the title and text of the article.
• Post_category-document category, INT (4) value.
• Post_excerpt-document abstract, text value.
• Post_status-Current State of the Article. Enum ('Publish ', 'draft', 'private', 'static ', 'object') values are enumerated. Publish is published, draft is a draft, private is a private content (not published), static (unknown), and object (unknown ). The default value is publish.
• Comment_status-the status set for comments. It is also the enum ('open', 'closed ', 'registered _ only') value for enumeration. Open indicates that comments are allowed, and closed indicates that comments are not allowed, registered_only indicates that only registered users can comment. The default value is "Open", meaning that everyone can comment.
• Ping_status-ping status. Enum ('open', 'closed ') values are enumerated. Open indicates that the pingback function is enabled, and closed indicates that the function is disabled. The default value is open.
• Post_password-Password, varchar (20) value. Only by editing the document can you set a password for the document. You can use the password to impose or modify the document.
• Post_name-article name, varchar (200) value. This is usually used to identify a text or number in an article when permalink is generated, that is, post slug.
• To_ping-force this article to ping a URI. Text value.
• Pinged-the history of the article being pingback. The text value is a URI.
• Post_modified-the last modification time of the article, datetime value, which is the result of the GMT time plus the time zone offset.
• Post_modified_gmt-the last modified GMT time and datetime value in the document.
• Post_content_filtered-unknown, text value.
• Post_parent-ID of the Superior article in the article, INT (11) value, corresponding to wp_posts.id. The default value is 0.
• Guid-this is an address of each article, with a varchar (255) value. The default format is: http://your.blog.site /? P = 1. If you form the permalink function, it will usually be your WordPress site address + Article name.
• Menu_order-unknown, INT (11) value. The default value is 0.
• Post_type-document type. For details, set the value of varchar (100. The default value is 0.
• Post_mime_type-Unknown. Varchar (100) value.
• Comment_count-Comment count. The specific purpose is unknown. The value of bigint (20.

Wp_usermeta:Table used to save user meta information (Meta). There are four fields in total:

• Umeta_id-meta information ID, bigint (20) value, additional property auto_increment.
• User_id-the user ID corresponding to the metadata. The value of bigint (20) is equivalent to wp_users.id.
• Meta_key-meta keyword, which is a varchar (255) value.
• Meta_value-detailed value of the metadata, longtext value.

Wp_users:A table used to save information about a WordPress user. WP officially described the situation of this table in version 2.0.2 in some contradictions (there are 22 fields, but there are only 11 in the detailed list ), therefore, only 11 fields can be listed here:

• ID-Unique User ID, which is a bigint (20) value and has an additional auto_increment attribute.
• User_login-User Registration Name, varchar (60) value.
• User_pass-User Password, varchar (64) value, which is encrypted. It seems that an irreversible MD5 algorithm is used.
• User_nicename-user nickname, varchar (50) value.
• User_email-user email address, varchar (100) value.
• User_url-user URL, varchar (100) value.
• User_registered-user registration time, datetime value.
• User_level-used for the level, INT (2) value, can be a number between 0 and 10, different levels have different operation permissions on WP.
• User_activation_key-the user activation code, which is unknown. Varchar (60.
• User_status-user status, INT (11) value. The default value is 0.
• Display_name-specifies the username and varchar (250) value displayed in the foreground.

 

Turn: http://www.oschina.net/question/163913_32508