Ask the master: How to design a better table for saving uploaded pictures?

When we did not use third-party storage, we made a table for image upload in our project, as shown above, which can be used in the image upload model to remove duplicate images. The url column stores the address of the imageUploads/Pic/2016-06-15/5760a0ed1d994.png, but the actual access address is www.xxx.com/Uploads/Pic/2016-06-15/5760a0ed1d994.png

This address can be split into: Uploads/Pic/ and 2016-06-15/ and 5760a0ed1d994.png

i.e.: 服务器相对目录, 动态目录, 动态文件名

We use this upload model for all image uploads. After uploading and removing duplicates, it feels more efficient. The main image of our product is directly placed in this table id. This design has always been okay, but now there is a problem Now:

1: First of all URLAll the pictures here are placed under the website access directory, which is not suitable for some copyright-protected pictures.

2: We now have a single server. After reading this url, add a URL in front of it or web access the root directory “/”. However, if there are multiple servers, it will be difficult to handle. (Obviously there is something wrong with this design, I just didn’t realize it at first)

3: If we use a third-party image storage at this time, then the previous product main image using this image table will fall apart. If we use a third party, then the product table will What is stored in the main image field? Is it the image access address of Qiniu? id

Supplement:

https://fuss10.elemecdn.com/e/bb/631e55d8cd93dab03a687807900c1jpeg.jpeg?imageMogr2/thumbnail/70x70/format/webp/quality/85

Seeing this address, I found that problem 2 seems to be saved, that is, extracting a layer of "directory" -

(https://fuss10.elemecdn.com/), hey, it just suddenly occurred to me and I have no idea. In this case, if the host If it changes, it will be over. Thinking about it makes my head hurt. 主机目录/资源目录

I even want to add image size, size fields, and exif information fields to this table. I don’t know if it is possible.

I hope experienced experts can give me some ideas, thank you^_^

Reply content:

When we did not use third-party storage, we made a table for image upload in our project, as shown above, which can be used in the image upload model to remove duplicate images. The

column stores the address of the imageurl, but the actual access address is Uploads/Pic/2016-06-15/5760a0ed1d994.pngwww.xxx.com/Uploads/Pic/2016-06-15/5760a0ed1d994.png

This address can be split into:

and Uploads/Pic/ and 2016-06-15/5760a0ed1d994.png

i.e.:

, 服务器相对目录, 动态目录动态文件名

We use this upload model for all image uploads. After uploading and removing duplicates, it feels more efficient. The main image of our product is directly placed in this table

. This design has always been okay, but now there is a problem Now: id

1: First of all

All the pictures here are placed under the website access directory, which is not suitable for some copyright-protected pictures. URL

2: We now have a single server. After reading this

, add a URL in front of it or url visit the root directory web. However, if there are multiple servers, it will be difficult to handle. (Obviously there is something wrong with this design, I just didn’t realize it at first) “/”

3: If we use a third-party image storage at this time, then the previous product main image using this image table

Wouldn’t this design fall apart? If we use a third party, then the product table What is stored in the main image field? Is it the image access address of Qiniu? id

Supplement:

https://fuss10.elemecdn.com/e/bb/631e55d8cd93dab03a687807900c1jpeg.jpeg?imageMogr2/thumbnail/70x70/format/webp/quality/85

Seeing this address, I found that problem 2 seems to be saved, that is, extracting a layer of "directory" -

(https://fuss10.elemecdn.com/), hey, it just suddenly occurred to me and I have no idea. In this case, if the host If it changes, it will be over. Thinking about it makes my head hurt. 主机目录/资源目录

I even want to add image size, size fields, and exif information fields to this table. I don’t know if it is possible.

I hope experienced experts can give me some ideas, thank you^_^

1: First of all, all the pictures here in the URL are placed under the website access directory, which is not suitable for some copyright-protected pictures.

Copyright protection, what does this refer to? Anti-hotlinking? Or prevent downloading or something. Qiniu supports both anti-leeching and token download permission verification. It should be able to meet your needs

2: We have a single server now. After reading this url, just add a URL or web access root directory "/" in front of it, but if there are multiple servers, it will be difficult to handle. (Obviously there is something wrong with this design, I just didn’t realize it at first)

You have already sent it to Qiniu, so there is no need to consider this issue at all. One image domain name does it all. Just write this domain name in the program configuration.

3: If we use a third-party image storage at this time, then the previous design of using the ID of this image table for the main product image will fall apart. If we use a third party, then the product What is stored in the main image field of the table? Is it the image access address of Qiniu?

Qiniu has an Etag and a clear algorithm that can be used to remove duplicates. Each picture has a unique Etag. You can use this as your original ID to save.

Additional~~What you wrote are all relative paths, so don’t worry about changing the host or anything. If you use a third party, just distinguish the domain name.
If you want to build your own, you can use consistent Hash and other solutions to distinguish hosts. . certainly. You can refer to Taobao for this structure. Relatively complete

Thank you for the invitation, like and cheers.
Coders will not consider your problem because they have no awareness. If you have more experience, you won’t consider this kind of problem because you have already considered it. So you're on your way.

First of all, there is no need to remove duplicate images because
1-As a product administrator, you will not upload two identical images for a product without any mistakes
2-When the products are different Even if the pictures are the same, there is no need to remove duplicates, because fundamentally they are different products and different pictures should be used. The same ones may be used now for some reasons, but it does not rule out the possibility of modification in the future, and you After deduplication, different products may use the same image ID. What if a product needs to update its corresponding image separately? It can be solved, but it is of no value.

Secondly, the problem of image access is actually not important. You can’t prevent it from being stolen. To prevent hotlinking, simply put, when receiving a resource request (such as css, js, image, etc.), the web server checks the source, such as It must be initiated from the xxx domain name, otherwise it will be a hot link. However, it is not very useful. You should be able to find the information and configure the server yourself.

Finally, what are the implications of using remote? There is no impact except the change of the image saving location. Your table is still necessary, but the value stored in the url field is the remote address. As for the digest values ​​of md5 and sha1, they are really useless.

This is the data sheet I used when using Qiniu for reference

<code>  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `json_detail` text, //七牛响应信息
  `parent_id` int(11) unsigned DEFAULT NULL, //生成的缩略图也要记下来，缩略图需要指明根据哪个图片来的
  `etag` varchar(64) NOT NULL, //实体标识（应该是七牛用来去重的）
  `src` varchar(255) NOT NULL, //远端地址
  `action_type` varchar(16) NOT NULL, //对应的七牛空间
  `mime_type` varchar(64) DEFAULT NULL, 
  `filesize` bigint(20) unsigned DEFAULT NULL,
  `key` varchar(192) NOT NULL, //唯一标识
  `original_filename` varchar(192) DEFAULT NULL,
  `width` int(11) DEFAULT NULL, //图片宽
  `height` int(11) DEFAULT NULL, //图片高 优化浏览器对图片的显示
  `extension` varchar(8) DEFAULT NULL,
  `format` varchar(16) DEFAULT NULL,
  `created_at` int(11) unsigned NOT NULL,
  `main_color` char(8) DEFAULT NULL, //图片的主色，七牛功能
  `user_id` int(11) unsigned DEFAULT NULL,</code>

Especially for the mall, we pay more attention to:
1- Whether the image is available
2- Whether the image size is appropriate (usually the mall always displays the thumbnail of the image instead of the original image, for the sake of speed, it is very difficult Important)
3- Display optimization, the tag will render faster in the browser when it contains height and width values ​​
4- Synchronous deletion of image files