alter table identity_userpassword add column old_user_name varchar(50); update identity_userpassword set old_user_name = user_name; alter table identity_user add column idnum varchar(50) not null, add constraint unique_identity_user_idnum unique (idnum); DROP INDEX `unique_identity_user_telephone`, MODIFY COLUMN `telephone` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL AFTER `name`; ALTER TABLE `tenant_users` DROP INDEX `unique_tenant_user_telephone`; INSERT INTO `tenant_user_credentials` (`id`, `created_time`, `address`, `credential_type`, `front_photo`, `number`, `reverse_photo`, `valid_date_from`, `valid_date_to`, `tenant_user_id`, `head_photo`, `name`, `selected`) select CONCAT(replace(replace(`id`,'--__qFCcCnozYNwzoG3O6Ms',''),'--__XQfjpd9pI9QLFaMYoYm',''),FLOOR(RAND() * 1000)), `created_time`, null, '0', null, telephone, null, null, null, id, null, `name`, '1' from tenant_users t1 where selected_credential_id is null and telephone != 'null' and not EXISTS( select 1 from tenant_user_credentials t2 where t1.telephone= t2.number); update t_user t1,tenant_employees t2,tenant_users t3,tenant_user_credentials t4 set t1.LOGINNO = t4.number,t1.name = t4.name,t3.name = t4.name where t1.id = t2.id and t2.tenant_user_id = t3.id and t3.selected_credential_id = t4.id; DROP TABLE IF EXISTS tmp_identity_user_id; create table tmp_identity_user_id as select * from ( select t3.id from identity_userpassword t1,identity_principals t2,identity_user t3 where t1.id = t2.id and t2.identity_user_id = t3.id group by t3.id HAVING count(1)>1 ) as table_sql; update tmp_identity_user_id tt,identity_user t0,identity_userpassword t1,identity_principals t2 set t1.user_name = CONCAT(t1.user_name,"_del") where tt.id = t0.id and t2.identity_user_id = t0.id and t1.id = t2.id and t1.user_name = t0.telephone; DROP TABLE IF EXISTS tmp_identity_userpassword_number; create table tmp_identity_userpassword_number as select * from ( select DISTINCT t2.number from identity_userpassword t1,tenant_user_credentials t2,identity_principals t3 where t1.user_name = t2.number and t1.id = t3.id ) as sql_table; ALTER TABLE `tmp_identity_userpassword_number` ADD INDEX `index_number` (`number`) ; DROP TABLE IF EXISTS tmp_identity_userpassword_identity; create table tmp_identity_userpassword_identity as select * from ( select DISTINCT t3.identity_user_id from identity_userpassword t1,tenant_user_credentials t2,identity_principals t3 where t1.user_name = t2.number and t1.id = t3.id ) as sql_table; ALTER TABLE `tmp_identity_userpassword_identity` ADD INDEX `index_identity_user_id` (`identity_user_id`) ; ALTER TABLE `identity_user` ADD INDEX `index_idnum` (`idnum`) ; update identity_user t1,tmp_identity_userpassword_identity t2 set t1.is_exist = 1 where t1.id = t2.identity_user_id; update identity_user t1,tmp_identity_userpassword_number t2 set t1.is_exist = 1 where t1.idnum = t2.number; DROP TABLE IF EXISTS tmp_identity_user_exist; create table tmp_identity_user_exist as select * from ( select t1.idnum from identity_user t1,identity_principals t2,identity_userpassword t3 where t1.is_exist is null and t1.id = t2.identity_user_id and t2.id = t3.id group by t1.idnum having count(1)>1 )as sql_table; update identity_user t1,identity_principals t2,identity_userpassword t3 set t3.user_name = t1.idnum where t1.is_exist is null and t1.id = t2.identity_user_id and t2.id = t3.id and t1.idnum not in (select idnum from tmp_identity_user_exist); DROP TABLE IF EXISTS tmp_identity_userpassword_del; create table tmp_identity_userpassword_del as select * from ( select * from identity_userpassword where user_name like '%del' ) as sql_table; ALTER TABLE `tmp_identity_userpassword_del` ADD UNIQUE INDEX `index_id` (`id`) ; delete from identity_userpassword where user_name like '%del'; delete from identity_principals where id in (select id from tmp_identity_userpassword_del); update tenant_users t1,tenant_user_credentials t2 set t2.number = t1.telephone where t1.selected_credential_id = t2.id and t2.number is null and t2.credential_type=0 and t1.telephone !='null';