MySQL Basic #

Preparation #

Install to macOS #

$ brew install mysql

Start #

$ mysql.server start
Starting MySQL
....... SUCCESS!

Check the vertion #

$ mysql --version
mysql  Ver 8.0.26 for macos11.3 on x86_64 (Homebrew)

Make initial settings #

$ mysql_secure_installation

パスワード強度検証プラグインの利用有無
パスワード強度の選択
root ユーザーのパスワード設定
匿名ユーザーの削除
リモートからの root ユーザーとしてのログインの禁止
サンプル用 test データベースの削除

$ mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
### パスワード強度検証プラグインの利用
Press y|Y for Yes, any other key for No: y ### y と入力

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
### パスワード検証ポリシーの選択
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2　### 2 と入力

Please set the password for root here.

New password: ### rootユーザー用のパスワードを入力

Re-enter new password: ### rootユーザー用のパスワードを再入力

Estimated strength of the password: 100
Do you wish to continue with the password provided?
(Press y|Y for Yes, any other key for No) : y　### y と入力
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
### 匿名ユーザーの削除
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y ### y と入力
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
### リモートからの root ログインの拒否
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y ### y と入力
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

### test データベースの削除
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y　### y と入力
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
### 特権テーブルの即時適用
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y　### y と入力
Success.

All done!

設定ファイル #

設定ファイル my.cnf のパス、適用範囲、読み込み順序

#	Path	Scope
1	`/etc/my.cnf`	グローバル
2	`/etc/mysql/my.cnf`	グローバル
3	`~/my.cnf`	ユーザー固有

ログイン #

e.g) rootユーザーでのログイン

$ mysql -uroot -p
Enter password: ### 設定したパスワードを入力

バージョン #

SELECT VERSION();

タイムゾーン #

SHOW VARIABLES LIKE '%time_zone%';

タイムゾーンの変換

-- CONVERT_TZ(datetime, before_time_zone, after_time_zone)
SELECT CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo');

文字コードと照合順序 #

文字コード

SHOW VARIABLES LIKE '%character\_set\_%';

照合順序(ソート順)

SHOW VARIABLES LIKE 'collation%';

サーバ全体 #

サーバの文字コードの確認

SHOW GLOBAL VARIABLES LIKE 'character_set_server';

サーバの照合順序(ソート順)の確認

SHOW GLOBAL VARIABLES LIKE 'collation_server';

DB単位 #

DB作成時に文字コードと照合順序(ソート順)を設定

CREATE DATABASE `sample_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DBの文字コードの確認

SHOW GLOBAL VARIABLES LIKE 'character_set_database';

DBの照合順序(ソート順)の確認

SHOW GLOBAL VARIABLES LIKE 'collation_database';

カラム単位 #

サーバやDBと異なる文字コードや照合順序(ソート順)を設定する方法

CREATE TABLE tbl1 (
    id bigint NOT NULL,
    col1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci;

照合順序が異なるカラムで結合 #

SELECT * FROM sample_table_1 a
INNER JOIN sample_table_2 b
ON a.name = b.name COLLATE utf8mb4_0900_as_ci;

文字コード `utf8mb4` のデフォルトの照合順序のMySQLバージョンごとの違い #

Version	Character Set(文字コード)	Collation(照合順序=ソート順)
>= 8.0	`utf8mb4`	`utf8mb4_general_ci`
<= 5.7	`utf8mb4`	`utf8mb4_0900_ai_ci`

データベース/テーブル/カラムの一覧 #

データベースの一覧 #

SHOW DATABASES;

SELECT
    DISTINCT table_schema AS database_name
FROM
    information_schema.tables
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
ORDER BY
    table_schema;

テーブルの一覧 #

USE sample_database;
SHOW TABLES;

SHOW TABLES FROM sample_database;

SELECT
    table_schema AS database_name,
    table_name
FROM
    information_schema.tables
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
    AND table_type = 'BASE TABLE'
ORDER BY
    table_schema,
    table_type,
    table_name;

テーブルごとの自動採番 AUTO_INCREMENT の一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    auto_increment
FROM
    information_schema.tables
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
    AND table_type = 'BASE TABLE'
ORDER BY
    table_schema,
    table_name;

テーブルごとのカラムの一覧（カンマ区切り） #

SELECT
    table_schema AS database_name,
    table_name,
    GROUP_CONCAT(column_name) AS column_names
FROM
    information_schema.columns
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
GROUP BY
    table_schema,
    table_name

テーブルの物理名と論理名（テーブル・コメント）の一覧 #

SELECT
    table_schema AS databae_name,
    table_name,
    table_comment
FROM
    information_schema.tables
WHERE
    table_schema = 'sample_database'
ORDER BY
    table_schema,
    table_name;

カラムの物理名と論理名（カラム・コメント）の一覧 #

SELECT
    table_schema AS databae_name,
    table_name,
    column_name,
    column_comment
FROM
    information_schema.columns
ORDER BY
    table_schema,
    table_name;

特定のカラムを含むテーブルの一覧 #

SELECT
    table_schema AS databae_name,
    table_name,
    column_name,
    column_comment
FROM
    information_schema.columns
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
    AND column_name LIKE '%sample_column%'
ORDER BY
    table_schema,
    table_name,
    column_name;

インデックスの一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    column_name,
    index_name
FROM
    information_schema.statistics
ORDER BY
    table_schema,
    table_name,
    column_name;

ビューの一覧 #

SELECT
    table_schema AS database_name,
    table_name AS view_name
FROM
    information_schema.tables
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
    AND table_type = 'VIEW'
ORDER BY
    table_schema,
    table_name;

テーブルのキー情報の一覧 #

テーブルごとのプライマリーキー制約の一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    column_name AS primary_key
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    constraint_name = 'PRIMARY';

テーブルごとのユニークキー制約の一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    GROUP_CONCAT(column_name) AS unique_keys
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    position_in_unique_constraint = 1
GROUP BY
    table_schema,
    table_name;

テーブルごとの外部キー制約の一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    GROUP_CONCAT(
        CONCAT(
            column_name,
            '=',
            referenced_table_name,
            '.',
            referenced_column_name
        )
    ) AS referenced
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    referenced_table_name IS NOT NULL
GROUP BY
    table_schema,
    table_name;

テーブルごとのプライマリーキー・ユニークキー・外部キー一覧 #

SELECT
    table_info.*,
    primary_info.primary_key,
    unique_info.unique_keys,
    reference_info.referenced
FROM
    (
        SELECT
            table_schema AS database_name,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
    ) AS table_info
    LEFT JOIN (
        SELECT
            table_schema AS database_name,
            table_name,
            GROUP_CONCAT(column_name) AS unique_keys
        FROM
            information_schema.KEY_COLUMN_USAGE
        WHERE
            position_in_unique_constraint = 1
        GROUP BY
            table_schema,
            table_name
    ) AS unique_info ON table_info.database_name = unique_info.database_name
    AND table_info.table_name = unique_info.table_name
    LEFT JOIN (
        SELECT
            table_schema AS database_name,
            table_name,
            column_name AS primary_key
        FROM
            information_schema.KEY_COLUMN_USAGE
        WHERE
            constraint_name = 'PRIMARY'
    ) AS primary_info ON table_info.database_name = primary_info.database_name
    AND table_info.table_name = primary_info.table_name
    LEFT JOIN (
        SELECT
            table_schema AS database_name,
            table_name,
            GROUP_CONCAT(
                CONCAT(
                    column_name,
                    '=',
                    referenced_table_name,
                    '.',
                    referenced_column_name
                )
            ) AS referenced
        FROM
            information_schema.KEY_COLUMN_USAGE
        WHERE
            referenced_table_name IS NOT NULL
        GROUP BY
            table_schema,
            table_name
    ) AS reference_info ON table_info.database_name = reference_info.database_name
    AND table_info.table_name = reference_info.table_name

データ容量の一覧 #

データベースごとのサイズの一覧 #

SELECT
    table_schema AS database_name,
    CONCAT(
        SUM(data_length + index_length) / (1024 * 1024),
        ' MB'
    ) AS db_size
FROM
    information_schema.tables
GROUP BY
    table_schema
ORDER BY
    table_schema;

データベースごとのテーブル数の一覧 #

SELECT
    table_schema AS database_name,
    COUNT(*) AS table_count
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
GROUP BY
    table_schema;

テーブルごとのサイズの一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    CONCAT(
        (data_length + index_length) / (1024 * 1024),
        ' MB'
    ) AS table_size
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
    AND table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
ORDER BY
    table_schema,
    (data_length + index_length) DESC,
    table_name;

テーブルごとのレコード数の一覧 #

SELECT
    table_schema AS database_name,
    table_name,
    table_rows
FROM
    information_schema.tables AS `target`
WHERE
    table_type = 'BASE TABLE'
    AND table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
ORDER BY
    table_schema,
    table_rows DESC,
    table_name;

データベース/テーブルごとの件数/容量/カラム/キー情報/文字コード/AUTO_INCREMENTの一覧 #

SELECT
    table_info.table_schema AS database_name,
    database_info.table_count,
    database_info.db_size,
    table_info.table_name,
    table_info.table_rows,
    table_info.table_size,
    columns_info.column_names,
    key_info.primary_key,
    key_info.unique_keys,
    key_info.referenced,
    table_info.auto_increment,
    table_info.table_collation AS character_info
FROM
    (
        -- 全テーブル情報
        SELECT
            *,
            CONCAT(
                (data_length + index_length) / (1024 * 1024),
                ' MB'
            ) AS table_size
        FROM
            information_schema.tables
        WHERE
            table_schema NOT IN (
                'mysql',
                'perfomance_schema',
                'information_schema'
            )
            AND table_type = 'BASE TABLE'
    ) AS table_info
    LEFT JOIN (
        -- データベースの容量とテーブル数
        SELECT
            table_schema AS database_name,
            CONCAT(
                SUM(data_length + index_length) / (1024 * 1024),
                ' MB'
            ) AS db_size,
            SUM(
                CASE
                    WHEN table_type = 'BASE TABLE' THEN 1
                    ELSE 0
                END
            ) AS table_count
        FROM
            information_schema.tables
        GROUP BY
            table_schema
    ) AS database_info ON table_info.table_schema = database_info.database_name
    LEFT JOIN (
        -- テーブルごとのカラム
        SELECT
            table_schema AS database_name,
            table_name,
            GROUP_CONCAT(column_name) AS column_names
        FROM
            information_schema.columns
        WHERE
            table_schema NOT IN (
                'mysql',
                'perfomance_schema',
                'information_schema'
            )
        GROUP BY
            table_schema,
            table_name
    ) AS columns_info ON table_info.table_schema = columns_info.database_name
    AND table_info.table_name = columns_info.table_name
    LEFT JOIN (
        -- テーブルのキー情報（プライマリーキー、ユニークキー、外部キー）
        SELECT
            table_info.*,
            primary_info.primary_key,
            unique_info.unique_keys,
            reference_info.referenced
        FROM
            (
                SELECT
                    table_schema AS database_name,
                    table_name
                FROM
                    information_schema.tables
                WHERE
                    table_type = 'BASE TABLE'
            ) AS table_info
            LEFT JOIN (
                SELECT
                    table_schema AS database_name,
                    table_name,
                    GROUP_CONCAT(column_name) AS unique_keys
                FROM
                    information_schema.KEY_COLUMN_USAGE
                WHERE
                    position_in_unique_constraint = 1
                GROUP BY
                    table_schema,
                    table_name
            ) AS unique_info ON table_info.database_name = unique_info.database_name
            AND table_info.table_name = unique_info.table_name
            LEFT JOIN (
                SELECT
                    table_schema AS database_name,
                    table_name,
                    column_name AS primary_key
                FROM
                    information_schema.KEY_COLUMN_USAGE
                WHERE
                    constraint_name = 'PRIMARY'
            ) AS primary_info ON table_info.database_name = primary_info.database_name
            AND table_info.table_name = primary_info.table_name
            LEFT JOIN (
                SELECT
                    table_schema AS database_name,
                    table_name,
                    GROUP_CONCAT(
                        CONCAT(
                            column_name,
                            '=',
                            referenced_table_name,
                            '.',
                            referenced_column_name
                        )
                    ) AS referenced
                FROM
                    information_schema.KEY_COLUMN_USAGE
                WHERE
                    referenced_table_name IS NOT NULL
                GROUP BY
                    table_schema,
                    table_name
            ) AS reference_info ON table_info.database_name = reference_info.database_name
            AND table_info.table_name = reference_info.table_name
    ) AS key_info ON table_info.table_schema = key_info.database_name
    AND table_info.table_name = key_info.table_name
ORDER BY
    table_info.table_schema,
    table_info.table_name;

データベース #

データベースの新規作成 #

CHARACTER SET 文字コード
utf8mb4 絵文字にも対応。
COLLATE 照合順序
utf8mb4_bin: 全ての区別あり（大文字小文字/全角半角の区別あり）
utf8mb4_general_ci: 大文字小文字の区別なし
utf8mb4_unicode_ci: 大文字小文字/全角半角の区別なし

CREATE DATABASE sample_db DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE = utf8mb4_bin;

データベースの文字コードの変更 #

ALTER DATABASE sample_db DEFAULT CHARACTER SET = utf8mb4;

ユーザー #

ユーザーの新規作成 #

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'sample_password';

ユーザーのパスワードの設定 #

ログイン中ユーザーのパスワードの設定 #

MySQL 5.5 & 5.6

-- ログイン中のユーザーを確認
SELECT CURRENT_USER();
-- パスワードを設定
SET PASSWORD = PASSWORD('sample_password');

MySQL 5.7

-- ログイン中のユーザーを確認
SELECT CURRENT_USER();
-- パスワードを設定
SET PASSWORD = 'sample_password';

MySQL 6

ALTER USER USER() IDENTIFIED BY 'password';

特定のユーザーのパスワードの設定 #

via Command Line

mysqladmin -u sample_user -h sample_host_name password "sample_password"

MySQL 5.5 & 5.6

SET PASSWORD FOR 'sample_user'@'sample_host_name' = PASSWORD('sample_password');

MySQL 5.7

SET PASSWORD FOR 'sample_user'@'sample_host_name' = 'sample_password';

MySQL 8

ALTER USER 'sample_user'@'sample_host_name' IDENTIFIED BY 'sample_password';

ユーザーの権限の設定 #

データベース権限 #

sample_user に sample_db の全テーブルの全ての権限を付与

GRANT ALL ON sample_db.* TO 'sample_user'@'localhost';

sample_user に sample_db の全テーブルの参照/新規登録の権限を付与

GRANT SELECT, INSERT ON sample_db.* TO 'sample_user'@'localhost';

テーブル権限 #

sample_user に sample_db の sample_table の参照権限を付与

GRANT SELECT ON sample_db.sample_table TO 'sample_user'@'localhost';

sample_user の既存の権限を変更せずに 1時間あたりの発行可能クエリ数を制限

MAX_QUERIES_PER_HOUR count クエリ数
MAX_UPDATES_PER_HOUR count 更新件数
MAX_CONNECTIONS_PER_HOUR count コネクション数（同時接続の最大数を制限）
(結果がクエリーキャッシュから得られたクエリーは、MAX_QUERIES_PER_HOUR 制限に対してカウントされません。) count が 0 (デフォルト) である場合、これは、このアカウントに対する制限が存在しないことを示します。

GRANT USAGE ON *.* TO 'sample_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

カラム権限 #

カラム権限で付与される権限 INSERT, SELECT, UPDATE の後にカッコで囲まれた 1つ以上のカラムを指定

GRANT SELECT (sample_column_1, sample_column_2), UPDATE (sample_column_1) ON sample_db.sample_table TO 'sample_user'@'localhost';

権限 #

ユーザーごとの権限の一覧 #

SELECT
    grantee AS user,
    is_grantable AS is_grantable,
    GROUP_CONCAT(privilege_type) AS privileges
FROM
    information_schema.user_privileges
GROUP BY
    grantee,
    is_grantable
ORDER BY
    grantee;

データベースごとの権限の一覧 #

SELECT
    grantee AS user,
    table_schema AS db,
    GROUP_CONCAT(privilege_type) AS privileges
FROM
    information_schema.schema_privileges
GROUP BY
    grantee,
    table_schema
ORDER BY
    grantee,
    table_schema;

テーブルごとの権限の一覧 #

SELECT
    grantee AS user,
    table_schema AS db,
    table_name AS `table`,
    GROUP_CONCAT(privilege_type) AS privileges
FROM
    information_schema.table_privileges
GROUP BY
    grantee,
    table_schema,
    table_name
ORDER BY
    grantee,
    table_schema,
    table_name;

カラムごとの権限の一覧 #

SELECT
    grantee AS user,
    table_schema AS db,
    table_name AS `table`,
    column_name AS `column`,
    GROUP_CONCAT(privilege_type) AS privileges
FROM
    information_schema.column_privileges
GROUP BY
    grantee,
    table_schema,
    table_name,
    column_name
ORDER BY
    grantee,
    table_schema,
    table_name,
    column_name;

ユーザーごと・データベースごと・テーブルごと・カラムごとの権限の一覧 #

SELECT
    *
FROM
    (
        SELECT
            grantee AS user,
            is_grantable AS is_grantable,
            '-' AS db,
            '-' AS `table`,
            '-' AS `column`,
            GROUP_CONCAT(privilege_type) AS privileges
        FROM
            information_schema.user_privileges
        GROUP BY
            grantee,
            is_grantable
        UNION
        ALL
        SELECT
            grantee AS user,
            is_grantable AS is_grantable,
            table_schema AS db,
            '-' AS `table`,
            '-' AS `column`,
            GROUP_CONCAT(privilege_type) AS privileges
        FROM
            information_schema.schema_privileges
        GROUP BY
            grantee,
            is_grantable,
            table_schema
        UNION
        ALL
        SELECT
            grantee AS user,
            is_grantable AS is_grantable,
            table_schema AS db,
            table_name AS `table`,
            '-' AS `column`,
            GROUP_CONCAT(privilege_type) AS privileges
        FROM
            information_schema.table_privileges
        GROUP BY
            grantee,
            is_grantable,
            table_schema,
            table_name
        UNION
        ALL
        SELECT
            grantee AS user,
            is_grantable AS is_grantable,
            table_schema AS db,
            table_name AS `table`,
            column_name AS `column`,
            GROUP_CONCAT(privilege_type) AS privileges
        FROM
            information_schema.column_privileges
        GROUP BY
            grantee,
            is_grantable,
            table_schema,
            table_name,
            column_name
    ) AS authority
ORDER BY
    user,
    db,
    `table`,
    `column`;

日付・時刻 #

MySQL Reference Manual - Date and Time Functions

日時フォーマット #

SELECT DATE_FORMAT(CURRENT_TIME(), '%Y-%m-%d %k:%i:%s'); -- 2020-01-04 13:30:10

現在時刻 #

SELECT NOW(); -- 2020-01-05 13:30:10

現在時刻の時刻のみ #

SELECT CURRENT_TIME(); -- 13:30:10

今日 #

SELECT CURRENT_DATE(); -- 2020-01-05

昨日 #

SELECT CURRENT_DATE() - INTERVAL 1 DAY; -- 2020-01-04

明日 #

SELECT CURRENT_DATE() + INTERVAL 1 DAY; -- 2020-01-06

1週間前 #

SELECT CURRENT_DATE() - INTERVAL 7 DAY; -- 2019-12-29

1週間後 #

SELECT CURRENT_DATE() + INTERVAL 7 DAY; -- 2020-01-12

当月1日 #

SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'); -- 2020-01-01

当月末日 #

SELECT LAST_DAY(CURRENT_DATE()); -- 2020-01-31

当月1日〜当月末日 #

SELECT
    *
FROM
    sample_table
WHERE
    sample_column >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
    AND sample_column <= LAST_DAY(CURRENT_DATE());

先月1日 #

SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01'); -- 2019-12-01
-- or
SELECT DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH), '%Y-%m-01'); -- 2019-12-01

先月末日 #

SELECT LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)); -- 2019-12-31
-- or
SELECT LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)); -- 2019-12-31

来月1日 #

SELECT DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01'); -- 2020-02-01

来月末日 #

SELECT LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)); -- 2020-02-29

昨年 #

SELECT DATE_FORMAT((NOW() - INTERVAL 1 YEAR), '%Y-%m-%d');

来年 #

SELECT DATE_FORMAT((NOW() + INTERVAL 1 YEAR), '%Y-%m-%d');

3年前 #

SELECT DATE_FORMAT((NOW() - INTERVAL 3 YEAR), '%Y-%m-%d');

3年後 #

SELECT DATE_FORMAT((NOW() + INTERVAL 3 YEAR), '%Y-%m-%d');

日付の差分 #

SELECT DATEDIFF('2020-12-31 23:59:59','2020-12-30'); -- 1
SELECT DATEDIFF('2020-11-30 23:59:59','2020-12-31'); -- -31

年のみ #

SELECT YEAR('2020-12-31'); -- 2020

月のみ #

SELECT MONTH('2020-12-31'); -- 12
-- or
SELECT DAYOFMONTH('2020-12-31'); -- 12

日のみ #

SELECT DAY('2020-12-31'); -- 31
-- or
SELECT DAYOFMONTH('2020-12-31'); -- 31

曜日 #

曜日インデックス (1 = Sunday、2 = Monday、…、7 = Saturday)

SELECT DAYOFWEEK('2007-02-03'); -- 7

曜日インデックス (0 = Monday、1 = Tuesday、…6 = Sunday)

SELECT WEEKDAY('2008-02-03 22:23:00'); -- 6

曜日（日本語） #

SELECT
    CASE
        DATE_FORMAT(NOW(), '%w')
        WHEN 0 THEN '日'
        WHEN 1 THEN '月'
        WHEN 2 THEN '火'
        WHEN 3 THEN '水'
        WHEN 4 THEN '木'
        WHEN 5 THEN '金'
        WHEN 6 THEN '土'
    END;

年内の歴週 #

SELECT WEEKOFYEAR('2008-02-20'); -- 8

年内の通日 #

SELECT DAYOFYEAR('2007-02-03'); -- 34

便利関数 #

値がNULLの場合の初期値を返却 IFNULL #

SELECT
    IFNULL(name, 'anonym') AS name
FROM
    sample_table;

最初のNULL以外の値を返却 COALESCE #

NULL以外の値がない場合 NULLを返却

SELECT
    COALESCE(
        phone_number_smart_private,
        phone_number_home,
        phone_number_office,
        NULL
    ) AS phone_number
FROM
    sample_table;

カンマ区切りで結合 GROUP_CONCAT #

SELECT
    GROUP_CONCAT(id) AS id -- '1,2,3,4,5'
FROM
    sample_table
ORDER BY
    id;

カンマ区切りの値を検索 FIND_IN_SET #

INDEXが効かないため要注意

SELECT
    *
FROM
    sample_table
WHERE
    FIND_IN_SET(id, '1,2,3,4');

指定した順番に並べ替え FIELD #

SELECT
    *
FROM
    sample_table
WHERE
    id IN(1, 2, 3)
ORDER BY
    FIELD(id, 3, 1, 2);

条件分岐 CASE #

SELECT
  CASE
    WHEN active_flag = true THEN 'ON'
    WHEN active_flag = false THEN 'OFF'
    ELSE '' END
FROM
  sample_table;

SELECT
    id,
    name,
    score,
    CASE
        WHEN score >= 80 THEN 'Excellent'
        WHEN 80 > score AND score >= 70 THEN 'Good'
        WHEN 70 > score AND score >= 60 THEN 'Average'
        WHEN 60 > score AND score >= 50 THEN 'Below Average'
        WHEN 50 > score THEN 'Poor'
        ELSE ''
    END AS 'evaluation'
FROM
    scores;

便利SQL #

SELECT句の値を変数にセット #

SELECT
    100,
    'sample'
INTO
    @HOGE,
    @FOO;

SELECT @HOGE, @FOO;
+-------+--------+
| @HOGE | @FOO   |
+-------+--------+
|   100 | sample |
+-------+--------+

SELECT/INSERT (SELECT したデータを INSERT) #

INSERT INTO
    sample_table_1 (sample_column_1, sample_column_2, sample_column_3)
SELECT
    sample_column_1,
    sample_column_2,
    sample_column_3
FROM
    sample_table_2;

一部の値を固定値で登録（以下の例では sample_column_3 に固定値 ‘hoge’ を登録

INSERT INTO
    sample_table_1 (sample_column_1, sample_column_2, sample_column_3)
SELECT
    sample_column_1,
    sample_column_2,
    'hoge' AS sample_column_3
FROM
    sample_table_2;

SELECT/UPDATE (SELECT したデータを UPDATE) #

UPDATE
    sample_table_2 dest,
    (
        SELECT
            first_name,
            last_name,
            birthday,
            email
        FROM
            sample_table_1
        WHERE
            updated_at > '2021-01-01 00:00:00'
    ) src
SET
    dest.fullname = CONCAT(src.first_name, ' ', src.last_name),
    dest.birthday = src.birthday,
    dest.email = src.email
WHERE
    dest.email = src.email
;

特定のカラムの値が重複しているレコードを抽出 HAVING/GROUP BY #

SELECT
    sample_column,
    COUNT(*) AS count
FROM
    sample_table
GROUP BY
    sample_column
HAVING
    count > 1
ORDER BY
    sample_column;

置換 #

文字列を置換して更新 #

UPDATE
    sample_table
SET
    sample_column = REPLACE(sample_column, 'Search String', 'Replacement String')
WHERE
    sample_column LIKE '%Search String%';

改行コード #

Carriage Return (CR) #

\r

CHAR(13)

Line Feed (LF) #

\n

CHAR(10)

改行コードを半角スペースに置換 #

SELECT
    REPLACE(REPLACE(REPLACE(sample_column, '\r\n', ' '), '\r', ' '), '\n', ' ')
FROM
    sample_table;

SELECT
    REPLACE(REPLACE(REPLACE(sample_column, CHAR(13) + CHAR(10), ' '), CHAR(13), ' '), CHAR(10), ' ')
FROM
    sample_table;

UPDATE
    sample_table
SET
    sample_column = REPLACE(REPLACE(REPLACE(sample_column, '\r\n', ' '), '\r', ' '), '\n',' ');

UPDATE
    sample_table
SET
    REPLACE(REPLACE(REPLACE(sample_column, CHAR(13) + CHAR(10), ' '), CHAR(13), ' '), CHAR(10), ' ');

VIEW #

VIEW の概要 #

VIEW（ビュー）とはテーブルから取得したいデータの条件を定義し、あたかも独立したテーブルのように扱うためのもの。
VIEWそのものはデータを持たず、元になったテーブルからデータを参照。
VIEWだけ閲覧できるよう権限を設定することも可能。
参照: ビューの作成

VIEWの作成 #

CREATE VIEW view_name AS select_statement

CREATE VIEW view_name (sample_column_1, sample_column_2) AS select_statement

e.g.

CREATE VIEW sales_report AS
SELECT
    DATE_FORMAT(receipt_date, '%Y%m') AS month,
    id AS product_id,
    SUM(amount) AS total_number,
    SUM(amount * price) AS total_amount
FROM
    orders
ORDER BY
    month, product_id;

SELECT * FROM sales_report;

CREATE VIEW sales_report (month, product_id, total_number, total_amount) AS
SELECT
    DATE_FORMAT(receipt_date, '%Y%m') AS sales_month,
    id,
    SUM(amount),
    SUM(amount * price)
FROM
    orders
ORDER BY
    sales_month, id;

SELECT * FROM sales_report;

VIEW の変更 #

CREATE OR REPLACE VIEW sample_view AS select_statement;

ALTER VIEW view_name AS select_statement

ALTER VIEW view_name (sample_column_1, sample_column_2) AS select_statement

VIEWのCREATE文の確認 #

SHOW CREATE VIEW sample_view;

VIEW を削除 #

DROP VIEW sample_view;

DROP VIEW IF EXISTS sample_view;

削除前後の確認

SHOW TABLES;

VIEWの一括DROP文 #

SELECT
    CONCAT('DROP VIEW ', table_name, ';') AS view_drop_sql
FROM
    information_schema.tables
WHERE
    table_schema NOT IN (
        'mysql',
        'perfomance_schema',
        'information_schema'
    )
    AND table_type = 'VIEW'
ORDER BY
    table_schema,
    table_name;

VIEWの一覧を作成するCREATE文の一覧 #

SELECT
    CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS target_view,
    CONCAT(
        'CREATE ALGORITHM=UNDEFINED DEFINER=`',
        DEFINER,
        '` SQL SECURITY ',
        SECURITY_TYPE,
        ' VIEW `',
        TABLE_NAME,
        '` AS ',
        VIEW_DEFINITION,
        ';'
    ) AS create_view
FROM
    information_schema.views;

その他 #

SQLの実行計画 #

EXPLAIN SELECT * FROM sample_table WHERE sample_flag = 1;

一時的なテーブルの作成 #

CREATE TEMPORARY TABLE new_table SELECT * FROM original_table

データベースへのコネクション数 #

threads へのアクセスには相互排他ロックは必要なく、サーバーパフォーマンスへの影響は最小です。INFORMATION_SCHEMA.PROCESSLIST と SHOW PROCESSLIST では相互排他ロックが必要になるため、パフォーマンスの低下につながります。 https://dev.mysql.com/doc/refman/5.6/ja/performance-schema-threads-table.html

SELECT
    processlist_host,
    COUNT(*) AS connection_count
FROM
    performance_schema.threads
WHERE
    TYPE = 'FOREGROUND'
GROUP BY
    processlist_host
ORDER BY
    processlist_host;

プロセスを終了(KILL) #

プロセスIDを確認

SHOW PROCESSLIST;
+----+-------------+----------------+-----------+---------+------+----------+------------------+
| Id | User        | Host           | db        | Command | Time | State    | Info             |
+----+-------------+----------------+-----------+---------+------+----------+------------------+
|  8 | sample_user | localhost:XXXX | sample_db | Sleep   |   40 |          | NULL             |
| 10 | sample_user | localhost:XXXX | sample_db | Query   |    0 | starting | SHOW PROCESSLIST |
+----+-------------+----------------+-----------+---------+------+----------+------------------+
1 row in set (0.01 sec)

MySQL のKILLコマンドでは1つのプロセスIDのみ指定可(複数IDの同時指定は不可)

KILL 8

特定の時間を経過しているプロセスを確認 #

60秒以上経過しているプロセス(処理)を確認

SELECT * FROM information_schema.processlist WHERE time > 59;

複数のプロセスを終了(KILL) #

SELECT GROUP_CONCAT(id) AS id_list FROM information_schema.processlist WHERE time > 59;
-- 10,11,12

mysqladmin コマンドの引数で kill (終了) を実行する場合は複数IDの同時指定可(カンマ区切り)

mysqladminn kill 10,11,12 -h localhost -u sample_user

テーブルのロック状態 #

ストレージエンジンがInnoDBの場合

SELECT * FROM information_schema.innodb_locks;

トランザクション #

BEGIN;

UPDATE sample_table SET email = 'sample1@email.com' WHERE id = 1;
UPDATE sample_table SET email = 'sample1@email.com' WHERE id = 2;

-- 失敗した場合は ROLLBACK;

COMMIT;

START TRANSACTION;

UPDATE sample_table SET email = 'sample1@email.com' WHERE id = 1;
UPDATE sample_table SET email = 'sample1@email.com' WHERE id = 2;

-- 失敗した場合は ROLLBACK;

COMMIT;

AUTO_INCREMENT（自動採番） #

AUTO_INCREMENT（自動採番）の値を確認 #

SELECT
    table_schema as database_name,
    table_name,
    auto_increment
FROM
    information_schema.tables
ORDER BY
    table_schema,
    table_name;

SHOW TABLE STATUS WHERE name = 'sample_table';

SHOW TABLE STATUS LIKE 'sample_table_prefix%';

AUTO_INCREMENT（自動採番）の値を変更 #

ALTER TABLE sample_table AUTO_INCREMENT = 1000;

設定 #

トランザクションのタイムアウト #

SHOW GLOBAL VARIABLES LIKE '%lock_wait_time_out'
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| lock_wait_timeout_ms | 300000 |
+----------------------+--------+
1 row in set (0.01 sec)

暗号化 #

MySQL Reference Manual - Encryption and Compression Functions

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('My secret passphrase',512);
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
+-----------------------------------------------+
| AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
+-----------------------------------------------+
| text                                          |
+-----------------------------------------------+