読者です 読者をやめる 読者になる 読者になる

MySQL

MySQL5.7 の mysql_secure_installation でパスワードが分からなかった

パスワードについては以下に載ってる。 http://weblabo.oscasierra.net/mysql-57-init-setup/パスワードのバリデーションについては以下に載ってる。 http://yoku0825.blogspot.jp/2015/08/mysql-578rpmvalidatepassword.htmlこれらについては何かの勉強会で…

CentOS7 に MySQL 5.7 をインストール

rpmをインストールしてからmysqlをインストールするだけ。以下からRPMを選択する。 https://dev.mysql.com/downloads/repo/yum/今回はCentOS7なので 「Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent), RPM Package」 が対象になる…

MySQLの join を利用するときに on と where ではどちらが速いのか

ちょっと気になったので調べてみた。stackoverflow だと以下が引っかかった。 http://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joinsどっちでも変わらないらしい…

Flyway で ERROR: Unable to scan for SQL migrations in location: classpath

以下のエラーが出た。ERROR: Unable to scan for SQL migrations in location: classpath: xxxx/xxxx/xxxxxconfファイルのsqlディレクトリへのパスが認識できないみたい。結論から言うと、confのパス指定が間違っていた。 以下はconfの説明文を抜粋。 Locati…

Flywayの環境切り分けについて

DBは開発、検証、本番で異なるのでflywayもそれに対応して運用する必要がある。利用するconfファイルはコマンドラインオプションで指定できるので、DBごとにconfを用意して実行することが可能。 また、オプションを指定したコマンドを実行するシェルを利用し…

Flyway と Liquibase の比較

Flyway と Liquibase の比較結論としてはFlywayを利用することにしました。 FlywayよりもLiquibaseの方が多機能であることは間違いない。 個人的には以下がLiquibaseで魅力的だった。1.ロールバックができる。 2.ドキュメント出力できる。 3.DBが変わっても…

楽観的ロックと悲観的ロック

説明用にメモ。【楽観的ロック】 アプリケーションレベルのロックのこと。 テーブルに最終更新日時を管理するカラムや 更新回数を管理するカラムを持てせておき、 コード上で制御する。具体的には・・・・・UPDATE対象のレコードをSELECTして、更新日時を取…

【MySQL】SELECT * (アスタリスク) (全取得)が遅いのか検証する

個人的にDBからデータを引っ張るときに必要なカラムのみを指定するのが嫌い。カラム指定すると 検索条件が同じなのに、 必要なカラムが異なるだけで複数のAPIを実装し、 使う側は適切なAPIを選択しなければならない。 APIの命名にも苦労するし、そもそもDAO…

【MySQL】INSERT SELECT で一部の値を固定にする

メモ。 INSERT INTO t_user_card SELECT null as id, id as m_card_id, name, 1 as card_level, 0 as experience, rare, attack, hp, now() as created_date, now() as modified_date FROM m_card WHERE m_card.id = ?

【MySQL】大量のランダム整数データを生成する

ランダムな整数データが大量に欲しかったので作ってみる。 今回はストアドプロシージャを使う。 1.テーブル定義 これはテキトーに作ればいい。 create table t1 (id int, score int); 2.ランダムデータを生成するINSERT文を用意 テキトーに作ればいいが、バ…

【MySQL】DB内のインデックスの一覧を表示する。

SHOW INDEX ではテーブル単位のインデックスしか表示されないので、 DB毎に表示する。information_schema の statistics というテーブルに格納されているので、 以下のようなSQLを打てばOK。 SELECT * FROM information_schema WHERE table_schema = 'tb_tes…

【MySQL】LEFT JOIN + UPDATE

LEFT JOIN + UPDATEpurchase_group_recordsテーブルに複数紐づくpurchase_recordsテーブルがあって、 purchase_recordsの最新issued_dateをpurchase_group_recordsのissued_dateにセットしたい。purchase_record_groupsのidとpurchase_recordsの最新issued_d…

【MySQL】INTERVAL で日付の加減算

日付の加減算は DATE_ADD(), DATE_SUB() を使うけど、 INTERVAL を使った方が簡単。//1日加算 mysql> SELECT '2014-01-27' + INTERVAL 1 DAY;//1月減算 mysql> SELECT '2014-01-27' - INTERVAL 1 MONTH; 詳しくは公式ページを見るといい。 https://dev.mysql…

【MySQL】CentOS で mysql_safe を起動する

フツーにデーモンで mysqld を起動すると mysql_safe で起動したことになる。 これは /etc/init.d/mysqld を vi か何かで開いて 中身を確認すればすぐに分かる。なので、今まで通り /etc/init.d/mysqld start でOK。MySQLクラッシュしたことないから知らなか…

【MySQL】SELECT の フィールドに IN() を使って、レコードの存在を確認する

存在の確認は EXISTS or NOT EXISTS を使うが、 「存在するものを取得」「存在しないものを取得」するだけで、 どれが存在してどれが存在するのかを一覧で取得することはできない。別に case で書いてもいいんだけど、 今回は SELECT IN() を使う。 以下は全…

【MySQL】Group By を使用した 一時テーブルを where in で利用する

単なるメモ。 ---【2013/11/21 追記】--- 確か IN + サブクエリ は EXISTS に置き換わるんじゃなかったっけ? JOIN とかでやったほうがいいかもね。 - SELECT t1.user_id, MAX(t1.point) AS m FROM tb_test AS t1 WHERE t1.user_id IN (SELECT t2.user_id FRO…

シャーディング時の AUTO INCREMENT

シャーディングする時に MySQL の AUTO INCREMENT は使えないので、 どうしたら実装できるのかなと・・・・。答えは簡単で、 Oracle / PostgreSQL の 「シーケンス」っていう機能をマネすればいい。シーケンスは異なるテーブル間でユニークなIDを管理するた…

【MySQL】日付の ORDER BY で開始時刻を指定する

time型のデータをソートしていたんだけど、 0時〜23時59分ではなく、 5時〜4時59分でソートする必要があった。なので、ORDER BY の開始時刻を指定する必要がある。 こーゆー場合は、 ORDER BY + CASE を使う。 SELECT update_time FROM stores ORDER BY CASE…

【MySQL】ERROR 1129 (HY000): Host '192.168.11.110' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

ジョブサーバからDBサーバ(MySQL)へリモート接続しようとしたら、 以下のエラーが出た。ERROR 1129 (HY000): Host '192.168.11.110' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'これは同じホストから大量の接続エ…

【MySQL】レプリケーション

VirtualBox複数起動でMySQL5.5のレプリケーションを試してみる。【Master設定】 my.cnfのserver-idをSlave重複しない値にする。 お好みで[mysqld]に slave-net-timeout=10 と innodb_doublewrite=0 を設定する。スレーブからマスターにアクセスできるように…

【MySQL】複数列を取得する場合に特定の列を指定して distinct をかける。

MySQLで重複チェックをする時にdistinctを使うことが多い。 ただ、複数列を返して特定列に distinct をかけることはできないので、 結論としては無理。なので、それの代わりになる方法をまとめる。 結構無理やりだけど・・・・。ここでの重複チェックとは、 …

【MySQL】大量データで LIMIT と OFFSET を使うと遅い

1000万件くらいのテーブルがあって、 そこから LIMIT OFFSET でデータを取得するんだけど、 OFFSET の値が大きければ大きいほど、遅くなる。なぜ遅いのか? と思って EXPLAIN を実行したところ、 LIMIT OFFSET は テーブルフルスキャン(ALL) だった・・・。な…

【CentOS】Gearman のジョブを MySQL に保存する

Gearman + PHP でジョブサーバを作ったが、 Gearmanはジョブをメモリに保存するので、 再起動とかするとジョブが消える。 なので、ジョブをMySQLに保存する。他のサイトだと、 libdrizzle とか MySQL-UDF を使ってるけど、 前回インストールしたノーマルGear…

【MySQL】ユニーク制約の削除

ユニーク制約を削除しようとして、 ALTER TABLE CHANGE , ALTER TABLE MODIFY を実行したけど、 削除できない・・・。ユニーク制約を追加すると、 自動的にインデックスができるらしく・・・。 そのインデックスを削除することでユニーク制約が削除できるら…

【MySQL】innodb_log_file_size を編集したら起動しなくなった

innodb_log_file_size を編集したらMySQLが起動しなくなった。 原因は既存のログ・ファイルとサイズが異なるかららしい。 なので、ログファイルを削除してから再起動する。 # rm -f /var/lib/mysql/.err # rm -f /var/lib/mysql/ib* # /etc/init.d/mysqld re…

【MySQL】slow query log が出力されない

以下のように設定し、再起動したが、出力されない。 [mysqld] slow_query_log=ON slow_query_log_file = /var/log/slow.log long_query_time=0エラーログを見ると、 「/var/log/slow.log' not found (Errcode: 13)」 とあった。slow.logを作成し、オーナーを…

【MySQL】テーブルのストレージエンジンを確認する。

テーブルのストレージエンジンを確認する。DBが「db_test」で、テーブルが「tb_test」の場合は 以下のように調べる。 show table status from db_test like 'tb_test' \G; DB内の全テーブルを調べる場合は「like」による検索条件を外せばいい。

【MySQL】ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE は データを INSERT する時に ユニーク制約に違反していれば任意のUPDATEを実行してくれる。日付ごとの何かをカウントするテーブルを更新する場合は以下になる。 (hit_dateにはユニーク制約を付けておく。)INSERT INTO hit_counte…

結果を縦に表示する

MySQLで、SELECTを発行すると、結果が表で出てくるけど、 それを横表示にできる。クエリ発行の後に「\G」を付けるだけ。 「\」はMacだとバックスラッシュになる。 select * from Teams \G; これは見やすい!

【MySQL】COALESCE() を使った null の置換

nullのデータを置換しようとして、 replace() を使ったけど、ダメだった。nullの置換には COALESCE() を使うらしい。 COALESCE() は、与えられた引数のうち、nullでない最初の引数を返す関数。 なので、COALESCE(point, 0) のように使うと、 列名point と 0 …

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つの…