1. 把一张表中的全部数据放到另一张备份表中。

CREATE TABLE IF NOT EXISTS task_backup( SELECT * FROM task);

2. 根据结果集创建中间表

CREATE TABLE IF NOT EXISTS employee_backup

( SELECT employee.id,employee.userId,employee.employeeId,employee.name,employee.position_id,position.name as positionName,employee.levelRole,employee.expiredDate

FROM employee left join position on employee.position_id=position.id);

3.批量更新Per的LevelRole,PositionName

update per.employee as e inner join pet.employee_backup as b on e.employee_id=b.employeeId

set e.position_name=b.positionName,e.level_role=b.levelRole;

4. 导出中间表

mysqldump -h -u root -ppassword dbname employee_backup>C:\AA\employee_backupOK.sql

 

显示字符编码

show global variables like '%char%';

show variables like 'character%';

show variables like 'collation_%';