深入WordPress数据库
深入WordPress数据库
目录
数据字典
WorDPress数据库关系图
实例WordPress数据库
4.. 资料引用
一. 数据字典
| Table | Description | Relevant Area(s) of WordPress User Interface |
|---|---|---|
| wp_users | The list of users is maintained in table wp_users. | Administration > Users |
| wp_usermeta | Each user features information called the meta data and it is stored in wp_usermeta | Administration > Users |
| wp_comments | The comments within WordPress are stored in the wp_comments table. | Administration > Comments > Comments |
| wp_commentmeta | Each comment features information called the meta data and it is stored in the wp_commentmeta. | Administration > Comments > Comments |
| wp_options | The Options set under the Administration > Settings panel are stored in the wp_options table. | Administration > Settings > General Administration > Settings |
| wp_posts | The core of the WordPress data is the posts. It is stored in the wp_posts table. Also Pages and navigation menu items are stored in this table. | Administration > Posts/Pages/Media/Menu |
| wp_term_relationships | Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships 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 wp_terms table. | |
| wp_terms | The categories for both posts and links and the tags for posts are found within the wp_terms table. | |
1. wp_user
+---------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_login | varchar(60) | NO | MUL | | |
| user_pass | varchar(255) | NO | | | |
| user_nicename | varchar(50) | NO | MUL | | |
| user_email | varchar(100) | NO | MUL | | |
| user_url | varchar(100) | NO | | | |
| user_registered | datetime | NO | | 0000-00-00 00:00:00 | |
| user_activation_key | varchar(255) | NO | | | |
| user_status | int(11) | NO | | 0 | |
| display_name | varchar(250) | NO | | | |
+---------------------+---------------------+------+-----+---------------------+----------------+2. wp_usermeta
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
+----------+---------+---------------------------------------+
| umeta_id | user_id | meta_key |
+----------+---------+---------------------------------------+
| 1 | 1 | nickname |
| 2 | 1 | first_name |
| 3 | 1 | last_name |
| 4 | 1 | description |
| 5 | 1 | rich_editing |
| 6 | 1 | comment_shortcuts |
| 7 | 1 | admin_color |
| 8 | 1 | use_ssl |
| 9 | 1 | show_admin_bar_front |
| 10 | 1 | wp_capabilities |
| 11 | 1 | wp_user_level |
| 12 | 1 | dismissed_wp_pointers |
| 13 | 1 | show_welcome_panel |
| 14 | 1 | session_tokens |
| 15 | 1 | wp_dashboard_quick_press_last_post_id |
+----------+---------+---------------------------------------+
3. wp_comments
+----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| comment_post_ID | bigint(20) unsigned | NO | MUL | 0 | |
| comment_author | tinytext | NO | | NULL | |
| comment_author_email | varchar(100) | NO | MUL | | |
| comment_author_url | varchar(200) | NO | | | |
| comment_author_IP | varchar(100) | NO | | | |
| comment_date | datetime | NO | | 0000-00-00 00:00:00 | |
| comment_date_gmt | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| comment_content | text | NO | | NULL | |
| comment_karma | int(11) | NO | | 0 | |
| comment_approved | varchar(20) | NO | MUL | 1 | |
| comment_agent | varchar(255) | NO | | | |
| comment_type | varchar(20) | NO | | | |
| comment_parent | bigint(20) unsigned | NO | MUL | 0 | |
| user_id | bigint(20) unsigned | NO | | 0 | |
+----------------------+---------------------+------+-----+---------------------+----------------+4. wp_commentmeta
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| comment_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+5. wp_options
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| option_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| option_name | varchar(191) | NO | UNI | | |
| option_value | longtext | NO | | NULL | |
| autoload | varchar(20) | NO | | yes | |
+--------------+---------------------+------+-----+---------+----------------+
+-----------+-------------------------------+
| option_id | option_name |
+-----------+-------------------------------+
| 1 | siteurl |
| 2 | home |
| 3 | blogname |
| 4 | blogdescription |
| 5 | users_can_register |
| 6 | admin_email |
| 7 | start_of_week |
| 8 | use_balanceTags |
| 9 | use_smilies |
| 10 | require_name_email |
| 11 | comments_notify |
| 12 | posts_per_rss |
| 13 | rss_use_excerpt |
| 14 | mailserver_url |
| 15 | mailserver_login |
| 16 | mailserver_pass |
| 17 | mailserver_port |
| 18 | default_category |
| 19 | default_comment_status |
| 20 | default_ping_status |
| 21 | default_pingback_flag |
| 22 | posts_per_page |
| 23 | date_format |
| 24 | time_format |
| 25 | links_updated_date_format |
| 26 | comment_moderation |
| 27 | moderation_notify |
| 28 | permalink_structure |
| 29 | rewrite_rules |
| 30 | hack_file |
| 31 | blog_charset |
| 32 | moderation_keys |
| 33 | active_plugins |
| 34 | category_base |
| 35 | ping_sites |
| 36 | comment_max_links |
| 37 | gmt_offset |
| 38 | default_email_category |
| 39 | recently_edited |
| 40 | template |
| 41 | stylesheet |
| 42 | comment_whitelist |
| 43 | blacklist_keys |
| 44 | comment_registration |
| 45 | html_type |
| 46 | use_trackback |
| 47 | default_role |
| 48 | db_version |
| 49 | uploads_use_yearmonth_folders |
| 50 | upload_path |
| 51 | blog_public |
| 52 | default_link_category |
| 53 | show_on_front |
| 54 | tag_base |
| 55 | show_avatars |
| 56 | avatar_rating |
| 57 | upload_url_path |
| 58 | thumbnail_size_w |
| 59 | thumbnail_size_h |
| 60 | thumbnail_crop |
| 61 | medium_size_w |
| 62 | medium_size_h |
| 63 | avatar_default |
| 64 | large_size_w |
| 65 | large_size_h |
| 66 | image_default_link_type |
| 67 | image_default_size |
| 68 | image_default_align |
| 69 | close_comments_for_old_posts |
| 70 | close_comments_days_old |
| 71 | thread_comments |
| 72 | thread_comments_depth |
| 73 | page_comments |
| 74 | comments_per_page |
| 75 | default_comments_page |
| 76 | comment_order |
| 77 | sticky_posts |
| 78 | widget_categories |
| 79 | widget_text |
| 80 | widget_rss |
| 81 | uninstall_plugins |
| 82 | timezone_string |
| 83 | page_for_posts |
| 84 | page_on_front |
| 85 | default_post_format |
| 86 | link_manager_enabled |
| 87 | finished_splitting_shared_terms |
| 88 | site_icon |
| 89 | medium_large_size_w |
| 90 | medium_large_size_h |
| 91 | initial_db_version |
| 92 | wp_user_roles |
| 93 | widget_search |
| 94 | widget_recent-posts |
| 95 | widget_recent-comments |
| 96 | widget_archives |
| 97 | widget_meta |
| 98 | sidebars_widgets |
| 99 | widget_pages |
| 100 | widget_calendar |
| 101 | widget_tag_cloud |
| 102 | widget_nav_menu |
| 103 | cron |
| 104 | _transient_doing_cron |
| 112 | can_compress_scripts |
| 125 | _transient_timeout_plugin_slugs |
| 126 | _transient_plugin_slugs |
| 133 | WPLANG |
| 138 | _site_transient_update_core |
| 139 | _site_transient_update_plugins |
| 140 | _site_transient_update_themes |
| 143 | _transient_timeout_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca |
| 144 | _transient_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca |
| 145 | _transient_timeout_feed_mod_ac0b00fe65abe10e0c5b588f3ed8c7ca |
| 146 | _transient_feed_mod_ac0b00fe65abe10e0c5b588f3ed8c7ca |
| 147 | _transient_timeout_feed_b9388c83948825c1edaef0d856b7b109 |
| 148 | _transient_feed_b9388c83948825c1edaef0d856b7b109 |
| 149 | _transient_timeout_feed_mod_b9388c83948825c1edaef0d856b7b109 |
| 150 | _transient_feed_mod_b9388c83948825c1edaef0d856b7b109 |
| 151 | _transient_timeout_dash_88ae138922fe95674369b1cb3d215a2b |
| 152 | _transient_dash_88ae138922fe95674369b1cb3d215a2b |
| 153 | recently_activated |
| 154 | _site_transient_timeout_poptags_40cd750bba9870f18aada2478b24840a |
| 155 | _site_transient_poptags_40cd750bba9870f18aada2478b24840a |
| 156 | _transient_twentyfifteen_categories |
| 157 | _site_transient_timeout_theme_roots |
| 158 | _site_transient_theme_roots |
| 159 | _transient_is_multi_author |
+-----------+------------------------------------------------------------------+
6. wp_posts
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+7. wp_postmeta
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+8. wp_term_relationships
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id | bigint(20) unsigned | NO | PRI | 0 | |
| term_taxonomy_id | bigint(20) unsigned | NO | PRI | 0 | |
| term_order | int(11) | NO | | 0 | |
+------------------+---------------------+------+-----+---------+-------+9. wp_term_taxonomy
+------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+----------------+
| term_taxonomy_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| term_id | bigint(20) unsigned | NO | MUL | 0 | |
| taxonomy | varchar(32) | NO | MUL | | |
| description | longtext | NO | | NULL | |
| parent | bigint(20) unsigned | NO | | 0 | |
| count | bigint(20) | NO | | 0 | |
+------------------+---------------------+------+-----+---------+----------------+10. wp_terms
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| term_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | MUL | | |
| slug | varchar(200) | NO | MUL | | |
| term_group | bigint(10) | NO | | 0 | |
+------------+---------------------+------+-----+---------+----------------+11. wp_termeta
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| term_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+二. WorDPress数据库关系图
三. 实例WordPress数据库
1. 关联查询用户信息
select u.ID, u.user_login, u.user_pass, u.user_nicename, u.user_email, u.user_url, u.user_registered, u.user_activation_key, u.user_status , u.display_name, um.umeta_id, um.user_id, um.meta_key from wp_users u inner join wp_usermeta um on u.ID = um.user_id;
2. 查找注册用户评论
select comment_author, comment_approved, comment_date, comment_date_gmt, comment_parent from wp_comments where user_id = (select ID from wp_users);3. 显示所有默认options
select option_id, option_name from wp_options order by option_id asc;4. 查看文章的评论内容及数量
select c.comment_content from wp_comments c inner join wp_posts p on c.comment_post_ID = p.ID where p.ID = 6
select count(c.comment_content) comment_count from wp_comments c inner join wp_posts p on c.comment_post_ID = p.ID where p.ID = 15. 查看已经发布的文章
select ID, post_title, post_type from wp_posts where post_status = "publish"6. 发布文章的所属分类
select wp.post_title, wpt.description from wp_posts wp inner join wp_term_relationships wpr on wp.ID = wpr.object_id inner join wp_term_taxonomy wpt on wpr.term_taxonomy_id = wpt.term_taxonomy_id;7. 查看所有已经发布页面
select ID, post_type from wp_posts where post_status = "publish" and post_type = "page";