wordpress 数据库

wordpress 数据库

Since it was released over a decade ago, WordPress has become the most popular CMS choice for beginners and experts alike. Although the installation of WordPress, and the addition of data does not require any knowledge of the underlying structure, you might find yourself in a situation where some knowledge of the database is required.

自从十多年前发布以来，WordPress已成为初学者和专家最喜欢的CMS选择。 尽管WordPress的安装和数据的添加不需要任何底层结构的知识，但是您可能会发现自己处在需要一些数据库知识的情况下。

Although the favorite choice of users when it comes to working with databases is often phpMyAdmin, we will focus on raw SQL queries, keeping in mind that everyone may not be comfortable with phpMyAdmin (you have an option in phpMyAdmin to run raw SQL queries too!).

尽管在使用数据库时用户最喜欢的选择通常是phpMyAdmin ，但我们将专注于原始SQL查询，但请记住，每个人可能都不喜欢phpMyAdmin(您也可以在phpMyAdmin中选择运行原始SQL查询！ )。

探索数据库 (Exploring the Database)

As you probably know, WordPress uses MySQL. To log into MySQL, run the following in your terminal:

您可能知道，WordPress使用MySQL。 要登录MySQL，请在终端中运行以下命令：

mysql -u [username] -p -D [database_name]

Once you have logged into the WordPress database in MySQL, you can check the tables that WordPress has created by running the following:

在MySQL中登录WordPress数据库后，可以通过运行以下命令检查WordPress创建的表：

show tables;

To check the structure of any table, run:

要检查任何表的结构，请运行：

desc [table_name];

Note that I would be describing the tables that are created by WordPress on a fresh installation. Installing new plugins may create new tables, which I will not be covering in this post.

请注意，我将描述由WordPress在全新安装中创建的表。 安装新的插件可能会创建新的表，我将不在本文中介绍。

Also, I will assume that your table prefix is “wp“. In case you have used a different table prefix, replace “wp” by your prefix in the table names.

此外，我将假设您的表前缀为“ wp ”。 如果您使用了不同的表前缀，请在表名称中用前缀替换“ wp ”。

I will explain the tables in a logical order, rather than the alphabetical order that you will find in most tutorials.

我将按照逻辑顺序解释这些表，而不是您在大多数教程中发现的字母顺序。

wp_options (wp_options)

The wp_options table stores all the settings of your WordPress site like title, tagline and timezone. All the options that you set in Settings in your Dashboard are stored in this table.

wp_options表存储WordPress网站的所有设置，例如标题，标语和时区。 您在仪表板的“设置”中设置的所有选项都存储在此表中。

wp_users，wp_usermeta (wp_users, wp_usermeta)

As the name suggests, wp_users stores the list of all the registered users on your WordPress site. It contains the basic information of a user like login, password (which is encrypted), email, time of registration, display name, status and activation key (if required).

顾名思义， wp_users将所有已注册用户的列表存储在WordPress网站上。 它包含用户的基本信息，例如登录名，密码(已加密)，电子邮件，注册时间，显示名称，状态和激活密钥(如果需要)。

wp_usermeta stores the meta data (or ‘data about data’) of the users. For instance, the last_name of a user is saved in the wp_usermeta table, rather than the wp_users table.

wp_usermeta存储用户的元数据(或“关于数据的数据”)。 例如，用户的last_name保存在wp_usermeta表中，而不是wp_users表中。

There are two fields in this table that you should know about — meta_key and meta_value. Plugins can store custom meta data values about users in the meta table by using new meta_key values.

您应该了解此表中的两个字段meta_key和meta_value 。 插件可以使用新的meta_key值将有关用户的自定义元数据值存储在元表中。

wp_posts，wp_postmeta (wp_posts, wp_postmeta)

wp_posts stores all the post related data of your website. All the posts (and pages), and their revisions are available in the wp_posts table. Even navigation menu items are stored in this table.

wp_posts存储您网站的所有帖子相关数据。 所有帖子(和页面)及其修订都可以在wp_posts表中找到。 甚至导航菜单项也存储在此表中。

The types of entries (posts, pages, menu items or revisions) are distinguished by the post_type column in the table. We will see later in this post how we can use queries in this table to our advantage.

条目的类型(帖子，页面，菜单项或修订版)通过表中的post_type列进行区分。 我们将在本文的后面部分看到如何利用此表中的查询来发挥作用。

wp_postmeta, just like the user meta data table, contains the meta data about posts.

就像用户元数据表一样， wp_postmeta包含有关帖子的元数据。

If you use an SEO plugin, all the meta tag data generated for posts are stored are stored in this table.

如果使用SEO插件，则为帖子生成的所有元标记数据都存储在此表中。

wp_terms，wp_term_relationships，wp_term_taxonomy (wp_terms, wp_term_relationships, wp_term_taxonomy)

Categories and tags for posts, pages or links are stored in the table wp_terms. A column that this table contains is a slug — which is a string that uniquely identifies the term, thereby used in the URL for the term. This helps in SEO as the Google Bot searches the URL for search terms too.

帖子，页面或链接的类别和标签存储在表wp_terms 。 该表包含的列是一个slug ，它是一个唯一标识术语的字符串，因此在术语的URL中使用。 这对SEO很有帮助，因为Google Bot也会在URL中搜索搜索字词。

wp_term_relationship links these terms to objects (posts, pages or links). It serves as a map between these objects and the terms.

wp_term_relationship将这些术语链接到对象(帖子，页面或链接)。 它充当这些对象和术语之间的映射。

wp_term_taxonomy describes the terms in details. You can imagine it as meta data of the terms for simplicity, just that a plugin can not add custom values to this table.

wp_term_taxonomy详细描述了这些术语。 为了简单起见，您可以将其想象为术语的元数据，只是插件无法向该表添加自定义值。

wp_comments，wp_commentmeta (wp_comments, wp_commentmeta)

wp_comments stores the comments on your posts and pages. This table also contains unapproved comments and information about the author of the comments and nesting of comments. wp_commentmeta contains meta data about comments.

wp_comments将评论存储在您的帖子和页面上。 该表还包含未批准的注释以及有关注释作者和注释嵌套的信息。 wp_commentmeta包含有关注释的元数据。

An important thing to note is that if you are using a third party commenting system like Disqus, your comments won’t be stored in your WordPress database, but in the servers of the commenting service.

需要注意的重要一点是，如果您使用的是诸如Disqus的第三方评论系统，则您的评论不会存储在WordPress数据库中，而是存储在评论服务的服务器中。

wp_links (wp_links)

This table contains information about custom links added to your site. It has been deprecated, but can be enabled again by using the Links Manager plugin.

下表包含有关添加到您的网站的自定义链接的信息。 它已被弃用，但可以使用Links Manager插件再次启用。

Here is the graphical view of the database, with tables linking to each other.

这是数据库的图形视图，表相互链接。

Source: WordPress.org

资料来源： WordPress.org

利用SQL发挥优势 (Using SQL to Your Advantage)

Structured Query Language (SQL) is a computer language that is used to manage data in relational database management systems like MySQL. You can retrieve or manipulate data from database tables using SQL. We will try to use some queries that help us in doing certain tasks that are otherwise very difficult, or impossible, to do with the WordPress dashboard.

结构化查询语言(SQL)是一种计算机语言，用于在关系数据库管理系统(如MySQL)中管理数据。 您可以使用SQL从数据库表中检索或操作数据。 我们将尝试使用一些查询来帮助我们完成某些在WordPress仪表板上很难或不可能完成的任务。

告诫 (A Word of Caution)

If you plan to execute the SQL queries that I am going to discuss, you should know that the changes they make are irreversible. Therefore, you must use BEGIN, COMMIT and ROLLBACK.

如果您打算执行我将要讨论SQL查询，您应该知道它们所做的更改是不可逆的。 因此，您必须使用BEGIN ， COMMIT和ROLLBACK 。

If you BEGIN a session before you execute the a command, you can always go back to the state before the command by using ROLLBACK.

如果您BEGIN你执行一个命令之前的会话，你可以随时使用该命令之前回到状态ROLLBACK 。

BEGIN; // Some Query ROLLBACK;

If, however, you want the changes to stay, you can COMMIT the changes, and they become permanent.

但是，如果您想保留更改，则可以COMMIT更改，这些更改将成为永久更改。

BEGIN; // Some Query COMMIT;

You could also take a backup of your WordPress database either using a database dump or a plugin like the DB Backup before running these commands.

在运行这些命令之前，您还可以使用数据库转储或插件(如数据库备份)对WordPress数据库进行备份 。

Now, let us look at a few tricks that we can use to make our work easier.

现在，让我们看一些可以使我们的工作更轻松的技巧。

更改默认用户名 (Change Default Usernames)

You probably know that once you create a user, WordPress doesn’t allow you to change the username. However, you can do that through SQL by running a simple command.

您可能知道，一旦创建用户，WordPress将不允许您更改用户名。 但是，您可以通过运行简单的命令通过SQL来实现。

UPDATE wp_users SET user_login = '[new_username]' WHERE user_login = '[old_username]';

The default administrator username in WordPress is ‘admin’ and because of its popularity, hackers often try this username. For security purposes, it’s advisable to change it to something else.

WordPress中默认的管理员用户名是“ admin”，由于其受欢迎程度，黑客经常尝试使用该用户名。 为了安全起见，建议将其更改为其他名称。

UPDATE: This worked at the time of writing, but doesn’t since recent updates. Please see the comments below for more information.

更新：此功能在撰写本文时有效，但自最近更新以来不起作用。 请参阅下面的评论以获取更多信息。

更改您的WordPress密码 (Change Your WordPress Password)

Imagine you are the admin and you forget your password. You can easily change it, if you have access to the database.

假设您是管理员，而您忘记了密码。 如果您有权访问数据库，则可以轻松更改它。

UPDATE wp_users SET user_pass = MD5( '[new_password]' ) WHERE user_login = '[username]';

We need to use MD5 because the passwords are not stored in clear text format, but are encrypted.

我们需要使用MD5因为密码不是以明文格式存储的，而是经过加密的。

批量更改帖子作者 (Change Post Authors in Bulk)

If you want to transfer posts from one author to another, you could try it with a simple SQL query.

如果要将帖子从一位作者转移到另一位作者，则可以使用简单SQL查询来尝试。

UPDATE wp_posts SET post_author = [new_author_id] WHERE post_author = [old_author_id];

You need to first check the ID of the author from the wp_users table. However, if you are lazy, you can try this query, which looks complex, but doesn’t require you to check the author ID.

您首先需要从wp_users表中检查作者的ID 。 但是，如果您很懒，可以尝试执行此查询，该查询看起来很复杂，但不需要您检查作者ID。

UPDATE wp_posts SET post_author = (SELECT ID FROM wp_users WHERE user_login = '[new_author_login]') WHERE post_author = (SELECT ID FROM wp_users WHERE user_login = '[old_author_login]');

处理垃圾邮件评论 (Dealing with Spam Comments)

If you do not use an external service for handling comments, chances are that you get a lot of spam comments. If you have a lot of spam, you could try using Akismet to keep a check on the spam. However, if you already have a lot of spam, you could try some SQL queries on them.

如果您不使用外部服务来处理评论，则很可能会收到大量垃圾评论。 如果您有大量垃圾邮件，则可以尝试使用Akismet来检查垃圾邮件。 但是，如果您已经有很多垃圾邮件，则可以尝试对它们进行一些SQL查询。

You can delete all spam comments in one go using the following:

您可以使用以下方法一次性删除所有垃圾邮件评论：

DELETE FROM wp_comments WHERE comment_approved = 'spam';

If you want to check where the spam comments are coming from, you can try this:

如果您想检查垃圾评论的来源，可以尝试以下操作：

SELECT comment_author_IP as ip_address, COUNT(*) AS count FROM wp_comments WHERE comment_approved = 'spam' GROUP BY ip_address ORDER BY count DESC

It will show you a list of how many spam comments came form each IP address.

它会显示一个列表，列出每个IP地址中有多少垃圾邮件评论。

删除所有帖子修订 (Delete All Post Revisions)

As I explained earlier, wp_posts saves the current version of a post whenever you click ‘Save’. Therefore, if you clicked ‘Save’ ten times while writing a post, there would be ten iterations of the post on your database. All revisions have the post_type as ‘revision’.

如前所述， wp_posts在您单击“保存”时保存帖子的当前版本。 因此，如果您在撰写文章时单击“保存”十次，则数据库中该文章将进行十次迭代。 所有修订的post_type “修订”。

If you are curious about how many revisions you have generated so far, run the following. I am sure that the number will be big.

如果您对到目前为止已生成的修订版本感到好奇，请运行以下命令。 我相信这个数字会很大。

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

If you want to delete all such revisions of published posts, run the following command:

如果要删除已发布帖子的所有此类修订，请运行以下命令：

DELETE p, t, m FROM wp_posts p LEFT JOIN wp_term_relationships t ON (p.ID = t.object_id) LEFT JOIN wp_postmeta m ON (p.ID = m.post_id) WHERE p.post_type = 'revision'

The reason I used LEFT JOIN is to remove the meta data and its connection with tags and categories.

我使用LEFT JOIN的原因是要删除元数据及其与标签和类别的连接。

更新帖子中的链接 (Update Links in Posts)

If you are moving to a new domain, chances are that there are many links in your posts, linking to your old domain. Changing them manually can be a tedious job. However, a simple SQL command may be used for this purpose.

如果您要迁移到新域，则您的帖子中可能有很多链接，这些链接都链接到您的旧域。 手动更改它们可能是一项繁琐的工作。 但是，可以将一个简单SQL命令用于此目的。

UPDATE wp_posts SET post_content = REPLACE (post_content, '[old_domain]', '[new_domain]');

This query searches for occurrences of your old domain in the post_content column of the table and replaces them with your new domain.

此查询在表的post_content列中搜索旧域的出现，并将它们替换为新域。

结论 (Conclusion)

With this, we come to the end of this tutorial. We hope that this post changed your view of how WordPress works in the background and helped you develop a clearer understanding of the database structure. Even though there are plugins for everything nowadays, you should think twice before installing all of them, especially when you have an easier and faster alternative.

至此，我们结束了本教程的结尾。 我们希望这篇文章能改变您对WordPress在后台工作方式的看法，并帮助您对数据库结构有更清晰的了解。 即使现在有适用于所有功能的插件，您也应该在安装所有插件之前三思而后行，尤其是当您有一个更简单，更快捷的选择时。

Do you know any other SQL tricks for WordPress? Did we miss something important? Do let us know in the comments below!

您是否知道WordPress的其他SQL技巧？ 我们错过了重要的事情吗？ 请在下面的评论中告诉我们！

翻译自: https://www.sitepoint.com/the-wordpress-database-demystified/

wordpress 数据库