MYSQL5.7~8.0 Workbench8.0 メモ

#基本コマンド
##設定確認
####文字コード確認
```
show variables like '%char%'
```

####データベース、テーブル確認
```
show databases;
show tables;
```

##テーブル
####テーブル名変更
```
RENAME TABLE `current_table_name` TO `new_table_name`;
```

####データタイプ変更
```
ALTER TABLE `members` CHANGE COLUMN `full_names` `fullname` char(250) NOT NULL;
```

#Workbench8.0のカラースキーム変更手順
Linux xfce4のカラースキームが`Preferences`から変更できない仕様なので`code_editor.xml`をいじる。

####<ポイント>
１. workbench版とworkbench-community版で若干記述が違う
  `community版`はcolorのあとに`light`か`dark`を明記する。

２. `<language name="SCLEX_MYSQL"> ここの内容を書き換える </language>`


```xml:記述の違いを比較
<!-- workbench -->
<style id="32" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- STYLE_DEFAULT       !BACKGROUND!   -->

<!-- workbench-community -->
<style id="32" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- STYLE_DEFAULT       !BACKGROUND!   -->
```

`Monokai`風にエディターの配色の変更手順は下記の通りです。

####mysql-workbench用
`<language name="SCLEX_MYSQL"> ここの内容を下記のように書き換える </language>`

<details>
  <summary>コードを見る。</summary>
  <div>
  
  ```xml:/usr/share/mysql-workbench/data/code_editor.xml
  <!-- 
      dark-gray:         #282828;
      brown-gray:        #49483E;
      gray:              #888888;
      light-gray:        #CCCCCC;
      ghost-white:       #F8F8F0;
      light-ghost-white: #F8F8F2;
      yellow:            #E6DB74;
      blue:              #66D9EF;
      pink:              #F92672;
      purple:            #AE81FF;
      brown:             #75715E;
      orange:            #FD971F;
      light-orange:      #FFD569;
      green:             #A6E22E;
      sea-green:         #529B2F; 
  -->

  <style id="32" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- STYLE_DEFAULT       !BACKGROUND!   -->
  <style id="33" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- STYLE_LINENUMBER                   -->
  <style id= "0" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_DEFAULT                  -->

  <style id= "1" fore-color="#999999" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_COMMENT                  -->
  <style id= "2" fore-color="#999999" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_COMMENTLINE              -->
  <style id= "3" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_VARIABLE                 -->
  <style id= "4" fore-color="#66D9EF" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_SYSTEMVARIABLE           -->
  <style id= "5" fore-color="#66D9EF" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_KNOWNSYSTEMVARIABLE      -->
  <style id= "6" fore-color="#AE81FF" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_NUMBER                   -->
  <style id= "7" fore-color="#F92672" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_MAJORKEYWORD             -->
  <style id= "8" fore-color="#F92672" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_KEYWORD                  -->
  <style id= "9" fore-color="#9B859D" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_DATABASEOBJECT           -->
  <style id="10" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_PROCEDUREKEYWORD         -->
  <style id="11" fore-color="#E6DB74" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_STRING                   -->
  <style id="12" fore-color="#E6DB74" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_SQSTRING                 -->
  <style id="13" fore-color="#E6DB74" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_DQSTRING                 -->
  <style id="14" fore-color="#F92672" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_OPERATOR                 -->
  <style id="15" fore-color="#9B859D" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_FUNCTION                 -->
  <style id="16" fore-color="#DDDDDD" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_IDENTIFIER               -->
  <style id="17" fore-color="#E6DB74" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_QUOTEDIDENTIFIER         -->
  <style id="18" fore-color="#529B2F" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_USER1                    -->
  <style id="19" fore-color="#529B2F" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_USER2                    -->
  <style id="20" fore-color="#529B2F" back-color="#282828" bold="No" />   <!-- SCE_MYSQL_USER3                    -->
  <style id="21" fore-color="#66D9EF" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_HIDDENCOMMAND            -->
  <style id="22" fore-color="#909090" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_PLACEHOLDER              -->
  <!-- All styles again in their variant in a hidden command -->
  <style id="65" fore-color="#999999" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_COMMENT                  -->
  <style id="66" fore-color="#999999" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_COMMENTLINE              -->
  <style id="67" fore-color="#DDDDDD" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_VARIABLE                 -->
  <style id="68" fore-color="#66D9EF" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_SYSTEMVARIABLE           -->
  <style id="69" fore-color="#66D9EF" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_KNOWNSYSTEMVARIABLE      -->
  <style id="70" fore-color="#AE81FF" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_NUMBER                   -->
  <style id="71" fore-color="#F92672" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_MAJORKEYWORD             -->
  <style id="72" fore-color="#F92672" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_KEYWORD                  -->
  <style id="73" fore-color="#9B859D" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_DATABASEOBJECT           -->
  <style id="74" fore-color="#DDDDDD" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_PROCEDUREKEYWORD         -->
  <style id="75" fore-color="#E6DB74" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_STRING                   -->
  <style id="76" fore-color="#E6DB74" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_SQSTRING                 -->
  <style id="77" fore-color="#E6DB74" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_DQSTRING                 -->
  <style id="78" fore-color="#F92672" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_OPERATOR                 -->
  <style id="79" fore-color="#9B859D" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_FUNCTION                 -->
  <style id="80" fore-color="#DDDDDD" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_IDENTIFIER               -->
  <style id="81" fore-color="#E6DB74" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_QUOTEDIDENTIFIER         -->
  <style id="82" fore-color="#529B2F" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_USER1                    -->
  <style id="83" fore-color="#529B2F" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_USER2                    -->
  <style id="84" fore-color="#529B2F" back-color="#49483E" bold="No" />   <!-- SCE_MYSQL_USER3                    -->
  <style id="85" fore-color="#66D9EF" back-color="#888888" bold="No" />   <!-- SCE_MYSQL_HIDDENCOMMAND            -->
  <style id="86" fore-color="#AAAAAA" back-color="#888888" bold="No" />   <!-- SCE_MYSQL_PLACEHOLDER              -->
  ```
 </div>
</details>


####mysql-workbench-community8
`<language name="SCLEX_MYSQL"> ここの内容を下記のように書き換える </language>`

<details>
  <summary>コードを見る。</summary>
  <div>

  ```xml:/usr/share/mysql-workbench/data/code_editor.xml
  <!-- 
      dark-gray:         #282828;
      brown-gray:        #49483E;
      gray:              #888888;
      light-gray:        #CCCCCC;
      ghost-white:       #F8F8F0;
      light-ghost-white: #F8F8F2;
      yellow:            #E6DB74;
      blue:              #66D9EF;
      pink:              #F92672;
      purple:            #AE81FF;
      brown:             #75715E;
      orange:            #FD971F;
      light-orange:      #FFD569;
      green:             #A6E22E;
      sea-green:         #529B2F;
  -->

  <style id="32" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- STYLE_DEFAULT       !BACKGROUND!   -->
  <style id="33" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- STYLE_LINENUMBER                   -->
  <style id= "0" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_DEFAULT                  -->

  <style id= "1" fore-color-light="#999999" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_COMMENT                  -->
  <style id= "2" fore-color-light="#999999" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_COMMENTLINE              -->
  <style id= "3" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_VARIABLE                 -->
  <style id= "4" fore-color-light="#66D9EF" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_SYSTEMVARIABLE           -->
  <style id= "5" fore-color-light="#66D9EF" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_KNOWNSYSTEMVARIABLE      -->
  <style id= "6" fore-color-light="#AE81FF" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_NUMBER                   -->
  <style id= "7" fore-color-light="#F92672" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_MAJORKEYWORD             -->
  <style id= "8" fore-color-light="#F92672" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_KEYWORD                  -->
  <style id= "9" fore-color-light="#9B859D" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_DATABASEOBJECT           -->
  <style id="10" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_PROCEDUREKEYWORD         -->
  <style id="11" fore-color-light="#E6DB74" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_STRING                   -->
  <style id="12" fore-color-light="#E6DB74" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_SQSTRING                 -->
  <style id="13" fore-color-light="#E6DB74" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_DQSTRING                 -->
  <style id="14" fore-color-light="#F92672" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_OPERATOR                 -->
  <style id="15" fore-color-light="#9B859D" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_FUNCTION                 -->
  <style id="16" fore-color-light="#DDDDDD" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_IDENTIFIER               -->
  <style id="17" fore-color-light="#E6DB74" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_QUOTEDIDENTIFIER         -->
  <style id="18" fore-color-light="#529B2F" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_USER1                    -->
  <style id="19" fore-color-light="#529B2F" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_USER2                    -->
  <style id="20" fore-color-light="#529B2F" back-color-light="#282828" bold="No" />   <!-- SCE_MYSQL_USER3                    -->
  <style id="21" fore-color-light="#66D9EF" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_HIDDENCOMMAND            -->
  <style id="22" fore-color-light="#909090" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_PLACEHOLDER              -->
  <!-- All styles again in their variant in a hidden command -->
  <style id="65" fore-color-light="#999999" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_COMMENT                  -->
  <style id="66" fore-color-light="#999999" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_COMMENTLINE              -->
  <style id="67" fore-color-light="#DDDDDD" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_VARIABLE                 -->
  <style id="68" fore-color-light="#66D9EF" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_SYSTEMVARIABLE           -->
  <style id="69" fore-color-light="#66D9EF" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_KNOWNSYSTEMVARIABLE      -->
  <style id="70" fore-color-light="#AE81FF" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_NUMBER                   -->
  <style id="71" fore-color-light="#F92672" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_MAJORKEYWORD             -->
  <style id="72" fore-color-light="#F92672" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_KEYWORD                  -->
  <style id="73" fore-color-light="#9B859D" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_DATABASEOBJECT           -->
  <style id="74" fore-color-light="#DDDDDD" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_PROCEDUREKEYWORD         -->
  <style id="75" fore-color-light="#E6DB74" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_STRING                   -->
  <style id="76" fore-color-light="#E6DB74" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_SQSTRING                 -->
  <style id="77" fore-color-light="#E6DB74" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_DQSTRING                 -->
  <style id="78" fore-color-light="#F92672" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_OPERATOR                 -->
  <style id="79" fore-color-light="#9B859D" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_FUNCTION                 -->
  <style id="80" fore-color-light="#DDDDDD" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_IDENTIFIER               -->
  <style id="81" fore-color-light="#E6DB74" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_QUOTEDIDENTIFIER         -->
  <style id="82" fore-color-light="#529B2F" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_USER1                    -->
  <style id="83" fore-color-light="#529B2F" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_USER2                    -->
  <style id="84" fore-color-light="#529B2F" back-color-light="#49483E" bold="No" />   <!-- SCE_MYSQL_USER3                    -->
  <style id="85" fore-color-light="#66D9EF" back-color-light="#888888" bold="No" />   <!-- SCE_MYSQL_HIDDENCOMMAND            -->
  <style id="86" fore-color-light="#AAAAAA" back-color-light="#888888" bold="No" />   <!-- SCE_MYSQL_PLACEHOLDER              -->

  ```
  </div>
</details>

#ERROR 1698 (28000): Access denied for user 'root'@'localhost'
mysql-workbenchでconectionできなかったので、`mysql -u root -p -h 127.0.0.1 -P 3306`しようとしたら怒られたのでrootパスワードを変更した。

1. rootパスワードを再設定
2. mysqlサービスをrestart

```terminal:rootパスワードを再設定
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ここに新しいパスワードを入力'; 
```

```terminal:mysqlサービスをrestart
sudo service mysql restart
```


#LOCAL INFILEしようとすると怒られる
>ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

`MySQL8.0`
mysqldが起動する前にlocalinfileがONになればいいので、`loose-local-infile=1`とすればいい。

```/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
loose-local-infile=1
[client]
loose-local-infile=1
```


#root以外でサーバに接続
```terminal:ローカルで遊ぶのに毎回これは面倒
sudo mysql -u root -p
```
解決方法
1.userをつくる
2.つくったuserにすべての権限を付与
3.ユーザの権限を確認


```terminal:userをつくる
create user 'hoge'@'localhost' identified by 'hogehoge';
```

```terminal:つくったuserにすべての権限を付与
grant all on *.* to 'hoge'@'localhost' identified by 'hogehoge'
```


```terminal:ユーザの権限を確認
show grants for 'hoge'@'localhost';
```

```
+------------------------------------------------------+
| Grants for hoge@localhost                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hoge'@'localhost' |
+------------------------------------------------------+

```


#文字化け
>`/etc/my.cnf` `~/.my.cnf` に追記する〜とかゆう記事で溢れているので詰まってた人は感謝してね！

文字化けの原因
**初期値が`latin1'になっており日本語が表示されない。**

次のファイルに追記すればいい！

```terminal:/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
...
character-set-server=utf8mb4
[clinet]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
```
####なにを変更できる？

```conf:[mysqld]
character_set_database                
```

```conf:[mysql]
character_set_clinet
character_set_connection
character_set_results 
character_set_server
```

```
```

```terminal:mysqlを再起動して変更を反映
以下、どちらか実行
sudo service mysql reload
/etc/init.d/mysql restart
```

```terminal:mysqlの文字コードを確認
show variables like '%char%';
```


#csvをインポート
手順
１．create database データベス名;
２．create table テーブル名(スキーマを定義);
３．csvインポート

```terminal:※[]は不要
LOAD DATA LOCAL INFILE '/myfile.csv' 
INTO TABLE tableName
FIELDS TERMINATED BY ','
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(column,column,column);
```

#csvのインポートが文字化けするORインポートできな場合
sqlファイルの１行目に
`set character-set-database=[文字コード]`
を記述してインポートする。

```sql:import_csv.sql
set character-set-database=文字コード;

LOAD DATA LOCAL INFILE '/myfile.csv' 
INTO TABLE tableName
FIELDS TERMINATED BY ','
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(column,column,column);
```