MySQLのSQLクエリーだけで棒グラフを表示する方法

MySQLの出力結果を棒グラフで表示できたらいいなぁと思っていろいろ実験していたらうまくいってしまったのでご紹介してみます。

【普通の場合】
普通は以下のようになりますよね。出力結果だけ見てもどうもよくわかりません。

■SQL文

mysql> select date_format(regdate, "%Y/%m/%d %H") date, count(*) count from usert group by date order by date asc;

■出力結果

+---------------+-------+
| date　　　　　| count |
+---------------+-------+
| 2010/11/01 00 |　 405 |
| 2010/11/01 01 |　 276 |
| 2010/11/01 02 |　 188 |
| 2010/11/01 03 |　 148 |
| 2010/11/01 04 |　　96 |
| 2010/11/01 05 |　　63 |
| 2010/11/01 06 |　　85 |
| 2010/11/01 07 |　 142 |
| 2010/11/01 08 |　 164 |
| 2010/11/01 09 |　 137 |
| 2010/11/01 10 |　 177 |
| 2010/11/01 11 |　 177 |
| 2010/11/01 12 |　 248 |
| 2010/11/01 13 |　 178 |
| 2010/11/01 14 |　 148 |
| 2010/11/01 15 |　 158 |
| 2010/11/01 16 |　 195 |
| 2010/11/01 17 |　 213 |
| 2010/11/01 18 |　 243 |
| 2010/11/01 19 |　 236 |
| 2010/11/01 20 |　 274 |
| 2010/11/01 21 |　 310 |
| 2010/11/01 22 |　 309 |
| 2010/11/01 23 |　 280 |
+---------------+-------+

【棒グラフの場合】
そして今度は出力結果を棒グラフにした場合の例になります。ものすごくわかりやすくなりました。

■SQL文

mysql> select date_format(regdate, "%Y/%m/%d %H") date, REPEAT("#", count(*)/10) graph from usert group by date order by date asc;

■出力結果

+---------------+-------------------------------------------+
| date　　　　　| graph　　　　　　　　　　　　　　　　　　 |
+---------------+-------------------------------------------+
| 2010/11/01 00 | ######################################### |
| 2010/11/01 01 | ############################　　　　　　　|
| 2010/11/01 02 | ###################　　　　　　　　　　　 |
| 2010/11/01 03 | ###############　　　　　　　　　　　　　 |
| 2010/11/01 04 | ##########　　　　　　　　　　　　　　　　|
| 2010/11/01 05 | ######　　　　　　　　　　　　　　　　　　|
| 2010/11/01 06 | #########　　　　　　　　　　　　　　　　 |
| 2010/11/01 07 | ##############　　　　　　　　　　　　　　|
| 2010/11/01 08 | ################　　　　　　　　　　　　　|
| 2010/11/01 09 | ##############　　　　　　　　　　　　　　|
| 2010/11/01 10 | ##################　　　　　　　　　　　　|
| 2010/11/01 11 | ##################　　　　　　　　　　　　|
| 2010/11/01 12 | #########################　　　　　　　　 |
| 2010/11/01 13 | ##################　　　　　　　　　　　　|
| 2010/11/01 14 | ###############　　　　　　　　　　　　　 |
| 2010/11/01 15 | ################　　　　　　　　　　　　　|
| 2010/11/01 16 | ####################　　　　　　　　　　　|
| 2010/11/01 17 | #####################　　　　　　　　　　 |
| 2010/11/01 18 | ########################　　　　　　　　　|
| 2010/11/01 19 | ########################　　　　　　　　　|
| 2010/11/01 20 | ###########################　　　　　　　 |
| 2010/11/01 21 | ###############################　　　　　 |
| 2010/11/01 22 | ###############################　　　　　 |
| 2010/11/01 23 | ############################　　　　　　　|
+---------------+-------------------------------------------+

急いでいる人のためのMySQLのユーザ権限付与講座

MySQLでは細かいレベルの権限付与が可能ですが、大抵の場合そこまで細かいレベルの権限付与は必要ないですよね？ マニュアルを読まないか、もしくはちょっとしたメモ書きを見る程度でおおよそ使い方が理解できるくらいならいいのに、といつも思います。

そこで今回は、MySQLのユーザ権限付与の中でも、とりわけよく行われる手順だけを簡単にまとめてみました。

【まず知っておいたほうが良いこと】
ユーザはmysqlデータベース内のuserテーブルに作られます。

次に例えば以下のユーザの情報を見てみると「_priv」系のカラム値が全部「Y」であることがわかります。この場合はグローバルレベル権限として全部「Y」なので、全てのテーブルに対して接続が可能となります。

mysql> select * from user where User='adminuser' \G
　
*************************** 1. row ***************************
                 Host: %
                 User: adminuser
             Password: 136b4c537575b6f1
          Select_priv: Y      ←
          Insert_priv: Y      ←
          Update_priv: Y      ←
　
～～～中略～～～
　
  Create_routine_priv: Y      ←
   Alter_routine_priv: Y      ←
     Create_user_priv: Y      ←
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

一方、以下のユーザの情報を見てみると「_priv」系のカラム値が全部「N」であることがわかります。

mysql> select * from user where User='testuser' \G
　 *************************** 1. row ***************************
                 Host: localhost
                 User: testuser
             Password: 136b4c537575b6f1
          Select_priv: N      ←
          Insert_priv: N      ←
          Update_priv: N      ←
　
～～～中略～～～
　
  Create_routine_priv: N      ←
   Alter_routine_priv: N      ←
     Create_user_priv: N      ←
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

この場合は、別途ユーザが接続するデータベースを「db」テーブルに設定します。

mysql> select * from db where User='testuser' \G
　
*************************** 1. row ***************************
                 Host: localhost
                   Db: testdatabase
                 User: testuser
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
　
～～～中略～～～
　
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
1 row in set (0.00 sec)

普段意識すべきはこのあたりまでだと思いますが、さらに細かい設定を検討する場合は以下もご参照下さい。

※参照:mysqlデータベース内のテーブル一覧

データベース名	説明
user	グローバルレベルの権限とパスワードを管理するテーブル
db	データベースレベルの権限を管理するテーブル
host(今回は考えない)	dbテーブルにホスト名が指定されていない場合に適用される権限を管理するためのテーブル
tables_priv(今回は考えない)	テーブルレベルの権限を管理するテーブル。テーブル内のすべてのフィールドに適用される権限について格納。
columns_priv(今回は考えない)	フィールドレベルの権限を管理するテーブル。テーブル内の一つのフィールドに適用される権限について格納。

これをふまえて。

【1.管理者用ユーザを作りたい】
いわゆる何でもできる管理者用ユーザは以下の要領で作ります。

GRANT ALL ON *.* TO adminuser@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

※MySQLにはGRANT権限というものがあります。GRANT権限とは、他のユーザに対して権限を付与することができる権限のことです。当然のことながらGRANT権限は通常管理者用ユーザにか付与しません。

IP制限をかける場合は以下の要領です。

GRANT ALL ON *.* TO adminuser@'172.16.0.0/255.255.255.0' IDENTIFIED BY 'password' WITH GRANT OPTION;

【2.一般ユーザを作りたい】
全てのデータベースにアクセスできる一般ユーザは以下の要領で作ります(WITH GRANT OPTIONがないのに注目)。ただし全てのデータベースにアクセスできる一般ユーザをGRANT ALL ON *.*で作ると、SUPER権限がつくのでinit_connectが無視されるなどの副作用があるそうです。(sh2さんご指摘ありがとうございました)

GRANT ALL ON *.* TO testuser@'%' IDENTIFIED BY 'password';

特定のデータベースにアクセスできる一般ユーザは以下の要領で作ります。

GRANT ALL ON testdatabase.* TO testuser@'%' IDENTIFIED BY 'password';

SELECT,INSERT,UPDATE,DELETEしかできない一般ユーザは以下の要領で作ります。

GRANT SELECT,INSERT,UPDATE,DELETE ON testdatabase.* TO testuser@'%' IDENTIFIED BY 'password';

【3.レプリケーション用ユーザを作りたい】
MySQLの大きな特徴であるレプリケーションを行う場合、MASTER DBに以下の要領でユーザを作ります。

GRANT REPLICATION SLAVE ON testdatabase.* TO repl@'172.16.0.0/255.255.255.0' IDENTIFIED BY 'password';

【最後に】
柔軟な権限設定ができるシステムは多いですが、概してどれも設定が複雑になるんですよね。しかし前提知識として今回の内容程度を押さえておけば、日常運用程度ではなんとかなるし、応用も利くようになるのではないでしょうか。

いろいろなデータベースのバージョンの調べ方

いろいろなデータベースのバージョンの調べ方をまとめてみました。（追加すべき情報がありましたら情報提供をお待ちしております)

■MySQL
select version();

■PostgreSQL
select version();

■Oracle
select BANNER from SYS.V_$VERSION;

■SQL Server 2005
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server 2005 SP3: 2005.90.4035
SQL Server 2005 SP2: 2005.90.3042
SQL Server 2005 SP1: 2005.90.2047

■SQL Server 2000
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server 2000 SP4: 2000.8.00.2039
SQL Server 2000 SP3a: 2000.80.760.0
SQL Server 2000 SP3: 2000.80.760.0
SQL Server 2000 SP2: 2000.80.534.0
SQL Server 2000 SP1: 2000.80.384.0

MySQLのデータをシフトJISからUTF-8に変換した際の記録

今回のエントリーは、先日Shift JISで作っていたデータを多言語対応にすべくUNICODEに変換しようとしたとき苦労した際の記録です。

今回対象としたのは、MySQLからdumpした100MB近くあるデータです。

【一通りの手順】
まずMySQLからデータをdumpします。

% mysqldump [database name] > dump.sql

次にdump.sqlをmoreしてみると「DEFAULT CHARSET=sjis」という記述があるのでこれをutf8に変更します。

% perl -p -w -e 's/sjis/utf8/g' dump.sql > dump2.sql

ただいくらDEFAULT CHARSETをutf8に変えても実際のデータがシフトJISなので、データ自体もUTF-8に変えます。

% iconv -c -f shift_jis -t utf-8 dump2.sql > dump3.sql

こうして完成したデータをMySQLに読ませてみてエラーが起きなければとりあえず作業完了です。

% mysql < dump3.sql

手順をまとめてしまうとまぁ上記の通りなんですが、文字コードをシフトJISからUTF8に変えてからMySQLに読み込ませてみると通らないという点非常に苦労しました。いろいろなところで説明されているようにシフトJISからUTF8に変換する際100%全く同じに変換できない、というのがその理由です。いろいろな方が何かしらの工夫で100%に近づけるよう努力してくださった成果がiconvやnkfなどの変換コマンドなわけですが、それらのコマンドを用いてもどうもSQL文の実行に影響ある箇所で文字化けが起こってしまっていたようです。データ量が少なければ発生箇所の特定まで行いましたが今回データ量が多くかつ重要なデータでもなかったのでそこまで特定せず、iconvコマンドに-cオプション（無効な文字を無視して無視して続行することができる）をつけてごまかしています。ちなみにiconvを試す前にはnkfを試しましたが、-cオプションに相当するオプションが存在しなかったようですのでiconvを使いました。

データベースで大文字と小文字の区別に注意を

経験豊富なシステム管理者であれば過去にきっと1度は経験するトラブルの一つに「DBで大文字と小文字の区別をしてくれない」というものがあります。これに関連するトラブルでよくあるのは「ユーザ登録時にUSERというユーザ名でアカウントを作ったはずなのにuserでもログインできてしまう」というものがあります。予めその現象をシステム管理者が知っていれば仕様という一言で済みますが、このことを知らなかったらきっと大騒ぎとなることでしょう。

もし大文字と小文字をしっかりと区別したいのであればその解決方法がしっかり用意されています。注：ただしこの辺り実は私はあまり詳しくないのでひょっとしたら間違っている、もしくはそれは昔のことで現在のバージョンでは別の解決方法が必要になっているかもしれないので参考程度に読んでください）。

・MySQLの場合はカラム型毎に挙動が違っているらしいです。例えばTEXT型は大文字と小文字を区別しないがBOB型は区別する。varcharは大文字と小文字を区別しないがcreate tableするときにvarcharの後にBINARYをつける。

・ORACLEの場合はselectするときにUPPERやLOWERを使う。(例:select * from test_tbl where upper(name) like '%D%';)

・SQLServerの場合はデータベース作成時に照合順序をバイナリに変更してください。

など。

また同様に半角・全角の区別にも気をつけてください(ORACLEであればTO_MULTI_BYTEやTO_SINGLE_BYTEを使う)。DBって面倒くさいですね。