MySQLではGROUP BYを使ってレコードをグループ化し、カラムの合計や最小値・最大値などを集計することができます。
集計した値をWHERE句の抽出条件指定に使用することはできません。
そんなときはHAVING句を使用します。

まずはサンプルのテーブルを用意しましょう。

CREATE TABLE `students` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `class` int(4) NOT NULL,
  `score` int(4) NOT NULL,
  PRIMARY KEY (`id`)
);

サンプルデータを登録します。

INSERT INTO `students` VALUES
    (DEFAULT, '赤井', 1, 168),
    (DEFAULT, '井田', 2, 133),
    (DEFAULT, '宇野', 3, 167),
    (DEFAULT, '江崎', 1, 185),
    (DEFAULT, '奥本', 2, 142),
    (DEFAULT, '加藤', 3, 176),
    (DEFAULT, '菊池', 1, 159),
    (DEFAULT, '久保', 2, 181),
    (DEFAULT, '剣持', 3, 155);

classでグループ化し、scoreの合計を抽出してみましょう。

SELECT class, SUM(score) AS sum_score FROM `students`
GROUP BY class;

結果は次のようになります。

+-------+-----------+
| class | sum_score |
+-------+-----------+
|     1 |       512 |
|     2 |       456 |
|     3 |       498 |
+-------+-----------+

試しにWHERE句を使ってみましょう。

SELECT class, SUM(score) AS sum_score FROM `students`
WHERE sum_score > 500
GROUP BY class;

すると

ERROR 1054 (42S22): Unknown column 'sum_score' in 'where clause'

このようにエラーとなります。

それではHAVING句を使ってみましょう。

SELECT class, SUM(score) AS sum_score FROM `students`
GROUP BY class
HAVING sum_score > 500;

すると結果は次のようになります。

+-------+-----------+
| class | sum_score |
+-------+-----------+
|     1 |       512 |
+-------+-----------+

HAVING句による条件指定で絞り込みができています。

WHERE句はグループ化で集計される前、HAVING句はグループ化で集計された後のデータに対して条件を指定し絞り込むことができます。

MySQLでGROUP BYを使って集計するときにWITH ROLLUPを使えば出力に集計行を追加することができます。

適当なサンプルデータを用意します。

mysql> create table t1 (a integer, b integer, c integer);
insert into t1 values (111,11,11),(222,22,22),(111,12,12),(222,23,23);

mysql> SELECT * FROM t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
+------+------+------+
4 rows in set (0.000 sec)

WITH ROLLUP

まず単純にグループ化してみましょう。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
+------+------+------+
4 rows in set (0.001 sec)

WITH ROLLUPを付けると出力結果に集計行が追加されます。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.000 sec)

MySQL5.7以前のバージョンでは、GROUP BYは常にソートされます。
並び順を指定すると次のようになります。
このとき集計行に影響はありません。
続きを読む…»

ストアドプロシージャで1行ずつデータを処理したいときはカーソルを使って処理します。
以下、簡単な例をご紹介します。

-- 読み出した値を格納する変数を宣言
DECLARE currentId INT;
DECLARE currentColumnA VARCHAR(255);
DECLARE currentColumnB VARCHAR(255);

-- カーソルが最終行に達した判定するフラグ
DECLARE done INT DEFAULT FALSE;

-- カーソルを定義
DECLARE myCursor CURSOR FOR
SELECT id, columna, columnb FROM example;

-- カーソルが最終行に達したときの動作を制御
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- カーソルを開く
OPEN myCursor;

-- ループで1行ずつ処理
read_loop: LOOP
    -- カーソルから1行読み出し
    FETCH myCursor INTO currentId, currentColumnA, currentColumnB;

    -- カーソルからの読み出しが最後に達していればループを抜ける
    IF done THEN
        LEAVE read_loop;
    END IF;

    -- 読み出したデータを使用した処理等を書く

END LOOP;

-- カーソルを閉じる
CLOSE myCursor;

タイトルにもある通り、MySQLトランザクションにてROLLBACK（ロールバック）ができないケースがあります。
一連の処理を記述し、特定条件下やどこかで失敗した場合等に全てを元に戻せるということで非常に便利なROLLBACKですが、
ROLLBACKできないSQL文がトランザクションに含まれていた場合、ROLLBACK自体がエラーとなり元に戻らないことがあります。

ROLLBACKできないSQLの例

• CREATE
• DROP
• ALTER
• TRUNCATE

ROLLBACKできるSQL

• INSERT
• UPDATE
• DELETE

但しこちらはMySQLに限ったお話ですので、他のDBの場合は要確認です。
他のDBではROLLBACKできる場合があります。

公式リファレンス
https://dev.mysql.com/doc/refman/8.0/ja/cannot-roll-back.html

MySQLのテーブルで文字コードutf8とutf8mb4が混在するシステムをutf8mb4に統一する機会がありました。
そんな時に文字コードや照合順序を確認する方法、変換する方法です。

MySQL：5.7

データベースの文字コードと照合順序を確認する

SELECT
    SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE
    SCHEMA_NAME = 'データベース名';

テーブルの文字コードと照合順序を確認する

SELECT
    TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'データベース名';

カラムの文字コードと照合順序を確認する

SELECT
    COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'テーブル名';

続きを読む…»