WordPress 数据库中的文章特色图片链接在哪里？

WordPress 数据库中的特色图片链接在哪里？我在 wp_postmeta表中搜索，但是找不到确切的 post_id和 links。

是这样吗? 有人能给我解释一下它是怎么工作的吗？

57129 次浏览
小开
最佳答案

The featured image ID is stored in wp_postmeta with a meta_key called _thumbnail_id. Example:

╔═════════╦═════════╦═══════════════╦═══════════╗
║ meta_id ║ post_id ║ meta_key      ║ meta_value║
╠═════════╬═════════╬═══════════════╬═══════════╣
║ 200     ║ 4       ║ _thumbnail_id ║ 48        ║
╚═════════╩═════════╩═══════════════╩═══════════╝

The actual thumbnail link is then contained in wp_posts with a post_type of attachment. Example:

╔════╦════════════╦═════════════════════════════════════════════════════╗
║ ID ║ post_type  ║ guid                                                ║
╠════╬════════════╬═════════════════════════════════════════════════════╣
║ 48 ║ attachment ║ http://example.com/wp-content/uploads/yourimage.png ║
╚════╩════════════╩═════════════════════════════════════════════════════╝
小开

I was curious, so here goes...

  • The wp_postmeta table will hold an entry for the post with meta_key of _thumbnail_id
  • the meta_value is a child post_id for the featured image
  • using that post_id, you can obtain further information from wp_posts and wp_postmeta

To put it all together, here's how to get the child wp_posts row for the featured image of post XXX...

SELECT childpost.*
FROM wp_posts childpost
INNER JOIN wp_postmeta parentmeta ON (childpost.ID=parentmeta.meta_value)
WHERE parentmeta.meta_key='_thumbnail_id'
AND parentmeta.post_id=XXX;

And here's the meta data for that same image

SELECT childmeta.*
FROM wp_postmeta childmeta
INNER JOIN wp_postmeta parentmeta ON (childmeta.post_id=parentmeta.meta_value)
WHERE parentmeta.meta_key='_thumbnail_id'
AND parentmeta.post_id=XXX;

The metadata will include a _wp_attached_file relative path, and a _wp_attachment_metadata containing some PHP serialized data.

小开

here my sql with full url image 

SELECT concat((select option_value from wp_options where option_name ='siteurl'  limit 1),'/wp-content/uploads/',childmeta.meta_value)
FROM wp_postmeta childmeta
INNER JOIN wp_postmeta parentmeta ON (childmeta.post_id=parentmeta.meta_value)
WHERE parentmeta.meta_key='_thumbnail_id' and childmeta.meta_key = '_wp_attached_file'
AND parentmeta.post_id = POST_ID ;


select option_name from wp_options where option_name ='siteurl'

the result will be like this

http://yourdomain/blog-wp/wp-content/uploads/2015/04/IMG_06062014_155904.png

小开

Even if rnevius answer seemed correct, the result returned some images with this kind of URL : http://www.example.com/?attachment_id=48 which was not working in my case.

Tested on Wordpress 4.9.3 :

Another solution is to use _wp_attached_file instead :

wp_postmeta

Search for value _thumbnail_id in meta_key

meta_id   | post_id   | meta_key            | meta_value
200       | 4         | _thumbnail_id       | 48

wp_postmeta

Again in wp_postmeta, search for value _wp_attached_file in meta_key where post_id is matching the meta_value find in previous query

meta_id   | post_id   | meta_key            | meta_value
1020      | 48        | _wp_attached_file   | 2018/09/picture.jpg

Query :

SELECT wp.ID, wpm2.meta_value
FROM wp_posts wp
INNER JOIN wp_postmeta wpm
ON (wp.ID = wpm.post_id AND wpm.meta_key = '_thumbnail_id')
INNER JOIN wp_postmeta wpm2
ON (wpm.meta_value = wpm2.post_id AND wpm2.meta_key = '_wp_attached_file')

提交答案