前言

我的项目中需要对MySQL数据进行数据迁移操作。例如 A库 → B库。A库作为源库，结构、数据会有变更；B库(可能不存在，不存在则需要通过SQL创建)表结构、数据需要多次不完全覆盖，以确保数据最新。

我的操作步骤

B库命名为 customer

SQL创建数据库

为避免后续一些特殊符号、表情符号无法存储直接使用utf8mb4编码格式，因为MySQL的utf8并非真正的utf8。

CREATE DATABASE IF NOT EXISTS customer default charset utf8mb4 COLLATE utf8mb4_general_ci;

删除库中tb开头的表

如下SQL拼装删除SQL，执行拼装后的SQL即完成删除操作。我后端语言使用JAVA执行SQL，你可以使用自己当前的语言操作。

select CONCAT('DROP TABLE IF EXISTS ',GROUP_CONCAT(CONCAT('customer.',table_name))) as dropsql  from information_schema.tables where table_schema='customer' AND table_name LIKE 'tb%';

创建表tb开头的表

tb开头的表是需要根据业务，动态生成的表。实际项目中有很多张动态表，这里只拿一张举例。


CREATE TABLE IF NOT EXISTS customer.tb_process (
  `id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '自增编号',
  `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  `content` varchar(1000) DEFAULT '' COMMENT '说明',
  `createBy` bigint(19) NOT NULL COMMENT '添加人',
  `createTime` datetime NOT NULL COMMENT '添加时间',
  `modifyBy` bigint(20) DEFAULT NULL COMMENT '修改人',
  `modifyTime` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) COMMENT='流程';

更新基础表数据

清空数据

TRUNCATE customer.schedule_job;
TRUNCATE customer.qrtz_scheduler_state;
TRUNCATE customer.qrtz_locks;
TRUNCATE customer.qrtz_cron_triggers;
TRUNCATE customer.qrtz_triggers;
TRUNCATE customer.qrtz_job_details;

导入新数据

insert into customer.qrtz_job_details SELECT * FROM A.qrtz_job_details;
insert into customer.qrtz_triggers SELECT * FROM A.qrtz_triggers;
insert into customer.qrtz_cron_triggers SELECT * FROM A.qrtz_cron_triggers;
insert into customer.qrtz_locks SELECT * FROM A.qrtz_locks;
insert into customer.qrtz_scheduler_state SELECT * FROM A.qrtz_scheduler_state;
insert into customer.schedule_job SELECT * FROM A.schedule_job;

说明及结果

以上6张表是一部分基础表。表关系 qrtz_triggers 外键 qrtz_job_details；其他4张表外键 qrtz_triggers 。我在处理时担心外键导致数据插入有问题，故意按照以上顺序处理。

结果： 1.有外键关联的表无法被删除，导致无法更新表结构 2.有外键关联的表无法被清空，插入数据时也会失败。

怎么解决呢

解除外键约束即可操作成功

SET FOREIGN_KEY_CHECKS=0;  #解除约束
...  这里执行SQL
SET FOREIGN_KEY_CHECKS=1;  #恢复约束

FOREIGN_KEY_CHECKS影响整个库吗

实际上，有两个foreign_key_checks变量：全局变量和本地变量(每个会话)。连接后，会话变量被初始化为全局变量的值。

命令SET foreign_key_checks修改会话变量。

若要修改全局变量，请使用SET GLOBAL foreign_key_checks或SET @@global.foreign_key_checks

修改全局变量，当前回话变量不会被改变。可以使用以下SQL查验

SHOW Variables WHERE Variable_name='foreign_key_checks'; # always shows local variable

请参阅下列手册章节：

http：//dev.mysql.com/doc/reflman/5.7/en/use-system-variables.html

http：//dev.mysql.com/doc/reflman/5.7/en/server-system-variables.html

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2020/04/05 17:22

MySQL有外键约束的库使用SQL迁移表结构、数据

前言