数据库 MySql 内存表 例子

1.创建表ycl_doctor_mem

DROP TABLE IF EXISTS `ycl_doctor_mem`;
CREATE TABLE `ycl_doctor_mem` 
ENGINE=MEMORY
MAX_ROWS=50000000
select loginId from base_doctor where loginId is not null and source<>1;
ALTER TABLE `ycl_doctor_mem` ADD INDEX `index_loginId` (`loginId`);

2.创建内存表ycl_not_login_tem（物理表base_userroles，内存表ycl_not_login_tem联合查询）

DROP TABLE IF EXISTS `ycl_not_login_tem`;
CREATE TABLE `ycl_not_login_tem` 
ENGINE=MEMORY
MAX_ROWS=50000000
SELECT userId from base_userroles,ycl_doctor_mem where base_userroles.userId= ycl_doctor_mem.loginId  and roleId='doctor' and lastLoginTime is null ;
ALTER TABLE `ycl_not_login_tem` ADD INDEX `index_userId` (`userId`);

3.更新数据
update  base_user, ycl_not_login_tem   set base_user.pwdModifyDt=null WHERE  base_user.id=ycl_not_login_tem.userId;
-- 预估数据量，当时内存表最多占2g内存

3.创建内存表，创建临时表

CREATE TEMPORARY TABLE tmp_table (...) TYPE = HEAP;

CREATE TEMPORARY TABLE tmp_table (...) ;