Menu

DB structure of woocommerce

2018-11-18 - Plugin, Wordpress

Tables of woocommerce

wc_download_log

CREATE TABLE `wc_download_log` (
  `download_log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `permission_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `user_ip_address` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`download_log_id`),
  KEY `permission_id` (`permission_id`),
  KEY `timestamp` (`timestamp`),
  CONSTRAINT `fk_wc_download_log_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `woocommerce_downloadable_product_permissions` (`permission_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


wc_webhooks

CREATE TABLE `wc_webhooks` (
  `webhook_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `delivery_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `secret` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `topic` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_created_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `api_version` smallint(4) NOT NULL,
  `failure_count` smallint(10) NOT NULL DEFAULT '0',
  `pending_delivery` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`webhook_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_api_keys

CREATE TABLE `woocommerce_api_keys` (
  `key_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `description` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permissions` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `consumer_key` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `consumer_secret` char(43) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nonces` longtext COLLATE utf8mb4_unicode_ci,
  `truncated_key` char(7) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_access` datetime DEFAULT NULL,
  PRIMARY KEY (`key_id`),
  KEY `consumer_key` (`consumer_key`),
  KEY `consumer_secret` (`consumer_secret`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_attribute_taxonomies

CREATE TABLE `woocommerce_attribute_taxonomies` (
  `attribute_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `attribute_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute_label` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attribute_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute_orderby` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute_public` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`attribute_id`),
  KEY `attribute_name` (`attribute_name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_downloadable_product_permissions

CREATE TABLE `woocommerce_downloadable_product_permissions` (
  `permission_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `download_id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `order_key` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_email` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `downloads_remaining` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `access_granted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `access_expires` datetime DEFAULT NULL,
  `download_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`permission_id`),
  KEY `download_order_key_product` (`product_id`,`order_id`,`order_key`(16),`download_id`),
  KEY `download_order_product` (`download_id`,`order_id`,`product_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

No data


woocommerce_log

CREATE TABLE `woocommerce_log` (
  `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `level` smallint(4) NOT NULL,
  `source` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `context` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`log_id`),
  KEY `level` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

No data


woocommerce_order_itemmeta

CREATE TABLE `woocommerce_order_itemmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_id` bigint(20) unsigned NOT NULL,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `order_item_id` (`order_item_id`),
  KEY `meta_key` (`meta_key`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_order_items

CREATE TABLE `woocommerce_order_items` (
  `order_item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_name` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `order_item_type` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `order_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`order_item_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

No data


woocommerce_payment_tokenmeta

CREATE TABLE `woocommerce_payment_tokenmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `payment_token_id` bigint(20) unsigned NOT NULL,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `payment_token_id` (`payment_token_id`),
  KEY `meta_key` (`meta_key`(32))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_payment_tokens

CREATE TABLE `woocommerce_payment_tokens` (
  `token_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gateway_id` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `type` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`token_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

No data


woocommerce_sessions

CREATE TABLE `woocommerce_sessions` (
  `session_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `session_key` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `session_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `session_expiry` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`session_id`),
  UNIQUE KEY `session_key` (`session_key`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

No data


woocommerce_shipping_zone_locations

CREATE TABLE `woocommerce_shipping_zone_locations` (
  `location_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_id` bigint(20) unsigned NOT NULL,
  `location_code` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`location_id`),
  KEY `location_id` (`location_id`),
  KEY `location_type_code` (`location_type`(10),`location_code`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

After setting up

+-------------+---------+---------------+---------------+
| location_id | zone_id | location_code | location_type |
+-------------+---------+---------------+---------------+
|           1 |       1 | JP            | country       |
+-------------+---------+---------------+---------------+

woocommerce_shipping_zone_methods

CREATE TABLE `woocommerce_shipping_zone_methods` (
  `zone_id` bigint(20) unsigned NOT NULL,
  `instance_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `method_id` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `method_order` bigint(20) unsigned NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Data

+---------+-------------+---------------+--------------+------------+
| zone_id | instance_id | method_id     | method_order | is_enabled |
+---------+-------------+---------------+--------------+------------+
|       1 |           1 | free_shipping |            1 |          1 |
|       0 |           2 | free_shipping |            1 |          1 |
+---------+-------------+---------------+--------------+------------+

woocommerce_shipping_zones

CREATE TABLE `woocommerce_shipping_zones` (
  `zone_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zone_order` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`zone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

After setting up

+---------+-----------+------------+
| zone_id | zone_name | zone_order |
+---------+-----------+------------+
|       1 | Japan     |          0 |
+---------+-----------+------------+

woocommerce_tax_rate_locations

CREATE TABLE `woocommerce_tax_rate_locations` (
  `location_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `location_code` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_rate_id` bigint(20) unsigned NOT NULL,
  `location_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`location_id`),
  KEY `tax_rate_id` (`tax_rate_id`),
  KEY `location_type_code` (`location_type`(10),`location_code`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

No data


woocommerce_tax_rates

CREATE TABLE `woocommerce_tax_rates` (
  `tax_rate_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tax_rate_country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tax_rate_state` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tax_rate` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tax_rate_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `tax_rate_priority` bigint(20) unsigned NOT NULL,
  `tax_rate_compound` int(1) NOT NULL DEFAULT '0',
  `tax_rate_shipping` int(1) NOT NULL DEFAULT '1',
  `tax_rate_order` bigint(20) unsigned NOT NULL,
  `tax_rate_class` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`tax_rate_id`),
  KEY `tax_rate_country` (`tax_rate_country`),
  KEY `tax_rate_state` (`tax_rate_state`(2)),
  KEY `tax_rate_class` (`tax_rate_class`(10)),
  KEY `tax_rate_priority` (`tax_rate_priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

No data


Change to existing tables

options

mysql> select option_id, option_name, substring(option_value, 1,50), autoload from bdroptions where option_name not like “%transient%” and option_id >140;

+-----------+
| option_id | option_name                                               | substring(option_value, 1,50)                      | autoload |
+-----------+
|       145 | woocommerce_store_address                                 | ..., Asahi-ku              | yes      |
|       146 | woocommerce_store_address_2                               |                                                    | yes      |
|       147 | woocommerce_store_city                                    | Yokohama                                           | yes      |
|       148 | woocommerce_default_country                               | JP:JP14                                            | yes      |
|       149 | woocommerce_store_postcode                                | 241-....                                           | yes      |
|       150 | woocommerce_allowed_countries                             | all                                                | yes      |
|       151 | woocommerce_all_except_countries                          |                                                    | yes      |
|       152 | woocommerce_specific_allowed_countries                    |                                                    | yes      |
|       153 | woocommerce_ship_to_countries                             |                                                    | yes      |
|       154 | woocommerce_specific_ship_to_countries                    |                                                    | yes      |
|       155 | woocommerce_default_customer_address                      | geolocation                                        | yes      |
|       156 | woocommerce_calc_taxes                                    | no                                                 | yes      |
|       157 | woocommerce_enable_coupons                                | yes                                                | yes      |
|       158 | woocommerce_calc_discounts_sequentially                   | no                                                 | no       |
|       159 | woocommerce_currency                                      | JPY                                                | yes      |
|       160 | woocommerce_currency_pos                                  | left                                               | yes      |
|       161 | woocommerce_price_thousand_sep                            | ,                                                  | yes      |
|       162 | woocommerce_price_decimal_sep                             | .                                                  | yes      |
|       163 | woocommerce_price_num_decimals                            | 0                                                  | yes      |
|       164 | woocommerce_shop_page_id                                  | 5                                                  | yes      |
|       165 | woocommerce_cart_redirect_after_add                       | no                                                 | yes      |
|       166 | woocommerce_enable_ajax_add_to_cart                       | yes                                                | yes      |
|       167 | woocommerce_placeholder_image                             |                                                    | yes      |
|       168 | woocommerce_weight_unit                                   | kg                                                 | yes      |
|       169 | woocommerce_dimension_unit                                | cm                                                 | yes      |
|       170 | woocommerce_enable_reviews                                | yes                                                | yes      |
|       171 | woocommerce_review_rating_verification_label              | yes                                                | no       |
|       172 | woocommerce_review_rating_verification_required           | no                                                 | no       |
|       173 | woocommerce_enable_review_rating                          | yes                                                | yes      |
|       174 | woocommerce_review_rating_required                        | yes                                                | no       |
|       175 | woocommerce_manage_stock                                  | yes                                                | yes      |
|       176 | woocommerce_hold_stock_minutes                            | 60                                                 | no       |
|       177 | woocommerce_notify_low_stock                              | yes                                                | no       |
|       178 | woocommerce_notify_no_stock                               | yes                                                | no       |
|       179 | woocommerce_stock_email_recipient                         | hajimekurita@1stclass.co.jp                        | no       |
|       180 | woocommerce_notify_low_stock_amount                       | 2                                                  | no       |
|       181 | woocommerce_notify_no_stock_amount                        | 0                                                  | yes      |
|       182 | woocommerce_hide_out_of_stock_items                       | no                                                 | yes      |
|       183 | woocommerce_stock_format                                  |                                                    | yes      |
|       184 | woocommerce_file_download_method                          | force                                              | no       |
|       185 | woocommerce_downloads_require_login                       | no                                                 | no       |
|       186 | woocommerce_downloads_grant_access_after_payment          | yes                                                | no       |
|       187 | woocommerce_prices_include_tax                            | no                                                 | yes      |
|       188 | woocommerce_tax_based_on                                  | shipping                                           | yes      |
|       189 | woocommerce_shipping_tax_class                            | inherit                                            | yes      |
|       190 | woocommerce_tax_round_at_subtotal                         | no                                                 | yes      |
|       191 | woocommerce_tax_classes                                   | Reduced rate
Zero rate                             | yes      |
|       192 | woocommerce_tax_display_shop                              | excl                                               | yes      |
|       193 | woocommerce_tax_display_cart                              | excl                                               | yes      |
|       194 | woocommerce_price_display_suffix                          |                                                    | yes      |
|       195 | woocommerce_tax_total_display                             | itemized                                           | no       |
|       196 | woocommerce_enable_shipping_calc                          | yes                                                | no       |
|       197 | woocommerce_shipping_cost_requires_address                | no                                                 | yes      |
|       198 | woocommerce_ship_to_destination                           | billing                                            | no       |
|       199 | woocommerce_shipping_debug_mode                           | no                                                 | yes      |
|       200 | woocommerce_enable_guest_checkout                         | yes                                                | no       |
|       201 | woocommerce_enable_checkout_login_reminder                | no                                                 | no       |
|       202 | woocommerce_enable_signup_and_login_from_checkout         | no                                                 | no       |
|       203 | woocommerce_enable_myaccount_registration                 | no                                                 | no       |
|       204 | woocommerce_registration_generate_username                | yes                                                | no       |
|       205 | woocommerce_registration_generate_password                | yes                                                | no       |
|       206 | woocommerce_erasure_request_removes_order_data            | no                                                 | no       |
|       207 | woocommerce_erasure_request_removes_download_data         | no                                                 | no       |
|       208 | woocommerce_registration_privacy_policy_text              | Your personal data will be used to support your ex | yes      |
|       209 | woocommerce_checkout_privacy_policy_text                  | Your personal data will be used to process your or | yes      |
|       210 | woocommerce_delete_inactive_accounts                      | a:2:{s:6:"number";s:0:"";s:4:"unit";s:6:"months";} | no       |
|       211 | woocommerce_trash_pending_orders                          |                                                    | no       |
|       212 | woocommerce_trash_failed_orders                           |                                                    | no       |
|       213 | woocommerce_trash_cancelled_orders                        |                                                    | no       |
|       214 | woocommerce_anonymize_completed_orders                    | a:2:{s:6:"number";s:0:"";s:4:"unit";s:6:"months";} | no       |
|       215 | woocommerce_email_from_name                               | Tour Guide in Japan                                | no       |
|       216 | woocommerce_email_from_address                            | hajimekurita@1stclass.co.jp                        | no       |
|       217 | woocommerce_email_header_image                            |                                                    | no       |
|       218 | woocommerce_email_footer_text                             | {site_title}
Powered by

posts

select * from bdrposts where id > 4;

+----+
| ID | post_author | post_date           | post_date_gmt       | post_content             | post_title | post_excerpt | post_status | comment_status | ping_status | post_password | post_name  | to_ping | pinged | post_modified       | post_modified_gmt   | post_content_filtered | post_parent | guid                              | menu_order | post_type | post_mime_type | comment_count |
+----+
|  5 |           1 | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 |                          | Shop       |              | publish     | closed         | closed      |               | shop       |         |        | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 |                       |           0 | https://vpsaws.com:10089/?page_id=5 |          0 | page      |                |             0 |
|  6 |           1 | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 | [woocommerce_cart]       | Cart       |              | publish     | closed         | closed      |               | cart       |         |        | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 |                       |           0 | https://vpsaws.com:10089/?page_id=6 |          0 | page      |                |             0 |
|  7 |           1 | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 | [woocommerce_checkout]   | Checkout   |              | publish     | closed         | closed      |               | checkout   |         |        | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 |                       |           0 | https://vpsaws.com:10089/?page_id=7 |          0 | page      |                |             0 |
|  8 |           1 | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 | [woocommerce_my_account] | My account |              | publish     | closed         | closed      |               | my-account |         |        | 2018-11-18 13:04:11 | 2018-11-18 13:04:11 |                       |           0 | https://vpsaws.com:10089/?page_id=8 |          0 | page      |                |             0 |
+----+

usermeta

select * from bdrusermeta where umeta_id > 20;

+----------+---------+--------------------------------+-------------------------+
| umeta_id | user_id | meta_key                       | meta_value              |
+----------+---------+--------------------------------+-------------------------+
|       21 |       1 | _woocommerce_persistent_cart_1 | a:1:{s:4:"cart";a:0:{}} |
|       22 |       1 | wc_last_active                 | 1542499200              |
+----------+---------+--------------------------------+-------------------------+

terms

select * from bdrterms where term_id > 1;
+---------+----------------------+----------------------+------------+
| term_id | name                 | slug                 | term_group |
+---------+----------------------+----------------------+------------+
|       2 | simple               | simple               |          0 |
|       3 | grouped              | grouped              |          0 |
|       4 | variable             | variable             |          0 |
|       5 | external             | external             |          0 |
|       6 | exclude-from-search  | exclude-from-search  |          0 |
|       7 | exclude-from-catalog | exclude-from-catalog |          0 |
|       8 | featured             | featured             |          0 |
|       9 | outofstock           | outofstock           |          0 |
|      10 | rated-1              | rated-1              |          0 |
|      11 | rated-2              | rated-2              |          0 |
|      12 | rated-3              | rated-3              |          0 |
|      13 | rated-4              | rated-4              |          0 |
|      14 | rated-5              | rated-5              |          0 |
|      15 | Uncategorized        | uncategorized        |          0 |
+---------+----------------------+----------------------+------------+