php - About product Infinitus classification Mysql table design

Question

Currently there are three tables, 1: goods main table id name price

          2： goods_type 商品类型表  tid name 
          3:  goods_type_map  商品和商品类型关系表  id gid gtid

The previous table structure is as above. Now since the corresponding type of the product can be one-to-many, and one product can correspond to multiple types, every time I update the type of the product, I need to delete the existing content of the relationship table and insert the new one. type relationship.

goods table:

    id  name  price  goods_type
    1   洗面奶    22    1,8
    2   面膜      33    11

goods_type table

    tid name 
    1    化妆品
    8    护肤品
    11   保健品

So I want to change it to: Add the goods_type field to the main product table to replace the goods_type_map table. Use commas to separate multiple types.

But there is currently a problem. When querying a certain type of product, using Like query often results in query errors. So I would like to ask how to deal with this situation

Answer 1

Using like is not a good choice. I recommend a Mysql function find_in_set which should be able to solve the original poster's needs. Official documentation: https://dev.mysql.com/doc/ref...

Answer 2

You can also consider using stored procedures for recursive implementation, but this is not recommended. Infinite classification itself is not a good user experience design, not to mention it will increase the pressure on the server and database.

Answer 3

First use like to find out part of the data, then loop in_array() to filter, or change the table structure

Answer 4

This is obviously a many-to-many relationship.
So I feel that it is better for you to keep three tables.
This is more in line with the paradigm.

Answer 5

It should be more reasonable to conform to the paradigm. If you insist on the original idea, add separators before and after each category ID, and add separators when matching with like

Answer 6

When you use like, for example, if you have a value like 1,2,11 like 1 will like 1 and 11
In this case, please use find_in_set

If you insist on using like, the field value can be like this, 1,2,11. When you like, like ,1, can also achieve the purpose