如何使用mysql开发简易客服系统_mysql客服系统数据库设计

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('customer', 'agent', 'admin') DEFAULT 'customer',
  email VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE conversations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  agent_id INT, -- 可为空，表示尚未分配
  status ENUM('open', 'closed', 'pending') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES users(id),
  FOREIGN KEY (agent_id) REFERENCES users(id)
);

CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT NOT NULL,
  sender_id INT NOT NULL,
  content TEXT NOT NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  FOREIGN KEY (sender_id) REFERENCES users(id)
);

CREATE TABLE agent_status (
  agent_id INT PRIMARY KEY,
  status ENUM('online', 'offline', 'busy') DEFAULT 'offline',
  last_seen DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO conversations (customer_id) VALUES (123);

INSERT INTO messages (conversation_id, sender_id, content) 
VALUES (456, 123, '您好，我想咨询产品问题');

SELECT c.id, u.username, c.created_at 
FROM conversations c 
JOIN users u ON c.customer_id = u.id 
WHERE c.status = 'open' AND c.agent_id IS NULL;

UPDATE conversations SET agent_id = 789, status = 'pending' 
WHERE id = 456 AND agent_id IS NULL;

UPDATE messages SET is_read = 1 
WHERE conversation_id = 456 AND sender_id != 789;