作成日:  (更新日: )

MySQL のスニペット集

このページは、MySQL の SQL のスニペットをまとめる予定のページです。

目次

注意

  • このページを作ったひとが備忘のためにまとめているため網羅性はありません。
  • mysqldump などのコマンド操作は MySQL のコマンド操作 にあります。
  • RDBMS の SQL の比較 にも MySQL の SQL について記載があります。

スニペット

文字列の結合

CONCAT()
SELECT CONCAT(sei, mei) FROM user;
PIPES_AS_CONCAT 設定時
-- SET sql_mode='PIPES_AS_CONCAT';
SELECT sei || mei FROM user;

LIKE

エスケープ文字列の指定
SELECT * FROM user WHERE name LIKE '100\%' ESCAPE '\\';

日付・時刻

フォーマット

SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y%m%d'); -- 20010203
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y%m%d%H%i%s'); -- 20010203040506
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y-%m-%d'); -- 2001-02-03
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y-%m-%d %H:%i:%s'); -- 2001-02-03 04:05:06
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y/%m/%d'); -- 2001/02/03
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%Y/%m/%d %H:%i:%s'); -- 2001/02/03 04:05:06

システム変数の表示

現在のセッションのシステム変数の表示
SHOW VARIABLES;
グローバルのシステム変数の表示
SHOW GLOBAL VARIABLES;
絞り込み (この例の場合現在のセッションの文字関係のシステム変数を表示)
SHOW VARIABLES LIKE '%char%';

変数のセット

SET @変数名 = 値やクエリなど;
行数のセットと表示
SET @count = (SELECT COUNT(*) FROM foo);
SELECT @count;

ログ

一般クエリログの有効化と確認
SET GLOBAL general_log = 1;
SHOW VARIABLES LIKE 'general_log%';
スロークエリログの有効化と確認
SET GLOBAL slow_query_log = 1;
SHOW VARIABLES LIKE 'slow_query_log%';
ログ出力方法を変更して確認
SET GLOBAL log_output = 'TABLE,FILE'; -- テーブルとファイルに出力
SHOW VARIABLES like 'log_output';
SELECT *, convert(argument using utf8) FROM mysql.general_log; -- ログを確認
データディレクトリの確認
SHOW VARIABLES LIKE 'datadir';

外部キー制約の無効化

外部キー制約の無効化
SET foreign_key_checks=0;
有効に戻す
SET foreign_key_checks=1;

CSV インポート

CSV インポート
LOAD DATA LOCAL INFILE 'ファイルパス' INTO TABLE テーブル名
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES STARTING BY '' TERMINATED BY '\r\n'
  IGNORE 0 LINES;
CSV インポート (CSV が Shift_JIS の場合)
SET character_set_database=sjis;
LOAD DATA LOCAL INFILE 'ファイルパス' INTO TABLE テーブル名
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES STARTING BY '' TERMINATED BY '\r\n'
  IGNORE 0 LINES;
CSV インポート (変数とSETを使って加工する例)
-- 列1, 列2 にはそのまま取り込んで、列3にはCSVの3列目と5列目を結合した値を取り込む (4列目は取り込まない)
LOAD DATA LOCAL INFILE 'ファイルパス' INTO TABLE テーブル名
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES STARTING BY '' TERMINATED BY '\r\n'
  IGNORE 0 LINES
  (列1, 列2, @csv3, @csv4, @csv5) SET 列3 = TRIM(CONCAT(@csv3, " ", @csv5));

トランザクションの開始

BEGIN; -- or START TRANSACTION;
-- INSERT 等のクエリ...
COMMIT; -- ロールバックする場合は ROLLBACK;

トランザクション特性 (分離レベル) の指定

REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

トランザクション特性の確認

現在のセッション
SELECT @@tx_isolation;
グローバル
SELECT @@GLOBAL.tx_isolation;

行ロック

SELECT 列名 FROM テーブル名 WHERE 抽出条件 FOR UPDATE;

実行計画の確認

EXPLAIN クエリ;
例
EXPLAIN SELECT * FROM foo;

CREATE TABLE 文の表示

SHOW CREATE TABLE テーブル名;

定義情報

テーブル名一覧の表示

information_schema
SELECT table_name FROM information_schema.tables WHERE table_schema = 'データベース名';
SHOW TABLES
SHOW TABLES;

テーブル列一覧の表示

information_schema
SELECT
  table_name AS "テーブル名",
  ordinal_position AS "No.",
  column_name AS "列名",
  data_type AS "型名",
  character_maximum_length AS "文字数",
  column_default AS "デフォルト値",
  CASE WHEN pk.column_name IS NOT NULL THEN 1 ELSE 0 END AS "主キー",
  column_comment AS "コメント"
FROM information_schema.columns AS c
LEFT JOIN (
  SELECT kc.table_catalog, kc.table_schema, kc.table_name, kc.column_name
  FROM information_schema.key_column_usage AS kc
  WHERE kc.constraint_name = 'PRIMARY'
) AS pk USING (table_catalog, table_schema, table_name, column_name)
WHERE c.table_schema = 'DB名'
ORDER BY c.table_name, c.ordinal_position;
SHOW COLUMNS
SHOW COLUMNS テーブル名;
列名を絞り込む場合
SHOW COLUMNS FROM テーブル名 LIKE '%絞り込みたい列名%';
別記法。DESC は DESCRIBE, EXPLAIN でも可
DESC テーブル名;
列名を絞り込む場合
DESC テーブル名 '%絞り込みたい列名%';
列名 (カンマ区切り)
SELECT GROUP_CONCAT(column_name) FROM information_schema.columns
WHERE table_schema = 'DB名' AND table_name = 'テーブル名'
ORDER BY ordinal_position

関数定義

加算する関数の例
DROP FUNCTION IF EXISTS F1;

DELIMITER $$
CREATE FUNCTION F1(v1 int, v2 int) RETURNS INT
BEGIN
    DECLARE ret INT; -- 変数定義 (初期値は型名の後に DEFAULT 初期値 で指定)
    SET ret = v1 + v2;
    RETURN ret;
END
$$
DELIMITER ;

-- 使用
SELECT F1(1, 2); -- 3

-- 破棄
DROP FUNCTION F1;
  • ストアドファンクションは SELECT などの中で実行できます。ストアドプロシージャは CALL で実行します。

レコードの取得

レコードの値の取得, 確認
DROP PROCEDURE IF EXISTS F1;

DELIMITER $$
CREATE PROCEDURE F1()
BEGIN
    DECLARE _emp_name TEXT DEFAULT '';
    SELECT emp_name INTO _emp_name FROM emp WHERE id = 1;
    SELECT _emp_name;
END$$
DELIMITER ;

CALL F1();
DROP PROCEDURE F1;
複数レコードの取得
DROP PROCEDURE IF EXISTS F1;

DELIMITER $$
CREATE PROCEDURE F1()
BEGIN
    DECLARE _emp_name TEXT DEFAULT '';
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT emp_name FROM emp WHERE gender = 1 ORDER BY id; -- カーソル定義
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- カーソルのレコード取得終了時にフラグを立てる

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO _emp_name; -- カーソルから値を取得
        IF done THEN -- レコード取得終了時、ループを抜ける
            LEAVE read_loop;
        END IF;

        SELECT _emp_name;
    END LOOP;

    CLOSE cur;
END$$
DELIMITER ;

CALL F1();
DROP PROCEDURE F1;
  • 複数の列を一つの変数で受け取ることはできません。

レコードを返す

複数レコードを返す
DROP PROCEDURE IF EXISTS F1;

DELIMITER $$
CREATE PROCEDURE F1()
BEGIN
    SELECT * FROM emp;
END$$
DELIMITER ;

CALL F1();
DROP PROCEDURE F1;
複数レコードを返す (処理が1行の場合はBEGIN～ENDを使わなくてもいい)
DROP PROCEDURE IF EXISTS F1;

CREATE PROCEDURE F1() SELECT * FROM emp;

CALL F1();
DROP PROCEDURE F1;
  • ストアドファンクションでクエリを返すことはできません。

ユーティリティ関数

連番生成

PostgreSQL の generate_series() に近い連番生成のしくみを追加します。(ストアドファンクションでクエリを返せないため一時テーブルを生成しています)

DROP PROCEDURE IF EXISTS create_generate_series;
DELIMITER $$
CREATE PROCEDURE create_generate_series(_start int, _stop int)
BEGIN
    DECLARE _i int DEFAULT _start;
    DROP TABLE IF EXISTS generate_series;
    CREATE TEMPORARY TABLE generate_series (i int);
    WHILE _i <= _stop DO
        INSERT INTO generate_series VALUES (_i);
        SET _i = _i + 1;
    END WHILE;
END
$$
DELIMITER ;

-- 使用例
CALL create_generate_series(1, 100);
SELECT * FROM generate_series;