MySQLとの暮らしに役立ちたいリンク集

タイムアウト

いろんなタイムアウトを確認する

show global variables like '%timeout%';

Variable_name	値	意味	参考URL
aurora_fwd_master_idle_timeout	秒	プライマリクラスターがセカンダリクラスターから転送された接続でアクティビティを終了するまで待機する秒数。この期間を超えてセッションがアイドル状態のままである場合、Aurora はセッションをキャンセルします。	Amazon Aurora Global Database の書き込み転送を使用する - Amazon Aurora
aurora_globaldb_rpo_wait_timeout
aurora_zdr_timeout_on_replica_fall_behind
connect_timeout		MySQLがクライアントからの接続パケットを待機する時間	[第75回　MySQLのさまざまなタイムアウトオプションについて
delayed_insert_timeout		(MySQL5.6.7以降非推奨)終了する前に、INSERT DELAYED ハンドラスレッドが INSERT ステートメントを待機する秒数。	MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
have_statement_timeout	Boolean	ステートメントの実行タイムアウト機能が使用可能かどうか	MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.8 サーバーシステム変数
innodb_flush_log_at_timeout	秒	ログを N 秒ごとに書き込んで、フラッシュする	MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数
innodb_lock_wait_timeout	秒	行ロックが解除されるまで InnoDB トランザクションが待機する時間	MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.14 InnoDB の起動オプションおよびシステム変数
innodb_rollback_on_timeout	Boolean	InnoDB rolls back では、トランザクションタイムアウトの最後のステートメントのみがデフォルトで実行	MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.14 InnoDB の起動オプションおよびシステム変数
lock_wait_timeout	秒	メタデータロックを取得するための待機時間	[第75回　MySQLのさまざまなタイムアウトオプションについて
net_read_timeout	秒	読み取りを中止する前に接続からのデータを待機する秒数	MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.8 サーバーシステム変数
net_write_timeout	秒	書き込みを中止する前にブロックが接続に書き込まれるのを待機する秒数	MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.8 サーバーシステム変数
rpl_stop_slave_timeout	秒	タイムアウトまでに STOP SLAVE が待機する時間	MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.4.3 レプリケーションスレーブのオプションと変数
slave_net_timeout	秒	マスター/スレーブ接続から後続のデータを待機する秒数 (これ以降は、読み取りを中止)	MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.4.3 レプリケーションスレーブのオプションと変数
interactive_timeout	秒	対話型の接続に対してのアイドルタイムアウト時間	[第75回　MySQLのさまざまなタイムアウトオプションについて
wait_timeout	秒	非対話型の接続に対してのアイドルタイムアウト時間	MySQL の wait_timeout デフォルト値のワナ（接続モードによる値の違い） - Qiita

ダンプファイルを〇〇したい

# 特定のデータベースのダンプを取りたい(GitBashでは対話ができないので「-pパスワード」を指定して実行する)
$ mysqldump --single-transaction -u {ユーザ名} {データベース} -p > {ダンプファイル}.dmp
# 特定のデータベースのダンプを入れたい(リストア)
$ mysql -u {ユーザ名} -D {データベース} -p < {ダンプファイル}.dmp

mysqldumpのオプション	意味	参考リンク
--all-databases -A	すべてのテーブルをダンプする	MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム
--databases {データベース1} {データベース2} -B	複数のデータベースをダンプする 1つだけの場合はmysqldump {オプション} {データベース}でOK	--databases :: MySQL 8.0 リファレンスマニュアル
--no-data -d	テーブルの行情報を書き出さない	MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム
--skip-column-statistics	ANALYZE TABLE文の自動生成をしない(MySQL8未満用)	mysqldumpコマンドで "Unknown table 'COLUMN_STATISTICS' in information_schema (1109)" と怒られる原因と対策 - モヒカンメモ
--set-gtid-purged={AUTO/OFF/ON/COMMENTED}	SET @@GLOBAL.GTID_PURGED を出力に追加するかどうか	MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

• MySQLのデータベースをmysqldumpでバックアップ/復元する方法 | WEB ARCH LABO
• mysqldumpでバックアップ・リストアする - とほほのWWW入門
• MySQLの覚書 | MySQLのコマンド

設定ファイル

• MySQLにおけるテーブル名の大文字／小文字区別について - REDOLOG
• MySQLのtime_zoneを設定する方法 - Qiita

-- 大文字小文字の設定を確認したい
show variables where variable_name='lower_case_table_names';
-- タイムゾーンを確認したい
show variables like '%time_zone%';

• 設定ファイルが読み込まれる順序
  • MySQL :: MySQL 8.0 Reference Manual :: 4.2.2.2 Using Option Files

順番	Windows	Unix系
1	%WINDIR%\my.ini, %WINDIR%\my.cnf	/etc/my.cnf
2	C:\my.ini, C:\my.cnf	/etc/mysql/my.cnf
3	BASEDIR\my.ini, BASEDIR\my.cnf	SYSCONFDIR/my.cnf
4	-	$MYSQL_HOME/my.cnf
5	defaults-extra-file	defaults-extra-file
6	-	~/.my.cnf
7	%APPDATA%\MySQL.mylogin.cnf	~/.mylogin.cnf
8	DATADIR\mysqld-auto.cnf	DATADIR/mysqld-auto.cnf

SQL

• MySQLでNOT INの代わりにNOT EXISTSを使うと速い｜楽しけりゃ何でもいいや

INSERT

-- カラムを指定して複数レコードをinsert(Djangoでテーブルを作るとmodels.pyでの定義と順番が変わっちゃうのでこの方法が便利です)
INSERT INTO テーブル (カラム1, カラム2, カラム3)
VALUES 
  (値1, 値2, 値3),
  (値1, 値2, 値3),
  (値1, 値2, 値3);

AUTO_INCREMENT制約が付いているカラムは0あるいはNULLを設定することで自動的に連番の値を設定することができます。
MySQLでデータを追加する「INSERT」 | UX MILK

• MySQLのDATE型にINSERTできる日付フォーマット | レコチョクのエンジニアブログ

削除

-- VIEWの削除
DROP VIEW IF EXISTS ビュー
-- カラムの削除
ALTER TABLE テーブル DROP COLUMN カラム
---------------------------------
-- 制約の削除
---------------------------------
-- 主キー
ALTER TABLE テーブル DROP PRIMARY KEY CASCADE;
-- 一意制約
ALTER TABLE テーブル DROP UNIQUE カラム CASCADE;
-- 制約名で削除する
ALTER TABLE テーブル DROP CONSTRAINT 制約名 CASCADE;

• CASCADE句 : 削除する整合性制約に依存するほかの整合性制約(外部キーなど)もすべて削除する
• 主キーになっているカラムを削除する
  1. CASCADE句をつけて主キー制約の削除
  2. カラムの削除
  3. 新しい主キー制約の作成

日時

データ型	「ゼロ」値
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000

上記表の出典は、MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3 日付と時間型

date_format の指定子	説明
%Y	4桁年
%y	2桁年
%M	月名(January..December)
%c	月(0..12)
%m	月(00..12)
%d	日(00..31)
%e	日(0..31)

/* date型のカラムを月でgroup byしたい */
select date_format(date_col, '%y-%m'), count(*) from table_name group by date_format(date_col, '%y%m');

• MySQL DATETIME型のミリ秒の扱いについて | データベース | DoRuby
• MySQLで月別、日別、時間、曜日別にレコード数を集計する方法 - おおらかにいこう
• DATE_FORMAT関数 / TIME_FORMAT関数 (日付または時刻を指定したフォーマットで整形する) | MySQLの使い方

インストールしたい

• Windows10にインストーラーでMySQLをインストールする方法 - Qiita
• Windows10にzipでMySQLをインストールする方法 - Qiita
• RDSでMySQLを作ってみる - ponsuke_tarou’s blog
• Ubuntuにmysqlコマンドをインストールする - ponsuke_tarou’s blog
• mysqlコマンドをインストールする方法 - Qiita

ログインしたい

# 起動
$ mysql.server start

# ログインしたい
$ mysql -h {ホスト} -u {ユーザー} -P {ポート} -D {データベース} -p
# GitBashでログインしたい
$ winpty mysql -h {ホスト} -u {ユーザー} -P {ポート} -D {データベース} -p
# ログインしたい(PWがない場合)
$ mysql -u root
# RDSにログインしたい
$ mysql -h {エンドポイント} -P 3306 -u {ユーザ} -p

データベースの情報が見たい

------------------------
-- データベース
------------------------
-- 一覧を見たい
show databases;
-- 現在使ってるのデータベースを見たい
select database();
-- 設定できる文字セットと照合順序をみたい
show character set;
------------------------
-- ユーザー
------------------------
-- 一覧を見たい
select host,user,account_locked from mysql.user;
-- 現在使ってる(ログイン中)のユーザーを見たい
select user();
------------------------
-- テーブルを検索
------------------------
-- テーブル名で検索
select * from information_schema.tables where table_schema = 'スキーマ名' and table_name like '%テーブル名の一部%';
-- 特定のカラム名のカラムがあるテーブル(viewは除く)の一覧を見たい
select table_name from information_schema.columns where table_schema = {スキーマ名} and column_name = {カラム名} and table_name in (select table_name from information_schema.tables where table_schema = {スキーマ名} and table_type = 'BASE TABLE');
-- 特定のカラム名のカラムがないテーブル(viewは除く)の一覧を見たい
select table_name from information_schema.tables where table_schema = {スキーマ名} and table_type = 'BASE TABLE' and table_name not in (select table_name from information_schema.columns where table_schema = {スキーマ名} and column_name = {カラム名});
------------------------
-- カラムを検索
------------------------
select * from information_schema.columns where table_schema = 'スキーマ名' and table_name = 'テーブル名' and column_name like '%カラム名の一部%';
------------------------
-- 制約を検索
------------------------
select * from information_schema.table_constraints where table_schema = 'スキーマ名' and table_name = 'テーブル名' and constraint_name like '%制約名の一部%';

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.2 MySQL での文字セットと照合順序
• MySQLでinformation_schemaから外部キー参照を確認する時のメモ - Qiita
• MySQLでユニークキーや外部キーを確認する - Qiita

○○を作りたい

-- データベースを作りたい
create database [if not exists] {データベース名} [character set {文字セット} collate {照合順序}];
create database if not exists ponsuke_db character set utf8 collate utf8_general_ci;
-- ユーザーを作りたい
create user 'ユーザー名'@'localhost' identified by 'パスワード';
grant all privileges on データベース名.* to 'ユーザー名'@'localhost' identified by 'パスワード';

• _ci で終わる名前は大文字と小文字を区別しない照合順序を示します。
• _cs で終わる名前は、大文字と小文字を区別する照合順序を示します。
• _bin で終わる名前はバイナリ照合順序を示します。文字の比較は、文字バイナリコード値に従って行われます。
  MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.7.1 照合順序名

• ユーザーのロックと解除 | MySQLの使い方
• MySQLのコンテナを作る方法 - Qiita
• ある列の値だけを変えてレコードをコピーしたい - Qiita

クライアントツール

• Sequel Pro
  • 選択中のクエリをSQL実行 : Command + R
  • MySQLクライアントソフトSequelProで便利なキーボードショートカット集 - Qiita
• MySQL Workbench
  • 拡張子MWBファイルを開く方法 - Qiita

データベース全般

• DBと仲良くしたいときに役立つリンク集 - Qiita

困った

• サービスに登録したMySQLが停止しなくなったときの対応方法 - Qiita
• ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES) - Qiita
• ERROR 1267 (HY000): Illegal mix of collations - Qiita
• ERROR 2003 (HY000): Can't connect to MySQL server on - Qiita
• MySQLのコンテナを作成したのに再起動し続ける - Qiita
• stdin is not a tty - Qiita