How to create tables in mysql to store posts and their categories?

Question

MySQL

Please tell a newbie how to store information about posts and their categories in the database? There is a table of categories

category
------------------------------
id  name
1   кат1
2  кат2
3   кат3
4   кат4

and post table

post
-----------------------------
id  name        category
1   раз           1
2   два           3
3   три           4

If such a structure, when one post belongs to one category, then it’s clear how to do it, and if a post can belong to two or more categories, then how to store them in a table and what tables need to be created correctly and how to make a selection of posts of the desired category?

Answer 1

Table post_category(post_id, category_id), whole record is primary key, post_id is foreign key on post.id, category_id is foreign key on category.id. The presence of a record (62, 8) in such a table means that post 62 is assigned category 8. To pull out all categories of a post - do select category_id from post_category where post_id = , all posts in the category - select post_id from post_category where category_id = .