一. 创建数据库的备份表的多种形式
- 使用Navicat工具, 直接右击复制表, 复制表的数据与结构(总是感觉有点low)
- 使用mysql 的命令行操作
create table bak_tableName select * from old_tableName;
例如创建 user 表的备份表(会同时备份表的数据和结构)
create table user_20220122_bak select * from user;
不会备份表的数据, 只会备份表的结构
create table user_20220123_bak like user;
二. 将查询到的数据插入到指定表中
insert into target_tableName(列名1,2,3...)
select 对应的列名 from source_tableName;
例:
insert into `c_work_attendance_user`(user_id,mobile,`name`,store_id,community_id,department_id,standard_working_start_time,standard_off_duty_time,standard_working_hours)
SELECT
uu.user_id,
ur.mobile,
uu.`name`,
'402' store_id,
'702' community_id,
t2.department_id,
t2.standard_working_start_time,
t2.standard_off_duty_time,
t2.standard_working_hours
FROM
u_user uu
INNER JOIN u_user_owner_relation ur ON ur.user_id = uu.user_id AND ur.identity_type = '1001' AND ur.status_cd = '0'
INNER JOIN building_owner bo ON bo.user_id = uu.user_id AND bo.status_cd = '0' AND bo.owner_type_cd = '1001' AND bo.community_id
= '0079'
INNER JOIN c_work_attendance_department_info t2 ON t2.mobile = ur.mobile
WHERE
uu.status_cd = '0'
三. 修改表的名称
rename table source_tableName to target_tableName;
例, 将表 user 修改为 u_user
rename table user to u_user;
四. 大数据量情况下的分页优化(id连续的时候,分布式id不适用)
-- 使用 子查询
SELECT * FROM table_name WHERE 条件 and id>= (
SELECT id FROM student 条件 LIMIT 'page',1) LIMIT 'row';
例: 查询出 姓 李的用户的数据
SELECT * FROM student WHERE name like '李%' and id >= (
SELECT id FROM student WHERE name like '李%' LIMIT 'page' , 1) LIMIT 'row';