MySQL 小技メモ

ローカルのMySQLのdatabaseをdumpする

$ mysqldump -u gesogeso -p -h localhost practice > practice.sql

DockeerのMySQLサーバーを建てる

zenn.dev

SELECT した結果を別テーブルに流し込む

INSERT INTO tbl_A (SELECT * FROM tbl_B);

例えば, AとBが水平分割されたテーブルだとしてその結果を一つのテーブルCに格納したい場合以下のような操作ができる.

CREATE TABLE tbl_C like tbl_A;
INSET INTO tbl_C (SELECT * FROM tbl_A) UNION (SELECT * FROM tbl_B);

SELECTの結果をCSVに出力

MySQLのSELECT結果をCSVで出力するには以下のURLのSQLを実行する.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

https://dev.mysql.com/doc/refman/5.6/ja/select-into.html

ただし、これを実行しようとすると

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

というエラーが起こる. これは特定のディレクトリ下にしかファイルを出力できない設定である.
特定のディレクトリとは下記のSQLで確認できる.

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

この場合は、/var/lib/mysql-files/にのみ出力できる.

SELECT a,b,a+b INTO OUTFILE '/var/lib/mysql-files/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

任意の場所に保存したいときは, /etc/mysql/my.cnfに下記を追加する.

[mysqld]
secure-file-priv = ""

MySQL Serverを再起動すると更新されて, 任意の場所に保存できるようになる.

service mysql restart

mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
|                                  |
+---------------------------+
1 row in set (0.00 sec)

テーブルの構造を見る

テーブル作成時のSQLが見れる

SHOW CREATE TABLE table1\G;

カラムの情報が見れる

SHOW COLUMNS IN table;
SHOW FULL COLUMNS IN table;

インデックス単位で見る

SHOW INDEX FROM table;

結果をlessで見る

設定方法

pager less

戻す方法

nopager

サーバーや接続情報を見る

MySQLのシステム変数

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

バッファに関するグローバル設定

SHOW VARIABLES LIKE '%BUFFER_SIZE%';

sessinoの有効時間の確認
セッション変数は@@SESSION

SELECT @@SESSION.WAIT_TIMEOUT;

グローバル変数は@@GLOBAL

SELECT @@GLOBAL.INNODB_DATA_FILE_PATH;

稼働中のプロセスを見る

SHOW PROCESSLIST;

エラーを見る

SHOW WARNINGS;

その他のSHOWコマンド

HELP SHOW;

sqlalchemy load_only()関数

from sqlalchemy.orm import load_only
fields = ['id', 'name']
companies = session.query().filter(Company.id = id, Company.name = name).all();

companies = session.query().filter(Company).options(load_only(*fields)).all();