75 lines
2.8 KiB
SQL
75 lines
2.8 KiB
SQL
-- ----------------------------
|
||
-- OA 用户 ↔ OpenIM 用户 绑定表
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `sys_user_im_bind`;
|
||
CREATE TABLE `sys_user_im_bind` (
|
||
`user_id` bigint(20) NOT NULL COMMENT 'OA 用户ID (sys_user.user_id)',
|
||
`phone` varchar(20) NOT NULL COMMENT '手机号（绑定时记录的）',
|
||
`im_user_id` varchar(64) NOT NULL COMMENT 'OpenIM userID（chat 生成的字符串）',
|
||
`bind_status` tinyint(1) DEFAULT 1 COMMENT '1=正常 0=禁用',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`user_id`) USING BTREE,
|
||
KEY `idx_phone` (`phone`),
|
||
KEY `idx_im_user_id` (`im_user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OA-OpenIM 用户绑定';
|
||
|
||
-- ----------------------------
|
||
-- 一次性回填：从 IM 侧 mongo 导出的 phone→userID 映射表
|
||
-- 与 sys_user.phonenumber 关联，命中即绑定
|
||
-- ----------------------------
|
||
DROP TEMPORARY TABLE IF EXISTS `tmp_im_phone_map`;
|
||
CREATE TEMPORARY TABLE `tmp_im_phone_map` (
|
||
`phone` varchar(20) NOT NULL,
|
||
`im_user_id` varchar(64) NOT NULL,
|
||
PRIMARY KEY (`phone`)
|
||
);
|
||
|
||
INSERT INTO `tmp_im_phone_map` (`phone`, `im_user_id`) VALUES
|
||
('15690169553','5546292033'),
|
||
('19932047759','2756281826'),
|
||
('15075462410','2553281144'),
|
||
('18915343363','5026155910'),
|
||
('18324818443','2877251793'),
|
||
('15383265119','8330802909'),
|
||
('18940843511','4349480400'),
|
||
('17714433303','2999550914'),
|
||
('13390839803','2181055021'),
|
||
('18803235354','7658449575'),
|
||
('13700303346','1663065057'),
|
||
('17783455912','2152870750'),
|
||
('17829506825','3477330474'),
|
||
('15591903178','8576556261'),
|
||
('15109286032','8251618343'),
|
||
('15615500565','1050079788'),
|
||
('13630266330','5347384098'),
|
||
('15956900268','2642329050'),
|
||
('18728390370','7870875282'),
|
||
('18976589764','5165937678'),
|
||
('15092755532','4952160908'),
|
||
('15380239551','2739756387'),
|
||
('18354752253','3394331602'),
|
||
('17692304283','4265596684'),
|
||
('13572086005','3585749899'),
|
||
('15081674433','1492306574'),
|
||
('13861602746','8736597942'),
|
||
('19711921991','3259846354'),
|
||
('15330091963','1537622221');
|
||
|
||
INSERT INTO `sys_user_im_bind` (`user_id`, `phone`, `im_user_id`)
|
||
SELECT u.user_id, u.phonenumber, m.im_user_id
|
||
FROM `sys_user` u
|
||
JOIN `tmp_im_phone_map` m ON m.phone = u.phonenumber
|
||
WHERE u.del_flag = '0'
|
||
ON DUPLICATE KEY UPDATE
|
||
`phone` = VALUES(`phone`),
|
||
`im_user_id` = VALUES(`im_user_id`);
|
||
|
||
-- 校验结果
|
||
SELECT COUNT(*) AS bound_count FROM `sys_user_im_bind`;
|
||
SELECT u.user_id, u.user_name, u.phonenumber, b.im_user_id
|
||
FROM `sys_user` u
|
||
LEFT JOIN `sys_user_im_bind` b ON b.user_id = u.user_id
|
||
WHERE u.del_flag = '0' AND u.phonenumber IS NOT NULL AND u.phonenumber <> ''
|
||
ORDER BY b.im_user_id IS NULL DESC;
|