MySQL のデータベースのバックアップ＆復元方法のメモです。
以下、
という例で書きます。
mysqldump コマンドで、MySQL のデータベースをバックアップできます。
mysqldump database -u tatsuya -p tacticsogre_tanosii > backup.sql
mysqldump でバックアップしたファイルから、データを復元する場合は、
mysql -u tatsuya -p tacticsogre_tanosii database < backup.sql
でOKです。
サブクエリを使うことによって、テーブル1の検索結果を元に、テーブル2に何らかの処理を行なう、というようなことが可能です。
例えば以下のように架空のショッピングサイトの、ユーザーのIDとアドレスを格納した「UserList」と、注文毎のIDと商品名と購入者のユーザーIDを格納した「OrderList」という二種類のテーブルがある場合。
| UserID | MailAddress |
|---|---|
| 1 | yukimidaifuku_daisuki@abe-tatsuya.com |
| 2 | nattou_kirai@abe-tatsuya.com |
| 3 | syabusyabu_tabetai@abe-tatsuya.com |
| OrderID | OrderItem | OrderUserID |
|---|---|---|
| 1 | しゃぶしゃぶ用鍋 | 3 |
| 2 | しゃぶしゃぶ用松坂牛 | 3 |
| 3 | 雪見だいふく とろける生キャラメル味 20個セット | 1 |
| 4 | 雪見だいふく W生チョコレート味 20個セット | 1 |
| 5 | 納豆撲滅装置 | 2 |
この場合、例えば「yukimidaifuku_daisuki@abe-tatsuya.com」というメールアドレスのユーザーが注文した商品をリストアップしたい、というときには、
という手順になるわけですが、この処理をサブクエリを使うことによって、以下のようにできます。
つぶやくselect OrderList.OrderItem
from OrderList
where OrderList.OrderUserID = (select UserList.UserID
from UserList
where UserList.MailAddress = 'yukimidaifuku_daisuki@abe-tatsuya.com');
こんな感じで、where句の中に括弧でくくった別の select文を書くことで、サブクエリとして実行できます。
update文やdelete文でも、同様にwhere句の中にサブクエリを使うことができます。
他にも、
update TABLE1 set TABLE1.col1 = (select TABLE2.col2 from TABLE2 where TABLE2.col3 = 'hoge') where TABLE1.col4 = 'huga';
こんな感じの使い方もできます。
update文で更新する値そのものを、別のテーブルから引っ張ってくる、というわけですね。
なおMySQLでは、update や delete でサブクエリを使う場合は「テーブル1の何らかの検索結果を元に、同じテーブル1に対して処理を実行する」ということができないようです（select文では可能）。
MySQL で、指定した件数のみ何らかの処理を行いたいときは、LIMIT句を利用します。
ORDER BY句の次に LIMIT句を書いて、件数指定します。
ORDER BY句は使用しなくても構いませんが、使用しないと当然意図した順番通りに抽出することができません。
最初の50件のみを SELECT文で抽出したい場合は、
select * from table_name order by user_id limit 50;
と書けば、user_id の昇順で、50件のみ抽出されます。
また、50件目以降のデータを100件抽出したい、というような場合は、
select * from table_name order by user_id limit 50, 100;
と書くことで、実現できます。
SELECT文と同様に、ORDER BY句の次に LIMIT句を書いて、件数指定します。
こちらも同様に、ORDER BY句は使用しなくてもいいですが、意図した順番通りの処理ができないので結果は SELECT文以上に致命的ですｗ
update table_name set user_flag = 1 order by user_entry_date limit 50; delete from table_name order by user_entry_date limit 50;
上記のように書くと、例えば user_entry_date が古いデータから順に50件に、user_flag を立てる、データを消す、というような処理を行なえます。
なお、UPDATE文や DELETE文では、残念ながら、SELECT文のように「limit 50,100」というような指定はできないようです。
普通にMT使ってると多分あんまり使うことはないかと思いますが、ちょっと仕事でMT4の非公開コメントを一括で公開状態に変更するように処理が必要になったことがあったので、メモがてら。
まず、MTに投稿されたコメントは、mt_comment というテーブルに格納されます。
このテーブルの中のcomment_visibleというフィールドが、コメントの公開/非公開に関するデータで、値が「0」だと非公開、「1」だと公開、という具合になっています。
なので、
update mt_comment set comment_visible = 1 where comment_visible = 0;
というSQL文で、一括公開できます。
ただし、それだけだとSPAM判定されたコメントまで公開状態になってしまうので、こう一つ条件を付け加えましょう。
comment_junk_statusというフィールドに、MTのSPAMフィルタの判定結果が格納されています。
どうも値が「0」だとSPAM判定されたコメントになるようなので、
update mt_comment set comment_visible = 1 where comment_visible = 0 and comment_junk_status >= 1;
というSQL文にすれば、SPAMフィルタに引っかからなかったコメントのみを、公開状態にできます。
SQLの実行が済んだら、再構築をすればコメントが反映されているはずです。
create文の中で select文を実行することで、特定のテーブルのコピーを作成することができます。
create table new_table as select * from old_table;
これで、「old_table」と同内容の「new_table」が作成できます。
ただし、インデックス等は別途作成する必要があります。
テーブルの構造だけをコピーして、中身のデータはいらない、という場合は、select文の where句で、ありえない条件を設定すればOK。
例えば、「user_id」というレコードが必ず1以上の数値であるテーブル「old_table」の構造だけコピーしたければ、
create table new_table as select * from old_table where user_id < 1;
という感じになります。
基本的な SQL 文は MySQL だろうが PostgreSQL だろうが、MS Access だろうが何だろうが、変わりはないんですが、一応メモ。
あるテーブルAからデータを select で取り出して、そのままそのデータを別のテーブルBに insert するには、以下のようにします。
insert into tableB (id, name, mailaddress) select tableA.id, tableA.name, tableA.mailaddress from tableA where tableA.id = 'hoge';
これで、テーブルAから特定の条件のデータだけを取り出して、直接テーブルBに insert することができます。
insert文や select文の基本については、PostgreSQLの初歩あたりにまとめてあります。
基本的な SQL 文は MySQL だろうが PostgreSQL だろうが、MS Access だろうが何だろうが、変わりはないんですが、一応メモ。
MySQL の update 文の中で inner join するには、以下のようにします。
update table1 inner join table2 on table1.id = table2.id set table1.cellname1 = hoge where table2.cellname2 = 'huga';
inner join については、PostgreSQLで複数のテーブルを結合して処理する方法に、詳しく書いています。
例えば、各ユーザーの好きな食べ物を格納した、以下のようなテーブルがあるとします。
| user_id | favorite_food |
|---|---|
| 1 | カツ丼 |
| 2 | カツカレー |
| 3 | カツ丼 |
| 4 | カツ丼 |
| 5 | カツカレー |
| 6 | 串カツ |
| 7 | 串カツ |
| 8 | メンチカツ |
| 9 | カツカレー |
| 10 | カツ丼 |
お前らどんだけカツ好きやねん。というのは置いておいて、こういうテーブルから好きな食べ物毎の件数をカウントして取得するのが、group by 関数というやつです。
select favorite_food, count(favorite_food) from t_favorite_food group by favorite_food;
上記SQLで、
| カツ丼 | 4 |
| カツカレー | 3 |
| 串カツ | 2 |
| メンチカツ | 1 |
という結果が返ってくるはずです。
正直、何ヶ月も前に対処したネタなので、うろ覚えなんですが、完全に忘れきってしまう前に、ちゃんとここに書いておこうと思います。
ローカルのテスト環境として導入した XAMPP の MySQL の内容が、なんだかやたらと文字化けすることが多かったので、色々と調べてみました。
とにかく下記のエントリが参考になりまくりました。
[MySQL] 文字化け問題を本気で直す - ヽ( ・∀・)ノくまくまー(2006-10-11)
まずは、上記エントリ通り、とりあえず現状の文字コード関連の設定を確認。
コマンドラインでやっても良いけど、phpMyAdmin の「MySQL のシステム変数」というメニューでも、同様の設定を確認することができるようです。
で、確認してみると案の定、
何も考えていないだけあって、"characterset" 関係が全て "latin1" になっている。
という状態。
で、これを何とかするには、設定ファイル /etc/my.cnf に、
[mysqld] default-character-set=utf8 skip-character-set-client-handshake
という記述を追加すれば良いとのこと。
ただ、今回 MySQL を使っているのは UNIX/Linux 系の OS ではなく Windows なので、当然 /etc なんていうディレクトリはありません。
といわけで、色々とめぼしいフォルダを探し回って、my.cnf はどこにあるんだ、と漁っていたんですが、それらしきファイルがさっぱり見つからず。
そんな感じで無駄に時間が経過して、かなりハマっちゃっていた時に見つけたのが、下記のエントリでした。
XAMPPのMySQLの設定ファイルは？
このエントリ、書いておいて下さって本当にありがとうございます。めちゃくちゃ助かりました。
と、同時に凄い脱力感が......。
......短縮ダイヤルって。
つまりは、結論から言うと、 my.cnf は XAMPP をインストールしたフォルダ内の mysql\bin フォルダ内にあります。
が、 .cnf という拡張子は Windows では「短縮ダイヤル」の拡張子で、これは Windows を新たにインストールした際に誰もがまずは行なうであろう「フォルダオプション」の「登録されている拡張子は表示しない」の設定をオフにする、という設定を行なっていても、何故かしっかり非表示になるタイプの拡張子だったのでした。
そのため、エクスプローラー上では「my.cnf」ではなく、単に妙なアイコンと一緒に「my」とだけ表示されていた、と。
そりゃ見落とすわ......。orz
こういう失敗をしないためにも、「フォルダオプション」の「ファイルの種類」で、.cnf の拡張子を、個別に「常に拡張子を表示する」設定オンにしておきました。
後は、my.cnf をテキストエディタで開いて、前述の設定を [mysqld] の欄に記述して完了。
再度、phpMyAdmin で「MySQL のシステム変数」を確認すると、"characterset" 関係の設定が "utf8" に代わっていました。
そして文字化け問題も解決。めでたしめでたし。
しっかし、ほんとに Windows の「極力拡張子は表示しない」という無意味な努力は邪魔以外の何者でもないですね......。
Windows上にPHPの開発環境を用意する上で、非常に便利な XAMPP の紹介です。
XAMPP を使えば、一括で以下の環境を Windows 上に用意することができます。
それぞれを別個のサイトからダウンロードしてインストールしなくても、XAMPPだけで、これらの開発環境が揃うわけです。
便利な世の中になったもんですよねー。
というわけで、以下導入手順です。
apache friends - xampp for windowsへアクセスし、XAMPP をダウンロードします。
画面を下の方にスクロールしていくと、ダウンロードサイトへのリンク一覧が出てきます。
特にこだわりや理由がなければ、インストーラをダウンロードするのが手っ取り早いと思います。
「XAMPP Windows版 〔ベーシックパッケージ〕」の「インストーラ」のリンクからダウンロードします。
ダウンロードした XAMPP インストーラを起動すると、言語選択のダイアログが表示されます。
「Japanese」を選択して「OK」をクリック。
「セットアップウィザードへようこそ」という画面が表示されるので、「次へ」をクリック。
インストール先のフォルダを指定します。
左記画面の例では、「C:\xampp」を指定しています。
ここで指定したフォルダに、 XAMPP のコントロールパネルとか、Apache とか MySQL とか PHP とか、その他もろもろが全部インストールされます。
指定したら「次へ」をクリック。
オプション設定画面。
「Apache をサービスとしてインストール」「MySQL をサービスとしてインストール」「FileZilla をサービスとしてインストール」にチェックを入れておくと、これらを Windows サービスとして利用できるので、便利です。
ただし、既にWindows 上でWebサーバーとして IIS を利用している場合等は、一旦 IIS を停止しておかないと、サービスとして Apache がインストールできなかったり、色々と不具合があるかもしれません。
チェックを入れたら「インストール」をクリック。
インストールが始まります。
そこそこ時間がかかるので、しばらく待ちましょう。
インストール完了画面。
これでXAMPP のインストールは完了です。
「完了」をクリック。
インストールが完了すると、「XAMPP コントロールパネルを今すぐ起動しますか？」というダイアログが表示されます。
「はい」をクリック。
XAMPP のコントロールパネルが起動します。
この手順通りにインストールしていれば、「Apache」「MySQL」「FileZilla」が「起動」の状態になっているかと思います。
各モジュールの右側にある「停止」または「停止中」ボタンをクリックすれば、それぞれのサーバーを停止できます。
さて、実際にApache が Webサーバーとして動いているのか、確認してみましょう。
ブラウザを立ち上げて、http://localhost/ あるいは、http://127.0.0.1/ へアクセスしてみます。
左記のような画面が表示されれば、Apache は正常に動作しています。
XAMPP の Web管理画面に入るため、右下の「日本語」をクリックします。
XAMPP の Web管理画面です。
とりあえず、最低限のセキュリティ設定を行なうため、左のメニューから「セキュリティ」をクリックします。
現在のセキュリティ関連の状態がどうなっているか、一覧で簡単に教えてくれます。
ほとんどの項目が「要注意」になっているかと思います。
そのテーブルの下に http://localhost/security/xamppsecurity.php へのリンクがあるので、それをクリック。
MySQL の root のパスワードを設定するフォームと、XAMPP の Web管理画面に .htaccess で Basic認証を設定するフォームの二つが表示されます。
とりあえず、まずは MySQL の root のパスワードを設定しましょう。
任意のパスワードを入力し、phpMyAdmin の認証方法(初期設定では Cookie)を選択。
フォーム下部のチェックボックスにチェックを入れると、設定したパスワードを、自動でテキストファイルに保存しておいてくれます。
まあ、その行為自体がセキュリティ的にどうなんだ、的な感じではありますので、その辺はお好みで。
そして「パスワードを変更しました」というボタンをクリック。
MySQL の root のパスワードを正常に設定できれば、「root のパスワードを変更しました。設定を有効にするために、MySQL を再起動してください。」と表示されます。
XAMPP のコントロールパネルを再度開きます。
MySQL が「起動」状態になっているはずなので、一旦「停止」ボタンをクリックします。
MySQL の「起動」状態が消えます。
再び「開始」ボタンをクリックして「起動」状態に戻します。
これで MySQL の再起動は完了。
再び、XAMPP の Web管理画面のセキュリティ設定フォームの方に戻ります。
次は、Web管理画面に .htaccess で Basic認証をかけるための設定を行ないます。
Basic認証については、「.htaccessによるBasic認証のかけ方」「.htaccess で特定のファイルのみにBasic認証を設定する方法」等も参考になれば。
フォームに、Basic認証に利用するユーザー名とパスワードを入力します。
MySQL の root のパスワードを設定した際と同様に、チェックボックスにチェックを入れると、ユーザー名とパスワードをテキストファイルに保存してくれますが、こちらもお好みで。
入力したら「安全な XAMPP ディレクトリを作成してください」というボタンをクリック。
「成功: XAMPP ディレクトリ制御が成功しました！ 全ユーザのデータは、以下のファイルに格納されました:」と表示されたら、設定完了です。
以後、XAMPP の Web管理画面にアクセスすると、左記画像のように、Basic認証のダイアログが出るようになります。
とりあえず、これで最低限の設定は完了です。
なお、 XAMPP をインストールしたフォルダ内に「htdocs」というフォルダが作られています。
このフォルダ内にファイルを放り込めば、「http://localhost/***.html」等の URI でアクセスできます。
ちょっとSNS用のCMS「OpenPNE」をインストールする機会があったんですが、つまらないところでハマった（というほどでもないけど）のでメモ。
とりあえず普通にインストールは完了し、管理画面にログイン。
試しにプロフィールの項目を追加してみようと思ってやってみたら、何度やっても追加されない。
おっかしいなぁ、と思ってMySQLデータベースの中身を見てみたら、テーブル「c_profile」に、普通に新規に追加したプロフィール項目のデータは入ってる。
ということは、どうも管理画面側でデータベースを select した際に、なんらかの原因でデータを引っ張ってこれてないんだな、と目星がついたので、該当する箇所のPHPソースコードを元にざっと grep してみたところ、あっさり原因判明。
OpenPNEの設定ファイル「config.php」には、
// MySQL ヒント使用設定
define('OPENPNE_USE_MYSQL_HINT', true);
という箇所があるんですが、「MySQL ヒント」って何だ？と思いつつ、とりあえずデフォルトで true になってるからそのままでいいや、と思ったら、そこが原因だったらしい。
先ほどの管理画面のプロフィール項目のデータを、テーブル「c_profile」から select してくる際に、MySQL ヒント使用設定（OPENPNE_USE_MYSQL_HINT）が true だと、SQL文の中に「/*! */」で括られたコメントを挿入してるみたい。
で、何故かそのコメントが入ってると、データを引っ張って来れなくて、データベースにはちゃんとデータが入っているのに、管理画面には項目が表示されない、ということになっていたようです。
なんでそのコメントが入ってると駄目なのかは不明（つまり原因判明と言いつつ、本当の意味では原因は判明してないわけです……）。
まあ、とにかくこの「MySQL ヒント使用設定」をオフにすれば、解決しました。
// MySQL ヒント使用設定
define('OPENPNE_USE_MYSQL_HINT', false);
けど、この「MySQLヒント」って言うのは何なんだろう？
ググっても特にそれらしきものは引っかからなくて、よくわかりません。
誰か教えて、エロい人。
MySQLでデータを取り扱う際、日付・時刻関連のデータはdatetime型で管理するようにしています。
（余談ですがPostgreSQLではtimestamp型を使っているので、MySQLをはじめて使ったときに、何も知らずに同じようにtimestamp型を使って痛い目にあった経験があります......)。
datetime型で保存したデータは「YYYY-MM-DD hh:mm:ss」という形になるわけですが、いざこのデータをperlやPHPで取り扱う、となった際に、そのままの形だと単なる文字列なので、比較処理や加算・減算なんかをする時にちょっと面倒なんですよね。
一度UNIX TIME(1970年1月1日0時0分0秒からの経過秒数)に戻してから処理をするのが一般的です。一手間かかるわけですね。
しかし、MySQLには非常に便利な「UNIX_TIMESTAMP」という関数があって、あっさりとその問題を解決できます。
例えば、テーブル「customer_data」から、顧客の生年月日を格納したフィールド「birthday」のデータを取り出す際、通常なら、
select birthday from customer_data;
という風にやるわけですが、これだと取り出したデータは「1976-05-06 12:34:56」みたいな感じになってます。
そこでUNIX_TIMESTAMP関数を使って、
select unix_timestamp(birthday) as u_birthday from customer_data;
とやると、取り出したフィールド「u_birthday」のデータは「200201696」という感じで、1970年1月1日0時0分0秒からの経過秒数(この例の場合は1976年5月6日 12:34:56までの経過秒数)になります。
逆に、UNIX TIMEで保存したデータを、datetime型に変換するための「FROM_UNIXTIME」という関数もあります。
こちらは、「200201696」のような形で保存されているデータを取り出す際に、
select from_unixtime(u_birthday) as birthday;
とやれば、「1976-05-06 12:34:56」という風に変換されて出てきます。
