snippet: mysql #MySQL

各ファイルの説明

MySQL :: MySQL 5.6 リファレンスマニュアル :: MySQL 用語集

カラムの追加、削除

追加

ALTER TABLE bbs ADD COLUMN created_at datetime NOT NULL default '0000-00-00 00:00:00'

削除

ALTER TABLE bbs DROP COLUMN created_at;

カラム定義の変更

テーブル構造を変更する(ALTER TABLE文) | MySQLの使い方

-- timestamp(14) を datetime に変換
ALTER TABLE bbs MODIFY regtime datetime;

値が 20191002095013 と入っていたものは 2019-10-02 09:50:13に変換されて保存される。

MyISAM

slow query

MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.1.1 mysqld コマンドラインオプション

/etc/my.cnf(4.0)

[mysqld]
log_slow_queries=/tmp/mysql-slow.log
long_query_time = 1
# mysql 4.1から以下オプション利用可能。
#log-queries-not-using-indexes

小技

mysqlコマンドをより便利に安全にするための小粒なTIPS集｜サイバーエージェント公式エンジニアブログ

select * from \e

\e で$EDITORが立ち上がり続きを編集できる。

プロンプトを変更

~/.my.cnf

[mysql]
prompt=\\u@\\h:\\d>\\_

where無しのupdateを実行させない

~/.my.cnf

[mysql]
safe-updates

mysql> update users set name='a';
ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

メモリ使用量の確認

MySQL5.6 メモリ使用量を抑える | 雅技術

$ ps alx | grep ^mysql | awk '{printf ("%d\t%s\n", $8,$13)}'
135092  /usr/libexec/mysqld

135MB使用中ということがわかった。

mysqldを再起動するとメモリ使用量は減る

$ sudo service mysqld stop
mysqld を停止中:                                           [  OK  ]
$ sudo service mysqld start
mysqld を起動中:                                           [  OK  ]
$ ps alx | grep ^mysql | awk '{printf ("%d\t%s\n", $8,$13)}'
38848   /usr/libexec/mysqld

テーブル定義キャッシュの値確認

mysql> show variables like 'table_definition_cache';
400 -- 定義キャッシュに保存できるテーブル定義数。テーブル数が多い場合は増やす。

/etc/my.cnf

[mysqld]
table_definition_cache=400

5.5まで400がデフォルト、5.6は1400
- なおmysql4.0にはその設定はない。

dumpからのリストア時、ERROR 1005 (HY000): Can't create table 〜 (errno: 150)

メモーる: MySQLでERROR 1005 (HY000): Can't create table 'user_db.groups' (errno: 150)
dumpファイルの先頭に以下追加

SET foreign_key_checks = 0;

mysqlのユーザのdump,restore

mysqlのユーザーだけ移行 - kikukawa's diary

dump

mysqldump --opt mysql user > user.dump

restore

mysql mysql < user.dump

user確認

FLUSH PRIVILEGES;
use mysql;
SELECT user, host FROM user;

processlist

show full processlist;

db
The default database, if one is selected, otherwise NULL.

status	説明
Sleep	ロック解除待ち

Sleepを除外して表示

mysql> \P grep -v Sleep

kill

mysql> kill ID
例) mysql> kill 6358435539

pager

pager nkf -w
pager less -S
pager nkf -w | less -S
pager grep hogehoge
pager vim -R -

SJISで表示して欲しいなら　pager nkf -s
カラムが多いと思いますので、pager nkf -s | less -Sで折り返しなし
(git bashにless、nkfがあるかは疑問ですが、cyggetとかでネットから取ってくれば良いと思います。)
my.cnfに書いておけば最初に発行してくれるわざもあったような。(非推奨だったかな)
my.cnf
[client]
default-character-set=sjis
でも良さそうですし、
mysql -uユーザ名 --default-character-set=sjis データベースの指定でsjis表示したような。
そもそもwindowsであればA5M2やMicrosoft AccessとかGUIクライアントを使うか
phpmyadminのようなWEBクライアントを使うのもよいでしょう。
vimmerであればvimからmysql接続できるdbext.vimもおすすめです。(前使っていました)

tee (ファイルに保存)

tee a.txt

異なるもののみ抽出

select * from tbl1 where start1 <> end1;

等しいもののみ抽出

select * from tbl1 where start1 = end1;

テーブル作成

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)

メモ: Idiorm使ってみた - Qiita

クリック数

CREATE TABLE clicks (
  id int(11) NOT NULL AUTO_INCREMENT,
  count int(11) NOT NULL default '0',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) TYPE=InnoDB COMMENT='クリック数';

varcharのカラムを10桁にするsql

varcharのカラムに数値しか入っていない。
今までそのままの値を使っていたが、桁が足りないものは先頭に「1」を付けて10桁にしたい。

10桁にする

update members set member_id = RIGHT('1000000000' + member_id, 10) 
 where code > 0 and code < 1000000000

確認

select member_id, name from members 
 where code > 0 and code < 1000000000