MySQL、カラム間で文字列データの入れ子を修正するSQL

　データベースのMySQLでAカラム（列）に入れるべき文字列データをBカラムに入れてしまい、本来、Bカラムに入れるべき文字列データをAカラムに入れてしまった場合、SQL文で解消する方法がありましたので下記に明記しておきます。

はじめに

　ことの発端はあるシステムのカラム内のデータが上記に書いたように間違っていることに気が付きました。そこでプログラムを書かないでSQLで何とかならないかな？と思ったのが始まり。いろいろと調べてみると下記サイトを発見！

裏MySQLクエリー入門(19) 応用編5 UPDATE文でカラムの値を入れ替えたい – イノベートな非日常

　これで何とかなると思ったのですが、ここに書かれているのはデータ型が数値の場合。文字列の場合はこれに当てはまりません。さあ、どうしたものかと言うことで「あれや」「これや」とやってみたらできたというわけです。このような入れ間違えなんてそんなに起きることはないと思いますが、もし起きた場合は役に立つと思います。

前提条件

　まず、前提条件となるテーブルは下記のSQLとします。

CREATE TABLE IF NOT EXISTS `testtb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `address` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

　このSQLに下記データが入っていたとします。

INSERT INTO `testtb` (`id`, `name`, `address`) VALUES
(1, '浜松', '山田'),
(2, '浜松', '鈴木'),
(3, '浜松', '佐藤'),
(4, '磐田', '田中'),
(5, '磐田', '伊藤'),
(6, '掛川', '佐藤'),
(7, '掛川', '轟'),
(8, '浜松', '松本');

　これを表にすると下記のようになります。

+----+------+---------+ | id | name | address | +----+------+---------+ | 1 | 浜松 | 山田　　 | | 2 | 浜松 | 鈴木　　 | | 3 | 浜松 | 佐藤　　 | | 4 | 磐田 | 田中　　 | | 5 | 磐田 | 伊藤　　 | | 6 | 掛川 | 佐藤　　 | | 7 | 掛川 | 轟　　　 | | 8 | 浜松 | 松本　　 | +----+------+---------+

入れ子にするSQL

　見ての通り、名前が入るべきカラム（name）に住所が入っていて、住所が入るべきカラム（address）に名前が入っています。そこで下記SQLを実行します。

SET @hoge = '';
UPDATE testtb SET 
name = CONCAT_WS("", (@hoge := name)=Null, address), 
address = @hoge;

　上記SQLを実行した結果は下記の通りです。

+----+------+---------+ | id | name | address | +----+------+---------+ | 1 | 山田 | 浜松　　 | | 2 | 鈴木 | 浜松　　 | | 3 | 佐藤 | 浜松　　 | | 4 | 田中 | 磐田　　 | | 5 | 伊藤 | 磐田　　 | | 6 | 佐藤 | 掛川　　 | | 7 | 轟　 | 掛川　　 | | 8 | 松本 | 浜松　　 | +----+------+---------+

　いかがでしょうか？nameとaddressがきちんと直っていますよね。

解説

　では先ほどのSQLを解説してみます。まず、はじめの「SET @hoge = ”;」は@hogeという変数を用意しています。ここはそれだけです。

　次のUPDATE文ですが「name = CONCAT_WS(“”, (@hoge := name)=Null, address), 」が重要になります。まず、「@hoge := name」ではnameカラムの値を先ほど用意した変数に格納しています。格納するとその値が出てきてしまうので「=Null」で空っぽにしてます。なぜ、格納した値が出てくるかはよくわかりません(^^ゞ。

　次にCONCAT_WS関数で先ほどのNullとaddressを結合しています。本来はCONCAT関数で文字列を結合すればいいのですが、Nullと結合するとNullが返ってきてしまいます。そこで、CONCAT_WS関数で結合するとNullを無視するのを利用して、結合文字を空（””）にして合体させています。これでできあがった住所をnameカラムに格納しています。ちょっとややこしいですね。

　そして、「address = @hoge」は上記でnameの値を格納した変数をaddressカラムへ戻しています。

最後に

　以上でカラム上で入れ子になった文字列データの修正が完了しました。こんなことがそんなにしょっちゅう起きることじゃないですが、起きてしまうと「さて、どうしたものか？」と考えてしまいます。

　ほかの方法として暫定的なカラムを用意する方法やスクリプトなどでプログラムを作って対応する方法もありますが、上記の方法がわかってしまうと非常に簡単ですね。また、カラム名を変える方法もありますが、全てのデータではなく一部のデータの場合はそうもいきません。もし、一部のデータの場合は上記SQLにWHEREの条件を付けてください。