mysql
MySQL のメモ
目次

ログイン

# ログイン
$ mysql -h host_name -P port_number -u user_name -D db_mame -p

データベース情報

-- データベース一覧
SHOW DATABASES;

-- データベースの切り替え
USE db_mame;

-- 現在の Character Sets 設定を表示する
SHOW VARIABLES LIKE "char%";

-- 現在のタイムゾーン設定を表示する
SHOW VARIABLES LIKE '%time_zone%';

-- 現在の状態を表示する
STATUS;

-- データサイズを確認する
SELECT
  SUM(data_length) / 1024 / 1024 / 1024 AS db_size_gb,
  SUM(data_length) / 1024 / 1024 AS db_size_mb,
  SUM(data_length) / 1024 AS db_size_kb
FROM
  information_schema.tables
WHERE
  table_schema = 'db_name'
;

テーブル情報

-- テーブル一覧
SHOW tables;

-- テーブルの列一覧
SHOW COLUMNS FROM table_name;

-- テーブル定義を確認する
DESC table_name;
SHOW FULL COLUMNS FROM table_name;
SHOW CREATE TABLE table_name;

INDEX

-- テーブルの INDEX 一覧
SHOW INDEX FROM table_name;

-- インデックス作成
ALTER TABLE table_name ADD INDEX index_name(index_col_name1, index_col_name1, ...);
CREATE INDEX index_name ON table_name (index_col_name1, index_col_name1, ...);
-- インデックス削除
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

/* インデックスヒント */
-- インデックスを指定
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

-- インデックスを強制
SELECT * FROM table1 FORCE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;

-- 指定したインデックスを無視
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;

CACHE

-- クエリキャッシュクリア
RESET QUERY CACHE;

-- キャッシュ状態確認
SHOW STATUS LIKE 'Qcache%';

PROCESSLIST

-- 実行中のスレッドを表示する
SHOW [FULL] PROCESSLIST
-- スレッドを終了する
KILL [CONNECTION | QUERY] processlist_id

dump

# dump を作る
$ mysqldump -u root -p db_mame > dump_filename.sql

# dump を入れる
$ mysql -h localhost -u root -p db_mame < dump_filename.sql

EXPLAIN

-- EXPLAIN: クエリ実行プラン（MySQL がクエリをどのように実行するか）を取得する
-- DESCRIBE: テーブル構造に関する情報を取得する
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

Partitioning

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

-- primary key を変更
-- すでに主キーがついているテーブルの場合、主キーなしに変更 or パーティショニングキーに使いたいキーを主キーに追加する必要がある
ALTER TABLE  table_name DROP PRIMARY KEY, ADD PRIMARY KEY(id, other_col_name);

-- partition つける
-- https://dev.mysql.com/doc/refman/5.7/en/partitioning-list.html
ALTER TABLE table_name
PARTITION BY LIST (other_col_name) (  -- LIST タイプ
    PARTITION pDog VALUES IN (0),     -- LIST なので値は複数指定できるよ
    PARTITION pCat VALUES IN (1)
);

-- partition 確認
select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='table_name';

便利

-- 拡張表示
SELECT * FROM users WHERE login = 'fumi23'\G

おぼえがき