snippet: mysql

## 各ファイルの説明

* [MySQL :: MySQL 5.6 リファレンスマニュアル :: MySQL 用語集](https://dev.mysql.com/doc/refman/5.6/ja/glossary.html)

## カラムの追加、削除

```sql:追加
ALTER TABLE bbs ADD COLUMN created_at datetime NOT NULL default '0000-00-00 00:00:00'
```

```sql:削除
ALTER TABLE bbs DROP COLUMN created_at;
```


## カラム定義の変更

* [テーブル構造を変更する(ALTER TABLE文) | MySQLの使い方](https://www.dbonline.jp/mysql/table/index18.html)

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

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


## MyISAM

* [運用視点なMyISAMとInnoDBと。 – LexTech](http://tech.lexues.co.jp/archives/1405)
* [MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.1 MyISAM テーブルの破損](https://dev.mysql.com/doc/refman/5.6/ja/corrupted-myisam-tables.html)

## slow query

* [MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.1.1 mysqld コマンドラインオプション](http://download.nust.na/pub6/mysql/doc/refman/4.1/ja/command-line-options.html)

```rb:/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集｜サイバーエージェント 公式エンジニアブログ](http://ameblo.jp/principia-ca/entry-10767163898.html)

```bash:\e
select * from \e
```

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

* プロンプトを変更

```bash:~/.my.cnf
[mysql]
prompt=\\u@\\h:\\d>\\_
```

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

```bash:~/.my.cnf
[mysql]
safe-updates
```

```mysql:
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 メモリ使用量を抑える | 雅 技術](http://www.miyabi-gijyutu.com/2015/08/19/mysql5-6-%E3%83%A1%E3%83%A2%E3%83%AA%E4%BD%BF%E7%94%A8%E9%87%8F%E3%82%92%E6%8A%91%E3%81%88%E3%82%8B/)

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

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

```bash: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:テーブル定義キャッシュの値確認
mysql> show variables like 'table_definition_cache';
400 -- 定義キャッシュに保存できるテーブル定義数。テーブル数が多い場合は増やす。
```

```bash:/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)](http://memoyasu.blogspot.jp/2012/12/mysqlerror-1005-hy000-cant-create-table.html)
* dumpファイルの先頭に以下追加

```
SET foreign_key_checks = 0;
```

## mysqlのユーザのdump,restore

* [mysqlのユーザーだけ移行 - kikukawa's diary](http://kkkw.hatenablog.jp/entry/2013/07/31/120720)

```bash:dump
mysqldump --opt mysql user > user.dump
```

```bash:restore
mysql mysql < user.dump
```

```bash:user確認
FLUSH PRIVILEGES;
use mysql;
SELECT user, host FROM user;
```

## processlist

```mysql
show full processlist;
```

* [koshigoe hiki - [MySQL]SHOWコマンド](http://hiki.koshigoe.jp/?%5BMySQL%5DSHOW%A5%B3%A5%DE%A5%F3%A5%C9)
* [MySQL :: MySQL 5.7 Reference Manual :: 14.7.5.29 SHOW PROCESSLIST Syntax](http://dev.mysql.com/doc/refman/5.7/en/show-processlist.html)

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

| status | 説明 |
|:--|:--|
| Sleep | ロック解除待ち |

```bash:Sleepを除外して表示
mysql> \P grep -v Sleep
``` 

```bash:kill
mysql> kill ID
例) mysql> kill 6358435539
```


## pager

```rb
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 (ファイルに保存)

```bash:
tee a.txt
```

## 異なるもののみ抽出

```sql:
select * from tbl1 where start1 <> end1;
```

## 等しいもののみ抽出

```sql:
select * from tbl1 where start1 = end1;
```


## テーブル作成

```sql:
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](http://qiita.com/tukiyo3/items/60b0dc1cc0c1b3e2e83f)

```sql:クリック数
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桁にしたい。

```sql:10桁にする
update members set member_id = RIGHT('1000000000' + member_id, 10) 
 where code > 0 and code < 1000000000
```

```sql:確認
select member_id, name from members 
 where code > 0 and code < 1000000000
```