一回のクエリで値の有無を探して、あれば見出し行も書き出し、無ければないで

検索した結果、戻り値がないなら[ありませんでした]
あったら、内容を返す。

これを一回のクエリで実行したい。

$strsql=“SELECT なんだ*, かんだ
FROM このテーブル
LEFT JOIN あのテーブル
ON このカラム = あのカラム
where なじょうけん ;

$sth=$dbh -> query($sql);  //ふつうにクエリ実行

$result = $sth->fetchAll(); //一度フェッチする

$array_size= count($result); //なければ 0がかえってくる

foreach ($result as $key => $value) {  //こいつを回す。
if($key==0){   // ループの一回目は普通ゼロ
foreach ($value  as $k => $v) {
$csvth[]= $k ;  //連想配列なのでキーを取り出して配列化
}
fputcsv($fp, $csvth);  //配列をCSVに
}
fputcsv($fp, $value);  //ここからは値行を一行目から
}

フェッチしないで$sthにさわるとポインタが送られてしまうので全部取り出せなくなる。

日付フォーマットをハイフン区切りに

datetimepicekrで入れた日付がMySqlに入らない。

→datetimepicekr.jsを開いて直接編集パターン

142  format:            'Y-m-d',
204  id:'ここに独自ID',
205  roundTime:'round', // ceil, floor
206  className:'独自クラス',

→PHPで直すパターン
datetimepicker timestampと同じ型にすれば入るだろう。

2015/02/28 17:20:00 → 2015-02-28 17:20:00

date( “Y-m-d H:i:s”,strtotime( $_POST[“dhms”] ))

これでスッキリ解決。

SQL文 LOAD DATA INFILEまとめ

行数の多いテキストファイルを超高速でテーブルに取り込むSQL文。

数万件合ってもコンマ数秒で取り込むので使わないわけにはいかない。INSERTではとてもやってられない。

ハマり所1
読み込むファイルのパーミッションとディレクトリのパーミッション= ともに755にしておく

ハマりの2
LOAD DATA [ local ] INFILE “data.txt” INTO TABLE テーブル名;
このパラメータlocalはMySqlユーザーにFILE権限なしでも実行可能にするものなので、入れておくべき。

ハマりの3
改行記号は  LINES TERMINATED BY ‘\n’;  と紹介しているところが多いが、 “\n”としないと一行しか入らなかった。

ハマりの4
コンソールから実行すると上手くいくが、PDOだとエラーになる。
$dbh = new PDO($db,$user,$password,array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));
array(・・)を追加したら取り込めた。

MySQL 既存テーブルにCSVをインポート

そのままインポートすると別の新規テーブルができてしまう。

  1. とりあえず カレントのディレクトリがここらしいのでファイルをコピーする→ /var/lib/mysql/mws/tmp/
  2. ファイルのパーミッションは755でいいらしい。tmpのパーミッションも755にしておく
  3. テーブルまで選択してSQL文
    LOAD DATA INFILE “tmp/data.csv”
    INTO TABLE t_us FIELDS TERMINATED BY “,”
    LINES TERMINATED BY “\r”
    IGNORE 1 LINES;
  4. 改行文字のrと先頭の1行がフィールド名なので入れない→IGNORE
    改行文字を間違えるとデータは1行しか入らない。

SQL文を最速にする11のポイント

SQL文を最速にする11のポイント

 たとえ最終的な結果が同じでも,SQL文は書き方一つでパフォーマンスがずいぶんと変わってきます。ここでは,速いSQL文を記述するためのポイ ントや注意点をいくつか紹介しておきましょう。

●WHEREの左辺で算術演算子や関数を使わない

 WHERE句の左辺に算術演算や関数を指定すると,インデックスが使われません。例えば,
SELECT NAME FROM CUSTOMERS
WHERE SAL – TAX > 1000
とすると,たとえSALフィールドにインデックスが定義されていてもテーブル全体を走査してしまいます。こうした場合は,
SELECT NAME FROM CUSTOMERS
WHERE SAL > TAX + 1000
のように記述すれば良いでしょう。

●「後方一致」検索はなるべく避ける

 インデックスが付加されているフィールドであっても,LIKE ‘%AAA’ のような「後方一致」を指定すると,インデックスを検索せずにデータ部の全表走査が行われます。したがって「後方一致」の使用はなるべく避けるようにしま しょう。どうしても必要であるなら,
・何らかの,少量まで絞り込める条件とAND条件で組み合わせる
・複数のフィールドに分割し,少しでも前方・完全一致できる範囲を広げる
といった方法を検討して下さい。

●IS NULL,IS NOT NULLを単独で使わない

 条件を表すWHERE句にIS NULL/IS NOT NULLを指定したときは,インデックスを定義したフィールドであっても,全表走査が行われます。したがって,これらの条件を指定するときは,単独で指定 するのではなく,何らかのかなり絞り込める条件を合わせて指定してください。例えば,問い合わせの結果を変更せずに「B = 10」の条件を付加できるなら
…WHERE A IS NULL
とする代わりに
…WHERE A IS NULL AND B = 10
とします。

●SELECT文で「*」を使わない

 レコード長が長いときや,フィールド数が多いときには,すべてのフィールドを表す「*」を指定するのはできるだけ避けて,使用するフィールドだけ を指定するようにします。「*」を指定すると,参照系のSQL文では,すべてのフィールドを繰り返してコピーするため,リソースを無駄に使うことになりま す。最低限度必要なフィールドだけを指定するのが基本です。

●ORはある程度絞り込んでから使う

 論理演算子ORを使用した場合,一応インデックスが使用されるものの,個々の条件が抽出する件数が少ない(数%程度)状態でないと,あまり効果が ありません。

●GROUP BY,ORDER BY,HAVINGは注意する

 GROUP BY句,ORDER BY句,HAVING句は,余分なディスク入出力が発生したりディスク領域を使うので,自分もしくはほかのプログラムのパフォーマンスに悪影響を及ぼしま す。このことを念頭において,使わずに済むならなるべく使わないようにしましょう。

●演算子の組み合わせで速度が変わる

 検索条件に,「>」「<」「=」をANDで組み合わせるときは,指定の仕方によってインデックスの使われ方が異なります。等号と不等号の組み合わ せは,等号のみインデックスが使われます。例えば,
SELECT NAME FROM CUSTOMERS
WHERE JOB = ‘MANAGER’
AND SAL > 1000
とすると,「JOB = ‘MANAGER’」にはインデックスが使われますが,「SAL > 1000」には使われません。また,不等号同士の組み合わせでは,先に指定した条件だけにインデックスが使われます。つまり
SELECT NAME FROM CUSTOMERS
WHERE TAX > 100
AND SAL > 1000
のSQL文では,RDBMSは「TAX > 100」だけにインデックスを使い「SAL > 1000」には使いません。

●テーブルの別名を利用する

 テーブルに別名をつけて,フィールド名にはその別名をつけると,SQL文の解析処理を減らすことができます。例えば,
SELECT ID, NAME FROM CUSTOMERS
WHERE SAL < 1000
よりも,
SELECT a.ID, a.NAME FROM CUSTOMERS a
WHERE SAL < 1000
のほうが高速になります。

●SQL文の表現を統一する

 本文中で述べたように,RDBMSは実行計画をキャッシュに保存しておいて再利用します。ところが,SQL文に定数を直接記述してしまう と,RDBMSは定数値だけが異なるSQL文を別のものと解釈するため,再利用されません*B。 バインド変数を使用して,できる限りSQL文を統一するようにします。また,文字の大小や記述の仕方なども統一しておかないと別のSQL文だと認識されて しまうので,気を付けてください。

●SQL文を簡潔に記述する

 SQL文はなるべく簡潔に記述するようにします。そうすることで,SQL文の処理時間を短縮することができます。

mysqlのストアドプロシージャ

delimiter //
CREATE PROCEDURE get_comment(IN id INT)
BEGIN 
select content from comment where entry_id = id;
END
  • データベース名 stad
  • テーブル名 : comment
  • テーブル名 : entry
  • これで作成↓
CREATE TABLE entry (
    id int auto_increment primary key,
    title varchar(255),
    body text,
    delete_flag tinyint default 0
);
 
CREATE TABLE comment (
    id int auto_increment primary key,
    name varchar(255),
    content varchar(255),
    entry_id int not null
);

これでentry_idが1のレコードをselectできる。

call get_comment(1);

delimiter //
CREATE PROCEDURE get_category(IN id INT)
BEGIN 
select * from m_shohin where cat_ID = id;
END

ストアドプロシージャの削除

DROP PROCEDURE get_category

MySqlの文字化けをなおす

データベースへ挿入した日本語が文字化けする場合の対応策

テーブルのエンコードはutf8_general_ciで作成したとする。

 以下のこれらのコメントアウトをはずす
my.ini の変更 MySqlの文字化けは実際これのみでOK )

[mysqld]
 character-set-server=utf8
 collation-server = utf8_general_ci
 init-connect = SET NAMES utf8
 skip-character-set-client-handshake

[mysqldump]
 quickmax_allowed_packet = 16M
 default-character-set=utf8

[mysql]
 no-auto-rehash
 # Remove the next comment character if you are not familiar with SQL
 #safe-updates
 default-character-set=utf8

これで文字化けが治った。