カテゴリー
SugiBlog Webデザイナー・プログラマーのためのお役立ちTips

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;
42 views

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

タイトルにもある通り、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

48 views

MySQL 既存テーブルの文字コードをutf8mb4に変換する

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 = 'テーブル名';

続きを読む…»

3,251 views

MySQL 外部キー制約を一時的に無効にする

MySQLで外部キー制約を設定しているテーブルは削除等、一部の命令を実行することができません。
開発をしている中でどうしても外部キー制約が邪魔になってしまう場合があります。
その場合は外部キー制約を一時的に無効にすることができます。

外部キー制約を一時的に無効にする

SET foreign_key_checks = 0;

処理が終わったら必ず元に戻しましょう。

SET foreign_key_checks = 1;
548 views

指定した文字列が最初に出現する位置を調べるLOCATE関数

LOCATE関数を使うと指定した文字列が最初に出現する位置を調べることができます。

【書式】
LOCATE(substr, str)
LOCATE(substr, str, pos)

【引数】
substr:検索文字列
str:検索対象文字列
pos:オフセット

posは何文字目以降を検索するかを数値で指定します。

例)

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

INSTR関数も同様の動作をしますが、引数の順序が逆なのと、第3引数がないのでご注意ください。

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
810 views