MySQL

LEFT JOIN で NULL が出ない

table1 と table2 を LEFT JOIN すると、 ON の結合条件に当てはまるデータが table2 に存在しない場合は、 NULL で返ってくる。でも、今回は返ってこなかった。 理由は table2 の条件を on ではなく、 where で設定していたから。 where で設定すると、そも…

列名を含めたLike検索

普段は以下のように使うけど・・・ SELECT * FROM tb_test WHERE name LIKE '%satou%'; 「satou」 の部分にテーブルの列名を指定したいことがある。 そんなときは文字列結合の「CONCAT()」を使う。 以下は family_name という列名を指定している。SELECT * F…

EXCEPT を NOT EXISTS で代用する

集合同士の差を求める EXCEPT が MySQL にはない・・・。 でも、NOT EXISTS で代用できるから問題ない。SELECT name FROM tb_test WHERE NOT EXISTS (SELECT * FROM tb_test2 WHERE tb_test.id = tb_test2.id);特に問題なし。

列名を含めたLike検索

普段は以下のように使うけど・・・ SELECT * FROM tb_test WHERE name LIKE '%satou%'; 「satou」 の部分に列名を指定したいことがある。 そんなときは文字列結合の「CONCAT()」を使う。SELECT * FROM tb_test WHERE name LIKE CONCAT('%', family_name, '%'…

InnoDBの設定

MySQLには my.cnf のテンプレートがあるので、それを編集する。CentOS6の場合、場所は「/usr/share/mysql」で、以下の種類がある。 my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnfサーバのメモリサイズによって分けられている…

テーブル情報を表示させる

テーブルの情報を表示させる。今までは statusコマンド でDBの文字コードを表示させるだけだったけど、 テーブルの情報も見たいなと・・・。【DBの全テーブル情報を表示させる】 # show table status form db_test;【DBのあるテーブルの情報を表示させる】 #…

ALL と サブクエリ

ALL はサブクエリの結果と全部比較して、結果を返す。SELECT name FROM tb_test1 WHERE name = ALL (SELECT name FROM tb_test2);上記は tb_test1 の name と tb_test2 の name を比較して、 tb_test2 の name 全てと一致する tb_test1 の name を取得する。

in と exists の違い

in と exists の違いとは・・・in : 副問い合わせを評価した後に、親問い合わせへ結果を渡す。 exists : 親問い合わせを評価した後に、副問い合わせへ結果を渡す。なので、取得レコードを 親 or 副 のどちらで絞り込むかによって変わる。 副問い合わせで絞り…

unique制約 と insert ignore into

unique制約を付けて、重複がない場合に insert って処理をする場合、 今まではデータの存在を確認してから insert を実行していた。サーバへの負荷は気にならない程度だったけど、 select 処理を書くのが面倒・・・。で、調べたら insert ignore into という…

ERROR 1030 (HY000): Got error 28 from storage engine

MySQLが動かなくなって、シェルで「use database」を叩いたところ、 「ERROR 1030 (HY000): Got error 28 from storage engine」のエラーが出た。これはハードディスクの容量がいっぱいということらしい・・・。 「df -h」で容量を確認すると、たしかに使用…

using

MySQLのテーブル結合では大体 on を使って外部キーと主キーを繋げるが、 using を使うことも出来る。using は「同じ列名で同じデータ型のもの」を指定すれば自動的に繋げてくれる。 以下の例は「test_row」という列で結合する。 例:select * from test1 joi…

update + case when

以下のようになる。update tb_test set id = case when ( ) then 1 else 0 end;これは便利。

order by rand() の group by

group by の結果を order by rand() でランダムに出力しようとしてもできない。 そんなときは order by rand() した結果を from に当てはめて出力する。構文は違うけど、ニュアンス的には以下になる。select url, name, mail from order by url;上記を from …

最大受信データの設定

mysqlがクライアントから受信出来るデータはデフォルトで1MB。 普段はこれで十分かもしれないけど、 画像とかだと1MBはキツい。なので、my.cnfに設定を追記する。[mysqld] max_allowed_packet=16MB 現在の設定値は以下のSQLで確認できる。 mysql> show varia…

group by

データの集計で使う。group by で指定された列は重複が除去され、 それ以外の列には集計関数を使わなければならない。 select category_id, count(sentence) from sentence group by category_id; こうすると、カテゴリーIDごとに文章数をカウントしてくれる…

最新レコードの取得

手法としては2つ。1.phpの「mysqliクラスのinsert_idプロパティ」を使う。2.Mysqlの「LAST_INSERT_ID()」を使う。 select last_insert_id as id;両方共取得する対象列がauto increment である必要がある。 これに関しては、普段から主キーとしてidを定義して…

ランダム抽出

以下でいける。 ORDER BY RAND()こんな風にlimitも使える。 ORDER BY RAND() LIMIT 10こいつは便利だ・・・。

トランザクションコマンド

今まではPHPの関数でやってたけど、 MySQLにクエリを発行すればトランザクションかけれる。トランザクション開始(コミットされるまではDBに反映されない) START TRANSACTION;コミット COMMIT;ロールバック ROLLBACK;

コマンドライン実行

コマンドラインで実行できると、 シェルで便利かも・・・。mysql -u user_name --password=test_pass -e 'create database db_test' -e オプションでSQLを実行できる。

【MySQL】複数のテーブルを対象にした left join

頑張ったので、メモ select c.name, a.url, k.word, r.created, r.rank from companies as c left join ((key as k left join ali_spec as a on k.id = a.keyword_id) left join records as r on k.id = r.keyword_id) on c.id = k.company_id; 今回は4つの…

定義のみのダンプ

定義のみダンプしたいことがあったので、メモ mysqldump -d でいける。 mysqldump -d -u root -p db_test > testQuery.sql 便利だー。

left join

今まで使う必要なかったけど、使ってみた。構文は以下になる。 tb_a as a left join tb_b as b on a.id = b.id 2つの表を結合するわけだから、 結合のキーとなる列が存在するはず。 それをon句で指定し、結合する。 as句でエイリアスを付けたほうが楽。以下…

外部のmysqlへ接続

外部に接続できるのか・・・ なんか便利かも・・・もちろん外部サーバでポートとか色々設定しなきゃダメだと思うけど・・・ mysql -h 192.168.1.120 -u test_user -p

ifnull関数

SQLの集計系関数とかで、nullが入ってるのがイヤ。なので、nullの場合は何かに変換する処理があったらなーと。ifnull関数を使うらしい。 select ifnull(列名, デフォルト値) from tb_test; これで、nullのデータがデフォルト値に置き換わる。

null判定

null判定はそれ用の書き方があった。 基本的にDBにはnullを入れないから分からなかった・・・。 where 列名 is not null where 列名 is null

テーブルのバックアップ

今まではDBのみだったけど、 テーブルのバックアップもとりたい。ってことで以下のコマンド。 mysqldump -u root -p db_test tb_test > test.sql

insert/select文

insert/select文はinsertするデータをselectでセットする。 あるテーブル内のデータを違うテーブルにinsertする時に使える。insert into tb_a(id, text) select id, text from tb_b;これは便利だ・・・。 SQLっていろいろできるんだね・・・。

外部からの接続

ハマった・・・・。ユーザー作成では@localhostを指定しているが、 当然ながらこれでは外部から接続できない・・・。ユーザー作成時にHost名を省略するとワイルドカード指定となり、 外部からアクセス可能になる。 grant all privileges on db_name.* to use…

クエリのインポート

今まではMySQLにログインして、sourceコマンドでクエリファイルをフルパス指定だった。mysql > use db_test; mysql > source /var/www/html/testdir/query.sql フルパスは面倒なので、調べてみるとコマンドライン上からインポートする方法がある。 # mysql -…