In-depth knowledge of WordPress database: structure, SQL skills and FAQs

This article will explore the structure of WordPress database, techniques for using SQL for data manipulation, and answers to some common questions. Even if WordPress installation and data addition do not require database knowledge, understanding the database structure can still improve website management, troubleshooting, and security performance.

Core points

• WordPress uses MySQL as the database management system, and key data and settings are stored in multiple tables, such as wp_options, wp_users, wp_usermeta, wp_posts, wp_postmeta, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_comments, wp_commentmeta, wp_links,
,
• ,
,
• , BEGIN and COMMIT. ROLLBACK
SQL (Structured Query Language) can be used to manipulate data in WordPress databases, such as changing the default username, updating passwords, transfer posts between authors, handling spam comments, deleting revisions, and updating links in posts.
Be careful when executing SQL queries, because changes are irreversible. Use

,

, and commands to help manage these changes and prevent unexpected and permanent changes.

Understanding WordPress database structure and how it works can help manage your website more effectively, troubleshoot problems, optimize website performance, and even improve website security. It is also crucial to back up WordPress databases regularly to prevent data loss.

mysql -u [用戶名] -p -D [數(shù)據(jù)庫名稱]

Database Exploration

show tables;

WordPress uses MySQL. To log in to MySQL, run the following command in the terminal:

desc [表名];

After logging in, you can use the following command to view the table created by WordPress:

To check the structure of any table, run:

• The following describes the tables created when WordPress is newly installed. Installing a new plug-in may create new tables. This article will not introduce them. Suppose your table prefix is ??"wp". If you use a different table prefix, replace "wpwp_options" in the table name.

• wp_users: Stores all settings of WordPress websites, such as titles, slogans, and time zones. All options set in Settings in the dashboard are stored in this table. wp_usermeta wp_users wp_usermetawp_usermetawp_users, meta_keymeta_value: meta_keyStorages a list of all registered users, including login name, password (encrypted), email, registration time, display name, status and activation Basic information such as keys (if needed).

  Storage user metadata. For example, the user's last name is stored in the table, not the table. The two fields and are important, and the plug-in can use the new value to store custom metadata values ??about the user in this table.

• wp_posts, wp_postmeta: wp_postsStorage all post-related data, including all posts (and pages) and their revisions. Even navigation menu items are stored in this table. The post_type column distinguishes between entry types (posts, pages, menu items, or revisions). wp_postmetaContains metadata about the post. If you use the SEO plugin, all meta tag data generated for the posts are stored in this table.

• wp_terms, wp_term_relationships, wp_term_taxonomy, wp_terms: wp_term_relationshipsStoring the categories and tags of posts. The table contains a slug column, which is a string that uniquely identifies the term, used in the URL of the term. This helps with SEO, as Google Bot also searches for search terms in the URL. wp_term_taxonomyLink these terms to objects (posts, pages, or links). It acts as a mapping between these objects and terms.

  Describe the terms in detail. It can be treated as metadata for the term, and the plugin cannot add custom values ??to this table.

• wp_commentswp_commentmeta, wp_comments: wp_commentmetaStoring comments on posts and pages, including unapproved comments and information about comment authors and comments nested.

  Contains metadata about comments. If you use a third-party comment system like Disqus, your comments are not stored in your WordPress database, but in the server of the comment service.

• wp_links

  : Stores custom link information added to the website. It has been deprecated, but can be re-enabled using the Links Manager plugin.

Use SQL

SQL is used to manage data in relational database management systems such as MySQL. SQL can be used to retrieve or manipulate data from database tables. Here are some examples of SQL query:

Cautionary tips BEGIN: SQL changes are irreversible. Use ROLLBACK before executing the command, and you can use COMMIT to return to the state before the command. If you want to keep the changes, you can use

. It is recommended to back up the WordPress database before running these commands.

• Change the default username
:

mysql -u [用戶名] -p -D [數(shù)據(jù)庫名稱]

• Note: This method may no longer work after recent updates.

• Change WordPress password

  :

show tables;

• Batch change post author
:

desc [表名];

Or, no need to find the author ID:

UPDATE wp_users SET user_login = '[新用戶名]' WHERE user_login = '[舊用戶名]';

• Dispose spam
:

Delete all spam comments:

UPDATE wp_users SET user_pass = MD5( '[新密碼]' ) WHERE user_login = '[用戶名]';

View spam source:

mysql -u [用戶名] -p -D [數(shù)據(jù)庫名稱]

• Delete all post revisions:

show tables;

• Update links in posts:

desc [表名];

Summary

This article introduces the structure of WordPress database and some SQL techniques. Although there are many plugins now, when there are simpler and faster alternatives, you should be careful to install them.

FAQ (FAQ)

(The FAQs listed in the original text are omitted here because the length is too long. These questions mainly cover the importance of WordPress databases, access methods, optimization methods, backup methods, database structures, and repairing corrupt databases. , changing database table prefixes, migrating databases, the relationship between MySQL database and WordPress, and database security. If you need to, you can ask these questions separately and I will answer them one by one. )

The above is the detailed content of The WordPress Database Demystified. For more information, please follow other related articles on the PHP Chinese website!