mysql 创建备份表 更新数据 查看编码格式

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_%';