-- 商户 H5 人员账号 openid（member.type=3），支持同一客户多人员独立登录
|
SET @db = DATABASE();
|
|
SET @sql = IF(
|
(SELECT COUNT(*) FROM information_schema.statistics
|
WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'member' AND INDEX_NAME = 'idx_member_openid') = 0,
|
'ALTER TABLE `member` ADD INDEX `idx_member_openid` (`openid`)',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 将历史 yw_customer.openid 迁移到默认联系人 member.openid
|
UPDATE `member` m
|
INNER JOIN `yw_customer` c ON c.member_id = m.id AND c.isdeleted = 0
|
SET m.openid = c.openid, m.edit_date = NOW()
|
WHERE m.isdeleted = 0
|
AND m.type = 3
|
AND c.openid IS NOT NULL
|
AND c.openid != ''
|
AND (m.openid IS NULL OR m.openid = '');
|
