設定 -> 文字コード

Posted by muchag | MySQL | 2015-05-22 (金) 22:39:16

最近の XAMPP に入っている MySQL は、my.ini をいじらなくても
phpMyAdwmin からデータベースを作成する際に
文字コードを指定することで、文字化けをしなかったので
そのままで利用していたけど、久しぶりに文字化けに遭った。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
デフォルト?

何もしてない気がするので、多分下記がデフォルト。

  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | cp932                          |
  7. | character_set_connection | cp932                          |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | cp932                          |
  11. | character_set_server     | latin1                         |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+
my.ini

インストール 1.7.3
以前、こちらの記事で書いたのが基本。

  1. [mysqld]
  2. # 2015-05-22 追記
  3. character-set-server=utf8
  4. skip-character-set-client-handshake
  5.  
  6. [mysqldump]
  7. # 2015-05-22 追記
  8. default-character-set=utf8
  9.  
  10. [mysql]
  11. # 2015-05-22 追記
  12. default-character-set=utf8
以前との変更点

以前 の通りに my.ini をいじったら、MySQL が起動しなくなった。

[ERROR] (xampp)\mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8’

MySQL 5.5 から仕様が変更になったらしい。

[mysqld] については
default-character-set=utf8 ではなく
character-set-server=utf8 と記述することで無事に起動。

  1. [mysqld]
  2. - default-character-set=utf8
  3. + character-set-server=utf8
参考サイト

cyberarchitect:[ERROR] mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8′

my.ini 編集後
  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

むw 1つだけ latin1。

global 値

参考サイトにしたがって、global も覗いてみる。

  1. mysql> show global variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | utf8                           |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

ほー。こちらはちゃんとなっている。

my,ini 設定前の global 値も見ておけばよかった~。

character_set_database
原因

character_set_database だけが値が異なっている原因は・・・

この値はどこを参照しているかというと、create database した時の値です。

よかろうもん!:MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

だそうです。

あれれ。
データベースを作成した時に、文字コードを設定しそこねたのかしら。

確認方法
  1. mysql> show create database `databese1`;
  2.  
  3. +-----------+----------------------------------------------------------------------+
  4. | Database  | Create Database                                                      |
  5. +-----------+----------------------------------------------------------------------+
  6. | databese1 | CREATE DATABASE `databese1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  7. +-----------+----------------------------------------------------------------------+

うほ!確かに。。。

解決方法
  1. mysql> ALTER DATABASE `databese1` default character set utf8;

直った~♪

経緯

問題解決にはあまり関係ないので
記事末に覚え書き用経緯を。

データ情報

駅データ.jp
こちらのサイトから、全国の駅データをいただいてきた。

編集

配布されているデータは、ちゃんと UTF8 なのだけど
データを追加する都合上、Excel で編集してから DB へ流し込んだ。

データが多く、phpMyAdmin からではタイムアウトになってしまったので
コマンドラインから実行。

手順
  1. 配布元よりデータを DL
  2. Terapad で、SHIFT-JIS、CR+LF へ変換
  3. Excel で、データ編集
  4. Excel から CSV 出力
  5. Terapad で、UTF-8N、LF へ変換
  6. コマンドラインより DB へ流し込み

すると、途中から文字化けという事態が起こった。
これはこれで不思議。。。

MySQL | 2015-05-22 (金) 22:39:16 |

CLI -> 基本

Posted by muchag | MySQL,コマンドプロンプト | 2015-05-22 (金) 11:41:07

MySQL のコマンドラインからの操作。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
開始

私は MySQL を XAMPP から利用しているので
MySQL も XAMPP 内にある。

コマンドプロンプトを開いたら、下記3種の利用方法がある。

  • そのまま記述(環境変数を登録している場合)
  • MySQL\bin まで cd
  • フルパスで記述
コマンドラインツールの起動
  1. $ mysql -u ユーザ名 -p

これでパスワードの入力を促されるので
パスワードを入力し、認証が通ったら
ツールを利用できるようになる。

  1. mysql>

プロンプトが上記のようになれば、起動成功。

パスワードも含めて起動
  1. $ mysql -u ユーザ名 -pパスワード

-p オプション指定の直後にパスワードを記述する。
スペースを開けずに記述する ところがミソ。

データベースを指定して起動
  1. $ mysql -u ユーザ名 -D データベース名 -p
データベースの選択
  1. mysql> use データベース名
テーブル一覧表示
  1. mysql> show tables
終了
  1. mysql> quit
  2.  
  3. または
  4.  
  5. mysql> \q
書式
文末

SQL 文の最後に ;(セミコロン)を付けること。
なぜって、下記のようにカスケード記述ができるから?

カスケード記述

長い SQL 文を分割して入力できる。

  1. mysql> SELECT a, b, c, d, e FROM table_a, table_b WHERE table_a.id = table_b.table_a_id AND table_a.id = 1;
  2.  
  3. // 途中で Enter キーを押下すると、継続して SQL 文を記述できる
  4. mysql> SELECT a, b, c, d, e(Enter)
  5.     -> FROM table_a, table_b(Enter)
  6.     -> WHERE table_a.id = table_b.table_a_id(Enter)
  7.     -> AND table_a.id = 1;

というわけで、文末にセミコロンを付けないと、永遠に -> が出てくる。 🙄

メモ
コマンドエイリアス
コマンド エイリアス
quit \q
MySQL,コマンドプロンプト | 2015-05-22 (金) 11:41:07 |

GROUP BY

Posted by muchag | MySQL | 2015-04-05 (日) 9:32:20

レコードのグループ化

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9 SELECT 構文

書式
  1. SELECT * FROM table_name GROUP BY (カラム名)
複数設定
  1. SELECT * FROM table_name GROUP BY (カラム名1), (カラム名2), ...

こうすることで、まず (カラム名1) でグループ化され
その後 (カラム名1) の中で (カラム名2) でグループ化され・・・
ということになる。

挙動

グループ化されたカラム以外の値はどうなるのか。

id amount
101 1
101 2
101 3
  1. SELECT * FROM table_name GROUP BY id

id:101, amount:1 となる。

つまり、先頭行 の値を取ってくる。

最大値、最小値

但し、ちゃんと指定すれば、期待する値を取ることもできる。

  1. SELECT id, MAX( amount ) FROM table_name GROUP BY id

id:101, amount:3 となる。

最小値は MIN( amount )

その他の指定

MySQL:12.19.1 GROUP BY (集約) 関数
最大値、最小値の他には、下記の指定ができる。
合計:SUM( amount ):6
平均:AVG( amount ):2
レコード数:COUNT( amount ):3

併用も可能
  1. SELECT id, MAX( amount ), MIN( amount ) FROM table_name GROUP BY id
それ以外

私の場合は、当該グループの最初と最後のデータが欲しかったので
最初はノーマルに取得。
最後はサブクエリでテーブルを逆順にソートして取得。

ORDER BY との併用

ORDER BY と併用する場合、グループ化された後にソートされる。
つまり! 無意味

あかつきのお宿:MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと

MySQL | 2015-04-05 (日) 9:32:20 |

UNION

Posted by muchag | MySQL | 2015-02-13 (金) 17:28:26

他の記事もそうなんだけど、この記事は特に勉強中で自信がないので、内容が間違っている可能性大。 8-X

【環境】
MySQL:5.5.27

MySQL:13.2.9.4 UNION Syntax

概要

クエリ結果が同一構成となる2つ以上のクエリ結果を合成する。
 
JOIN による結合と違って、リレーションしているわけではない。
単に2つ以上のクエリ結果を1つのテーブル上にする。
 

注意
  1. それぞれのクエリが同カラム数であること
  2. 各カラムが同じ順番に並んでいること
  3. 各カラムのデータ型が同一であること
  4. 場合によって、メイン SELECT の部分にも()が必要
  5. FROM 句サブクエリ内で利用する場合は、サブクエリ全体にエイリアスをつけること

 

基本
  1. SELECT id, name, created_at FROM table_a
  2.     UNION SELECT id, name, modified_at FROM table_b

2つの SELECT がある。
カラム数は同じく 3。
カラム名は異なっていてもよい。→メインの SELECET 文のカラム名が返る。
2015-02-13_UNION_1

 

重複

この項は勉強、実証ともに特に不十分

UNION は、そのまま使うと重複した行は無視されるらしい。

重複した行を拾いたいときは、下記のように ALL をつける。

  1. SELECT id, name, created_at FROM table_a
  2.     UNION ALL SELECT id, name, modified_at FROM table_b

 

注文

こうして合成されたクエリ結果に ORDER BY, LIMIT などの注文を付けたい場合は、更にその後ろに続ける。
そして、SELECT 文を()で括る

  1. ( SELECT id, name, created_at FROM table_a )
  2.     UNION ( SELECT id, name, modified_at FROM table_b )
  3.     ORDER BY id

 

FROM 句

FROM 句にて UNION を利用するときは、エイリアスを付ける。

  1. SELECT id, name, created_at
  2.     FROM (
  3.     SELECT id, name, created_at FROM table_a
  4.     UNION SELECT id, name, modified_at FROM table_b
  5.     ) as sub

このとき、UNION SELECT 文にエイリアスを付けてしまわないように注意。
あくまでも FROM 句全体 に()を付ける。
 
これはサブクエリのルールだから当然か~。
サブクエリ

MySQL | 2015-02-13 (金) 17:28:26 |

サブクエリ

Posted by muchag | MySQL | 2015-02-13 (金) 1:38:03

まだおぼろげにしか捉えられていないけど、メモ。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.10 Subquery Syntax

概要

SQL 文の中に、独立した SQL 文を入れ込む。

  1. SELECT *
  2.     FROM hoge
  3.     WHERE user_id = ( SELECT id FROM user WHERE name = "田中" )

例えば、こんな風に書くと、user テーブルで田中さんの id を突き止め
hoge テーブルにある、田中さんの情報だけを取り出すことができる。 😎
 

使い方
ルール

サブクエリは()で括りましょう。
 

SELECT 句

言葉でうまく説明できないので、参考サイト参照。

でも、こういう使い方ができると便利だね~。
 

FROM 句

本来テーブル名を書くはずの FROM 句。
ここにサブクエリを置くことで、クエリをテーブルとして扱うことができる。
 

ルール

必ずエイリアスをつけましょう。
 
私は見事にはまりました。 👿
サブクエリ -> エイリアス
 

WHERE 句、HAVING 句

クエリ結果を条件に設定することができる。
 
まだまだこの程度しかわかっていないので
また判明したことが出てきたら追記!

相関サブクエリ

メインクエリとサブクエリに共通のレーブルが登場するタイプ?
MySQL は苦手とするみたい。
 

参考サイト

Smart:SELECT構文:サブクエリ構文

TECHSCORE:7. サブクエリ

MySQL | 2015-02-13 (金) 1:38:03 |

サブクエリ -> エイリアス

Posted by muchag | MySQL | 2015-02-13 (金) 1:21:21

FROM 句に入れるサブクエリではまった。

【環境】
MySQL:5.5.27
現象
  1. SELECT * FROM ( SELECT a, b RROM hoge );

この SQL 文が通らない。

  1. SELECT a, b RROM hoge

これが通っていることは確認済みなのに、何でか通らない。
 

原因

サブクエリにエイリアスがついていなかった。
FROM 句内でサブクエリを使っているサイトを見ていると
どれにもエイリアスがついているので、もしや・・・と思い試してみたらビンゴ。

どこにも使わないから、エイリアスをつけてなかったんだけど
それが原因でした。

MySQL | 2015-02-13 (金) 1:21:21 |

カラムの演算 -> 負の数

Posted by muchag | MySQL | 2015-02-13 (金) 0:54:53

負の数の結果を得る SQL が通らなくて難儀した。

【環境】
MySQL:5.5.27
現象

あるカラムの値を負の数として取得したくて、下記のような SQL を投げた。

  1. SELECT num * -1 FROM hoge;

しかし、これが通らない。

  1. SELECT num - 100 FROM hoge;

これもだめ。
 

原因

num カラムの属性を unsigned にしていたことにあったようだ。
属性を外したら結果を取得できた。

属性が unsigned だと、演算結果が負の数になるような演算を受け付けてくれないみたい。
テーブルの属性が、クエリにも影響するとは思わなかった。
エイリアスまでつけたのにな~。 😯

MySQL | 2015-02-13 (金) 0:54:53 |

GROUP BY -> WITH ROLLUP

Posted by muchag | MySQL | 2015-02-12 (木) 22:59:00

素敵な集計オプション。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.17.2 GROUP BY Modifiers

効能

集計の集計をしてくれる。
 

2015-02-12_WITH ROLLUP_1これが一覧表。

 
2015-02-12_WITH ROLLUP_2WITH ROLLUP をつけると、こういう風に集計できる。
黄色い網掛けは、国別集計。
緑の網掛けは、年度集計。
赤の網掛けは、全体集計。
素晴らしい!

 

参考サイト

FLATZ:MySQLの知っていると便利な構文(その2)

MySQL | 2015-02-12 (木) 22:59:00 |

Date and Time

Posted by muchag | MySQL | 2015-02-12 (木) 21:50:26

日付関数は便利~! 😎
MySQL の勉強は遅れてしまってるにゃ><

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.7 Date and Time Functions
 
DATE, TIME, DATETIME, TIMESTAMP, YEAR 型のフィールドに対して使える関数。

YEAR, MONTH, DAY, DAYNAME

YEAR(date):年だけ返す
MONTH(date):月だけ返す
DAY(date):日だけ返す
DAYNAME(date):曜日だけ返す
※ date は日付型のカラム名
 

  1. mysql> SELECT YEAR('1987-01-01');
  2.         -> 1987
  3.  
  4. mysql> SELECT MONTH('2008-02-03');
  5.         -> 2

これを hoge テーブルに使うと

  1. mysql> SELECT YEAR(date) as year, num FROM hoge;
year num
2014 3
2014 2
2015 6

これだと、だから? ってなっちゃうけど、集計を考えるとすぐわかる。
 

集計

年別の個数集計は、下記のように取れる。

  1. mysql> SELECT YEAR(date) as year, SUM(num) FROM hoge GROUP BY YEAR(date);
year num
2014 5
2015 6

素敵~♪
 

DATE_FORMAT

MySQL:DATE_FORMAT(date,format)
日付データをフォーマットして返す

  1. SELECT DISTINCT DATE_FORMAT( date, "%Y-%m" ) FROM table

これで date カラムのデータを YYYY-MM というフォーマットへ変換し
重複行を除いて取り出してくれる。

フォーマット指定方法については、マニュアル参照。
 

EXTRACT

MySQL:EXTRACT(unit FROM date)
EXTRACT():指定した部分だけ返す

YEAR なんかの複合型。

  1. mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
  2.        -> 2009
  3. mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
  4.        -> 200907
  5. mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
  6.        -> 20102
  7. mysql> SELECT EXTRACT(MICROSECOND
  8.     ->                FROM '2003-01-02 10:30:00.000123');
  9.         -> 123

これもすごい~!
 

参考サイト

FLATZ:MySQLの知っていると便利な構文(その2)
→2007年の記事なのに、検索の2番目に出てきた。すごいね~。ありがとうございます!

MySQL | 2015-02-12 (木) 21:50:26 |

INSERT…ON DUPLICATE KEY UPDATE

Posted by muchag | MySQL | 2015-02-09 (月) 16:07:51

ON DUPLICATE KEY UPDATE という、すごく便利な命令を随分前に知って使っていたけど、まだまだ奥が深かったので、メモ。

【環境】
[MySQL] 5.5.27
基本
  1. INSERT INTO table_name (id, name)
  2.     VALUES (val1, val2)
  3.     ON DUPLICATE KEY UPDATE name = val2;

これで
・新しいレコードを挿入してね
・値は、id が val1 で、name は val2 でお願いね
・でももし val1 という値が id に既にあったら、そのレコードの name を val2 で上書きしてね
というお願いになる。
便利だよね~。
事前に存在を確認して、あれば UPDATE、なければ INSERT を発行する、という手間がなくなった。
 

DUPLICATE KEY

ここで「でももし val1 という値が id に既にあったら」この部分について。

特定のキーとは、Uniqueキーとして登録してあるか、Primary Keyとして登録してある必要がありますが、1つのカラムに限らず、複数のカラムでも一つの Uniqueキー(Primary Key)の設定を行うことで対応が可能です。
 
ただ、Uniqueキーの設定が必要ですので、設定するカラムで複数レコードが存在するような仕組みの場合は利用することはできません。

— snip —

「Unique」は、nullは重複しないと見なします。
「Primary Key」は、nullは重複するとみなすため、nullを入れようとするとエラーになります。

有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

ふむふむ。キー周りの知識が怪しいので、ひとまずはこれにて。
 

式も入れられる

参考サイトを読み取っただけで、実証していない。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = val3;

このとき

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + VALUES(val3);

このようにすることで、id が被った場合は、point に val3 を上書きするのではなく、元の値に val3 を加算して上書きする。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + 10;

こうすると、元の値に 10 を加算して上書きする。(新しい値 val3 は無視)
すごい。。。でも、少し複雑になってきた。
 

条件式

CASE や IF も使えるらしい・・・。
すぐにはわからないので、丸コピー。

DUPLICATE KEY UPDATE にはCASEやIFが使えるので、フィールド毎に更新条件を設定できる。
デフォルト値を使って値を更新されないようにもできる。

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ

  1. INSERT INTO
  2.        table (user_id, fb_like, tweet, updated)
  3.    VALUES
  4.        (1, 12, 10, NOW()), (2, 8, 9, NOW())...()...,(1000, 42, 30, NOW())
  5.    ON DUPLICATE KEY UPDATE
  6.        fb_like = IF(fb_like > VALUES(fb_like), fb_like, VALUES(fb_like))
  7.        , tweet = IF(tweet > VALUES(tweet), tweet, VALUES(tweet))
  8.         , updated = NOW()

 

プリペアドステートメント

プリペアドステートメントを利用する場合は、下記のように記述。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (?,?,?)
  3.     ON DUPLICATE KEY UPDATE name = VALUES(name), point = VALUES(point);

 

REPLACE

同じ結果を得られるものとして、REPLACE 文がある。
両者の違いは、下記の通りらしい。

ON DUPLICATE KEY UPDATE:UPDATE
Replace:DELETE & INSERT

 

参考サイト

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ
極上の人生:MySQL でプレースホルダを使ったINSERT … ON DUPLICATE KEY UPDATE 文を書く
有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

MySQL | 2015-02-09 (月) 16:07:51 |
« 前ページへ次ページへ »