カテゴリー
SugiBlog Webエンジニアのためのお役立ちTips

テーブルのカラム名とデータ型を取得する

MySQLのシステムテーブルから指定したテーブルのカラム名とデータ型を取得します。

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'テーブル名';
312 views

MySQL GROUP BYとHAVING

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句はグループ化で集計された後のデータに対して条件を指定し絞り込むことができます。

307 views

MySQL GROUP BYのWITH ROLLUP修飾子とGROUPING関数

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は常にソートされます。
並び順を指定すると次のようになります。
このとき集計行に影響はありません。
続きを読む…»

462 views

MySQLでカーソルを使って1行ずつ処理したい

ストアドプロシージャで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;
781 views

MySQLトランザクションでROLLBACKが効かないケース

  • MySQL
  • 2024-01-30 - 更新:2025-09-05

MySQLのトランザクションは、処理の途中で失敗した場合に ROLLBACK（ロールバック）で元の状態に戻せる便利な仕組みです。
しかし「必ず元に戻せる」と思っていると落とし穴にはまります。

実は、MySQLには ROLLBACKが効かない（厳密には「元に戻せない」）SQL文 が存在します。

ROLLBACKできるSQL（DML）

一般的なデータ操作言語（DML）はトランザクションで管理でき、ROLLBACKも可能です。

  • INSERT
  • UPDATE
  • DELETE

これらは通常通り、トランザクションの途中で ROLLBACK すれば元の状態に戻せます。

ROLLBACKできないSQL（DDL）

一方、スキーマを変更するようなデータ定義言語（DDL）は注意が必要です。

  • CREATE
  • DROP
  • ALTER
  • TRUNCATE

これらのDDL文を実行すると、MySQLでは 暗黙的に COMMIT が発生 します。そのため、実行後に ROLLBACK しても元に戻すことはできません。
つまり「ROLLBACKがエラーになる」のではなく、「DDLが実行された時点でトランザクションが確定してしまう」イメージです。

他のデータベースとの違い

これはMySQL固有の挙動です。
データベースによってはDDLもトランザクション管理下に置ける場合があります（例：PostgreSQLでは多くのDDLがトランザクション内でROLLBACK可能）。

MySQLを使う際には「DDLは必ず即時確定する」と覚えておくのが安全です。

まとめ

  • INSERT / UPDATE / DELETE はトランザクションで管理でき、ROLLBACK可能
  • CREATE / DROP / ALTER / TRUNCATE は実行時に自動コミットされ、ROLLBACK不可
  • ROLLBACK自体がエラーになるわけではなく、「巻き戻せない」だけ
  • PostgreSQLなど他のDBではDDLもROLLBACK可能な場合がある

参考： MySQL公式ドキュメント – ステートメントをロールバックできない場合

987 views