webchat-user.sql 5.5 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788
  1. -- 用户信息主表
  2. CREATE TABLE webchat_user.`web_chat_user` (
  3. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  4. `USER_ID` char(60) NOT NULL COMMENT '用户ID',
  5. `USER_NAME` char(30) NOT NULL COMMENT '用户名',
  6. `PHOTO` varchar(400) NOT NULL COMMENT '用户头像',
  7. `MOBILE` char(20) NOT NULL COMMENT '手机号',
  8. `PASSWORD` char(100) NOT NULL COMMENT '密码',
  9. `SIGNATURE` varchar(500) DEFAULT '暂无签名' COMMENT '签名',
  10. `STATUS` INT NOT NULL DEFAULT 1 COMMENT '用户状态状态',
  11. `ROLE_CODE` INT NOT NULL DEFAULT 1 COMMENT '角色',
  12. `CREATE_BY` char(100) DEFAULT NULL COMMENT '创建人',
  13. `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  14. `UPDATE_BY` char(100) DEFAULT NULL COMMENT '更新人',
  15. `UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
  16. `VERSION` int DEFAULT '0' COMMENT '版本',
  17. PRIMARY KEY (`ID`),
  18. KEY `INDEX_USER_ID` (`USER_ID`),
  19. KEY `INDEX_MOBILE_PASSWORD` (`MOBILE`, `PASSWORD`),
  20. KEY `INDEX_STATUS` (`STATUS`)
  21. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息主表';
  22. -- 初始化默认管理员账号
  23. INSERT INTO `webchat_user`.`web_chat_user` (`ID`, `USER_ID`, `USER_NAME`, `PHOTO`, `MOBILE`, `PASSWORD`, `STATUS`, `ROLE_CODE`,
  24. `CREATE_BY`, `CREATE_DATE`, `UPDATE_BY`, `UPDATE_DATE`, `VERSION`) VALUES
  25. (1, 'U_770cce9f632543588b4e8aa6ec43e6a2', '管理员', 'https://coderutil.oss-cn-beijing.aliyuncs.com/bbs-image/file_dd489633f1bb4513a9db81be6e9d692f.png',
  26. 'admin', '06525f4969c6cf1886ee0db86bef82df', 1, 2, 'U_770cce9f632543588b4e8aa6ec43e6a2',
  27. '2022-03-12 05:55:26', NULL, '2022-03-22 10:28:38', 1);
  28. -- 初始化我的AI助手
  29. INSERT INTO `webchat_user`.`web_chat_user` (`USER_ID`, `USER_NAME`, `PHOTO`, `MOBILE`, `PASSWORD`, `STATUS`, `ROLE_CODE`,
  30. `CREATE_BY`, `CREATE_DATE`, `UPDATE_BY`, `UPDATE_DATE`, `VERSION`) VALUES
  31. ('AI_ef352f698ad7b60c1d8a3aaa4a948030', '我的Ai助手', 'https://coderutil.oss-cn-beijing.aliyuncs.com/bbs-image/file_5a09444d0eb4431abfb01f4efb3afddc.png',
  32. 'aiagent', 'AI_ef352f698ad7b60c1d8a3aaa4a948030', 1, 0, 'AI_ef352f698ad7b60c1d8a3aaa4a948030',
  33. '2022-03-12 05:55:26', NULL, '2022-03-22 10:28:38', 1);
  34. -- 初始化webchat支付服务号
  35. INSERT INTO `webchat_user`.`web_chat_user` (`USER_ID`, `USER_NAME`, `PHOTO`, `MOBILE`, `PASSWORD`, `STATUS`, `ROLE_CODE`,
  36. `CREATE_BY`, `CREATE_DATE`, `UPDATE_BY`, `UPDATE_DATE`, `VERSION`) VALUES
  37. ('S_51a7fbf50155c4b08c55fbbcfc5911db', 'WebChat支付', 'https://coderutil.oss-cn-beijing.aliyuncs.com/bbs-image/file_bf000793461b49ffa5ffe224611450ee.png',
  38. 'webchat_pay', 'S_51a7fbf50155c4b08c55fbbcfc5911db', 1, 7, 'S_51a7fbf50155c4b08c55fbbcfc5911db',
  39. '2022-03-12 05:55:26', NULL, '2022-03-22 10:28:38', 1);
  40. -- 好友关系表
  41. CREATE TABLE webchat_user.`web_chat_friend` (
  42. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  43. `USER_ID` char(60) NOT NULL COMMENT '用户ID',
  44. `FRIEND_ID` char(60) NOT NULL COMMENT '好友ID',
  45. `STATUS` int(11) DEFAULT '0' COMMENT '好友状态',
  46. `APPLY_DATE` datetime DEFAULT NULL COMMENT '申请时间',
  47. `HANDLE_DATE` datetime DEFAULT NULL COMMENT '处理时间',
  48. `VERSION` int DEFAULT '0' COMMENT '版本',
  49. PRIMARY KEY (`ID`),
  50. KEY `INDEX_USER_ID` (`USER_ID`),
  51. KEY `INDEX_FRIEND_ID` (`FRIEND_ID`),
  52. KEY `INDEX_STATUS` (`STATUS`)
  53. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='好友关系表';
  54. -- 群组用户表
  55. CREATE TABLE webchat_user.`web_chat_group_user` (
  56. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  57. `GROUP_ID` char(60) NOT NULL COMMENT '群组ID',
  58. `USER_ID` char(60) NOT NULL COMMENT '用户ID',
  59. `STATUS` INT NOT NULL DEFAULT 1 COMMENT '用户状态状态',
  60. `CREATE_BY` char(100) DEFAULT NULL COMMENT '创建人',
  61. `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  62. `UPDATE_BY` char(100) DEFAULT NULL COMMENT '更新人',
  63. `UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
  64. `VERSION` int DEFAULT '0' COMMENT '版本',
  65. PRIMARY KEY (`ID`),
  66. KEY `INDEX_GROUP_ID` (`GROUP_ID`),
  67. KEY `INDEX_USER_ID` (`USER_ID`),
  68. KEY `INDEX_STATUS` (`STATUS`)
  69. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='群组用户表';
  70. -- 账号关系表
  71. CREATE TABLE webchat_user.`web_chat_account_relation` (
  72. `id` bigint NOT NULL AUTO_INCREMENT COMMENT '关系主键',
  73. `source_account` char(64) NOT NULL COMMENT '原账号',
  74. `target_account` char(64) NOT NULL COMMENT '目标账号',
  75. `type` int(4) NOT NULL COMMENT '关系类型',
  76. `status` int(4) NOT NULL COMMENT '关系状态',
  77. `create_date` date NOT NULL COMMENT '创建时间',
  78. `update_date` date NOT NULL COMMENT '更新时间',
  79. PRIMARY KEY (`id`),
  80. UNIQUE KEY `UK_IDX_SOURCE_TARGET_ACCOUNT`(source_account, target_account),
  81. KEY `IDX_STATUS_TYPE`(status, type)
  82. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='账号关系表';