mysql如何实现站内信功能

CREATE TABLE user_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender_id INT DEFAULT NULL,
  receiver_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  is_read TINYINT DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  deleted_by_receiver TINYINT DEFAULT 0,
  INDEX idx_receiver (receiver_id),
  INDEX idx_created (created_at)
);

INSERT INTO user_messages (sender_id, receiver_id, title, content) 
VALUES (1001, 1002, '你有一条新消息', '你好，这是一条测试消息');

SELECT COUNT(*) AS unread_count 
FROM user_messages 
WHERE receiver_id = ? AND is_read = 0 AND deleted_by_receiver = 0;

SELECT id, sender_id, title, content, is_read, created_at 
FROM user_messages 
WHERE receiver_id = ? AND deleted_by_receiver = 0 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 0;

UPDATE user_messages 
SET is_read = 1 
WHERE id = ? AND receiver_id = ?;

UPDATE user_messages 
SET is_read = 1 
WHERE receiver_id = ? AND is_read = 0;

UPDATE user_messages 
SET deleted_by_receiver = 1 
WHERE id = ? AND receiver_id = ?;