[MySQL] チートシート

私の作業用として、よく使うSQLやTIPS、トラブルシューティング的なものをまとめました。

SQL

データベース操作

バージョン取得

select version();

データベース一覧

show databases;

カレントデータベースの変更

use database_name;

カレントデータベースの確認

select database();

データベースの作成

-- データベースの文字セットをUTF8MB4で作成
create database database_name character set utf8mb4;
show databases;

-- パスワードを生成
select password('user_password');

-- 生成したパスワードでユーザーを新規作成
create user 'user_name'@'localhost' identified by password '*xxxx'; -- ↑「*xxxx」はpassword()で取得した値

-- 新規作成したユーザーにデータベースへのアクセス権を付与
grant all on *.* to 'user_name'@'localhost';
grant file on *.* to 'user_name'@'localhost';

データベースのサイズ確認

select
  table_schema
  , sum(data_length + index_length) / 1024 / 1024 / 1024 AS GB
from
  information_schema.tables 
group by
  table_schema 
order by
  sum(data_length + index_length) desc
;

データベースの照合順序確認と変更

-- データベースの照合順序確認と変更
show variables like '%collation%';
alter database collate 'utf8mb4_bin';

ユーザー操作

ユーザー一覧

select * from mysql.user;

ユーザーの作成

-- パスワードを生成
select password('user_password');

-- 生成したパスワードでユーザーを新規作成
create user 'user_name'@'localhost' identified by password '*xxxx'; -- ↑「*xxxx」はpassword()で取得した値

-- 新規作成したユーザーにデータベースへのアクセス権を付与
grant all on *.* to 'user_name'@'localhost';
grant file on *.* to 'user_name'@'localhost';

ユーザーの権限確認

-- ユーザーごとの権限確認
show grants for 'username'@'localhost';

-- データベースに対するユーザーの権限確認
select * from mysql.db;

テーブル操作

テーブル作成SQL確認

show create table table_name;

主キー追加

alter table table_name add primary key (column_name_1, column_name_2);

テーブル・カラム一覧

select
  t.table_name, t.table_comment, t.table_type, t.engine, t.row_format
  , c.column_name, c.column_comment, c.data_type, c.is_nullable, c.character_maximum_length, c.character_octet_length, c.numeric_precision, c.numeric_scale, datetime_precision
from
  information_schema.tables t
  inner join information_schema.columns c
    on c.table_schema = t.table_schema
    and c.table_name = t.table_name
where
  t.table_schema = 'table_schema'
  AND t.table_name = 'table_name'
order by
  t.table_name
  , c.ordinal_position
;

テーブルの照合順序確認と変更

show table status from database_name;
alter table table_name collate 'utf8mb4_bin';

インデックス操作

インデックス確認

show index from table_name;

インデックス作成

create index index_name on table_name(column_name_1, column_name_2);

カラム操作

あるテーブルのカラム一覧

select * from information_schema.columns c where c.table_schema = 'table_name';

カラム追加

alter table table_name add column column_name {data_type} {null/not null} comment 'comment' after column_name_1;

カラム変更

-- 型変更
alter table table_name modify column_name {data_type} {null/not null};

-- カラム名・デフォルト値変更
alter table table_name change column_name new_column_name {data_type} {null/not null} {default value};

カラム削除

alter table table_name drop column column_name;

カラムの照合順序確認、変更

select table_schema, table_name, column_name, data_type, character_maximum_length, character_set_name, collation_name
  from information_schema.columns
  where table_schema = 'database_name'
;
alter table table_name modify column column_name varchar(256) collate 'utf8mb4_bin';

ストアドプロシージャ操作

ストアドプロシージャ作成

delimiter //
create procedure create_number_table(in max_number int)
begin
  declare num int;
  set num = 1;
 
  drop table if exists __numbers;
  create table __numbers(
    number int not null
    , primary key (number)
  );
 
  while max_number > num do
    insert into __numbers(number) values(num);
    set num = num + 1;
  end while;
END;
//

ストアドプロシージャ削除

drop procedure if exists stored_procedure_name;

ストアドプロシージャ実行

call stored_procedure_name(10);

SELECT

select * from table_name where column_1 = 'condition'

INSERT

insert into table_name(column_1, column_2) values ('value_1', 'value_2');
insert into table_name select column_1, column_2 from table_name_1;

UPDATE

update table_name set column_1 = 'value_1' where column_1 = 'condition';

update
  main m
  left outer join sub s
    on s.key = m.key
set
  m.value1 = s.value1
where
  m.key in (1, 2)
;

DELETE

delete from table_name where column_1 = 'condition';

コマンド

mysqlクライアント

mysqlクライアント実行

mysql -hlocalhost -P3306 -uuser_name -ppassword database_name
  • オプションについて
    • -hの後にホスト名
    • -Pの後にポート番号(3306なら省略可)
    • -uの後にユーザー名
    • -pの後にパスワード
    • アクセスするデータベース
  • その他のコマンドラインオプションはこちら

mysqlクライアントでSQLファイルを実行

mysql -hlocalhost -P3306 -uuser_name -ppassword database_name --default-character-set=utf8 < test.sql
  • 実行するSQLファイルの内容によっては–default-character-setでキャラクターセットを指定しないとエラーになる可能性があります

mysqldump

データベースをエクスポート

mysqldump -hlocalhost -P3306 -uuser_name -ppassword database_name > dump.sql
  • リダイレクト先で指定したファイルにデータベースの内容がSQLとして出力されます
    • このファイルをmysqlクライアントに指定することで復元することが可能です
  • その他のコマンドラインオプションはこちら

テーブルを指定してエクスポート

mysqldump -hlocalhost -P3306 -uuser_name -ppassword database_name table_name_1 table_name_2 > table_data.sql
  • データベース名の後にスペース区切りでテーブル名を指定することで、そのテーブルだけのデータがエクスポートできます

テーブルデータだけをエクスポート

mysqldump -hlocalhost -P3306 -uuser_name -ppassword -t database_name table_name_1 table_name_2 > table_data.sql
  • -tオプションをつけることでテーブルデータのみエクスポートが可能です

パフォーマンスチェック/チューニング

SQLの確認

実行中のSQLを確認

show full processlist;

セッションの削除

kill 999;
  • processlistのid番号を指定してください

スロークエリ(実行に時間のかかるSQL)の確認

set global slow_query_log = 1;
set global slow_query_log_file = 'C:/xxx/slow_query.log';
set global long_query_time = 10;
  • サーバー変数を変更するためそれなりの権限のあるユーザーでのみ実行可能です
  • 上記の例では10秒以上かかるSQLがログファイルに出力されます

実行SQLをロギング

set global general_log = 'ON';
  • 全てのSQLをgeneral_log_fileで指定されているファイルに出力します
  • ログファイルが膨大なサイズになるため本番環境等では注意が必要です

実行計画の取得

explain select * from xxx;
  • 結果セットの見方(主なもの)
    • id:実行順序
    • select_type(以下は主なもの)
      • SIMPLE:単純なselect
      • PRIMARY:一番外側のselect
      • UNION:unionされたselect
      • SUBQUERY:サブクエリのselect
      • DERIVED:派生テーブルselect
    • table:テーブル
    • type(以下は主なもの)
      • const:プライマリキー/ユニークキーでのアクセス
      • eq_ref:結合でのconst
      • range:インデックスでの範囲アクセス
      • index:インデックスでの全件アクセス
      • all:テーブルでの全件アクセス
    • ref:抽出条件のカラム
    • rows:推定される抽出結果の行数

チューニング

テーブルの結合における順序が想定通りになってくれない場合(駆動テーブルが結合先のテーブルになっている)、STRAIGHT_JOINが効くかもしれません。

select STRAIGHT_JOIN * from base_table innser join join_table_1 on ...;
  • 上記の例で言えばbase_tableに対してjoin_table_1が結合されるはずですが、explainするとjoin_table_1が駆動テーブルになってしまっているという状態です
  • JOIN_FIXED_ORDERヒントも効くかもしれません
    • 結合順序がfrom句で指定された順番になります

explainで確認した時、こちらが用意したインデックスを使ってくれていない場合にuse indexが効くかもしれません。

select * from base_table use index (base_table_idx1) inner join join_table_1 on ...;
  • use indexを指定してもmysqlがインデックスを使ってくれない場合、force indexを指定すると使ってくれることがあります

TIPS

MySQLのzipファイルを使用してインストール

MySQL Community Downloadsの「Windows (x86, 64-bit), ZIP Archive」からzipファイルをダウンロードします

zipファイルを展開し以下のフォルダを作成(以下はC:\mysql8に展開した例)

  • C:\mysql8
    • data
    • temp

C:\mysql8にmy.iniファイルを作成し以下を記載します。

[mysqld]
port = 3306
default-time-zone = 'Asia/Tokyo'
default_authentication_plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_ja_0900_as_cs
init-connect = SET NAMES utf8mb4
 
basedir  = "C:/mysql8"
datadir  = "C:/mysql8/data"
tmpdir   = "C:/mysql8/temp"
pid-file = "C:/mysqld.pid"

初期化を実行しMySQLを実行します。

C:\mysql8bin\mysqld --initialize
C:\mysql8bin\mysqld --standalone --console

rootパスワード変更のため、C:\mysql8\data\mysqld.errファイルに出力されているパスワードをコピーして以下を実行します。

C:\mysql8> C:\mysql8bin\mysql -u root -p -h localhost -P 3306
Enter password: {コピーしたパスワードを貼り付け}
mysql> alter user root@'localhost' identified by '{新しパスワード}';

タイムゾーンの変更

ここのページから「The other set is for 5.7+」の下にあるtimezone_2022a_posix_sql.zipをダウンロードしSQLファイルを解凍します。(ファイル名は都度変わると思います)

上記のSQLファイルをログインしたmysqlクライアントから実行します。

mysql> use mysql
mysql> \. C:¥Users\xxxx\Downloads\timezone_posix.sql

起動は以下のコマンドで実行します。

C:\mysql8\bin\mysqld.exe ----defaults-file=C:\mysql8\my.ini --console

DBコピー

mysqldumpでダンプファイルを作成、mysqladminで新規DBを作成してそこにダンプファイルからインポート

mysqldump -hloclahost -P3306 -uuser_name -ppassword database_name > dump.sql
mysqladmin --user root create new_database_name
mysql -hloclahost -P3306 -uuser_name -ppassword new_database_name < dump.sql

CSVデータエクスポート

select * from table_name
  into outfile 'table_name.csv'
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n';
  • カンマ区切り、フィールド囲みはダブルコーテーション、行区切りはCRLFです
  • select @@datadirのディレクトリに作成されます

CSVデータインポート

ローカルDBにインポート

load data local infile
  'c:/table_name.csv'
  into table table_name
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines;
  • カンマ区切り、フィールド囲みはダブルコーテーション、行区切りはCRLFです
  • 1行目はスキップしています

リモートDBにインポート

load data infile
  'c:/table_name.csv'
  into table table_name
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines;
  • カンマ区切り、フィールド囲みはダブルコーテーション、行区切りはCRLFです
  • 1行目はスキップしています

MySQL→SQLServer/Oracleへのデータ移行

以下のSQLでは各種コマンド文を生成しています。

  • mysqldumpでデータダンプファイルを出力
  • sqlcmdでデータダンプファイルを取り込む
  • sqlplusでデータダンプファイルを取り込む
SELECT
  0 AS statement_orderby
  , NULL AS TABLE_NAME
  , NULL AS export
  , NULL AS import_sqlserver
  , 'set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS' AS import_oracle
UNION ALL
SELECT
  1 AS statement_orderby
  , t.table_name
  , concat('mysqldump -uuser_name -ppassword --compact --skip-quote-names --complete-insert --skip-extended-insert -t database_name ', t.table_name,' > ', t.table_name, '.sql') AS export
  , concat('sqlcmd -S instance_name\\SERVER_NAME -U sa -P sqlserverP@ssw0rd -d database_name -f i:65001 -i ', t.table_name,'.sql') AS import_sqlserver
  , concat('echo exit | sqlplus.exe USER_NAME/password@SERVER_NAME:1521/DATABASE_NAME @', t.table_name, '.sql') AS import_oracle
FROM
  information_schema.tables t
WHERE
  t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'phpmyadmin')
  AND t.table_schema = 'schema_name'
  AND t.table_type = 'BASE TABLE'
  AND t.table_rows > 0
  AND t.table_name NOT LIKE '%_logs'
  AND t.table_name NOT LIKE '$$%'
  AND t.table_name NOT LIKE '\_%'
ORDER BY
  1
  , 2
;
  • MySQLのエクスポート
    • –compact
      • insert文だけ生成します
    • –skip-quote-names
      • テーブル名・カラム名を囲うダブルコーテーションやシングルコーテーションをつけない
    • –complete-insert
      • カラム名も指定します
    • –skip-extended-insert
      • valuesの後に複数の値を指定させない(insert into (…) values (…), (…);)
      • SQLServerでは1000個までの制約があったためです
  • SQLServerのインポート
    • sqlcmdでinsert文を実行しています
    • あんまり行数が多いと途中でメモリが足らなくて落ちることがあります
      • その場合は1000行区切りでファイルを分割し、実行する必要があります
  • Oracleのインポート
    • sqlplusで実行しています
      • 実行時にexitしないとコミットしてくれないので、そのようにしています
      • 頭で環境変数に以下をセットしないと日時を自動変換してくれずエラーになります
        • set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

テーブル一覧を使ってSQL作成

以下のSQLでは各種SQL文を生成しています。

  • テーブル削除
  • テーブルデータ削除
  • バックアップ(先頭にアンダースコアを付与した新規テーブルに既存テーブルデータをselect+insert)
  • mysqldumpコマンド
  • ↑のdumpコマンドで出力したSQLファイルを実行するmysqlコマンド
  • ↑のdumpコマンドで出力したSQLファイルをzipコマンドで圧縮
  • csv出力
SELECT
  t.table_schema, t.table_name, t.table_comment, t.table_type, t.engine
  , concat('drop table if exists ', t.TABLE_SCHEMA, '.', t.TABLE_NAME, ';') AS "drop table"
  , concat('truncate table ', t.TABLE_SCHEMA, '.', t.TABLE_NAME, ';') AS "truncate table"
  , concat('create table _', t.table_name, ' select * from ', t.table_name, ';') AS "create backup table"
  , concat('mysqldump.exe -hlocalhost -P3307 -u' , :username , ' -p' , :password, ' ', :table_schema, ' ', t.table_name, ' > ', t.table_name, '.sql') AS "mysqldump command cd /d %~dp0"
  , concat('mysql.exe -hlocalhost -P3307 -u' , :username , ' -p' , :password, ' ', :table_schema, ' --default-character-set=utf8 < ', t.table_name, '.sql') AS "mysql command cd /d %~dp0"
  , concat('zip ', t.table_name, '.zip ', t.table_name, '.sql') AS "zip command"
  , concat('select * from ', t.table_name, ' into outfile ', concat('\'', t.table_name, '.csv\''), ' fields terminated by \',\'', ' optionally enclosed by \'"\'', ';') AS "csv data"
FROM
  information_schema.tables t
WHERE
  t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'phpmyadmin')
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema = :table_schema
ORDER BY
  t.table_schema
  , t.table_name
  • パラメータを使っている部分は環境に応じて変更してください

group concat

行列変換、複数行にわたるデータを一列にconcatしながら展開します

SELECT
  a.key_column
  , b.key_column
  , group_concat(b.value_column ORDER BY b.sub_key_column separator ', ') AS b_values
FROM
  (
          SELECT 1 AS key_column, 'key 1' AS value_column
    UNION SELECT 2 AS key_column, 'key 2' AS value_column
  ) a
  INNER JOIN (
          SELECT 1 AS key_column, 1 AS sub_key_column, 'key 1-1' AS value_column
    UNION SELECT 2 AS key_column, 1 AS sub_key_column, 'key 2-1' AS value_column
    UNION SELECT 2 AS key_column, 2 AS sub_key_column, 'key 2-2' AS value_column
  ) b
    ON a.key_column = b.key_column
GROUP BY
  a.key_column
;
key_columnkey_column_1b_values
11key 1-1
22key 2-1, key 2-2

文字→整数

select convert('1', signed);

YYYYMMDD文字列から日付計算

date_format(date_add(concat(table_name.yyyymm_column, '01'), INTERVAL -6 MONTH), '%Y%m')

小数点指定桁で切り捨て

SELECT TRUNCATE(1.234, 1);

結果セットに行番号をつける

SELECT
  test_table.key
  , @row_num := @row_num + 1 AS index_number  -- インクリメントした値を変数にセットしつつ行データとして返す
FROM
  test_table
  , (SELECT @row_num := 0) AS a  -- ここで変数の初期化

ある値を境界として1から採番したい場合(例えばキーが2つあり、そのうちの1つで採番したい)は以下のようにします。

select
  t.group_key_value
  , t.key_value
  -- 境界として使うキーの値が前回と違っていたら1を、そうでなければインクリメントした値をセット
  , @row_num := if(@prev_group_key_value = t.group_key_value, @row_num + 1, 1) as seq
  -- 境界として使うキーの値をセット
  , @prev_group_key_value := t.group_key_value as prev_group_key_value
from
  (
          select '1' as group_key_value, '1-1' as key_value
    union select '2' as group_key_value, '2-1' as key_value
    union select '2' as group_key_value, '2-2' as key_value
    union select '3' as group_key_value, '3-1' as key_value
    union select '3' as group_key_value, '3-2' as key_value
    union select '3' as group_key_value, '3-3' as key_value
  ) t
  , (select @row_num := 0) as a -- 行番号用
  , (select @prev_group_key_value := 0) as b -- 境界として使うキーの値保持用
order by
  t.group_key_value
;

ランダムな行データの取得

以下は行データ10,000件のうち100件をランダムに取得したい場合のSQLです。

SELECT
  index_number
FROM
  test_table
WHERE
  AND rand() <= (100 / 10000) + 0.001
LIMIT
  100
;
  • rand()は0~1までの浮動小数点を返します
  • 10,000件から100件欲しい時は、100/1,000=0.01であるため、理論上100件とれるそうです

暗黙のコミットを引き起こすステートメント

Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE … UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN, RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN.
ALTER FUNCTION, CREATE FUNCTION and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with loadable functions. (ALTER FUNCTION can only be used with stored functions.)

13.3.3 Statements That Cause an Implicit Commit
  • truncate tableはコミットされます
  • create temporary tableもロールバックされずテーブルが残ったままになります

バイナリログの確認/削除

バイナリログの確認

show master logs;

バイナリログの削除

purge master logs TO 'binlog.000001';
  • show master logsの結果セットにあるlog_nameを指定して実行するとファイルが削除されます

SETステートメント実行権限

MySQL 8.0.27からSETステートメントによるシステム変数の変更にはSESSION_VARIABLES_ADMIN権限が必要となったそうです。

権限の追加は以下のSQLです。

GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'user_name'@'%';

ログ設定

my.ini(またはmy.cnf)に以下の設定を追加します。

# ログをファイル出力
log-output=FILE

# 一般クエリログ
general_log=0
general_log_file="general.log"

# スロークエリログ
slow_query_log=1
slow_query_log_file="slow_query.log"
long_query_time=10

# エラーログ
log_error="error.log"
  • 一般クエリログ
    • クライアントからMySQLサーバーに対する接続・接続解除、クライアントから実行されたSQLを出力します
  • スロークエリログ
    • long_query_timeで指定された秒数を超えたSQLをログ出力します
  • エラーログ
    • サーバー起動/停止等で発生したエラーをログ出力します

設定ファイルで設定する以外にもsetステートメントで一時的に変更も可能です。

set global general_log = 'ON';
set global slow_query_log = 'ON';