環境

MySQL 5.7系、ストレージエンジンInnoDB

ユースケース

業務において、あるテーブルAからテーブルBに対してSELECT INSERTをするケースがありました。

しかし、本番作業前に件数確認のためにSELECT文を流したらめちゃくちゃ遅かったので、『SELECT INSERTは危険だね』という話になり、別の手法を取ることになりました。

そもそもSQLの実行時間測定とかはSTG環境までで検知しておけよ・・という話は（・X・）

別の方法として以下が挙げられました。

SELECT INSERTのSELECT部分をテーブルAに対して実行し、得られた結果を作業用PCにエクスポート、PCでINSERT文を作って、テーブルBに実行するというもの。

今回はSELECT元のデータがシステムによって変更されないという確証があったため静的なINSERT文を作ることができました。オンラインでデータ変更が生じる場合は別の方法を検討する必要があったでしょう。

後者の方法だとどうしても手作業が入ってしまい、品質担保のために、前者の実行結果と後者の実行結果が完全一致するかを比較検証する必要がありました。

そこで、STG環境のテーブルAとテーブルBを本番データ同期した後、以下テーブル複製しました。

テーブルB_SELECT_INSERT_TEST：前者の方法を試す用のテーブル
テーブルB_BULK_INSERT_TEST：後者の方法を試す用のテーブル

テーブル複製の仕方

大きく2つの方法が存在します。

CREATE TABLE ... SELECT 構文

まずはこちらの構文です。

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

特徴
・新テーブルに元テーブルのデータもコピーされる
・AUTO_INCREMENT 属性が保持されない
・インデックスを自動的には作成しない
など

僕の場合、new_tblが『テーブルB_SELECT_INSERT_TEST』だったり、『テーブルB_BULK_INSERT_TEST』で、orig_tblが『テーブルB』でした。

これを実行すると、なんとエラーが起きました・・

Caused by: java.sql.SQLException: Statement violates GTID consistency: CREATE TABLE ... SELECT.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
	at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more

どういうこと? と思ってエラー調べたところ、

MySQL 8.0.21 以前のバージョンだと、GTIDベースのレプリケーションを使っているときは、CREATE TABLE ... SELECT構文が使えない

らしいです。

CREATE TABLE ... SELECT statements. Prior to MySQL 8.0.21, CREATE TABLE ... SELECT statements are not allowed when using GTID-based replication.

プライベートPCの MySQL 8.0.26 環境で実行してみたところ、正常に使えることが確認できました!

ただし、今回は社用PCで全社共通のMySQLだったこともあり、諦めて次の方法を取ることにしました。

CREATE TABLE ... LIKE 構文

次はこちらの構文です。

CREATE TABLE new_tbl LIKE orig_tbl;

特徴
・新テーブルに元テーブルのデータはコピーされない
・元テーブルで定義されている全てのカラム属性やインデックスがコピーされる
・AUTO_INCREMENTの値はリセットされる
など

特徴にもあるとおり、元テーブルのデータまではコピーされないので、別途データを投入してあげる必要があります。

INSERT INTO new_tbl SELECT * FROM orig_tbl;

これでデータがコピーされます！

テーブル間の差分比較の方法

DBは前職までOracleを使っていたので、テーブル間の差分を出すのは、MINUS 演算子を使えば良かったので簡単でした。

しかし、MySQLにはテーブル間の差分を比較する便利関数はないらしいんですよね・・・

そこで、MySQLでは『OUTER JOIN句』を使って差分レコードを抽出します。

テーブルBのテーブル定義は以下と想定します。（複製した2つのテーブルも構成は同じです）

CREATE TABLE `EMPLOYEE` (
  `EMPLOYEE_ID` char(14) NOT NULL,
  `NAME` varchar(10) NOT NULL,
  `GENDER` varchar(10) NOT NULL,
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`EMPLOYEE_ID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EMPLOYEE_SELECT_INSERT_TESTテーブルとEMPLOYEE_BULK_INSERT_TESTテーブルのレコードの主要カラムの値が完全一致しているかを確認したいときは、以下のSQLになります。

SELECT *
FROM EMPLOYEE_SELECT_INSERT_TEST AS T1
	 LEFT OUTER JOIN EMPLOYEE_BULK_INSERT_TEST AS T2 
	 	ON T1.EMPLOYEE_ID = T2.EMPLOYEE_ID
		AND T1.NAME = T2.NAME
		AND T1.GENDER = T2.GENDER
WHERE T2.EMPLOYEE_ID IS NULL;

もしくは、NOT EXISTSを使う方法でも良いかもしれません。

SELECT
	*
FROM
	EMPLOYEE_SELECT_INSERT_TEST AS T1
WHERE
	NOT EXISTS (
	SELECT
		*
	FROM
		EMPLOYEE_BULK_INSERT_TEST AS T2
	WHERE
		T1.EMPLOYEE_ID = T2.EMPLOYEE_ID
		AND T1.NAME = T2.NAME
		AND T1.GENDER = T2.GENDER
);

これで実行結果の比較検証ができて、無事本番で実行することができました!

以上です!

参考サイト