What are the best practices for displaying a one-to-many table relationship in MYSQL?

Users have many attributes (TAGS), such as singing, dancing, and playing games. The table structure is shown in the following mysql statement for creating a table: {code ...} the SQL statement for inserting data is as follows: {code ...} I want to display the following on the page: first, the user list, but at the same time, all the user tags...

Users have many attributes (TAGS), such as singing, dancing, and playing games. The table structure is as follows:
The mysql statement for creating a table is as follows:

drop table if exists user;create table user(   user_id              int not null auto_increment,   user_name            varchar(20),   primary key (user_id));drop table if exists tags;create table tags(   tag_id               int not null auto_increment,   tag_name             varchar(100),   primary key (tag_id));drop table if exists user_tag;create table user_tag(   user_id              int,   tag_id               int);

The SQL statement for inserting data is as follows:

Insert into user values (1, 'small A'); insert into user values (2, 'small B '); insert into user values (3, 'small C '); insert into tags values (1, 'change'); insert into tags values (2, 'Dancing '); insert into tags values (3, 'house '); insert into tags values (4, 'reading'); insert into tags values (5, 'travel '); insert into user_tag values ); insert into user_tag values (2, 1); insert into user_tag values (3, 1); insert into user_tag values (3, 4); insert into user_tag values (3, 5 );

I want to display the following on the page:

The first is the user list, but all user tags are included.
The final form is similar to the segmentfault homepage list. The question list is displayed and many tags are displayed by the way.

Currently, I have two methods:

1. Select the user first, then loop the user's result set in php, and then select the attribute.
2. Join the table to find the following records and filter the records when they are displayed.

Is there any other solution?

Thank you.

Reply content:

Users have many attributes (TAGS), such as singing, dancing, and playing games. The table structure is as follows:
The mysql statement for creating a table is as follows:

drop table if exists user;create table user(   user_id              int not null auto_increment,   user_name            varchar(20),   primary key (user_id));drop table if exists tags;create table tags(   tag_id               int not null auto_increment,   tag_name             varchar(100),   primary key (tag_id));drop table if exists user_tag;create table user_tag(   user_id              int,   tag_id               int);

The SQL statement for inserting data is as follows:

Insert into user values (1, 'small A'); insert into user values (2, 'small B '); insert into user values (3, 'small C '); insert into tags values (1, 'change'); insert into tags values (2, 'Dancing '); insert into tags values (3, 'house '); insert into tags values (4, 'reading'); insert into tags values (5, 'travel '); insert into user_tag values ); insert into user_tag values (2, 1); insert into user_tag values (3, 1); insert into user_tag values (3, 4); insert into user_tag values (3, 5 );

I want to display the following on the page:

The first is the user list, but all user tags are included.
The final form is similar to the segmentfault homepage list. The question list is displayed and many tags are displayed by the way.

Currently, I have two methods:

1. Select the user first, then loop the user's result set in php, and then select the attribute.
2. Join the table to find the following records and filter the records when they are displayed.

Is there any other solution?

Thank you.

If the data volume is small, use left join. Many enterprise-level products use this method.
If the data volume is large, hot and cold data is required. Hot data puts uid uname tag_name in the cache, and cold data uses multiple simple query methods.

One of the two methods you mentioned is better, and many websites do the same.
But one thing is thatuser_tagThe table needs to be cached at a layer, so that all the user-tag queries are carried out in the memory.

If you don't need to lookup users by tag, I will simply put all of them into one table. managing user_tag will be a pain later on. it will also be easier if you have a max on the number of tags a user can have...

This is a many-to-many relationship ??