Archive for the ‘MySQL’ Category

MySQLを使用したファイル管理等を行うものを構築していたときに、
ファイル名のみ抜き出したいケースがあった。
#ファイルの区切り文字は/とする

  1.  
  2. SELECT SUBSTIRNG_INDEX( "/AAAAA/BBBBB/CCCCC/DDDDD/EEEEE.jpg" , "/", -1 );
  3.  

これで、ファイル名のみ取得可能。
これをStoredFunctionで登録しておけば、らくちんでした。

MySQLのSELECTした結果をCSVで書き出す方法はいろいろなサイトで確認できる。

  1.  
  2. SELECT * FROM data INTO OUTFILE "ファイルパス" FIELDS TERMINATED BY ‘,’;
  3.  

ファイルパスは、Windowsの場合は「c:\list.csv」など
Macの場合は「/Users/ユーザ名/list.csv」など

JOINした結果をCSVで取り出したい場合がある。
そんなときは、以下のようにすると取得できる

  1.  
  2. SELECT t1.id, t1.value1, t1.value2, t2.value1
  3. INTO OUTFILE "ファイルパス" FIELDS TERMINATED BY ","
  4. FROM table1 t1
  5. LEFT JOIN table2 t2 on
  6. t1.id=t2.id
  7. WHERE ・・・
  8.  

JOINした結果のcsv取り出しは、場所がFROM句の前になることに注意が必要ですね。
忘れてちょっとはまってしまった。。。

週単位で件数やある値の合計値、平均値等を取りたい場合のやり方。

例えば、以下のようなテーブルがあった場合以下のようになる
TabelA
——————
id primary
date1 datetime
size int
——————

週単位での件数を取得する

  1.  
  2. SELECT CONCAT(YEAR(date1),"/",lpad(WEEK(date1),2,0),"w") as week,
  3.        COUNT(*)
  4. FROM TableA
  5.  

解説:
 YEAR関数:引数の日付の年の部分だけ取得 ’2012-10-30 00:11:22′だったら”2012″
 WEEK関数:引数の日付の最初の日曜日から何週目か 
 lpad関数:左埋めをしてくれる関数(値,桁数,埋める文字)
      例えば(1,3,’0′)とすると001となる
      ORDER BYでうまくソートするために使用

 GROUP BY句で連結した週単位の文字列でグルーピングすることによって
 週単位の集計が行える

合計値を出力

  1.  
  2. SELECT CONCAT(YEAR(date1),"/",lpad(WEEK(date1),2,0),"w") as week,
  3.        SUM(size)
  4. FROM TableA
  5.  

SQLが微妙だったので書き換え(2012-12-12)

いろいろ応用が利きそうな書き方ですね。

対象環境:MySQL4.1以降

テスト環境で、テーブルを作成しそのテーブルを
本番のテーブルに流し込みたいが、
本番に入っている既存のデータには手を入れたくない場合のやり方。

勿論、テスト環境で差分だけ本番に持っていって、
INSERTすればいいのだが、レコード数が多いと、
なかなかめんどーだなと思ったので、
良いやり方がないか調べてみた。

TableAにはすでにデータが入っているが、
追加で何行か追加する場合

1.一時的にテーブルを作成
–TableAの構造のみコピー
CREATE Table TmpTableA LIKE TableA;

2.TmpTableAに対して、テスト環境のデータをINSERT
— 今回は、テスト環境からmysqldumpでsqlを出力したものを使用
— dumpしたSQLから必要のない行を削除したり、テーブル名書き換えたり等行う

3.sql流し込み

4.テーブル同士の差分を取得し、それをTableAにINSERT

  1. INSERT INTO TableA
  2.   SELECT * FROM TmpTableA
  3.       SELECT * FROM TableA WHERE
  4.          TableA.id = TmpTableA.id );
  5.  

これで、TmpTableAに入っていて、TableAに入っていないものが、
TableAにINSERTされる。

SELECT句だけを再度実行して、検索件数が0件なら、
すべてINSERTされたことになる。