MySQLで日付の差分を取得する

2009-09-18
このエントリーをはてなブックマークに追加

timestampdiff(day, from_date, till_date)

これだけです。
第一引数は「month」、「year」で月、年の差も取れます。第二第三引数はtimestampじゃなくてもいいです。まぁTime型だとダメなんだろうけど、Date型だったらちゃんと差分が取得できました。

「登録から~日の情報」みたいな取得の仕方をすることが多いので、こういう関数は使用頻度が高いです。

Tags:

PHPでMySQLのload data infileを実行する

2009-08-20
このエントリーをはてなブックマークに追加

クエリで

load data local infile ‘ファイルパス’ into table テーブル名;

を実行するだけなのですが、「local」をつけないと動いてくれなかったのでメモ。SSHでログインしていたときは「load data infile」だけでインポートできていたので、気がつかずに苦労しました。

Tags: ,

RubyでMySQLに接続

2009-03-12
このエントリーをはてなブックマークに追加

MySQL/Ruby と Ruby/MySQL の二つがあるようです。どっちがどっちか混乱しましたが、MySQL/Rubyがgemでとってこれて、Cで書かれたAPIのようです。Ruby/MySQL はRubyで書かれているようで、比較的低速らしいです。今回はMySQL/Rubyを利用しました。

gem install mysql

で一式インストールできます。PHPのPEARやRubyのgemは、一度つかったらやめられません。これは堕落なのでしょうか。

で、今回はWindows上にMySQL5.0をインストールしてつないでみたのですが、バリバリ文字化けました。DBはUTF8で作ってあって、ソースも以下のようにUTF8であることを明記していました。

require ‘rubygems’
require “mysql”
require ‘iconv’

$KCODE = ‘UTF-8’

db = Mysql::connect(“ホスト名”, “ユーザ名”, “パスワード”, “DB名称”)
rs = db.query ‘クエリ’
rs.each do |item|
  p item[0]
end

なんでなんで?と調べていたら、my.ini(あー、iniってあたりがWindowsだなー、Linuxだとmy.cnfだったかな?)の[mysqld]に

default-character-set=utf8
skip-character-set-client-handshake

の追記が必要らしい。これはクライアントからの接続時に「文字コードはUTF8でっせ」とデフォルトで設定してあげるためのものらしいです。「set names utf8」と同じ意味ですね。これで文字化けずにWindows上でRubyとMySQLを利用することが出来るようになりました。

これまではC#とSQL Serverで統計とか集計のプログラム書いてたけど、Rubyの方がラクチンなんですよね。Railsばっかり注目されてますけど、Rubyは便利ですよ。

 

Tags: ,

MySQLで複数のテーブルを外部結合

2008-11-20
このエントリーをはてなブックマークに追加

ネストしてもいいのですが、可読性が悪かったりパフォーマンスが悪くなったり。

select * from table_0 i
left join table_1 j on i.id = j.id
left join table_2 k on i.id = k.id

つまり、「table_0に対してtable_1とtable_2を結合しまっせ。」ということです。ネストしてないのでとっても読みやすいです。パフォーマンスも、よい気がします(計測しろよというご指摘はごもっともです)。

Tags: ,

MySQLでストアドプロシージャを使う

2008-11-13
このエントリーをはてなブックマークに追加

MySQLでトリガーを使う

の続きといいますか、おんなじような内容なのですけが、ストアドの使い方です。これもいたって簡単です。

delimiter //
create procedure ストアド名(in 引数名 データ型)
begin
実行する処理、引数はそのままの名前で使える
end;
//
delimiter ;

大量の更新処理なんかには便利でしょう。

Tags:

MySQLでのバックアップとリストア

2008-11-03
このエントリーをはてなブックマークに追加

MySQL5.xでのお話です。バックアップは

mysqldump -u root –password=xxxxxxxx データベース名 > 保存先パス

保存先はMySQLが書き込める権限を持っている必要があるみたいです。mysqldumpを実行するユーザが書き込めればいいのかなぁ。今度検証しておこう。

リストアは簡単です。

mysql -u root –password=xxxxxxxx データベース名 < バックアップファイル

バックアップって言っても、バイナリじゃなくてDDLとDMLがぎっしり書かれたテキストファイルなので、リストアするのも「このファイルに書かれた大量のSQLをこのデータベースに実行しよう」ってイメージですね。

そういえばmysqldump実行中って、書き込み制御してくれるのかなぁ。

Tags:

MySQLで先月末日の日付を取得する

2008-10-28
このエントリーをはてなブックマークに追加

select last_day(date_add(now(), interval -1 month))

まぁなんてことない手法なのですが、なんとなく便利だったので記録。「今日の一ヶ月前の月の末日」っていう意味ですね。

Tags: ,

MySQLでトリガーを使う

2008-10-24
このエントリーをはてなブックマークに追加

MySQLも5.xからトリガーが使えるようになりました。SQL SERVERやPostgreSQLでストアド、トリガーを多用していたハセテツとしては大変ありがたいわけです。

delimiter //

これで書き始めます。デリミタは「;」ですが、トリガーの記述の中でもセミコロンを使うために一時的に「デリミタはスラスラだよ」と宣言しておきます。べつにスラスラじゃなくてもOKでしょう。

create trigger トリガー名称
before insert on 対象テーブル for each row begin
declare target_id tinyint;
select hoge_id into target_id from マスタデータテーブル where hoge_code = new.hoge_code;
set new.log_hoge_id = target_id;
end; //

まぁなんのこっちゃわからんサンプルですが、declareで変数宣言、select intoで変数へ代入、setで値を設定しています。この例は「ログデータテーブルに値を突っ込む前にマスタテーブルから値を引っ張ってきておいて、それをinsertします」っていう感じです。

終わったら

delimiter ;

でデリミタを元に戻すのをお忘れなく。

テーブル例も書いたほうがいいんだろうけど、申し訳ないけど割愛。特定テーブルの値が更新されたら更新ログを残す、なんてことも「after update」でできるので、フロントエンド側のコードをごちゃごちゃさせずに複雑な処理がさせられると思います。

Tags:

MySQLの最適化

2008-09-17
このエントリーをはてなブックマークに追加

mysqlcheck -o DB名

これだけです。ドキュメントを読む限り、内部的にcheck table、repair table、analyze table、optimize tableを呼んでいるようです。

「対象はmyisamだけ」みたいなこともどこかで読んだのですが、公式ドキュメントにはそういう記述はなし。4.xの話なのでしょうか。あ、ハセテツは5.0を利用してます。

必要なのはanalyze table、optimize tableだけで、これらはinnodbにも対応しているから特に問題はないでしょう。

Tags:

MySQLにテキストファイルをインポートする

2008-06-17
このエントリーをはてなブックマークに追加

mysql> load data infile ‘ファイル名フルパス’ into table インポート先テーブル名 fields terminated by ‘t’;

もちろん、ファイルはMySQLが触れる場所にないといけない。terminated(区切り文字)はカンマでもタブでも、任意で。文字コードに注意。

Tags: