hust-epi/whepi-doc/creatYeWeiHui.sql

-- 新建临时表,用来记录用户信息,减少步骤
DROP TABLE IF EXISTS tp_user;
CREATE TABLE tp_user  (
  user_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '用户ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (user_id) USING BTREE
) ENGINE = InnoDB default charset=utf8mb4 comment = '用户ID临时表';

-- 在临时表插入user_id,之后直接使用插入的user_id
insert into tp_user
(select ur.user_id,u.uptown_id,aa.linkman,aa.phone,now(),now() from sys_user_role ur
left join sys_uptown_house up on up.house_id = ur.property_id
left join sys_uptown_unit uu on uu.unit_id = up.unit_id
left join sys_uptown u on u.uptown_id = uu.uptown_id
left join (
select ur.user_id,up.linkman,up.phone from sys_user_role ur
left join sys_uptown_home up on up.house_id = ur.property_id
where ur.role_id = 1 and up.phone = '17771809480') aa on aa.user_id = ur.user_id
where ur.role_id = 1 and ur.user_id = (select user_id from sys_user_role where property_id = (select house_id from sys_uptown_home where phone = '17771809480') and role_id = 1))

-- 插入业委会表,根据已有的最大ID加1生成新的ID
insert into sys_owner
select (select owner_id from (
(select ifnull(max(owner_id+0)+1,1) as owner_id from sys_owner)
) as tmp),uptown_id,linkman,phone,user_id,now(),user_id,now() from tp_user order by time_create desc limit 1;


-- 插入权限表(不需要任何修改),根据已有的最大ID加1生成新的ID
insert into sys_user_role
select (select ur_id from (
(select ifnull(max(ur_id+0)+1,1) as ur_id from sys_user_role)
) as tmp),user_id, 2,
(select owner_id from sys_owner where user_create = (select user_id from tp_user order by time_create desc limit 1))
,user_id,now(),user_id,now() from tp_user order by time_create desc limit 1;


select count(1) FROM sys_user_role sur
inner join sys_uptown_house suh on suh.house_id=sur.property_id
inner join sys_uptown_unit suu on suu.unit_id=suh.unit_id and suu.uptown_id = 1238790987234


select count(1) FROM sys_uptown_house suh
inner join sys_uptown_unit suu on suu.unit_id=suh.unit_id and suu.uptown_id = 1238790987234


SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
ALTER DATABASE whepi CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE ms_suspected CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


set character_set_client=utf8mb4;

set character_set_connection=utf8mb4;

set character_set_database=utf8mb4;

set character_set_results=utf8mb4;

set character_set_server=utf8mb4;

set character_set_system=utf8mb4;

ALTER TABLE ms_report CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_user_open CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_user_role CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_uptown CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_uptown_home CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_uptown_house CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;