Wordpress 在哪里存储自定义菜单？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4705413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Where does Wordpress store custom menus?
提问by Martti Laine
I had to copy a whole wordpress-installation to another server. I simply installed a new Wordpress there and imported all the data from an xml-file previous Wordpress "exported" for me. Anyway, custom menus were not included. Do you know where in the database are they located, so I could grab them from there?
我不得不将整个 wordpress 安装复制到另一台服务器。我只是在那里安装了一个新的 Wordpress，然后从以前 Wordpress 为我“导出”的 xml 文件中导入了所有数据。无论如何，不包括自定义菜单。你知道它们在数据库中的哪个位置，所以我可以从那里获取它们吗？
采纳答案by txyoji
This setting happens in the wp_posts table. Look in the table for records where the menu_order greater than zero.
此设置发生在 wp_posts 表中。在表中查找 menu_order 大于零的记录。
select * from wp_posts where menu_order > 0;
It will also give you the name of the option in the wp_options table where the menu option is configured.
它还将为您提供 wp_options 表中配置菜单选项的选项的名称。
select * from wp_options where option_name = "nav_menu_options";
Also be aware that that wordpress import/export tool will not import media (images,video etc) from the media library which are not being used in posts. If you've got stuff that you directly linked to, its not going to be moved either.
另请注意，wordpress 导入/导出工具不会从媒体库中导入未在帖子中使用的媒体（图像、视频等）。如果你有直接链接的东西，它也不会被移动。
回答by random_user_name
I've got a site with a Magento install and WordPress install sitting next to each other, and cross-linking.
我有一个站点，其中 Magento 安装和 WordPress 安装并排放置，并且交叉链接。
I recently spent several hours writing a class to drop into the Magento installation so that I could render the WordPress menu as the navigation menu in the Magento site.
我最近花了几个小时编写了一个类以进入 Magento 安装，以便我可以将 WordPress 菜单呈现为 Magento 站点中的导航菜单。
Posts here have been helpful, but none of them have completely explained the structure of how WordPress menus are stored. Like many WP things, it is stored in a series of relationships. Here's the structure:
这里的帖子很有帮助，但都没有完全解释 WordPress 菜单的存储结构。像许多WP的东西一样，它存储在一系列的关系中。这是结构：
(note that this example presumes a table prefix if of "wp_")
（请注意，此示例假定表前缀为“wp_”）
- First, it's important to recognize that a menu item can be a post(technically it's a page, but pages are stored in the post table), a category, or it can be a customlink.
- Because WP supports multiple menus, you first look in the wp_term_taxonomytable table to find any terms with the taxonomy of 'nav_menu'. Note the term_id from that table.
- To find the name and slug of the menu, visit the wp_termstable and find the term with the id noted from step 2 above.
- Go to wp_term_relationshipstable and list all of the records with the term_taxonomy_id that matched the term_id from step 1. The object_id field tells you the wp_post.id record where you can find the menu record.
- Finally, go to wp_postmetato find many elements describing the menu. Of particular interest are:
- _menu_item_object- the TYPE of menu item (page, custom, or category)
- _menu_item_object_id- the id of the actual POST (or category, if it's a category) that the menu item references
- _menu_item_menu_item_parent- the heirarchical parent structure of the MENU (which can be different than the post parent relationships)
- _menu_item_url- the slug of the menu item (if it is a custom linkmenu item)
- 首先，重要的是要认识到菜单项可以是一个帖子（技术上它是一个页面，但页面存储在帖子表中）、一个类别，或者它可以是一个自定义链接。
- 由于 WP 支持多个菜单，因此您首先查看wp_term_taxonomy表以查找具有“nav_menu”分类法的任何术语。请注意该表中的 term_id。
- 要查找菜单的名称和 slug，请访问wp_terms表并查找具有上述步骤 2 中记录的 id 的术语。
- 转到wp_term_relationships表并列出具有与步骤 1 中的 term_id 匹配的 term_taxonomy_id 的所有记录。 object_id 字段告诉您 wp_post.id 记录，您可以在其中找到菜单记录。
- 最后，去wp_postmeta找到许多描述菜单的元素。特别感兴趣的是：
- _menu_item_object- 菜单项的类型（页面、自定义或类别）
- _menu_item_object_id- 菜单项引用的实际 POST（或类别，如果是类别）的 ID
- _menu_item_menu_item_parent- 菜单的分层父结构（可以与后父关系不同）
- _menu_item_url- 菜单项的 slug（如果它是自定义链接菜单项）
Sample SQL statements to perform the above described operation:
执行上述操作的示例 SQL 语句：
SELECT t.term_id
FROM wp_term_taxonomy as tax
LEFT JOIN wp_terms as t ON tax.term_id = t.term_id
WHERE taxonomy = 'nav_menu' and name like '%top%'
(looks for a menu item with the name of 'Top', and gets the term id)
（查找名称为“Top”的菜单项，并获取术语 id）
SELECT p.ID, p.post_title, p.post_name, p.menu_order, n.post_name as n_name, n.post_title as n_title, m.meta_value, pp.meta_value as menu_parent
FROM wp_term_relationships as txr
INNER JOIN wp_posts as p ON txr.object_id = p.ID
LEFT JOIN wp_postmeta as m ON p.ID = m.post_id
LEFT JOIN wp_postmeta as pl ON p.ID = pl.post_id AND pl.meta_key = '_menu_item_object_id'
LEFT JOIN wp_postmeta as pp ON p.ID = pp.post_id AND pp.meta_key = '_menu_item_menu_item_parent'
LEFT JOIN wp_posts as n ON pl.meta_value = n.ID
WHERE txr.term_taxonomy_id = 3 AND p.post_status='publish'
AND p.post_type = 'nav_menu_item' AND m.meta_key = '_menu_item_url'
ORDER BY p.menu_order
(loads the data for the menu, based on the term_id of 3)
（加载菜单数据，基于 term_id 为 3）
Note that this sql statement will work for pages and custom menus (I don't have any categories, so didn't include that). The data loaded will allow you to build the permalink using the siteurl from the wp_optionstable, and appending the post_name to the end (technically, it's not getting the parent structure, but WP finds the page/post properly without it)
请注意，此 sql 语句适用于页面和自定义菜单（我没有任何类别，因此没有包含）。加载的数据将允许您使用wp_options表中的 siteurl 构建永久链接，并将post_name附加到末尾（从技术上讲，它没有获取父结构，但 WP 可以在没有它的情况下正确找到页面/帖子）
Update
A commenter asked about assembling the child menu items with the parent menu items. That will need to be done with PHP. Something like below will do that for you:
更新
一位评论者询问如何将子菜单项与父菜单项组合在一起。这需要用 PHP 来完成。像下面这样的东西会为你做到这一点：
// run the query from above
$results = $wpdb->get_results('SELECT....');
// declare new variable to store "assembled" menu
$menu = array();
// loop over the items assigning children to parents
foreach( $results AS $row ) {
// assemble key bits for the menu item
$item = array(
// handles custom navigation labels
'title' => ( $row->post_title ) ? $row->post_title : $row->n_title,
// handles custom links
'permalink' => ( $row->meta_value ) ? $row->meta_value : get_permalink( $row->ID ),
// declares empty placeholder for any child items
'children' => array()
);
// if the menu item has a parent, assign as child of the parent
if ( $row->menu_parent ) {
$menu[ $row->menu_parent ][ 'children' ][] = $item;
} else {
$menu[ $row->ID ] = $item;
}
}
var_dump( $menu );
// outputs something like below:
/**
* array (size=6)
* 77 =>
* array (size=3)
* 'title' => string 'About Us' (length=8)
* 'permalink' => string 'http://www.example.com/about' (length=33)
* 'children' =>
* array (size=7)
* 0 =>
* array (size=3)
* 'title' => string 'Welcome' (length=22)
* 'permalink' => string 'http://www.example.com/welcome' (length=35)
* 'children' =>
* array (size=0)
* empty
* 1 =>
* array (size=3)
* 'title' => string 'Mission' (length=20)
* 'permalink' => string 'http://www.example.com/mission' (length=33)
* 'children' =>
* array (size=0)
* empty
* 90 =>
* array (size=3)
* 'title' => string 'Contact Us' (length=10)
* 'permalink' => string 'http://www.example.com/contact' (length=33)
* 'children' =>
* array (size=5)
* 0 =>
* array (size=3)
* 'title' => string 'Why Us' (length=12)
* 'permalink' => string 'http://www.example.com/why' (length=35)
* 'children' =>
* array (size=0)
* empty
* 1258 =>
* array (size=3)
* 'title' => string 'Login' (length=12)
* 'permalink' => string 'https://customlink.example.com/some/path/login.php' (length=82)
* 'children' =>
* array (size=0)
* empty
*/
回答by Ben Erwin
I found this just because I was looking for the answer myself. I see your post is quite old, but the answer is in wp_postmeta, run this query:
我发现这个只是因为我自己在寻找答案。我看到您的帖子很旧，但答案在 wp_postmeta 中，运行以下查询：
SELECT *
FROM `wp_postmeta`
WHERE meta_key LIKE '%menu%'
LIMIT 0, 30
You'll find many entries.
你会发现很多条目。
回答by Bob Jones
I have been searching high and low for the entire structure and I finally cracked the code:
一直在高低搜索整个结构，终于破解了密码：
SELECT
p.ID,
m.meta_value,
md.post_author,
wp_users.user_nicename,
p.post_parent,
p.menu_order,
md.post_title
FROM
wp_posts AS p
INNER JOIN wp_postmeta AS m ON m.post_id = p.ID
INNER JOIN wp_posts AS md ON md.ID = m.meta_value AND m.meta_value = md.ID
INNER JOIN wp_users ON md.post_author = wp_users.ID
WHERE
p.menu_order > 0 AND
p.post_type = 'nav_menu_item' AND
m.meta_key = '_menu_item_object_id'
ORDER BY
p.menu_order ASC
回答by kangbu
I added additional column on cale_b's query. The column lvlhas depth for indentation.
我在 cal_b 的查询中添加了额外的列。该列lvl具有缩进深度。
Assume that:
假使，假设：
- max depth is 6, so i defined 6 variables.
- menu's post_id is over 6. Most of you would've already exceed that number.
- 最大深度为 6，所以我定义了 6 个变量。
- 菜单的 post_id 超过 6。你们中的大多数人已经超过了这个数字。
Query:
询问：
select case when a.meta_value = 0 then least(@lvl:=1, @p1:=a.id)
when a.meta_value = @p1 then least(@lvl:=2, @p2:=a.id)
when a.meta_value = @p2 then least(@lvl:=3, @p3:=a.id)
when a.meta_value = @p3 then least(@lvl:=4, @p4:=a.id)
when a.meta_value = @p4 then least(@lvl:=5, @p5:=a.id)
when a.meta_value = @p5 then least(@lvl:=6, @p6:=a.id)
end lvl
,a.id
from (
SELECT p.id
,p.post_title
,p.post_name
,pp.meta_value
,p.menu_order
,n.post_name as n_name
,n.post_title as n_title
,m.meta_value meta2
FROM wordpress_dw.stat_term_relationships as txr
INNER JOIN wordpress_dw.stat_posts as p ON txr.object_id = p.ID
LEFT JOIN wordpress_dw.stat_postmeta as m ON p.ID = m.post_id and m.meta_key = '_menu_item_url'
LEFT JOIN wordpress_dw.stat_postmeta as pl ON p.ID = pl.post_id AND pl.meta_key = '_menu_item_object_id'
LEFT JOIN wordpress_dw.stat_postmeta as pp ON p.ID = pp.post_id AND pp.meta_key = '_menu_item_menu_item_parent'
LEFT JOIN wordpress_dw.stat_posts as n ON pl.meta_value = n.ID
,(select @lvl:=0, @p1:=0, @p2:=0, @p3:=0, @p4:=0, @p5:=0, @p6:=0) x
WHERE txr.term_taxonomy_id = 2 -- your menu term id
AND p.post_status='publish'
AND p.post_type = 'nav_menu_item'
ORDER BY p.menu_order
) a
Result:
结果：
lvl |id |
----|----|
1 |508 |
1 |509 |
2 |510 |
3 |511 |
3 |512 |
3 |513 |
3 |514 |
2 |515 |
1 |516 |
2 |517 |
2 |518 |
3 |519 |
3 |520 |
3 |521 |
3 |522 |
