アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

MySQL

日時を扱ういろいろ MySQL版

MySQL。 現在日時の取得 -- YYYY-MM-DD hh:mm:ss SELECT NOW() 月の最終日の取得 -- 2013-06-10 を渡すと 2013-06-30 SELECT LAST_DAY(日付) フォーマットして取得 SELECT DATE_FORMAT(日付, フォーマット) フォーマット部分 %Y 西暦年(4桁) %y 西暦年(2桁) …

文字列のトリム(左端・右端にある特定の文字列を削除 )

以下MySQLで確認していますが、Oracle・PostgreSQLでも同じ書き方である模様。例えば都道府県欄に「○○県」と入っており「県」は除いて表示したい場合など、は以下のようにします。 SELECT TRIM(オプション 削除する文字列 FROM フィールド名) FROM テーブル…

頭文字検索

用語集みたいなやつを作っていて、「あ行」「か行」…「英数字」という頭文字検索があったので、 調べてみたところLIKEかREGEXPが使えそうかなと思ったのですが…LIKE →複雑な正規表現は使用不可 →日本語に対応REGEXP →正規表現使用可 →日本語に非対応でした。…

フィールドの順序を変更する

以下、MySQLで確認。あるテーブルで、フィールドが「field_a」「field_b」がこの順に存在し、これを入れ替えたいときは以下のようにします。 ALTER TABLE テーブル名 MODIFY COLUMN `field_a` 型名 AFTER `field_b`;

テーブルの再構築

MySQLの話。いつもは速いクエリが突然やたらと遅くなったので調べてみると、何故か適切なインデックスが使用されなくなっていた模様。 とりあえずインデックスを一旦削除して張り直すと戻りました。 (FORCE INDEX なんかも知ってはいますが、リリース済みの…

複雑な条件でGROUP BYする

以下MySQLで確認。下記Aのテーブルについて、 「idが4未満と4以上のグループに分けて、それぞれで一番小さいvalueをSELECT」 してBの結果が欲しいとき…(A)test_table id value 1 68 2 15 3 32 4 19 5 73 6 59 (B) 15 19 苦し紛れに以下のようなクエリを書い…

データベースごとのサイズを調べる方法

データ部分、インデックス部分、合計について、それぞれMG単位とGB単位。 SELECT `TABLE_SCHEMA` ,ROUND(SUM(`DATA_LENGTH`) / 1024 / 1024, 2) AS `data_MB` ,ROUND(SUM(`DATA_LENGTH`) / 1024 / 1024 / 1024, 2) AS `data_GB` ,ROUND(SUM(`INDEX_LENGTH`) …

ユニークなインデックスが存在しないテーブルを求める

先日弊社のphpMyAdminを3.x系から4.x系にアップデートしまして 「PRIMARY/UNIQUEのいずれのインデックスも張られていないテーブルのレコードは編集/削除不可」という問題まっとうな仕様変更にぶち当たったので、 そのとき影響範囲の調査に使った 「PRIMARY/U…

テーブルごとのサイズを調べる方法

MySQL。 サイズというか容量というか使用量というか。アレ。参考:MySQLのデータベースやテーブルのサイズを確認する方法 - Little Braver http://little-braver.com/379/ SELECT `TABLE_SCHEMA` -- DB名 ,`TABLE_NAME` -- テーブル名 ,`TABLE_ROWS` -- レコ…

複数のテーブルの構造とデータを簡単にコピーする方法

CREATE TABLE [コピー先テーブル] LIKE [コピー元テーブル]; INSERT INTO [コピー先テーブル] SELECT * FROM [コピー元テーブル]; 以上。具体的にはこんな感じ。 (fugaテーブルをhogeという名前でコピー) CREATE TABLE `hoge` LIKE `fuga`; INSERT INTO `h…

特定のデータベースが存在するかどうか確認する

MySQLにて。クエリ一発で確認できます。 SHOW DATABASES LIKE 'DB名' DB名指定しなければ一覧取れます。 SHOW DATABASES さらに、PHPでユーザ入力値の名前のデータベースが存在するかどうか確認したかったんですが、 // クエリ生成 $query = "SHOW DATABASES…

半角スペースに2種類あって困った話

いや、2種類あること自体は知ってたんですけど。 文字コード32と160ですね。※10進数普通にスペースキー打って出てくるのは32の方です。 これ→「 」 160の方は、キーボードで打つことができるのかどうかは分かりませんが、 これ→「 」(尚、ここでは↓のように…

MySQLで latin1_swedish_ci を utf8_general_ci に変換

MySQLで悩まされた文字化けについて。誰が作ったか分からない旧システムのDBを見ていたら、 日本語(マルチバイト文字)の入ったフィールドが大概文字化けしている…。 それを使ったシステムはちゃんと日本語で表示しているのですが。そのデータを新しく開発…

複数の条件のCOUNT値を取るときのSQL速度検証

まあ前にも似たようなことをやってるんですが 以下のテーブル「table」があるとします。 id a b 1 1 3 2 2 4 3 1 5 4 2 NULL 5 2 NULL ここから、 「aが1であるレコードの数」 「aが2であるレコードの数」 「bが3であるレコードの数」 を取るために、次の3つ…

MySQLでフリーワード検索

おしごとで、フリーワード検索機能を組み込む機会があったのでメモ。 というか当時のメモが見つかったのでリファクタリング。「最終的に何をどう導入したか」しか書いてないので、もうちょっと詳しく知りたい方は以下参考サイトをどうぞ。参考:MySQLで全文…

2つのデータベース間の差分を求めるクエリ

本番環境と開発環境に違いがないか確認するのが面倒でテンプレクエリ作ったのでメモ。 テーブル構成の差分を求める 以下クエリで、「database_1」「database_2」を比較し、 いずれか片方にしかないカラムを取得することができます。 SELECT `schema`.`databa…

「MySQL Cluster」とは

前提 弊社運営のサイトのうち、特定の時期にアクセスが集中するサイトがあるのでなんとか負荷を分散できないか調べたやつ。 結論から言うと不採用になりそうだがせっかくなので調べた内容を残しておく。データベース(というかSQL)は大好物だが、サーバやネッ…

ランダムで取得&速度検証

`table` テーブルよりランダムで1件取得したいという場合、次のように書きます。 SELECT * FROM `table` ORDER BY RAND() LIMIT 1 速度検証 ランダムは遅いと言われますし、 585万件入ったテーブルを使って色々試してみました。 -- ①絞り込みを行わず1件取得…

GROUP句で「n件ごと」にまとめる

ややタイトル詐欺。1000件ごとの統計情報(件数、平均値、合計値…など)を出したいことがあったので以下のようなクエリで解決。 -- ざっくり1000件ごとの件数、平均、合計、最小値、最大値 SELECT MIN(`id`) AS `id_min` ,COUNT(`id`) AS `count` ,AVG(`valu…

NULLの場合に別の値を使用する

NULLを別の値に置き換える方法。 SQLServerとMySQLで異なっていたので両方記載。 -- SQLServer SELECT ISNULL(`hoge`, 0) FROM `table`; -- MySQL SELECT COALESCE(`hoge`, 0) FROM `table`; これで「tableテーブルのhoge列を取得(ただしNULLなら0とする)…

データ処理の件数を制限する

データを1件だけSelectやDeleteしたいときの書き方。SQLServerとMySQLで異なっていたので両方メモ。 -- SQLServer SELECT TOP(1) * FROM `table` ORDER BY `column`; DELETE TOP(1) FROM `table` ORDER BY `column`; -- MySQL SELECT * FROM `table` ORDER B…

トランザクションの挙動検証

トランザクションの挙動を簡単に検証。 MySQL5.6、InnoDBです。 ごく普通のコミット BEGIN; INSERT INTO `table`(`id`) VALUES(NULL); --① INSERT INTO `table`(`id`) VALUES(NULL); --② COMMIT; 当然①②ともにコミットされます。 ごく普通のロールバック BEG…

重複レコードを削除

移転前の記事で書いてたクエリがめちゃくちゃ頭悪かったので完全に書き直し。 やりたいこと テーブル `table` があるとする。 フィールドは `id`, `hoge` の2つで、`id` がプライマリキー。`hoge`の値が一意でない場合、重複しないように余分なレコードを削…

【未解決】ワイルドカードとエスケープ

※この記事では、MySQLのバージョンは5.6です ※以下、「\」となっているのは半角バックスラッシュです。SQLのワイルドカードには「%」と「_」があります。 -- 「hoge」で前方一致検索 SELECT * FROM `table` WHERE `column` LIKE 'hoge%'; -- 任意の1文字+「…

大量のテストデータを作る

WEBアプリ・データベースにデータを作る話。少なくとも自分の場合、テストデータを作るときは大きく分けて下記の3パターンがあります。 ロジックのテスト等の用途で、パターンを網羅したデータを揃える 負荷テスト等の用途で、中身をランダムにしたデータを…

サーバのステータス情報を取得・保存するシェルスクリプト

初めてLinuxのシェルスクリプトを作ってみました。 こういう感じでいいのだろうか。MySQLサーバのステータス情報を取得・保存するシェルスクリプト。 これを定期的に自動実行するようcronにでも登録しておけば 後々解析などがしやすい…はず。下記を参考に…と…

COUNT関数の使い方と速度検証

COUNT関数の基本的な使い方いろいろ -- `table`テーブルの件数(全件) SELECT COUNT(*) AS `count` FROM `table`; -- `table`テーブルの件数(カラム指定) -- この場合、指定されたカラムがNULLでないものをカウント。 -- プライマリキーなど、NULL不可のカ…

HAVING句の使い方と速度検証

「HAVING句は使うな」とよく言われていたので未だに使ったことがないのですが、 なんだかよくわからないまま使わないというのも何なので、今更。そもそもHAVING句とは、 「GROUP句でグループ化した結果を絞り込む」ためのものですね。 各教科の得点平均が○点…

複雑な条件でGROUP BYする

下記Aのテーブルについて、 「idが4未満と4以上のグループに分けて、 それぞれで一番小さいvalueをSELECT」 してBの結果が欲しいとき… (A) test_table --------- id value --------- 1 68 2 15 3 32 4 19 5 73 6 59 --------- (B) -- 15 19 --苦し紛れに以下…