インストール -> 8.0.15

Posted by muchag | CentOS,MySQL,VirtualBox |
初回投稿:2019-02-26 (火) 22:09:16 | 最終更新:2019-03-09 (土) 17:00:59

【環境】
MySQL: 8.0.15
CentOS: 7.6.1810
VirtualBox グラフィカルユーザーインターフェース: 5.1.24 r117012 (Qt5.6.2)
リポジトリ
取得

rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm

noarch の前の数値が、前回の 1 から 2 へ増えている。
気になるけど、次回!

確認

yum repolist all | grep mysql
インストール

yum install -y mysql-community-server
起動と停止

// 起動
systemctl start mysqld.service

// 停止
systemctl stop mysqld.service

// 自動起動
systemctl enable mysqld.service
systemctl list-unit-files -t service | grep mysqld
mysqld.service                                enabled
mysqld@.service                               disabled
初期パスワード
確認

cat /var/log/mysqld.log | grep "A temporary password"
2019-03-09T05:40:56.206659Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: hogehoge
変更
ログイン

mysql -uroot -p -hlocalhost
Enter password:

上記初期パスワードを入力。

変更

今回は、my.cnf で設定しただけではダメで
下記処理が必要だった。

パスワード変更
まで面倒なことをしなくても


ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

こちらでうまくいった。


use mysql;
select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
タイムゾーン

タイムゾーン

設定

my.cnf 8.0.x

参考サイト

Qiita:CentOS7にMySQL8.0をインストール(2019-02-17)

試行錯誤
起動しない
原因想像

インストール直後に例の「初期パスワードを調べなきゃ」と思ってやり方を探していた際
Qiita:MySQL 8.0 初期設定覚書(2019-02-07)
を見つけた。

初期パスワードを調べなくてよい方法が書かれているようだったので
ロクに調べもせずに、盲従して


mysqld  --initialize-insecure
mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

となり、参考サイトとは異なる道順となってしまった。

あれ?と思い、


systemctl  enable  mysqld
systemctl  start   mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

と起動してみたところ、エラーとなった。


systemctl  status   mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since 水 2019-02-27 00:57:05 JST; 16s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 15500 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 15483 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 15500 (code=exited, status=1/FAILURE)
   Status: "SERVER_BOOTING"
    Error: 2 (そのようなファイルやディレクトリはありません)

 2月 27 00:57:05 localhost.localdomain systemd[1]: Starting MySQL Server...
 2月 27 00:57:05 localhost.localdomain systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
 2月 27 00:57:05 localhost.localdomain systemd[1]: Failed to start MySQL Server.
 2月 27 00:57:05 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
 2月 27 00:57:05 localhost.localdomain systemd[1]: mysqld.service failed.
初期パスワード確認

ちなみにこのタイミングで初期パスワードを確認してみたら


grep -i password /var/log/mysqld.log
2019-02-26T15:54:54.194358Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

となった。

エラー

ログを確認してみたところ


vi /var/log/mysqld.log

2019-02-26T15:57:05.414331Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable

となっていたので

権限確認

当該ファイルの場所を探し


find / -name 'ibdata1'
/var/lib/mysql/ibdata1

当該ファイルの権限を確認してみたら


ll /var/lib/mysql

ほとんどの権限者が root になっていた。

他の仮想マシンを参照したところ
全ての権限者が mysql になっていた。

あちゃー。。。

再インストール

rm -rf /var/lib/mysql/*
yum remove mysql-community-server
yum install -y mysql-community-server

この時点では


ll /var/lib/mysql
合計 0

続いて


systemctl status mysqld.service
systemctl start mysqld.service

こうしたら


ll /var/lib/mysql
合計 168000
drwxr-x--- 2 mysql mysql      187  3月  9 14:41 #innodb_temp
-rw-r----- 1 mysql mysql       56  3月  9 14:40 auto.cnf
-rw-r----- 1 mysql mysql      155  3月  9 14:41 binlog.000001
-rw-r----- 1 mysql mysql       16  3月  9 14:41 binlog.index
-rw------- 1 mysql mysql     1680  3月  9 14:40 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112  3月  9 14:40 ca.pem
-rw-r--r-- 1 mysql mysql     1112  3月  9 14:40 client-cert.pem
-rw------- 1 mysql mysql     1680  3月  9 14:40 client-key.pem
-rw-r----- 1 mysql mysql     5748  3月  9 14:40 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648  3月  9 14:41 ib_logfile0
-rw-r----- 1 mysql mysql 50331648  3月  9 14:40 ib_logfile1
-rw-r----- 1 mysql mysql 12582912  3月  9 14:41 ibdata1
-rw-r----- 1 mysql mysql 12582912  3月  9 14:41 ibtmp1
drwxr-x--- 2 mysql mysql      143  3月  9 14:40 mysql
-rw-r----- 1 mysql mysql 25165824  3月  9 14:41 mysql.ibd
srwxrwxrwx 1 mysql mysql        0  3月  9 14:41 mysql.sock
-rw------- 1 mysql mysql        6  3月  9 14:41 mysql.sock.lock
drwxr-x--- 2 mysql mysql     4096  3月  9 14:40 performance_schema
-rw------- 1 mysql mysql     1680  3月  9 14:40 private_key.pem
-rw-r--r-- 1 mysql mysql      452  3月  9 14:40 public_key.pem
-rw-r--r-- 1 mysql mysql     1112  3月  9 14:40 server-cert.pem
-rw------- 1 mysql mysql     1680  3月  9 14:40 server-key.pem
drwxr-x--- 2 mysql mysql       28  3月  9 14:40 sys
-rw-r----- 1 mysql mysql 10485760  3月  9 14:41 undo_001
-rw-r----- 1 mysql mysql 10485760  3月  9 14:41 undo_002

無事に権限者が mysql になった。

前回は何だったのかしらね。。。

Posted by muchag | CentOS,MySQL,VirtualBox |
初回投稿:2019-02-26 (火) 22:09:16 | 最終更新:2019-03-09 (土) 17:00:59

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column …

Posted by muchag | MySQL |
初回投稿:2018-05-19 (土) 19:09:04 | 最終更新:2018-05-19 (土) 19:16:18

【環境】
MySQL: 5.7.18
経緯

GROUP BY を用いたら、下記エラーが出た。

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dbnam.tablename.columnname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因

Google 先生に質問すると、一杯出てきた。。。
MySQL 5.7 から仕様変更になったのが原因だそうな。

sql_mode=only_full_group_by
この部分。

my.cnf のデフォルトで sql_mode に only_full_group_by が追加されたため
GROUP BY を用いたときは、一意である値しか SELECT できなくなったとのこと。

解決策1

my.cnf の sql_mode から only_full_group_by を外してしまえば、5.6 までと同じように動作するとのこと。

解決策2
  • GROUP BY で指定したカラム
  • 集約関数を利用しているカラム
  • 全て同値であるカラム(?)

これらについては、SELECT できるので
それだけを指定する。

参考サイト

Qiita:脱!ゆるふわMySQL(2015-12-16)

Posted by muchag | MySQL |
初回投稿:2018-05-19 (土) 19:09:04 | 最終更新:2018-05-19 (土) 19:16:18

CSV から null をロード

Posted by muchag | MySQL,phpMyAdmin |
初回投稿:2018-04-28 (土) 10:01:31 | 最終更新:2018-04-28 (土) 10:17:08

【環境】
MySQL: 5.7.18
phpMyAdmin: 4.7.6
経緯
phpMyAdmin

CSV からデータをロードしようとしたら

エラー
#1292 – Incorrect datetime value: ‘null’ for column ‘hoge’ at row 1

というエラーが出た。

CSV の中身を見ると、当該カラムの値は “null” となっている。

CLI

MySQL の CLI から入れようとしたら Warning が出た。

気持ち悪いので、なんとかしてみようと思った。

データ型

ちなみに対象カラムのデータ型は DATETIME。

手法1

LOAD DATA LOCAL INFILE '/path/to/csv_file.csv' INTO TABLE hoge FIELDS TERMINATED BY ',' ENCLOSED BY '"' SET deleted = nullif(deleted, '0000-00-00 00:00:00');

どこで見たか忘れてしまったけど
こういうやり方があるらしい。

以前試したときは上手くいった。

複数カラム対象

ただ今回は、複数カラムが対象で


LOAD DATA LOCAL INFILE '/path/to/csv_file.csv' INTO TABLE hoge FIELDS TERMINATED BY ',' ENCLOSED BY '"' SET registered = nullif(deleted, '0000-00-00 00:00:00'), deleted = nullif(deleted, '0000-00-00 00:00:00');

このように書いてみたけど、上手く行かなかった。

その後

すぐ諦めたので、何が悪いのか、そもそもこの書き方が正しいのかすら不明。

手法2

null のカラムの CSV 値を “\N” とする手法。

これもどこかに書いてあったのを見かけた記憶があったので試してみたところ、
なんと、上手くいった♪

Excel のセルに \N とそのまま記述して
CSV では、”\N” となるように吐き出したところ
OK だった。

Posted by muchag | MySQL,phpMyAdmin |
初回投稿:2018-04-28 (土) 10:01:31 | 最終更新:2018-04-28 (土) 10:17:08

タイムゾーン

Posted by muchag | MySQL |
初回投稿:2017-12-10 (日) 23:47:19 | 最終更新:2017-12-11 (月) 0:38:18

【環境】
MySQL: 5.7.18
経緯

CakePHP3 を利用しているときに、下記エラーが出た。

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: ‘Asia/Tokyo’

調べてみると、MySQL のタイムゾーン設定がなされていないとのこと。

mysql データベースを見ると、確かに空っぽだった。


mysql> select * from mysql.time_zone;
Empty set (0.00 sec)
手順
Linux 環境
コマンド

mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone1970.tab' as time zone. Skipping it.
結果

3つ位失敗したみたいだけど、無事にタイムゾーンが mysql データベース time zone name テーブルへ登録された♪


select * from mysql.time_zone;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
|            1 | N                |
|            2 | N                |
|            3 | N                |

            __ snip __

|         1778 | Y                |
|         1779 | Y                |
|         1780 | Y                |
+--------------+------------------+
1780 rows in set (0.00 sec)
参考サイト

有限会社 エス技研:MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も(2017-06-16)

Posted by muchag | MySQL |
初回投稿:2017-12-10 (日) 23:47:19 | 最終更新:2017-12-11 (月) 0:38:18

インストール

Posted by muchag | phpMyAdmin |
初回投稿:2017-12-10 (日) 11:39:12 | 最終更新:2017-12-10 (日) 14:00:40

いつも大変お世話になっている phpMyAdmin。
この度、初めてインストール作業を行うことになりました。

公式:Bringing MySQL to the web

【環境】
phpMyAdmin: 4.7.6
DL

上記公式サイト右上の [Douwload 4.7.6] をクリックすると DL できる。

2017-12-10 現在
phpMyAdmin-4.7.6-all-languages.zip

ダウンロードページ

または、ダウンロードページ から、詳細な選択をすることも可能。

配置

DL した .zip ファイルを解凍して、任意のディレクトリへ配置。

今回は、アプリケーションディレクトリ直下へ配置した。

また、ディレクトリ名が長いので
phpMyAdmin-4.7.6-all-languages → pma とリネームした。

ログイン

Cookie を有効にしておかないと、ログインできない。

設定

必須ではない。

設定ファイルの配置
/pma/config.sample.inc.php

をコピーリネームして

/pma/config.inc.php

を配置。

デフォルト設定
/pma/libraries/config.default.php

これがデフォルトの設定らしい。

パスフレーズ

今回はローカル環境なので、適当に。


$cfg['blowfish_secret'] = 'hoge'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
大問題!

上記設定をしたら、pma 側で下記エラーが出だ。

phpMyAdmin – エラー
設定ファイルのパーミッションが正しくありません。誰でも書き込み可能になっています!

現在の VirtualBox を利用した仮想環境においては
config.inc.php に、パーミッションを設定できないので
こうなるらしい。

config.inc.php を削除しました。。。

ここまで

その他の config.inc.php については
ko-iya528:phpMyAdminのインストール(2017-10-26)

テーマ

phpMyAdmin は、テーマの概念があり
テーマを変更することで外観を変更することができる。

意義

テスト環境と本番環境において
取り違えによる大事故を起こさないためにも
テーマは設定しておこうね!

策定

色々とあるのかもしれないけど
公式:Themes
で、配布しているので、こちらから頂戴する。

今回は、4.7.6 なので、4.7 の中から選択。

4種類ある中から、Metro 2.6 にした。

DL

metro-2.6.zip というリンクから DL。

配置

DL してきた .zip ファイルを解凍して
出てきた metro ディレクトリをそのまま

/pma/themes

へ配置。

設定
pma

ホームページ
「外観の設定」
Theme:[metro] を選択

Metro のテーマ
/metro/layout.inc.php

にて、Metro テーマのテーマを選択できる。(ややこしいw)


/* Theme color scheme
 * Values: "teal", "redmond", "blueeyes", "mono", "win"
 * Set this value for the desired color scheme
 */

$scheme                                     = "win";
カスタマイズ
/metro/layout.inc.php

に、色設定があるので、そちらをカスタマイズ。


case "win":

    $GLOBALS['cfg']['NaviColor']                = '#EEEEEE';
    $GLOBALS['cfg']['NaviBackground']           = '#377796';	// ページ上部ナビゲーションバーの背景色
    $GLOBALS['cfg']['NaviBackgroundLight']      = '#428EB4';
    $GLOBALS['cfg']['NaviPointerColor']         = '#333333';
    $GLOBALS['cfg']['NaviPointerBackground']    = '#377796';
    $GLOBALS['cfg']['NaviDatabaseNameColor']    = '#333333';
    $GLOBALS['cfg']['NaviHoverBackground']      = '#428EB4';
    $GLOBALS['cfg']['MainColor']                = '#444444';
    $GLOBALS['cfg']['MainBackground']           = '#FFFFFF';
    $GLOBALS['cfg']['BrowsePointerColor']       = '#377796';
    $GLOBALS['cfg']['BrowseMarkerColor']        = '#000000';
    $GLOBALS['cfg']['BrowseWarningColor']       = '#D44A26';
    $GLOBALS['cfg']['BrowseSuccessColor']       = '#01A31C';
    $GLOBALS['cfg']['BrowseGrayColor']          = '#CCCCCC';
    $GLOBALS['cfg']['BrowseMarkerBackground']   = '#EEEEEE';
    $GLOBALS['cfg']['BorderColor']              = '#DDDDDD';
    $GLOBALS['cfg']['ButtonColor']              = '#FFFFFF';
    $GLOBALS['cfg']['ButtonBackground']         = '#377796';
    $GLOBALS['cfg']['ButtonHover']              = '#428EB4';
    $GLOBALS['cfg']['ThBackground']             = '#F7F7F7';
    $GLOBALS['cfg']['ThDisabledBackground']     = '#F3F3F3';
    $GLOBALS['cfg']['ThColor']                  = '#666666';
    $GLOBALS['cfg']['ThPointerColor']           = '#000000';
    $GLOBALS['cfg']['BgOne']                    = '#F7F7F7';
    $GLOBALS['cfg']['BgTwo']                    = '#FFFFFF';
    $GLOBALS['cfg']['BlueHeader']               = '#3A7EAD';	// 注意書きなどの背景色
    break;
Posted by muchag | phpMyAdmin |
初回投稿:2017-12-10 (日) 11:39:12 | 最終更新:2017-12-10 (日) 14:00:40

ERROR 1452 (23000): Cannot add or update a child row: …

Posted by muchag | MySQL |
初回投稿:2016-01-19 (火) 13:58:54 | 最終更新:2016-01-19 (火) 14:05:18

【環境】
MySQL:5.5.27
状況

とあるテーブルに CSV データを入れ込もうとして下記エラーが出た。

エラー
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`table_b`, CONSTRAINT `[constraint_name]` FOREIGN KEY (`table_a_id`) REFERENCES `table_a` (`id`) ON DELETE CASCADE)

このエラーは外部キー制約に違反したデータを保持した状態で
当該テーブルを更新しようとすると出るとのこと。

原因

親テーブル(table_a)の取り込み時に、2行欠落していた。

解決

親テーブル(table_a)を取り込み直して解決。
かなすぃ。 XO

調査過程

あ~、またやってしまったと思い、
あれこれデータを調べてみたけど、不整合を見つけることができなかった。

検索

親テーブル(table_a)の ID は、1~365。
子テーブル(table_b)の table_a_id カラムに入っている値を
phpMyAdmin で検索して、1 より小さいもの、365 より大きいものを見てみたけど
検索結果は 0。

データ件数

ふと気になって、table_a のデータ件数を見てみたら
なんと! 364件。
あり? と思って、データを追ってみたら
ID:358 がない。。。

ログ

改めて、LAOD DATA LOCAL INFILE のログを見てみたら、あらら。。。

  1. Query OK, 363 rows affected (0.05 sec)
  2. Records: 365  Deleted: 0  Skipped: 2  Warnings: 0

じぇんじぇん見てなかったじぇ。。。
これを確認していれば、無駄な時間を取られずにすんだのに~。

Skipped: 2 なのに、364件あったのは。。。
い、今は気が付かなかったことにしましょ。。。

Posted by muchag | MySQL |
初回投稿:2016-01-19 (火) 13:58:54 | 最終更新:2016-01-19 (火) 14:05:18

HAVING

Posted by muchag | MySQL |
初回投稿:2015-12-28 (月) 9:49:07 | 最終更新:2015-12-28 (月) 11:12:00

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9.2 JOIN Syntax

用途

グループ化されたデータ(GROUP BY)に対して
検索条件を指定する。

検索対象

よって、GROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できない。

記述位置

HAVING 句は、すべての GROUP BY 句のあとで、かつすべての ORDER BY 句の前にある必要があります。

MySQL:13.2.9 SELECT 構文

この記事は、日本語版 5.5用はなかった。

適用順

HAVING 句は、ほぼ最後 (項目がクライアントに送信される直前) に最適化なしで適用されます。(LIMIT は HAVING のあとに適用されます。)

MySQL:13.2.9 SELECT 構文

エイリアス

カラムエイリアスを使用できる。

  1. SELECT COUNT(user_id) AS cnt
  2. FORM hoge
  3. HAVING cnt > 10;
Posted by muchag | MySQL |
初回投稿:2015-12-28 (月) 9:49:07 | 最終更新:2015-12-28 (月) 11:12:00

調査

Posted by muchag | MySQL |
初回投稿:2015-12-27 (日) 23:18:52 | 最終更新:2015-12-28 (月) 0:07:19

ちょ、ちょっと私にはハードルが高い。。。と思われる。
でも、少しずつでも触っていれば、理解していけるかもしれないので
いつもの通り特攻してみるメモ。

EXPLAIN

クエリの実行内容解析?
SELECT ステートメントの頭に EXPLAIN を付けて実行する。

  1. EXPLAIN
  2. SELECT *
  3. FROM tableA
  4. WHERE column1 = a;

上のクエリ例と下の EXPLAIN 例の内容は全く合致してないけど。。。

参考サイト

漢のコンピュータ道:MySQLのEXPLAINを徹底解説!!(2009-03-31)
→ちょっと古い記事だけど、詳しくてわかりやすい

Qiita:MySQLのEXPLAIN(2014-10-23)
→上記記事を参考にまとめられた記事

FROFILE

MySQL 5.6 では、非推奨になってしまったらしい。
なくなっちゃうんだって。
GMOメディア エンジニアブログ:MySQL 5.6ではprofilingが非推奨になってしまった件

参考サイト

漢のコンピュータ道:プロファイリングで快適MySQLチューニング生活(2009-02-03)
→ちょっと古い記事だけど、詳しくてわかりやすい

MySQL Casual:show profileでフェーズごとの実行時間を見る – MySQL Casual Advent Calendar 2010(2010-12-05)
→PROFILE を貯める方法? と、それを参照する方法が載っている

障害解析
参考サイト

Qiita:誰も教えてくれなかったMySQLの障害解析方法(2014-07-12)
→まだまだサッパリわからない。というか試す暇がないけど、いつかやりたい!

Posted by muchag | MySQL |
初回投稿:2015-12-27 (日) 23:18:52 | 最終更新:2015-12-28 (月) 0:07:19

String

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 15:10:04 | 最終更新:2015-11-20 (金) 15:17:40

面白い関数もあったよ~♪

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.5 String Functions
 
文字列処理関数。

REPLACE

REPLACE

書式
  1. REPLACE(str,from_str,to_str)

第1引数:対象文字列
第2引数:検索文字列
第3引数:置換文字列

第1引数の対象文字列には、もちろん直接文字列を指定してもいいんだけど
要はデータを扱いたいので、そこにフィールド名を指定する。
フィールド名の場合は、”” は無し。

  1. UPDATE sample SET name=REPLACE(name, 'Tanaka', 'Yamada');
Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 15:10:04 | 最終更新:2015-11-20 (金) 15:17:40

CASE

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 14:39:18 | 最終更新:2016-01-16 (土) 23:52:17


いわゆる CASE 文。
こんなこともできるなんてね~。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.6.5.1 CASE Syntax

書式

THEN の後ろの値が戻ってくる。

  1. CASE case_value
  2.     WHEN when_value THEN statement_list
  3.     [WHEN when_value THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE sex
  9.     WHEN 1 THEN '男'
  10.     WHEN 2 THEN '女'
  11.     ELSE 'その他'
  12. END
条件式
  1. CASE
  2.     WHEN search_condition THEN statement_list
  3.     [WHEN search_condition THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE
  9.     WHEN sex = 1 THEN '男'
  10.     WHEN sex = 2 THEN '女'
  11.     ELSE 'その他'
  12. END
  13.  
  14. // 不等号はもちろん、IN 句も使える
  15. CASE
  16.     WHEN age IN (13, 14, 15) THEN '中学生'
  17.     WHEN age IN (16, 17, 18) THEN '高校生'
  18.     ELSE 'その他'
  19. END
利用
SELECT

散らばった値を集約して表示できる。

点数に応じて振り分け

80点を超えた人は合格。
60点を超えた人は再テスト。
それ以外の人は不合格。

  1. SELECT
  2.     name, result,
  3.     CASE
  4.         WHEN result > 80 THEN 'Pass'
  5.         WHEN result > 60 THEN 'ReTest'
  6.         ELSE 'Fail'
  7.     END as judgment
  8. FROM test;
集約関数併用
  1. SELECT  dept_nbr,
  2.     COUNT(CASE WHEN sex = 1 THEN 1 ELSE 0 END), // 男の人数
  3.     COUNT(CASE WHEN sex = 2 THEN 1 ELSE 0 END)  // 女の人数
  4. FROM Table_A
  5. GROUP BY dept_nbr;
GROUP BY

散らばった値を集約できる。
ただし、SELECT にも同内容の指定をすること。

豪快に GROUP BY句にも SELECT句の CASE式をコピーしてやるのがポイントです。
単純に「GROUP BY pre_name」と変換前の列を指定すると、
正しい結果が得られませんので注意してください。

ミックのページ:CASE式のススメ

ORDER BY

任意のソート順を設定できる。

CHECK 制約との併用

CASE式は CHECK制約と非常に相性が良いのです。
実際に CASE式を使う局面の半分は CHECK制約の中ではないかと思うほどです。

ミックのページ:CASE式のススメ

と、あるけど、まだ理解できていない。

複数カラムの最大値を返す

ミックのページ:CASE式のススメ 参照。

参考サイト

DBOnline:カラムの値に応じて異なる結果を返す条件式の記述

サンプルコードによるPerl入門:SQLのhaving句はcase式で書くと直感的に記述できる(2011-04-13)

DN-Web64:MySQL : CASE文で複雑な条件を指定
→簡潔で読みやすい。SELECT, GROUP BY, ORDER BY 別にサンプルあり

ミックのページ:CASE式のススメ
→いつもお世話になっております。相変わらずの詳細な解説をありがとうございます。

Posted by muchag | MySQL |
初回投稿:2015-11-20 (金) 14:39:18 | 最終更新:2016-01-16 (土) 23:52:17
次ページへ »