MySQLでVIEWを作成したが空になる

Question

###前提・実現したいこと
SQL ゼロからはじめるデータベース操作を読みながらSQLを勉強している初心者です。現在ビューの作成をしているのですがうまく行っていません。問題のCREATE VIEW文を実行する前に、TABLEとVIEWを作成しているので、それらを先に示します。

TABLE Shohin:

+-----------+----------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei     | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+----------------+---------------+--------------+--------------+------------+
| 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20 |
| 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11 |
| 0003      | カッターシャツ | 衣服          |         4000 |         2800 | NULL       |
| 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20 |
| 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15 |
| 0006      | フォーク       | キッチン用品  |          500 |         NULL | 2009-09-20 |
| 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28 |
| 0008      | ボールペン     | 事務用品      |          100 |         NULL | 2009-11-11 |
+-----------+----------------+---------------+--------------+--------------+------------+

VIEW ShohinSum:

CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS
SELECT shohin_bunrui, count(*)
FROM Shohin
GROUP BY shohin_bunrui;

###発生している問題
次のCREATE VIEW文でビューを定義したのですが、

CREATE VIEW ShohinSumJim (shohin_bunrui, cnt_shohin) AS
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui = '事務用品';

mysql> SELECT * FROM ShohinSumJim;としても

Empty set (0.00 sec)

と言われてしまいます。

###環境
Windows7にてxamppを使ってインストールしたMySQLを使っています。

• MySQL: Server version: 5.6.25 MySQL Community Server (GPL)
• 文字コード: cp932 (Shift JIS)

###自分で調べたこと
VIEWの定義にあるSELECT文

SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui = '事務用品';

では

+----------------+-------------+
| shohin_bunrui | cnt_shohin |
+----------------+-------------+
| 事務用品 | 2 |
+----------------+-------------+

となり、空ではありません。このことからWHERE句には問題がないように見えます。

---

VIEWの定義にあるSELECT文の中のWHERE句の等号を不等号に変える。

CREATE VIEW ShohinSumJim (shohin_bunrui, cnt_shohin) AS
SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui <> '事務用品';

この場合はmysql> SELECT * FROM ShohinSumJim;で

+----------------+-------------+
| shohin_bunrui | cnt_shohin |
+----------------+-------------+
| キッチン用品 | 4 |
| 事務用品 | 2 |
| 衣服 | 2 |
+----------------+-------------+

となり、VIEW ShohinSumがそのまま取り出されています。
これは先程の結果とは異なり、WHERE句に問題があるように見えます。

---

先ほどのVIEWの定義にあるSELECT文を実行。

SELECT shohin_bunrui, cnt_shohin
FROM ShohinSum
WHERE shohin_bunrui <> '事務用品';

+----------------+-------------+
| shohin_bunrui | cnt_shohin |
+----------------+-------------+
| キッチン用品 | 4 |
| 衣服 | 2 |
+----------------+-------------+

今度は不等号の処理が正しく行われているように見えます。

###最後に
SQLを勉強し始めたばかりなので、勝手がわからず困っています。
何かご存知であればよろしくお願いします。

###追記
eripongさんから”show create viewの結果はどうなりますか？"という情報の追加依頼がありました。結果はすごい横長の1行のテーブルだったので、少し直して書きます。

View:
  shohinsumjim
Create View:
  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `shohinsumjim` AS select `shohinsum`.`shohin_bunrui` AS `shohin_bunrui`,`shohinsum`.`cnt_shohin` AS `cnt_shohin` from `shohinsum` where (`shohinsum`.`shohin_bunrui` = '闔 蜿･貍?€包ｽｨ陷ｩ')
character_set_client:
  cp932
collation_connection:
  cp932_japanese_ci

このCreate View列のwhere (shohinsum.shohin_bunrui = '闔 蜿･貍?€包ｽｨ陷ｩ')が文字化けしているからテーブル内の「事務用品」と一致せずEmpty setと言われてしまうのでしょうかね。

###追記 - まとめ
eripongさんの回答と、その中で教えてくださったリンク「10.1.12. メタデータ用の UTF-8 」を参考にして、スクリプトファイルもしくはコピペ元の文字コードをUTF-8にすることで解決しました。その際に調べたこと、分かったことを軽くまとめておこうと思います。

####原因
今回発生した問題はmysql> SHOW VARIABLES LIKE 'character\_set\_%';で確認できるメタデータと呼ばれる文字セットの不一致が原因だと思われます。MySQLクライアントを開始時には次のように変数がセットされていました。

mysql> show variables like 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
+--------------------------+--------+

この時点では文字セットはutf8で統一されているのですが、set names cp932;もしくはCHARSET cp932;で文字コードを変えると、

mysql> show variables like 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | cp932  |
| character_set_connection | cp932  |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | cp932  |
| character_set_server     | utf8   |
| character_set_system     | utf8   |
+--------------------------+--------+

となって一部はcp932に変更されますが、全てではないようです。この後のどの操作がどの変数を使っているのかまでは理解していないのですが、character_set_databaseが一致していないのはよくなさそうですね。

####対策
一つはcp932を使わずにutf8を使うこと。コマンドプロンプトから直接入力したりコピペするとやはりShift-JISだからうまく表示はさません。しかしutf8で書いたスクリプトファイルにまとめて実行するとちゃんと表示されるようです。しかしコマンドプロンプト上で簡単に確認したい場合はとても面倒になりますね。

もう一つは上の変数をcp932に統一すること。私はWindows7でXAMPPを利用してインストールしたMySQLを利用しています。C:\xampp\mysql\bin\my.iniが設定ファイルのようですが、それを変えることでcp932に統一できればと思います。まだここについてはよく調べていないので、本当にうまくいくかはわかりません。

いずれにせよ今は練習段階で簡単に触れる用にWindows上でやっていますが、将来的にはLinux上で扱うことになると思うので、その場合はmy.confを設定してutf8で統一してできるようになりたいと思います。

####最後に
初めての質問投稿でこんなにも多くの回答をいただけるとは思いませんでした。皆様大変有難うございました。

Comments

[eripong]

show create viewの結果はどうなりますか？

Answer 1

あまり詳しくは無いので、推測混じりですが、
10.1.12. メタデータ用の UTF-8
を見ると、VIEWの定義データなどが格納されるINFORMATION_SCHEMAはUTF-8なので、
cp932でVIEWを作ると、文字化けしてしまうのではないでしょうか？

charsetコマンドなどで、一時的に文字コードをutf8にしてVIEWを作るとどうでしょうか？

 CHARSET utf8

Comments

[Ushiki]

今までは文字セットを指定するには、`SET NAMES ~;`を使うものだと思っていたのですが、その部分を`CHARSET utf8;'に変えてスクリプトファイルもUTF-8で保存して実行したらうまくいきました！
またWindowsでコマンドプロンプトを通して使う場合はcp932もしくはShift-JISしか使えないと勘違いしていたのですが、そういう訳ではないのですね。
同様に今までどおりスクリプトファイルをShift-JISで記述し、`CHARSET cp932;`でもう一度やり直したらやはりうまく行きませんでした。`SHOW VARIABLES LIKE 'character_set%';`で出てくる変数が鍵のようですね。少しまとめて記事本文内に追記できたらと思います。

大変有難うございました。

[eripong]

うまくいって良かったですね。
set namesでも同じと思います。

ただ、create viewするときだけ、utf8にして、selectするなど他の操作はcp932で行うと、うまくいくのでは？
と言いたかったのですが、伝わっているでしょうか？

[Ushiki]

すみません、ご指摘の部分勘違いしていました。
今コメントのようにしてみたところ、CREATE VIEW文だけをutf8にしてうまくいきました。

なるほど、情報の追加依頼であった「show create view」で表示されたのがINFORMATION_SCHEMAに保存されていたVIEWの定義文で、それがutf8で記述されているので、CREATE VIEW文を書くときはutf8じゃないといけないということですね。

何度もありがとうございました。

[eripong]

あ、やはり伝わって無かったですね。
分かりにくくすみません。
何にしてもうまくいって良かったです。

そうですね。
動作を見ると、shohin_bunruiの条件として間違った文字列が
指定されているような動作だったので、文字化けを疑って
「show create view」をお願いしました。
実際に文字化けしていたので、
viewの定義が保存されているところの文字コードがcp932ではないのだろうと
推測し、調べて回答した、という流れです。

Answer 2

SELECT文とCREATE文は同じところから入力してますか？
CREATE文だけが'事務用品'を'事務用品'として認識していないように見えます。
'事務用品'ではなく、'衣服'や他のものでも同じ現象になるのなら、
SQLを書いているシェルなどの文字コードが怪しい気がします。

Comments

[Ushiki]

コメントありがとうございます。

'衣服'に対してやってもEmpty setとなってしまいました。
私は一度エディタAtomに命令文を書き下して、コンソールにコピーペーストすることで実行していました。テーブル作成もCREATE VIEWもSELECT * FROM ShohinSumJimも全部同様にやっていたので、どこでずれてしまったのかはわかりません。
しかし先ほど記事本文に追記したように、SHOW CREATE VIEWの結果に文字化けが見られたので、それが原因なようですが・・・

[kokardy]

貼り付けた段階では文字化けはしてませんか？
マニュアルによると
「 SQL ステートメントは次のように、スクリプトファイル (バッチファイル) 形式で実行できます。
shell> mysql db_name < script.sql > output.tab」
だそうなので
ファイルにcp932でsqlを書いて保存して試してみてください。

[Ushiki]

kokardyさんコメントありがとうございます。

貼り付けた段階での文字化けは確認できませんでした。
スクリプトファイルにテーブルの作成から始めて実行してみたのですが、やはりうまくは行きませんでした。
一応その際に使ったスクリプトファイルと結果のテキストをGitHubにあげてみたので、お時間があればご覧ください。
->https://github.com/Ushiki/CheckMySQLCreateView

Answer 3

これで良いのではないでしょうか？

CREATE VIEW ShohinSumJim
  AS SELECT shohin_bunrui, count(*)
     FROM Shohin
     GROUP BY shohin_bunrui;

要するに、AS の前の (shohin_bunrui, cnt_shohin) が不要だと思います。

Comments

[Ushiki]

pi-chanさん早速のコメントありがとうございます。

確かに今回の場合はビューを作るときに新しいフィールド名を付けているわけではないので、[CREATE VIEW構文](http://dev.mysql.com/doc/refman/5.6/ja/create-view.html)で言うところの`[(column_list)]`は必須ではないです。
ですがそれがビューがemptyとなる理由とは思えません。
今問題となっているのはWHERE句だと思います。

試しにpi-chanさんの提示されたCREAT VIEW文に`WHERE shohin_bunrui = '事務用品'`を追加してやってみました。
```MySQL
CREATE VIEW ShohinSumJim
AS SELECT shohin_bunrui, count(*)
FROM Shohin
WHERE shohin_bunrui = '事務用品'
GROUP BY shohin_bunrui;
```
これでもビューは空でした。

[pi-chan]

「WHERE shohin_bunrui = '事務用品'」の結果が欲しいなら、「GROUP BY shohin_bunrui;」は不要では！？
矛盾する条件を指定しているので空になるのだと思いますが、私の勘違いでしょうか…

[Ushiki]

初心者で返答が要領を得ないものになってしまっているかもしれませんが、お付き合い頂きありがとうございます。

仰るとおりで、「WHERE shohin_bunrui = '事務用品'」を入れたので、GROUP BYは不要でした。しかし矛盾はしていないと思います。
WHEREをした時点で'事務用品'のレコードだけが選ばれます。そこでGROUP BYをしても元々'事務用品'だけだったものが1つのグループになると思います。CREATE VIEW文のSELECT以降を単独で実行すると、
+----------------+-----------+
| shohin_bunrui | count(*) |
+----------------+-----------+
| 事務用品 | 2 |
+----------------+-----------+
となって空にはなりませんでした。

[pi-chan]

矛盾とは、全く不適切な表現でした、申し訳ありません。
そもそもWindows環境での問題であることを見落とし的外れな回答になっていました。重ねてお詫び致します。

ところで、Windows環境でUTF-8を扱うのは厄介ですが、せっかく頑張ってプログラミングの勉強をされているのですから、ついでに「Git for Windows」をインストールして活用されてはいかがですか？
というのは、仮にGitを使わないとしても、Git をインストールすると「Git bash」というUnixライクなシェル環境が使えるようになるので、UTF-8で作業しても文字化けが発生せず便利です。
> http://d.hatena.ne.jp/m-hiyama/touch/20140203/1391381365
ついでにGitの使い方も習得出来ればなお結構ですし。
以上、ご参考まで。

[Ushiki]

返信が遅れてしまい申し訳ありません。新規の回答はメールで通知があるようですが、それに対するコメントでは来ないようですね。

コメントありがとうございます。いえいえ自分一人では早々に諦めて、悩んでいたことも忘れていたと思います。
「Git for Windows」ですが、以前にGitをドットインストールあたりで勉強した時にインストールしていました。ですが、一人でプログラミングしている分には、Gitのありがたさが余り感じられず最近は使っておらず、勉強したことも忘れてしまいました。
pi-chanさんに言われて久しぶりにGit Bashを起動してみましたが、bashコマンドが使えたり、キーバインディングもvi風なのが使えたりと便利そうですね。
私は最近インストールしたConEmuというコンソールエミュレータを使って、その上でコマンドプロンプトを使っていたのですが、それにもデフォルトでGit Bashが使える状態になっていたので、これから活用する道を探ってみたいと思います。

色々と教えてくださってありがとうございました。