Menu

DB structure of wordpress core

2018-11-18 - Wordpress

Purpose of this document

Show initial status of wordpress core’s database structure


Tables

commentmeta

CREATE TABLE `commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

No data


comments

CREATE TABLE `comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_author_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

1 comment example


links

CREATE TABLE `links` (
  `link_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `link_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_target` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_visible` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Y',
  `link_owner` bigint(20) unsigned NOT NULL DEFAULT '1',
  `link_rating` int(11) NOT NULL DEFAULT '0',
  `link_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `link_rel` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `link_notes` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `link_rss` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`link_id`),
  KEY `link_visible` (`link_visible`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

No data


options

CREATE TABLE `options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Initial Data
select option_id, option_name, substring(option_value, 1,50), autoload from options;

|         1 | _site_transient_timeout_theme_roots                                       | 1542544206                                         | no       |
|         2 | _site_transient_theme_roots                                               | a:19:{s:5:"astra";s:7:"/themes";s:9:"basepress";s: | no       |
|         3 | siteurl                                                                   | https://vpsaws.com:10089/wp                          | yes      |
|         4 | home                                                                      | https://vpsaws.com:10089/wp                          | yes      |
|         5 | blogname                                                                  | Tour Guide in Japan                                | yes      |
|         6 | blogdescription                                                           | Just another WordPress site                        | yes      |
|         7 | users_can_register                                                        | 0                                                  | yes      |
|         8 | admin_email                                                               | ....@1stclass.co.jp                        | yes      |
|         9 | start_of_week                                                             | 1                                                  | yes      |
|        10 | use_balanceTags                                                           | 0                                                  | yes      |
|        11 | use_smilies                                                               | 1                                                  | yes      |
|        12 | require_name_email                                                        | 1                                                  | yes      |
|        13 | comments_notify                                                           | 1                                                  | yes      |
|        14 | posts_per_rss                                                             | 10                                                 | yes      |
|        15 | rss_use_excerpt                                                           | 0                                                  | yes      |
|        16 | mailserver_url                                                            | mail.example.com                                   | yes      |
|        17 | mailserver_login                                                          | login@example.com                                  | yes      |
|        18 | mailserver_pass                                                           | password                                           | yes      |
|        19 | mailserver_port                                                           | 110                                                | yes      |
|        20 | default_category                                                          | 1                                                  | yes      |
|        21 | default_comment_status                                                    | open                                               | yes      |
|        22 | default_ping_status                                                       | open                                               | yes      |
|        23 | default_pingback_flag                                                     | 1                                                  | yes      |
|        24 | posts_per_page                                                            | 10                                                 | yes      |
|        25 | date_format                                                               | F j, Y                                             | yes      |
|        26 | time_format                                                               | g:i a                                              | yes      |
|        27 | links_updated_date_format                                                 | F j, Y g:i a                                       | yes      |
|        28 | comment_moderation                                                        | 0                                                  | yes      |
|        29 | moderation_notify                                                         | 1                                                  | yes      |
|        30 | permalink_structure                                                       |                                                    | yes      |
|        31 | rewrite_rules                                                             |                                                    | yes      |
|        32 | hack_file                                                                 | 0                                                  | yes      |
|        33 | blog_charset                                                              | UTF-8                                              | yes      |
|        34 | moderation_keys                                                           |                                                    | no       |
|        35 | active_plugins                                                            | a:0:{}                                             | yes      |
|        36 | category_base                                                             |                                                    | yes      |
|        37 | ping_sites                                                                | http://rpc.pingomatic.com/                         | yes      |
|        38 | comment_max_links                                                         | 2                                                  | yes      |
|        39 | gmt_offset                                                                | 0                                                  | yes      |
|        40 | default_email_category                                                    | 1                                                  | yes      |
|        41 | recently_edited                                                           |                                                    | no       |
|        42 | template                                                                  | twentyseventeen                                    | yes      |
|        43 | stylesheet                                                                | twentyseventeen                                    | yes      |
|        44 | comment_whitelist                                                         | 1                                                  | yes      |
|        45 | blacklist_keys                                                            |                                                    | no       |
|        46 | comment_registration                                                      | 0                                                  | yes      |
|        47 | html_type                                                                 | text/html                                          | yes      |
|        48 | use_trackback                                                             | 0                                                  | yes      |
|        49 | default_role                                                              | subscriber                                         | yes      |
|        50 | db_version                                                                | 38590                                              | yes      |
|        51 | uploads_use_yearmonth_folders                                             | 1                                                  | yes      |
|        52 | upload_path                                                               |                                                    | yes      |
|        53 | blog_public                                                               | 1                                                  | yes      |
|        54 | default_link_category                                                     | 2                                                  | yes      |
|        55 | show_on_front                                                             | posts                                              | yes      |
|        56 | tag_base                                                                  |                                                    | yes      |
|        57 | show_avatars                                                              | 1                                                  | yes      |
|        58 | avatar_rating                                                             | G                                                  | yes      |
|        59 | upload_url_path                                                           |                                                    | yes      |
|        60 | thumbnail_size_w                                                          | 150                                                | yes      |
|        61 | thumbnail_size_h                                                          | 150                                                | yes      |
|        62 | thumbnail_crop                                                            | 1                                                  | yes      |
|        63 | medium_size_w                                                             | 300                                                | yes      |
|        64 | medium_size_h                                                             | 300                                                | yes      |
|        65 | avatar_default                                                            | mystery                                            | yes      |
|        66 | large_size_w                                                              | 1024                                               | yes      |
|        67 | large_size_h                                                              | 1024                                               | yes      |
|        68 | image_default_link_type                                                   | none                                               | yes      |
|        69 | image_default_size                                                        |                                                    | yes      |
|        70 | image_default_align                                                       |                                                    | yes      |
|        71 | close_comments_for_old_posts                                              | 0                                                  | yes      |
|        72 | close_comments_days_old                                                   | 14                                                 | yes      |
|        73 | thread_comments                                                           | 1                                                  | yes      |
|        74 | thread_comments_depth                                                     | 5                                                  | yes      |
|        75 | page_comments                                                             | 0                                                  | yes      |
|        76 | comments_per_page                                                         | 50                                                 | yes      |
|        77 | default_comments_page                                                     | newest                                             | yes      |
|        78 | comment_order                                                             | asc                                                | yes      |
|        79 | sticky_posts                                                              | a:0:{}                                             | yes      |
|        80 | widget_categories                                                         | a:2:{i:2;a:4:{s:5:"title";s:0:"";s:5:"count";i:0;s | yes      |
|        81 | widget_text                                                               | a:0:{}                                             | yes      |
|        82 | widget_rss                                                                | a:0:{}                                             | yes      |
|        83 | uninstall_plugins                                                         | a:0:{}                                             | no       |
|        84 | timezone_string                                                           |                                                    | yes      |
|        85 | page_for_posts                                                            | 0                                                  | yes      |
|        86 | page_on_front                                                             | 0                                                  | yes      |
|        87 | default_post_format                                                       | 0                                                  | yes      |
|        88 | link_manager_enabled                                                      | 0                                                  | yes      |
|        89 | finished_splitting_shared_terms                                           | 1                                                  | yes      |
|        90 | site_icon                                                                 | 0                                                  | yes      |
|        91 | medium_large_size_w                                                       | 768                                                | yes      |
|        92 | medium_large_size_h                                                       | 0                                                  | yes      |
|        93 | wp_page_for_privacy_policy                                                | 3                                                  | yes      |
|        94 | show_comments_cookies_opt_in                                              | 0                                                  | yes      |
|        95 | initial_db_version                                                        | 38590                                              | yes      |
|        96 | bdruser_roles                                                             | a:5:{s:13:"administrator";a:2:{s:4:"name";s:13:"Ad | yes      |
|        97 | fresh_site                                                                | 1                                                  | yes      |
|        98 | widget_search                                                             | a:2:{i:2;a:1:{s:5:"title";s:0:"";}s:12:"_multiwidg | yes      |
|        99 | widget_recent-posts                                                       | a:2:{i:2;a:2:{s:5:"title";s:0:"";s:6:"number";i:5; | yes      |
|       100 | widget_recent-comments                                                    | a:2:{i:2;a:2:{s:5:"title";s:0:"";s:6:"number";i:5; | yes      |
|       101 | widget_archives                                                           | a:2:{i:2;a:3:{s:5:"title";s:0:"";s:5:"count";i:0;s | yes      |
|       102 | widget_meta                                                               | a:2:{i:2;a:1:{s:5:"title";s:0:"";}s:12:"_multiwidg | yes      |
|       103 | sidebars_widgets                                                          | a:5:{s:19:"wp_inactive_widgets";a:0:{}s:9:"sidebar | yes      |
|       104 | bedrock_autoloader                                                        | a:2:{s:7:"plugins";a:0:{}s:5:"count";i:0;}         | no       |
|       105 | widget_pages                                                              | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       106 | widget_calendar                                                           | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       107 | widget_media_audio                                                        | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       108 | widget_media_image                                                        | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       109 | widget_media_gallery                                                      | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       110 | widget_media_video                                                        | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       111 | widget_tag_cloud                                                          | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       112 | widget_nav_menu                                                           | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       113 | widget_custom_html                                                        | a:1:{s:12:"_multiwidget";i:1;}                     | yes      |
|       114 | cron                                                                      | a:3:{i:1542542410;a:4:{s:34:"wp_privacy_delete_old | yes      |
|       115 | _transient_doing_cron                                                     | 1542542962.7744550704956054687500                  | yes      |
|       116 | _site_transient_update_core                                               | O:8:"stdClass":4:{s:7:"updates";a:1:{i:0;O:8:"stdC | no       |
|       117 | _site_transient_update_plugins                                            | O:8:"stdClass":4:{s:12:"last_checked";i:1542542414 | no       |
|       118 | _site_transient_update_themes                                             | O:8:"stdClass":4:{s:12:"last_checked";i:1542542417 | no       |
|       119 | _site_transient_timeout_browser_45042d96ffcb6365f0c618982bb5a7ff          | 1543147218                                         | no       |
|       120 | _site_transient_browser_45042d96ffcb6365f0c618982bb5a7ff                  | a:10:{s:4:"name";s:6:"Chrome";s:7:"version";s:13:" | no       |
|       121 | can_compress_scripts                                                      | 0                                                  | no       |
|       122 | _site_transient_timeout_community-events-bf177db4cdd8a3d4c99b4463854de2bd | 1542585620                                         | no       |
|       123 | _site_transient_community-events-bf177db4cdd8a3d4c99b4463854de2bd         | a:2:{s:8:"location";a:1:{s:2:"ip";s:10:"172.19.0.0 | no       |
|       124 | _transient_timeout_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca                  | 1542585621                                         | no       |
|       125 | _transient_feed_ac0b00fe65abe10e0c5b588f3ed8c7ca                          | a:4:{s:5:"child";a:1:{s:0:"";a:1:{s:3:"rss";a:1:{i | no       |
|       126 | _transient_timeout_feed_mod_ac0b00fe65abe10e0c5b588f3ed8c7ca              | 1542585621                                         | no       |
|       127 | _transient_feed_mod_ac0b00fe65abe10e0c5b588f3ed8c7ca                      | 1542542421                                         | no       |
|       128 | _transient_timeout_feed_d117b5738fbd35bd8c0391cda1f2b5d9                  | 1542585623                                         | no       |
|       129 | _transient_feed_d117b5738fbd35bd8c0391cda1f2b5d9                          | a:4:{s:5:"child";a:1:{s:0:"";a:1:{s:3:"rss";a:1:{i | no       |
|       130 | _transient_timeout_feed_mod_d117b5738fbd35bd8c0391cda1f2b5d9              | 1542585623                                         | no       |
|       131 | _transient_feed_mod_d117b5738fbd35bd8c0391cda1f2b5d9                      | 1542542423                                         | no       |
|       132 | _transient_timeout_dash_v2_88ae138922fe95674369b1cb3d215a2b               | 1542585623                                         | no       |
|       133 | _transient_dash_v2_88ae138922fe95674369b1cb3d215a2b                       |