Бесплатная консультация юриста

+7 (800) 301-07-30

Реклама jurik.ru

template

(posts)

(comments)

| | | ||———|——|——|
| id | INT (PK) | ， |
| post_id | INT (FK) | ，posts.id |
| user_id | INT (FK) | ，users.id |
| content | TEXT | |
| created_at | TIMESTAMP | |

(likes)

—
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

—

CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
VARCHAR(255) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
—
CREATE TABLE comments (
id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT NOT NULL,
user_id INT NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

— CREATE TABLE likes (
id INT AUTO_INCREMENT PRIMARY KEY,
post_id INT NOT NULL,
user_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY unique_like (post_id, user_id) —
);

| | | |
|——|——|——|
| : | | |
| : | | |
| : | | |
| : | | likes |

2.

— *：，/
— ：`likes«(post_id, user_id)`，
— ：`username`, `email`, «

3.

— （MySQL）
— `posts.user_id`、`comments.post_id`

—
SELECT FROM posts WHERE user_id = 1;
— （）
SELECT c., u.username
FROM comments c
JOIN users u ON c.user_id = u.id
WHERE c.post_id = 1;

—
SELECT COUNT() FROM likes WHERE post_id = 1;

—
SELECT * FROM likes WHERE post_id = 1 AND user_id = 2;