MySQLで詰まったところ

開発環境は、Windows 7 Professional(32bit) + MariaDB 10.1.25。

■シングルクォーテーションをエスケープする
SQLインジェクション対策で、シングルクォーテーションをエスケープしたい場合、『'』を『''』という風にもう１つシングルクォーテーションを追加する。『'こんにちは'』であれば、『''こんにちは''』となる。

参考サイト
SQLでは「'」をエスケープする際に「''」を使う

■selectで複数のテーブルからデータを取得する場合に、カラム名を指定する場合の省略の仕方
もしかしたら、こういう書き方もありなんじゃないかとやってみると、うまくできたのでメモしておく。プログラミングという論理の世界で生まれた問題を、直感で生まれた論理を試してみると、解決できるということが、自分の場合、たまにある。

select posts.*, users.imageurl from posts inner join users on posts.username = users.username;

■WHERE句の指定の仕方

# idが5のデータを取得する
select * from tablename where id = 5;
# idが5以外のデータを取得する - パターン1
select * from tablename where id <> 5;
# idが5以外のデータを取得する - パターン2
select * from tablename where id != 5;
# idが5より大きいデータを取得する
select * from tablename where id > 5;
# idが5以上のデータを取得する
select * from tablename where id >= 5;
# idが5より小さいデータを取得する
select * from tablename where id < 5;
# idが5以下のデータを取得する
select * from tablename where id <= 5;
# statusがonのデータを取得
select * from tablename where status = 'on';
# statusがoff以外のデータを取得 - パターン1
select * from tablename where status <> 'off';
# statusがoff以外のデータを取得 - パターン2
select * from tablename where status != 'off';

■乱数を作成する
【例】仮データを入れる際に用いる。

# 0～9までの乱数が発生
select floor(rand() * 10);
# 文字列の乱数(10桁文字列)
select substring(md5(rand()),1,10);
# 日付の乱数 - 2018年3月1日～3月31日まで
select add_date('2018-03-31',interval 31 * rand() day);

参考サイト
https://www.ilovex.co.jp/blog/system/mysql/mysql-9.html

■inner joinの使い時
・取得したテーブルに1対1の関係で別のテーブルからデータを取得して、連結させたい場合は、inner joinを用いる。
【例】投稿したある記事に対して、投稿したユーザーの情報を結合して表示したい場合

■left join/right joinの使い時
・ある条件に合致した時に別のテーブルデータからデータを取得したい場合は、left join/right joinを用いる。
【例】自分がお気に入りアイコンをクリックしたものだけ、アイコンの表示を変えたい場合など。

■カラム名が被っている場合
as句を用いて、一時的に名前を変更することで、カラム名が被っていても使用することができる。

current_column_name as new_column_name;

■group byとorder byを併用したい場合どうするか？
・group byでユーザー名をまとめた、それぞれに対して、max関数を用いて、最大のものを抽出して、それを、サブクエリとしてwhere in 句に渡す。
・過程で分かったこととして、サブクエリの中でorder byは使うことができない。group byは使うことができる。
調べたところ、実行の順序は、group by→order byじゃないといけないみたいだ。
【例】各ユーザーの最新の投稿を１件だけ表示したい場合。

select * from posts where created in (select max(created) from posts group by user_id) order by id limit 20;

【例】過去1時間に検索されたキーワードから上位5件だけ取得したい場合

select search_keyword, count(search_keyword) as kensu from search where created > date_add(sysdate(),interval - 1 hour) group by search_keyword order by kensu desc limit 5;

参考リンク
MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと | 日記の間 | あかつきのお宿
 サブクエリーでORDER BYは使えない - R42日記
 group byはorder byより先に処理される。これ重要。 - Webtech Walker
MySQLコマンド・関数一覧（データベース）｜ITリファレンス
 翌月を取得する - MySQL 逆引きリファレンス

■ユーザー定義変数を使う
例えば、PHPから、変数$hogeを受け取って、その値をSQL文の変数に入れて、テーブルのデータとして追加したい場合、

<?php
$hoge = "test";
$sql = <<<"EOF"
select (@hoge := '$hoge') as hoge from posts;
"EOF";

?>

:=じゃなくて=だけだと、値が入らずに変わりに、NULLが入る。

参考リンク
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.4 ユーザー定義変数
 mysqlでユーザ定義変数をつかう - 眠すぎて明日が見えない
 MySQLのユーザー変数

k01ken’s b10g

He110 W0r1d!

MySQLで詰まったところ