WordPress uses a MySQL database. The data table structure differs slightly from that of conventional systems; data can be stored either in the form of standard data tables or as data elements – the latter approach is less intuitive when viewed directly.
To become a developer, you must understand the basic structure of the WordPress database and use it in your own plugins or themes to write to or read from the database.
I. Data Table
As of WordPress version 4.52, WordPress supports a total of 12 table types. The default table prefix `wp_` has been added here.
- ◉ wp_commentmeta: Stores metadata for comments
- ◉ wp_comments: Store comments
- ◉ wp_links: Store friendly links
- ◉ wp_options: Stores WordPress system options, plugins, and theme configurations.
- ◉ wp_postmeta: Stores metadata for posts (including pages, uploaded files, and revisions).
- ◉ wp_posts: Stores posts (including pages, uploaded files, and revisions)
- ◉ wp_terms: Stores each directory and tag
- ◉ wp_termmeta: Stores the ID, key, and value for each directory and tag.
- ◉ wp_term_relationships: Stores the relationship between each post, link, and its corresponding category.
- ◉ wp_term_taxonomy: Stores the taxonomy associated with each directory and tag.
- ◉ wp_usermeta: Stores user metadata
- ◉ wp_users: Store user information
II. Data Table Structure
Next, let's take a closer look at the sub-items in each table.
wp_categories:
A table used to store classified information. It contains five fields:
◈ cat_ID – A unique identifier for each category; it is a bigint (20) value with the auto_increment attribute.
◈ cat_name – The name of a category; it is a VARCHAR(55) value.
◈ category_nicename – A memorable name assigned to a category, also known as a slug; this is a VARCHAR(200) value.
◈ category_description – Detailed description of a specific category; value of type longtext.
◈ category_parent – The parent category; it is an integer (4-bit) value corresponding to the `cat_ID` in the current table (i.e., `wp_categories.cat_ID`). When there is no parent category, this value is 0.
wp_comments:
A table used to store comment information. It contains 15 fields, namely:
`comment_ID` – The unique identifier for each comment; it is a `BIGINT(20)` value. This column has the `auto_increment` attribute.
`comment_post_ID` – The ID of the article to which each comment belongs; an integer (11-bit) value, equivalent to `wp_posts.ID`.
◊ comment_author – The name of the commenter for each comment; a TinyText value.
◊ comment_author_email – The email address of the commenter; a VARCHAR(100) value.
◊ comment_author_url – The URL of the commenter for each comment; a VARCHAR(200) value.
◊ comment_author_IP – The IP address of the commenter; a VARCHAR(100) value.
◊ comment_date – The timestamp when each comment was posted; a datetime value (including the time zone offset).
◊ comment_date_gmt – The time when each comment was posted; a datetime value (in standard GMT time).
◊ comment_content – The specific content of each comment; the `text` value.
◊ comment_karma – Unknown; type: int(11); default value: 0.
◊ comment_approved – The current status of each comment; it is an enum value (’0’, ’1’, ’spam’). 0 indicates the comment is pending moderation, 1 indicates the comment is approved for publication, and spam indicates a spam comment. The default value is 1.
◊ comment_agent – Client information for the commenter of each comment (a VARCHAR(255) value), primarily including details such as the type and version of the browser and operating system.
◊ comment_type – Unknown, a VARCHAR(20) value.
◊ comment_parent – The parent comment ID of a given comment (int(11) value), corresponding to wp_comment.ID; the default value is 0, indicating no parent comment.
◊ user_id – The user ID associated with a specific comment; this ID is generated only after the user has been registered. It is an integer (11-bit) value corresponding to wp_users.ID. For unregistered users (i.e., external commentators), the value of this ID is 0.
wp_linkcategories:
A table used to store relevant information for links added to the WP backend. It contains 13 fields:
◈ cat_id – Unique identifier for each link category; a bigint (20) value with auto-increment functionality.
◈ cat_name – The name of each link category; a TinyText value.
◈ auto_toggle – This field contains a special attribute. If the value is set to "Y", all other links will become invisible when a new link is added to this category. It is an enum value ('Y', 'N'), with the default value being "N".
◈ show_images – This field also takes enum values ("Y" or "N"), with the default value set to "Y". It specifies whether image links should be displayed within this link category.
◈ show_description – This field specifies whether the links under the corresponding link category should have their descriptions displayed separately [new line]. This is an enum value ('Y', 'N'); the default is 'N', meaning the descriptions will not be displayed directly (but will be shown via the `title` attribute).
◈ show_rating – Displays the rating of the links within this category. It is also an enum value ('Y', 'N'), with the default set to 'Y'. You can use this link rating to sort the links under this category.
◈ show_updated – Indicates whether to display this link category when an update occurs; enum values: "Y", "N"; default: Y.
◈ sort_order – Specifies the sorting criterion for links within this link category; a VARCHAR(64) value. Typically, this can be the link name (name, i.e., wp_links.link_name) or the link ID (id, i.e., wp_links.link_id).
◈ sort_desc – Specifies the sorting order for linked categories; enum values are 'Y' or 'N'; the default is 'N' (descending order).
◈ text_before_link – The preceding HTML text for each link within this link category; a VARCHAR(128) value; the default is 'List Start Tag'.
◈ text_after_link – The HTML text between the link and its descriptive text (wp_links.link_description) for each link within this link category; a VARCHAR(128) value, defaulting to '<br/>' (newline tag).
◈ text_after_all – The HTML text appended after each link within this link category; a VARCHAR(128) value, defaulting to '</ul>'.
◈ list_LIMIT – Used to specify the number of links (whether configurable?) to be displayed within a given link category; accepts an integer value (11), with a default of-1, indicating no limit on the number of links within the link category.
wp_links :
A table used to store links entered by users into WordPress (via the Link Manager). It contains a total of 14 fields:
◊ link_id – Unique identifier for each link; a bigint (20) value with the auto_increment attribute.
◊ link_url – The URL address for each link; a VARCHAR(255) value, in the format of an address starting with http://.
◊ link_name – The name of a single link; a VARCHAR(255) value.
◊ link_image – A link can be defined as an image link; this field is used to store the URL of the image and holds a VARCHAR(255) value.
◊ link_target – The method used to open the link; there are three options: _blank opens the link in a new window; _top opens the link within the current window at the top level; none does nothing and opens the link within the current window. This field contains a VARCHAR(25) value.
◊ link_category – The link category associated with a given link; it is an integer (11) value. Equivalent to wp_linkcategories.cat_id.
◊ link_description – The descriptive text for the link. Users can choose whether this text should be displayed below the link or within the `title` attribute. A `VARCHAR(255)` value.
◊ link_visible – Whether this link is visible; an enum value (’Y’, ’N’) – default is Y (visible).
◊ link_owner – The creator of a link; it is an integer (11) value, with a default of 1. (This should correspond to wp_users.ID.)
◊ link_rating – Link rating, an integer (11) value. The default value is 0.
◊ link_updated – The timestamp indicating when the link was defined or modified; a datetime value.
◊ link_rel – The relationship between the link and its creator; configured by the XFN Creator; VARCHAR(255) value.
◊ vlink_notes – Detailed description of the link; mediumtext value.
◊ link_rss – The RSS feed URL for this link; a VARCHAR(255) value.
wp_options:
A table used to store WordPress-related settings and parameters, containing a total of 11 fields. The most important of these is the `option_value` field, which contains a wealth of critical information.
◈ option_id – ID of option, bigint(20) value, appended with auto_increment attribute.
◈ blog_id – Unknown. Maybe it’s not important to use it only on the user’s WP version, maybe it’s a value set for different users’ blogs. int(11) value, the default is 0, which is the current blog.
◈ option_name – option name, varchar(64) value.
◈ option_can_override – Whether this option can be rewritten and updated, enumeration enum (’Y’, ’N’) value, the default is Y, can be rewritten and updated.
◈ option_type – Type of option, unknown function, int(11) value, default is 1.
◈ option_value – The value of the option, longtext value, the content of this field is more important. When WordPress is initialized, about 70 default values will be set, which will not be introduced here.
◈ option_width – The width of the option (?), its function is unknown. int(11) value, default is 20.
◈ option_height – The height (?) of the option, its function is unknown. int(11) value, default is 8.
◈ option_description – Description of an option, tinytext value.
◈ option_admin_level – Set the user level at which an option can be manipulated (see my related articles for details), int (11) value, default is 1.
◈ autoload – Whether the option is automatically loaded every time, enumeration enum (’yes’, ’no’) value, the default is yes.
wp_postmeta:
Table used to save the meta information (meta) of the article, four fields:
◊ meta_id – Meta information ID, bigint (20) value, additional attribute is auto_increment.
◊ post_id – Post ID, bigint (20) value, equivalent to wp_posts.ID.
◊ meta_key – Metadata keyword; a VARCHAR(255) value.
`meta_value` – The value of the metadata; this can be a text value. These settings are primarily configured under the "Add a new custom field to this post (page)" option on the Article or Page editing page (Write Post, Write Page). The `meta_key` corresponds to an item in the dropdown list labeled "key," while the value is entered manually by the user (in some cases, WordPress may automatically populate this field—for example, when adding an audio media file to an article).
wp_posts:
This table is crucial for storing all relevant information about your posts; generally, it contains the largest amount of data. It consists of a total of 21 fields.
◈ ID – The unique identifier for each article; a BIGINT(20) value with the auto_increment attribute.
◈ post_author – The author ID for each article; it is an int(4) value and should correspond to wp_users.ID.
◈ post_date – The publication date and time of each article (a datetime value). This value is calculated as the GMT time plus the time zone offset.
◈ post_date_gmt – The GMT (Greenwich Mean Time) timestamp when each article was published; a datetime value.
◈ post_content – The specific content of each article, represented as a longtext value. All content you enter on the article editing page in the backend is stored here.
◈ post_title – The article's title; the `text` value.
◈ post_category – Article category; integer (4) value.
◈ post_excerpt – Article abstract; text value.
◈ post_status – The current status of the article; an enum value ('publish', 'draft', 'private', 'static', 'object'). 'publish' means the article is published; 'draft' means it is a draft; 'private' means it is private content (not publicly accessible); 'static' (meaning unknown); and 'object' (meaning unknown). The default value is 'publish'.
◈ comment_status – The status of comment settings; it is also an enum value ('open', 'closed', 'registered_only'). 'open' allows comments, 'closed' prohibits comments, and 'registered_only' restricts comments to registered users only. The default value is 'open', meaning anyone can leave comments.
◈ ping_status – Ping status; an enumeration ('open', 'closed'). 'Open' indicates that the pingback feature is enabled; 'closed' indicates that it is disabled. The default value is 'open'.
◈ post_password – Article password; a VARCHAR(20) value. Only the article editor can set a password for an article; this password is required to re-secure or modify the article.
◈ post_name – Article title, a VARCHAR(200) value. This is typically used when generating a permalink to identify a specific article with a text or numeric string – known as the post slug.
◈ to_ping – Forces this article to ping a specific URI (text value).
◈ pinged – The historical record of this article's pingback; the `text` value consists of individual URIs.
◈ post_modified – The timestamp indicating when the article was last modified; this is a datetime value calculated as the GMT time plus the time zone offset.
◈ post_modified_gmt – The GMT time of the last modification to the article; a datetime value.
◈ post_content_filtered – Unknown; text value.
◈ post_parent – The ID of the parent article; an int(11) value corresponding to wp_posts.ID. The default value is 0, indicating no parent article exists.
◈ Permalink – This is the URL for each article, stored as a VARCHAR(255) value. By default, it looks like: http://your.blog.site/?p=1; if the permalink feature is enabled, it will typically be your WordPress site URL followed by the article's slug.
◈ menu_order – Unknown; type: int(11); default value: 0.
◈ post_type – Article type; the exact definition is unknown; value is of type VARCHAR(100). The default value is 0.
◈ post_mime_type – Unknown. A VARCHAR(100) value.
◈ comment_count – Number of comments; specific usage is unknown; value is a BIGINT(20) type.
wp_usermeta :
A table used to store user metadata (meta data), containing four fields:
◊ umeta_id – Metadata ID; a bigint(20) value with the auto_increment attribute.
◊ user_id – The user ID corresponding to the metadata; a bigint(20) value, equivalent to wp_users.ID.
◊ meta_key – Metadata keyword; value of type VARCHAR(255).
◊ meta_value – Detailed value of metadata; longtext value.
wp_users:
A table used to store relevant information about WordPress users.
◈ ID – Unique user identifier; a BIGINT(20) value with the auto_increment attribute.
◈ user_login – The user's registered username; a VARCHAR(60) value.
◈ user_pass – User password (VARCHAR(64) value); this represents the encrypted result. It appears that an irreversible MD5 algorithm is being used.
◈ user_nicename – User nickname; a VARCHAR(50) value.
◈ user_email – User's email address, a VARCHAR(100) value.
◈ user_url – User URL; value of type VARCHAR(100).
◈ user_registered – User registration timestamp (datetime value).
◈ user_level – Used to specify user level; an integer (2) value ranging from 0 to 10; different levels grant different operational permissions for WP.
◈ user_activation_key – User activation code (unknown value); type: VARCHAR(60).
◈ user_status – User status; an integer (11) value, default is 0.
◈ display_name – The username displayed on the frontend; a VARCHAR(250) value.
postscript
In the WordPress database structure, the `wp_options` table – which stores system settings and plugin configurations – represents a relatively independent data structure. As will be discussed later, this table employs a key-value storage model; the advantage of this approach is its ease of scalability, allowing each plugin to effortlessly store its own configuration data within this table.
The `post`, `comment`, and `user` tables represent a combination of three base tables and an extended table. Taking `wp_users` as an example, this table stores the basic information that every user requires—such as `login_name`, `display_name`, `password`, and `email`. However, if we need to store additional, less frequently used data, the best approach is not to add a new column to the table (which would disrupt the default table structure), but rather to store this data in the `wp_usermeta` table. The `wp_usermeta` extended table has a structure similar to that of the `wp_options` table; we can use it to store, for example, each user's QQ number, mobile phone number, or theme options for the WordPress admin dashboard.
The term `wp_terms`, `wp_term_relationships`, and `wp_term_taxonomy` can be somewhat difficult to understand. In the WordPress system, the common categories we encounter include article categories and link categories; however, there is also the `TAG` – another specialized category type – and we can even create our own custom category systems. WordPress stores all categories, category systems, and their corresponding structures in these three tables. The `wp_terms` table records the name and basic information for each category; for example, this site has categories such as "WordPress Development" and "WPCEO Plugins." Here, the term "category" is used in the broad sense, so each `TAG` is also considered a "category." The `wp_term_taxonomy` table records which category system each category belongs to; for instance, "WordPress Development" and "WPCEO Plugins" belong to the "Category" system (used for article categories), while categories such as "My Friends" and "My Colleagues" (used for hosting friendly links) belong to the "Link Category" system. The `wp_term_relationships` table records the category system to which each article (or link) is associated.
