数据结构

12126

表总览

通用表

• _user
• _user_session
• _role
• _page
• _resource
• _group
• _user_group_role
• _user_group_role_page
• _user_group_role_resource
• _constant
• _constant_ui
• _record
• _record_history
• _cache
• _file
• _view01_user

业务表

_ui

• ui 施工方案

CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255)  DEFAULT NULL COMMENT 'ui 动作类型，如：fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  `uiActionHook` text  COMMENT '[{"service": "xx", "serviceFunction": "xxx"}]',
  `uiActionConfig` text  COMMENT 'ui 动作数据',
  `appDataSchema` text  COMMENT 'ui 校验数据',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 433 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'ui 施工方案'

duoxing_chat_session

• 聊天会话

CREATE TABLE `duoxing_chat_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT '用户id',
  `type` varchar(255) DEFAULT NULL COMMENT '类型',
  `chatId` varchar(255) DEFAULT NULL COMMENT '聊天ID',
  `lastMessageHistoryId` int(11) DEFAULT NULL COMMENT '最近一条历史消息id',
  `topChatOrder` varchar(255) NOT NULL DEFAULT '' COMMENT '置顶顺序',
  `muted` int(11) DEFAULT '0' COMMENT '免打扰',
  `unreadCount` int(11) NOT NULL DEFAULT '0' COMMENT '未读数',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `_chat_session_chatId_type_userId_index` (`chatId`, `type`, `userId`) USING BTREE,
  KEY `_userId_topChatOrder_lastMessageHistoryId_index` (`userId`, `topChatOrder`, `lastMessageHistoryId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1139 DEFAULT CHARSET = utf8mb4 COMMENT = '聊天会话'

duoxing_message_history

• 消息历史表;

CREATE TABLE `duoxing_message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `messageFingerprint` varchar(255) DEFAULT NULL COMMENT '消息指纹, 多个消息id对应一个指纹',
  `fromUserId` varchar(255) DEFAULT NULL COMMENT '消息发送方userId',
  `toUserId` varchar(255) DEFAULT NULL COMMENT '接收方userId',
  `toRoomId` varchar(255) DEFAULT NULL COMMENT '接收方groupId',
  `messageType` varchar(255) DEFAULT NULL COMMENT '消息类型; user: 用户聊天, group:群组聊天',
  `noticeType` varchar(255) DEFAULT NULL COMMENT '通知类型; E.g delGroupUser,addGroupUser ',
  `messageContent` text COMMENT '消息内容',
  `messageContentType` varchar(255) DEFAULT NULL COMMENT '消息内容类型;  text,atText,image,file,notice,audio,video,userCard,revoke',
  `messageTimeString` varchar(255) DEFAULT NULL COMMENT '发送消息的时间; 格式: 2021-11-16 14:45:34',
  `messageStatus` varchar(255) DEFAULT 'active' COMMENT '消息状态 active revoke',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `messageFingerprint_unique_index` (`messageFingerprint`) USING BTREE,
  KEY `messageType_index` (`messageType`) USING BTREE,
  KEY `fromUser_index` (`fromUserId`) USING BTREE,
  KEY `toUserId_index` (`toUserId`) USING BTREE,
  KEY `toGroupId_index` (`toRoomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2123 DEFAULT CHARSET = utf8mb4 COMMENT = '消息历史表;'

duoxing_user_friend

• 好友表;

CREATE TABLE `duoxing_user_friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT '用户id;',
  `friendId` varchar(255) DEFAULT NULL COMMENT '好友id',
  `friendStatus` varchar(255) DEFAULT NULL COMMENT '请求加好友状态; isFriend, notFriend, waitingApprove, rejectApply',
  `requestTime` varchar(255) DEFAULT NULL COMMENT '请求时间; ''2022-03-05T14:23:59.077+08:00''',
  `requestRemark` varchar(255) DEFAULT NULL COMMENT '备注',
  `responseTime` varchar(255) DEFAULT NULL COMMENT '响应时间; ''2022-03-05T14:23:59.077+08:00''',
  `rejectTimes` varchar(255) DEFAULT NULL COMMENT '拒绝次数',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE,
  KEY `friend_index` (`friendId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '好友表;'

room

• 群组表;

CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` varchar(255) NOT NULL COMMENT 'roomId',
  `roomName` varchar(255) DEFAULT NULL COMMENT '群组名',
  `roomType` varchar(255) DEFAULT NULL COMMENT '群组类型; system, cgg,course',
  `roomDesc` varchar(255) DEFAULT NULL COMMENT '群组描述',
  `roomAvatar` varchar(255) DEFAULT NULL COMMENT '群logo',
  `roomExtend` text COMMENT '拓展字段; { groupNotice: ''xx'' }',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `groupId_index` (`roomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 242 DEFAULT CHARSET = utf8mb4 COMMENT = '群组表;'

user_room_role

• 用户群组角色关联表;

CREATE TABLE `user_room_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) NOT NULL COMMENT '用户id',
  `roomId` varchar(255) NOT NULL COMMENT '群组Id',
  `roleId` varchar(255) DEFAULT NULL COMMENT '角色Id',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `groupId_index` (`roomId`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3550 DEFAULT CHARSET = utf8mb4 COMMENT = '用户群组角色关联表;'

visitor

CREATE TABLE `visitor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT '游客名',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT '访客来源',
  `agentId` varchar(255) DEFAULT NULL COMMENT '接待的客服id',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT '性别; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT '上次登录时间',
  `lastIp` varchar(255) DEFAULT '' COMMENT '上次登录 ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT '上次登录 ip 位置',
  `browser` varchar(255) DEFAULT '' COMMENT '浏览器信息',
  `fromUrl` varchar(255) DEFAULT '' COMMENT '页面url',
  `fullName` varchar(255) DEFAULT '' COMMENT '全名',
  `address` varchar(128) DEFAULT '' COMMENT '地址',
  `phone` varchar(128) DEFAULT '' COMMENT '手机号',
  `email` varchar(128) DEFAULT '' COMMENT 'email',
  `wechatId` varchar(255) DEFAULT NULL,
  `remark` text COMMENT '备注',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_agentId_index` (`agentId`) USING BTREE,
  KEY `visitor_lastLoginTime_index` (`lastLoginTime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 77 DEFAULT CHARSET = utf8mb4

visitor_history

CREATE TABLE `visitor_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT '游客名',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT '访客来源',
  `agentId` varchar(255) DEFAULT NULL COMMENT '接待的客服id',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT '性别; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT '上次登录时间',
  `lastIp` varchar(255) DEFAULT '' COMMENT '上次登录 ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT '上次登录 ip 位置',
  `lastReferer` varchar(1000) DEFAULT '' COMMENT '上次打开的页面',
  `browser` varchar(255) DEFAULT '' COMMENT '浏览器信息',
  `fromUrl` varchar(255) DEFAULT '' COMMENT '页面url',
  `fullName` varchar(255) DEFAULT '' COMMENT '全名',
  `address` varchar(128) DEFAULT '' COMMENT '地址',
  `phone` varchar(128) DEFAULT '' COMMENT '手机号',
  `email` varchar(255) DEFAULT '' COMMENT 'email',
  `remark` text COMMENT '备注',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  `wechatId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_history_agentId_index` (`agentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 487 DEFAULT CHARSET = utf8mb4

visitor_ticket

CREATE TABLE `visitor_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticketStatus` varchar(255) NOT NULL DEFAULT 'unread' COMMENT '留言状态：待处理、已处理',
  `visitorId` varchar(255) NOT NULL DEFAULT '0' COMMENT '游客 id',
  `ticketFullName` varchar(255) NOT NULL DEFAULT '' COMMENT '全名',
  `ticketIp` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip',
  `ticketIpZone` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip 区域',
  `ticketPhone` varchar(255) NOT NULL DEFAULT '' COMMENT '手机',
  `ticketEmail` varchar(255) NOT NULL DEFAULT '' COMMENT 'email',
  `ticketWechatId` varchar(255) DEFAULT NULL COMMENT '微信id',
  `ticketContent` text COMMENT '留言内容',
  `ticketCreateTime` varchar(255) NOT NULL DEFAULT '0' COMMENT '订单创建时间',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4

web_constant_ui

• 常量表;

CREATE TABLE `web_constant_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constantKey` varchar(255) DEFAULT NULL,
  `constantType` varchar(255) DEFAULT NULL COMMENT '常量类型; object, array',
  `pageId` varchar(255) DEFAULT 'all' COMMENT '页面id',
  `desc` varchar(255) DEFAULT NULL COMMENT '描述',
  `en` text COMMENT '常量内容; object, array',
  `zh` text COMMENT '常量内容; object, array',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `pageId_constantKey_unique` (`constantKey`, `pageId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 DEFAULT CHARSET = utf8mb4 COMMENT = '常量表;'

业务视图

_view01_user_room_role

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view01_user_room_role` AS
select
  `jianghu_feige`.`user_room_role`.`id` AS `id`,
  `jianghu_feige`.`user_room_role`.`userId` AS `userId`,
  `jianghu_feige`.`user_room_role`.`roomId` AS `roomId`,
  `jianghu_feige`.`user_room_role`.`roleId` AS `roleId`,
  `_view01_user`.`username` AS `username`,
  'default' AS `userAvatar`,
  `_role`.`roleName` AS `roleName`,
  `jianghu_feige`.`room`.`roomName` AS `roomName`,
  `jianghu_feige`.`room`.`roomAvatar` AS `roomAvatar`,
  `jianghu_feige`.`user_room_role`.`operation` AS `operation`,
  `jianghu_feige`.`user_room_role`.`operationByUserId` AS `operationByUserId`,
  `jianghu_feige`.`user_room_role`.`operationByUser` AS `operationByUser`,
  `jianghu_feige`.`user_room_role`.`operationAt` AS `operationAt`,
  `jianghu_feige`.`room`.`roomExtend` AS `roomExtend`,
  '普通' AS `userType`
from
  (
    (
      (
        `jianghu_feige`.`user_room_role`
        join `jianghu_feige`.`_view01_user` on (
          (
            `jianghu_feige`.`user_room_role`.`userId` = `_view01_user`.`userId`
          )
        )
      )
      join `jianghu_feige`.`room` on (
        (
          `jianghu_feige`.`user_room_role`.`roomId` = `jianghu_feige`.`room`.`roomId`
        )
      )
    )
    join `jianghu_feige`.`_role` on (
      (
        `jianghu_feige`.`user_room_role`.`roleId` = `_role`.`roleId`
      )
    )
  )

_view02_user

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view02_user` AS
select
  `_user`.`id` AS `id`,
  `_user`.`userId` AS `userId`,
  `_user`.`username` AS `username`,
  `_user`.`userAvatar` AS `userAvatar`,
  `_user`.`contactNumber` AS `contactNumber`,
  `_user`.`gender` AS `gender`,
  `_user`.`birthday` AS `birthday`,
  `_user`.`signature` AS `signature`,
  `_user`.`email` AS `email`,
  `_user`.`password` AS `password`,
  `_user`.`md5Salt` AS `md5Salt`,
  `_user`.`userType` AS `userType`,
  `_user`.`userStatus` AS `userStatus`,
  `_user`.`config` AS `config`
from
  `_user`

_view02_user_app

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `_view02_user_app` AS
select
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`id` AS `id`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`userId` AS `userId`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` AS `appId`
from
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`
where
  (
    `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` = 'feige'
  )

view01_duoxing_chat_session

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_chat_session` AS
select
  `duoxing_chat_session`.`id` AS `id`,
  `duoxing_chat_session`.`userId` AS `userId`,
  `duoxing_chat_session`.`type` AS `type`,
  `duoxing_chat_session`.`chatId` AS `chatId`,
  `duoxing_chat_session`.`lastMessageHistoryId` AS `lastMessageHistoryId`,
  `duoxing_chat_session`.`topChatOrder` AS `topChatOrder`,
  `duoxing_chat_session`.`muted` AS `muted`,
  `duoxing_chat_session`.`unreadCount` AS `unreadCount`,
  `room`.`roomName` AS `chatRoomName`,
  `room`.`roomAvatar` AS `chatRoomAvatar`,
  `room`.`roomExtend` AS `chatRoomExtend`,
  `view01_visitor_room_info`.`lastLoginTime` AS `lastLoginTime`,
  `view01_visitor_room_info`.`visitorId` AS `visitorId`,
  `view01_visitor_room_info`.`visitorSource` AS `visitorSource`,
  `view01_visitor_room_info`.`agentId` AS `agentId`,
  `view01_visitor_room_info`.`agentName` AS `agentName`
from
  (
    (
      `duoxing_chat_session`
      left join `room` on (
        (
          (`duoxing_chat_session`.`chatId` = `room`.`roomId`)
          and (`duoxing_chat_session`.`type` = 'room')
        )
      )
    )
    join `view01_visitor_room_info` on (
      (
        `duoxing_chat_session`.`chatId` = `view01_visitor_room_info`.`roomId`
      )
    )
  )

view01_duoxing_message_history

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_message_history` AS
select
  `dmh`.`id` AS `id`,
  `dmh`.`messageType` AS `messageType`,
  `dmh`.`fromUserId` AS `fromUserId`,
  `fromuser`.`username` AS `fromUsername`,
  '普通' AS `fromUserType`,
  'default' AS `fromUserAvatar`,
  `dmh`.`toUserId` AS `toUserId`,
  `touser`.`username` AS `toUsername`,
  'default' AS `toUserAvatar`,
  `dmh`.`toRoomId` AS `toRoomId`,
  `dmh`.`messageContent` AS `messageContent`,
  `dmh`.`messageFingerprint` AS `messageFingerprint`,
  `dmh`.`messageTimeString` AS `messageTimeString`,
  `dmh`.`messageContentType` AS `messageContentType`,
  `dmh`.`noticeType` AS `noticeType`,
  `dmh`.`messageStatus` AS `messageStatus`
from
  (
    (
      `jianghu_feige`.`duoxing_message_history` `dmh`
      left join `jianghu_feige`.`_view01_user` `fromuser` on ((`fromuser`.`userId` = `dmh`.`fromUserId`))
    )
    left join `jianghu_feige`.`_view01_user` `touser` on ((`touser`.`userId` = `dmh`.`toUserId`))
  )

view01_visitor_group_info

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_group_info` AS
select
  `jianghu_feige`.`visitor`.`visitorName` AS `username`,
  `jianghu_feige`.`visitor`.`visitorId` AS `visitorId`,
  `_user_group_role`.`userId` AS `userId`,
  `jianghu_feige`.`visitor`.`visitorSource` AS `visitorSource`,
  `jianghu_feige`.`visitor`.`agentId` AS `agentId`,
  `jianghu_feige`.`_user`.`username` AS `agentName`,
  `jianghu_feige`.`visitor`.`visitorAvatar` AS `userAvatar`,
  `jianghu_feige`.`visitor`.`visitorGender` AS `visitorGender`,
  `jianghu_feige`.`visitor`.`lastLoginTime` AS `lastLoginTime`,
  `jianghu_feige`.`visitor`.`lastIp` AS `lastIp`,
  `jianghu_feige`.`visitor`.`ipZone` AS `ipZone`,
  `jianghu_feige`.`visitor`.`browser` AS `browser`,
  `jianghu_feige`.`visitor`.`fromUrl` AS `fromUrl`,
  `jianghu_feige`.`visitor`.`fullName` AS `fullName`,
  `jianghu_feige`.`visitor`.`address` AS `address`,
  `jianghu_feige`.`visitor`.`phone` AS `phone`,
  `jianghu_feige`.`visitor`.`email` AS `email`,
  `_group`.`groupId` AS `groupId`,
  `_group`.`groupName` AS `groupName`
from
  (
    (
      (
        `jianghu_feige`.`_user_group_role`
        join `jianghu_feige`.`visitor` on (
          (
            `_user_group_role`.`userId` = convert(
              `jianghu_feige`.`visitor`.`visitorId` using utf8mb4
            )
          )
        )
      )
      join `jianghu_feige`.`_group` on (
        (`_user_group_role`.`groupId` = `_group`.`groupId`)
      )
    )
    left join `jianghu_feige`.`_user` on (
      (
        `jianghu_feige`.`visitor`.`agentId` = `jianghu_feige`.`_user`.`userId`
      )
    )
  )

view01_visitor_room_info

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_room_info` AS
select
  `visitor`.`visitorName` AS `username`,
  `visitor`.`visitorId` AS `visitorId`,
  `user_room_role`.`userId` AS `userId`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `_user`.`username` AS `agentName`,
  `visitor`.`visitorAvatar` AS `userAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`wechatId` AS `wechatId`,
  `visitor`.`remark` AS `remark`,
  `room`.`roomId` AS `roomId`,
  `room`.`roomName` AS `roomName`
from
  (
    (
      (
        `user_room_role`
        join `visitor` on (
          (
            `user_room_role`.`userId` = convert(`visitor`.`visitorId` using utf8mb4)
          )
        )
      )
      join `room` on ((`user_room_role`.`roomId` = `room`.`roomId`))
    )
    left join `_user` on ((`visitor`.`agentId` = `_user`.`userId`))
  )

view01_visitor_ticket

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_ticket` AS
select
  `visitor`.`visitorName` AS `visitorName`,
  `visitor_ticket`.`visitorId` AS `visitorId`,
  `visitor_ticket`.`ticketStatus` AS `ticketStatus`,
  `visitor_ticket`.`id` AS `id`,
  `visitor_ticket`.`ticketFullName` AS `ticketFullName`,
  `visitor_ticket`.`ticketIp` AS `ticketIp`,
  `visitor_ticket`.`ticketIpZone` AS `ticketIpZone`,
  `visitor_ticket`.`ticketPhone` AS `ticketPhone`,
  `visitor_ticket`.`ticketEmail` AS `ticketEmail`,
  `visitor_ticket`.`ticketWechatId` AS `ticketWechatId`,
  `visitor_ticket`.`ticketContent` AS `ticketContent`,
  `visitor_ticket`.`ticketCreateTime` AS `ticketCreateTime`,
  `visitor_ticket`.`operation` AS `operation`,
  `visitor_ticket`.`operationByUserId` AS `operationByUserId`,
  `visitor_ticket`.`operationByUser` AS `operationByUser`,
  `visitor_ticket`.`operationAt` AS `operationAt`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `visitor`.`visitorAvatar` AS `visitorAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`remark` AS `remark`
from
  (
    `visitor`
    join `visitor_ticket` on (
      (
        `visitor`.`visitorId` = `visitor_ticket`.`visitorId`
      )
    )
  )