MySQLメモ


# Mac への MySQLインストール手順

```bash
# とりあえず Homebrew をアップデート
$ brew upgrade

# mysql を検索
$ brew search mysql

# 5.7 をインストール
$ brew install mysql@5.7

# 設定
$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

$ source ~/.bash_profile

$ vim ~/.bashrc
export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

$ source ~/.bashrc

# MySQL起動
$ brew services start mysql@5.7
# もしくは
$ mysql.server start

# MySQL アクセス
$ mysql -uroot

# パスワード変更
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新しいパスワード';
```


# コマンド集

```sql
-- 設定値確認
show variables\G

-- DB一覧
show databases;

-- DB選択
use データベース;

-- テーブル一覧
show tables;

—- ユーザ確認
SELECT * FROM mysql.user;

—- パスワード変更
SET PASSWORD FOR ユーザ名@ホスト名 = password(変更後のパスワード);

—- ユーザ削除
DELETE FROM mysql.user WHERE user = ‘ユーザ名’;
```


# データベース作成

```sql
-- DB作成（MySQLに絵文字を登録できるエンコードを設定）
CREATE DATABASE example CHARACTER SET utf8mb4
```

# ユーザー作成＆権限付与

```sql
-- ユーザー作成
CREATE USER 'admin1'@'%' IDENTIFIED BY 'some_pass';

-- 権限付与
GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'%' WITH GRANT OPTION;

-- Windowsでは、上記のホスト全てに対するGRANT文を実行してもログインできなかった
>mysql -u admin1 -p
Enter password: *****
ERROR 1045 (28000): Access denied for user 'admin1'@'localhost' (using password: YES)

-- localhostを指定したGRANT文を実行するとログインできるようになりました。
mysql> grant all privileges on *.* to admin@localhost identified by 'admin' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql > \q
>mysql -u admin1 -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 96
Server version: 5.6.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


-- ユーザー作成と権限付与の合わせ技（便利ですが警告出ます）
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin2'@'%' IDENTIFIED BY 'admin2' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
1 row in set (0.00 sec)

-- ユーザー確認
select * from mysql.user;
```

# フォーマット

## 日付フォーマット

```sql
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') |
+-----------------------------------------+
| 2019-01-19 11:27:18                     |
+-----------------------------------------+
1 row in set (0.00 sec)
```

## 数値フォーマット

FORMAT関数の第２引数は、小数点の桁数。切り捨てられる値は、四捨五入されます。

```sql
mysql> SLECT FORMAT(123456789.423456, 0);
+-----------------------------+
| FORMAT(123456789.423456, 0) |
+-----------------------------+
| 123,456,789                 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(123456789.523456, 0);
+-----------------------------+
| FORMAT(123456789.523456, 0) |
+-----------------------------+
| 123,456,790                 |
+-----------------------------+
1 row in set (0.00 sec)
```
<img width="60%" alt="スクリーンショット 2019-01-19 11.47.16.png" src="https://qiita-image-store.s3.amazonaws.com/0/158071/7702c5e5-0922-6f69-6948-2d8ff0666e73.png">

# テーブル

```sql
-- テーブル作成
CREATE TABLE tbl1 (col1 VARCHAR(10), col2 CHR(10));

-- テーブル定義確認
SHOW CREATE TABLE tbl1;

-- テーブル名の変更
ALTER TABLE テーブル名 RENAME TO 新しいテーブル;

-- ストレージエンジンの変更
ALTER TABLE テーブル名 ENGINE=新しいストレージエンジン名;

-- 列の追加
ALTER TABLE テーブル名 ADD [COLUMN] 列名 データ型;

-- 列の削除
ALTER TABLE テーブル名 DROP [COLUMN] 列名;

-- 列名／列のデータ型の変更
ALTER TABLE テーブル名 CHANGE [COLUMN] 旧列名 新列名 新データ型;

-- 文字コードの変更
ALTER TABLE テーブル名 CHARACTER SET 文字コード名;

```


# MySQL JDBC

JDBCのドライバークラス名が、deprecated だというエラーメッセージが出力。
新しいものを指定するだけで解決しました。

#### エラーメッセージ

```java
Loading class `com.mysql.jdbc.Driver'. This is deprecated.
```

#### 正しいJDBCドライバークラス名

```properties
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
```

# 暗号化

```sql

SELECT TO_BASE64(AES_ENCRYPT('hoge','EA703E7AA1EFDA0064EAA507D9E8AB7E'));
-- SELECT AES_DECRYPT(FROM_BASE64('JrLQBmDIe9pmb4yS0oj4rA=='), 'hoge');
-- SELECT AES_DECRYPT(FROM_BASE64('bNjrqPL0BJ3IrFsueauMbA=='), 'hoge');

SELECT AES_DECRYPT(FROM_BASE64('TTAn0kLBcyPjAisxxO3xSw=='), 'EA703E7AA1EFDA0064EAA507D9E8AB7E', 128);

-- SELECT AES_ENCRYPT(TO_BASE64('alice@kacky.com'), 'hoge');
-- SELECT HEX(AES_ENCRYPT('alice@kacky.com','hoge'));
-- SELECT AES_DECRYPT('U2FsdGVkX1+9zoTboh/6ehJI/89hFnpoHK/nutXVVVfPD8Xdf+EbM59IhwtpUBa+','hoge');

-- SHOW VARIABLES
```

# その他
## JavaでMAMPのmysqlに接続した時のエラー

```bash
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:590)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.example.demo.DBManager.getConnection(DBManager.java:18)
at com.example.demo.Main.main(Main.java:9)
Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: Communications link failure
```

MAMPログを確認するとポートが3306ではなく8889が使われていた！
/Applications/MAMP/logs/mysql_error_log.err

# References

+ [Mysqlのrootユーザのpasswordを初期化(変更)する](https://qiita.com/is0me/items/91a0af0342c307b94a16)
+ [MySQLでデータベースをuft8mb4で作成する](https://qiita.com/setouchi/items/553b9927cf3bcf13fb41)
+ [version 5.6 
MySQL 5.6 リファレンスマニュアル](https://dev.mysql.com/doc/refman/5.6/ja/)
+ [version 5.6 
MySQL 5.6 リファレンスマニュアル  /  ...  /  ユーザーアカウントの追加](https://dev.mysql.com/doc/refman/5.6/ja/adding-users.html)
+ [SpringBoot　DB接続　つまずき](http://nanaco-programming.hatenablog.com/entry/2016/06/21/190836)