投稿者: MuramotoHiroaki

CASE式みたいな処理って、自分の場合はこれまでアプリケーション内で行うことが多くてMySQLで行うことが少なかったです。
しかし、使う機会があって若干はまったので、備忘録として残します。

やりたかったことは、「NULLか空文字だったら値を0として取得する」ことでした。
CASE式には単純CASE式と検索CASE式があってそれが頭に入っておらず、はまっておりました。

サンプルテーブルを以下に用意します。

START TRANSACTION;
    CREATE TABLE member (
        id int(11) NOT NULL,
        name varchar(64) NOT NULL,
        gender enum('male','female') NOT NULL,
        age int(11) NOT NULL,
        memo mediumtext,
        created datetime NOT NULL,
        updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ALTER TABLE member
        ADD PRIMARY KEY (id),
        ADD KEY gender (gender),
        ADD KEY age (age);

    ALTER TABLE member
        MODIFY id int(11) NOT NULL AUTO_INCREMENT;

    INSERT INTO member (name, gender, age, memo, created) VALUES
        ('suzuki', 'male', 25, 'イケメン', '2021-04-01 00:00:00'),
        ('ohtani', 'female', 30, NULL, '2021-05-01 00:00:00'),
        ('sato', 'female', 35, '', '2021-06-01 00:00:00'),
        ('nakamura', 'female', 40, 'おもしろい', '2021-07-01 00:00:00'),
        ('tanaka', 'female', 45, NULL, '2021-08-01 00:00:00'),
        ('inoue', 'male', 50, '博識', '2021-09-01 00:00:00'),
        ('ishida', 'female', 55, '優しい', '2021-10-01 00:00:00'),
        ('matsumoto',  'male', 60, '穏やか', '2021-11-01 00:00:00'),
        ('sasaki', 'male', 65, '', '2021-12-01 00:00:00'),
        ('kato', 'female', 70, '', '2022-01-01 00:00:00');
COMMIT;

テーブルの中身はざっとこんな感じ。

性別が英語で入っているので日本語で取得してみたいと思います。
比較しやすいように名前と英語での性別も取得します。
以下のようにCASEのすぐ後ろにカラム名が来て、そのWHENの値と同値のCASE式を単純CASE式と呼ぶそうです。

# 単純CASE式
SELECT
    name,
    gender,
    CASE gender
        WHEN 'male' THEN '男性'
        WHEN 'female' THEN '女性'
    END AS gender_ja
FROM member;

検索結果

maleの場合は「男性」、femaleの場合は「女性」と表示されます。

次は
60歳以上であれば「senior」
40歳以上60歳未満であれば「middle-age」
40歳未満を「young」
と出力するようにします。

検索CASE式ではWHENの後に検索条件が入ります。
CASEの後に列名は必要ありません。

# 検索CASE式
SELECT
    name,
    age,
    CASE 
        WHEN age >= 60 THEN 'senior'
        WHEN age >= 40 && age < 60 THEN 'middle-age'
        ELSE 'young'
    END AS generation
FROM member;

検索結果

応用が効くのは検索CASE式で単純CASE式は、検索CASE式でも行うことが出来ます。

最初に行った性別を日本語で表示する単純CASE式を検索CASE式で書いてみます。

# 検索CASE式
# 結果は二つ上の表と同じ
SELECT
    name,
    gender,
    CASE
        WHEN gender LIKE 'male' THEN '男性'
        WHEN gender LIKE 'female' THEN '女性'
    END AS gender_ja
FROM member;

今回やりたかったことは、「NULLか空文字だったら値を0として取得する」だったので、検索CASE式を使えば簡単に出来そうです。
CASEの後ろに memo と書いていたので若干はまっていました。

SELECT
    name,
    memo,
    CASE
        WHEN (memo IS NULL || memo = '') THEN 0
        ELSE 1
    END AS memo_exists
FROM member;

検索結果

めでたしです。
と、最初は上のSQLを書いたのですがCHAR_LENGTHを使えば空文字判定出来そうですね…。
TRIMも必要なら一緒にすれば良さそうです(全角スペースは除かれないので注意)。

SELECT
    name,
    memo,
    CASE
        WHEN CHAR_LENGTH(memo) > 0 THEN 1
        ELSE 0
    END AS memo_exists
FROM member;

上のIF文を使って書くことも可能です。

SELECT
    name,
    memo,
    IF(CHAR_LENGTH(memo) > 0, 1, 0) AS memo_exists
FROM member;

単純CASEで書けるところも検索CASE式で書けるので、検索CASE式 → 単純CASE式の順に学んだ方が覚えやすい気がします。

そもそも大量にDELETEしない方が安心ですが…。
LIMITをつけて刻んだDELETEでも同様です。
DBのストレージ容量の肥大化を防ぐため、ログテーブルなどのもう利用しない古いレコードを大量に物理削除したいときとかってあります。
物理削除した場合は、

OPTIMIZE TABLE table_name;

を実行しないとディスク領域が解放されません。
ただし、実行した場合はテーブルロックがかかります。
サービスがリアルタイムで稼働中の場合は、実行タイミングに注意が必要です。

試しに実行してみます。
以下のような適当なサンプルテーブルを用意してみます。

CREATE TABLE people (
    id int(11) NOT NULL,
    name varchar(64) NOT NULL,
    height int(11) NOT NULL,
    weight int(11) NOT NULL,
    gender enum('male','female') NOT NULL,
    updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE people
    ADD PRIMARY KEY (id),
    ADD KEY height (height),
    ADD KEY weight (weight);

ALTER TABLE people
    MODIFY id int(11) NOT NULL AUTO_INCREMENT;

まず、10行入れます。

INSERT INTO people (name, height, weight, gender) VALUES
 ('suzuki', 170, 65, 'male'),
 ('ohtani', 190, 90, 'male'),
 ('sato', 150, 45, 'female'),
 ('nakamura', 180, 75, 'male'),
 ('tanaka', 160, 55, 'female'),
 ('inoue', 155, 50, 'female'),
 ('ishida', 160, 60, 'male'),
 ('matsumoto', 165, 60, 'female'),
 ('sasaki', 175, 70, 'male'),
 ('kato', 145, 40, 'female');

10000010行に増やします。
自分の環境では2分30秒ぐらいかかりました。

INSERT INTO people (name, height, weight, gender)
SELECT p1.name, p1.height, p1.weight, p1.gender
FROM people p1, people p2, people p3, people p4, people p5, people p6, people p7;

ここで容量を確認しておきます。

SELECT table_name,
       table_rows AS rows,
       floor((data_length+index_length) / 1024 / 1024) AS all_mb,
       floor(data_length / 1024 / 1024) AS data_mb,
       floor(index_length / 1024 / 1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY (data_length + index_length) DESC;

およそ660MBになっています。
DELETE文でおよそ900万行削除したいと思います。
時間かかるのでブレイクタイムにすることをオススメします。
実行したら自分の環境では12分でした。

DELETE FROM people WHERE id > 1000000;

消し終えたらもう一回、容量を確認します。

行数は変わったけど容量は変わってないんですよね…。
そこで最適化します。

OPTIMIZE TABLE people;

もしくは

ALTER TABLE people ENGINE INNODB;

14秒くらいかかりました。

減りましたのでめでたしです。
しかし、DELETE文が遅いのとテーブルがロックされるのが嫌がられるのがわかりますね。
一時テーブルを作成する方法が取られるのもこちらの結果を考えると納得がいきます。

参考サイト

• 大人のためのInnoDBテーブルとの正しい付き合い方
• OPTIMIZE TABLEでテーブルを最適化する
• MySQLで大量のデータを追加してみる