当前位置：首页 > article >正文

2 php8.0 中开发一个websocket 聊天表设计

article 2024/11/16 1:17:30

表设计：

-- 聊天记录表
CREATE TABLE chat (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 聊天记录ID，自增主键
    from_userid INT NOT NULL, -- 发送消息的用户ID
    to_user_id INT NOT NULL, -- 接收消息的用户ID
    from_companyId INT, -- 发送消息的公司ID
    to_companyId INT, -- 接收消息的公司ID
    from_company_name VARCHAR(255), -- 发送消息公司的名称
    to_company_name VARCHAR(255), -- 接收消息公司的名称
    from_role VARCHAR(50) NOT NULL, -- 发送者角色（字符串类型）
    to_role VARCHAR(50) NOT NULL, -- 接收者角色（字符串类型）
    create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    type ENUM('text', 'image', 'file', 'audio', 'video') NOT NULL, -- 消息类型，文本、图片、文件、语音、视频
    text TEXT, -- 消息内容（适用于文本消息）
    withdraw BOOLEAN DEFAULT FALSE, -- 是否撤回消息
    withdraw_at TIMESTAMP NULL, -- 消息撤回时间
    view_flag BOOLEAN DEFAULT FALSE, -- 消息是否已读
    from_user_avatar VARCHAR(255), -- 发送消息用户的头像URL
    to_user_avatar VARCHAR(255), -- 接收消息用户的头像URL
    from_company_avatar VARCHAR(255), -- 发送消息公司头像URL
    to_company_avatar VARCHAR(255) -- 接收消息公司头像URL
);

-- 订单关联表
CREATE TABLE chat_order (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 订单记录ID，自增主键
    order_id VARCHAR(50) NOT NULL, -- 订单编号
    chat_id INT NOT NULL, -- 关联的聊天记录ID
    type ENUM('product', 'service') NOT NULL, -- 订单类型，商品或服务
    price DECIMAL(10, 2) NOT NULL, -- 订单价格，最多10位数，两位小数
    customer_phone VARCHAR(20), -- 客户的联系电话
    status ENUM('pending', 'completed', 'cancelled', 'failed') NOT NULL, -- 订单状态，待处理、已完成、已取消、失败
    create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    FOREIGN KEY (chat_id) REFERENCES chat(id) -- 外键，关联聊天记录表的ID
);

-- 添加索引以优化查询性能
CREATE INDEX idx_from_userid ON chat(from_userid); -- 发送消息用户ID索引
CREATE INDEX idx_to_user_id ON chat(to_user_id); -- 接收消息用户ID索引
CREATE INDEX idx_create_at ON chat(create_at); -- 创建时间索引
CREATE INDEX idx_order_id ON chat_order(order_id); -- 订单编号索引
CREATE INDEX idx_chat_id ON chat_order(chat_id); -- 聊天记录ID索引
CREATE INDEX idx_type ON chat_order(type); -- 订单类型索引
CREATE INDEX idx_status ON chat_order(status); -- 订单状态索引

相关的界面代码：

<!DOCTYPE html>
<html lang="zh">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>WebSocket 聊天室</title>
  <style>
    body {
      font-family: Arial, sans-serif;
      background-color: #f4f4f9;
      margin: 0;
      padding: 0;
    }

    .chat-container {
      width: 50%;
      margin: 50px auto;
      background-color: #fff;
      border: 1px solid #ddd;
      border-radius: 8px;
      box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.1);
    }

    .chat-header {
      background-color: #007bff;
      color: white;
      padding: 15px;
      text-align: center;
      font-size: 18px;
      font-weight: bold;
    }

    .chat-messages {
      height: 400px;
      overflow-y: auto;
      padding: 20px;
      background-color: #f9f9f9;
      border-bottom: 1px solid #ddd;
    }

    .message {
      display: flex;
      margin-bottom: 10px;
      padding: 10px;
      border-radius: 8px;
      max-width: 70%;
    }

    .message.sent {
      background-color: #dcf8c6;
      margin-left: auto;
      text-align: right;
      flex-direction: row-reverse;
    }

    .message.received {
      background-color: #e1f5fe;
    }

    .avatar {
      width: 40px;
      height: 40px;
      border-radius: 50%;
      margin-right: 10px;
    }

    .message-content {
      max-width: 100%;
      word-wrap: break-word;
    }

    .chat-footer {
      padding: 20px;
      display: flex;
      justify-content: space-between;
      align-items: center;
    }

    .chat-footer input[type="text"] {
      width: 70%;
      padding: 10px;
      border: 1px solid #ddd;
      border-radius: 4px;
    }

    .chat-footer button {
      padding: 10px 20px;
      background-color: #007bff;
      color: white;
      border: none;
      border-radius: 4px;
      cursor: pointer;
    }

    .chat-footer button:hover {
      background-color: #0056b3;
    }

    .file-input {
      display: none;
    }

    .extra-options {
      display: none;
      flex-direction: column;
    }

    .extra-options button {
      margin-top: 5px;
      background-color: #17a2b8;
    }
  </style>
</head>
<body>

<div class="chat-container">
  <div class="chat-header">聊天室</div>

  <div class="chat-messages" id="chatMessages"></div>

  <div class="chat-footer">
    <div>
      <button id="attachBtn">📎</button>
      <div class="extra-options" id="extraOptions">
        <button id="uploadImageBtn">发送图片</button>
        <button id="uploadFileBtn">发送文件</button>
        <button id="sendAudioBtn">发送语音</button>
        <button id="sendVideoBtn">发送视频</button>
      </div>
      <input type="file" id="imageInput" class="file-input" accept="image/*">
      <input type="file" id="fileInput" class="file-input" accept="*/*">
    </div>
    <input type="text" id="messageInput" placeholder="输入消息...">
    <button id="sendMessageBtn">发送</button>
  </div>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
  $(document).ready(function() {
    let ws = new WebSocket("ws://localhost:3000");

    let userName = prompt("请输入您的用户名:");
    let groupName = "defaultGroup";
    let userAvatar = "https://i.pravatar.cc/40"; // 使用随机头像

    ws.onopen = function() {
      // 加入群组
      ws.send(JSON.stringify({
        action: "joinGroup",
        groupName: groupName,
        userName: userName
      }));
    };

    ws.onmessage = function(event) {
      let data = JSON.parse(event.data);

      // 处理接收到的消息
      if (data.action === "sendMessage") {
        appendMessage(data.user_name, data.message, data.user_avatar, 'received');
      }

      if (data.type === "join" || data.type === "left") {
        appendSystemMessage(data.content);
      }
    };

    $('#sendMessageBtn').click(function() {
      let messageText = $('#messageInput').val().trim();
      if (messageText !== '') {
        let messageData = {
          action: "sendMessage",
          group_name: groupName,
          user_name: userName,
          message: messageText,
          user_avatar: userAvatar
        };

        ws.send(JSON.stringify(messageData));
        appendMessage(userName, messageText, userAvatar, 'sent');
        $('#messageInput').val('');
      }
    });

    // Toggle extra options for attachments
    $('#attachBtn').click(function() {
      $('#extraOptions').toggle();
    });

    // Handle image upload
    $('#uploadImageBtn').click(function() {
      $('#imageInput').click();
    });

    $('#imageInput').change(function() {
      let file = this.files[0];
      let reader = new FileReader();
      reader.onload = function(e) {
        ws.send(JSON.stringify({
          action: "sendMessage",
          group_name: groupName,
          user_name: userName,
          message: e.target.result,  // Send base64-encoded image
          user_avatar: userAvatar,
          fileType: 'image'
        }));
        appendMessage(userName, '<img src="' + e.target.result + '" width="100">', userAvatar, 'sent');
      };
      reader.readAsDataURL(file);
    });

    // Handle file upload
    $('#uploadFileBtn').click(function() {
      $('#fileInput').click();
    });

    $('#fileInput').change(function() {
      let file = this.files[0];
      let reader = new FileReader();
      reader.onload = function(e) {
        ws.send(JSON.stringify({
          action: "sendMessage",
          group_name: groupName,
          user_name: userName,
          message: file.name,  // Send file name
          fileData: e.target.result,  // Send base64-encoded file
          user_avatar: userAvatar,
          fileType: 'file'
        }));
        appendMessage(userName, '<a href="' + e.target.result + '" download>' + file.name + '</a>', userAvatar, 'sent');
      };
      reader.readAsDataURL(file);
    });

    // Scroll to bottom
    function appendMessage(name, message, avatar, messageType) {
      let messageHtml = `
                <div class="message ${messageType}">
                    <img src="${avatar}" class="avatar">
                    <div class="message-content">
                        <strong>${name}</strong><br>${message}
                    </div>
                </div>
            `;
      $('#chatMessages').append(messageHtml);
      scrollToBottom();
    }

    function appendSystemMessage(content) {
      let systemMessageHtml = `<div class="system-message">${content}</div>`;
      $('#chatMessages').append(systemMessageHtml);
      scrollToBottom();
    }

    function scrollToBottom() {
      $('#chatMessages').scrollTop($('#chatMessages')[0].scrollHeight);
    }

    $('#messageInput').keypress(function(e) {
      if (e.which == 13) {
        $('#sendMessageBtn').click();
      }
    });
  });
</script>

</body>
</html>