データベースのテーブルを更新する際のコツ

Question

PHPmyadminにてMysqlのテーブルを更新する際、いつも、一度テーブルを空(TRUNCATE)にしてからインポートしています。
しかしこれでは、インポートが完了するまでWebサイトが見れなくなってしまいます。

Webサイトが見られる状態でインポートする方法はあるのでしょうか？

※いつもインポートファイルはSQLを使っていますが、CSVなど他の方法を採用することで可能になるのでしょうか？

Answer 1

がると申します。

トランザクションがよいかとは思うのですが。
別の方法として
・「別名のテーブル」に一端INSERTする
・INSERTが終わったら
→対象のテーブルを削除する
→「別名のテーブル」を対象のテーブルにrenameする
といった方法も、あるにはあります。
これですと、「Webが見れない」のは「対象のテーブルを削除」から「別名のテーブルをrenameする」までなので、ダウンタイムは比較的少なくすることができるか、と思いますが如何でしょうか。

以上、何かの参考にでもなれば幸いです。

Answer 2

トランザクションを使えば、事足りると思います。

1. begin transaction
2. truncate
3. insert
4. commit

Answer 3

insert into on duplicate key update構文でバルクのupdateができます

sample

create table tbl (id int unique,val int);
insert into tbl values(1,100),(2,200),(3,300),(4,400);

id	val
1	100
2	200
3	300
4	400

これに対して3を更新5を追加します

insert into tbl values(1,100),(2,200),(3,30),(5,50) on duplicate key update val=values(val);

1,2は同じ値を投入しているので更新されません
（もちろん4のように投入しなくてもOK）

id	val
1	100
2	200
3	30
4	400
5	50

また、INSERTがいらなければELTとFIELDを利用してこういう風にかけます
2,3番を更新

UPDATE tbl set val=COALESCE(ELT(FIELD(id,2,3),210,310),val)

※ちょっとなれるまでわかりづらいかもしれません

Answer 4

全削除して全部入れ直すから、Webサイトが見れない時間が気になるほどの時間が掛かるのではないでしょうか？
更新に必要な分だけ処理すればもっと軽いのでは？insertだけじゃなくupdateとかdeleteとか使う感じで

Comments

[退会済みユーザー]

例えばテーブルごとエクスポートしてあるSQLファイルで、一括してファイルごとインポート(update)ということはできるのでしょうか？
やはりupdateだと、レコードごとにsql文を記述して処理しなければならないでしょうか？

[oskbt]

簡単にできる方法を調べてみたのですがどうやらないですね。
定期的な作業としてWEBサイトのデータベースを更新しているのなら、更新用のSQLを作るプログラムを組む方が後々楽になるっていう場合もあるので検討する余地はあるかなと思いますが、作るのはそれなりに面倒そうです。

Answer 5

コピーしたものを一時的に参照するようにして、
その間に TRUNCATE & IMPORT して、
終わったら元に戻す。
ただし、その間に更新したものを反映させたかったら
ちと苦労するかなあ。

Answer 6

同じ処理であればCSVでもSQLファイルでも同じだと思います。
時間のかかる処理であればバッチファイルを作ってcronでアクセスの一番少ない時間帯に処理させるのはどうでしょうか？
(処理中は一時的に閲覧できなくする仕組みも必要かもしれません)