基本コマンド
設定確認
文字コード確認
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>
記述の違いを比較
<!-- 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>
コードを見る。
/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 -->
mysql-workbench-community8
<language name="SCLEX_MYSQL"> ここの内容を下記のように書き換える </language>
コードを見る。
/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 -->
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
mysql-workbenchでconectionできなかったので、mysql -u root -p -h 127.0.0.1 -P 3306しようとしたら怒られたのでrootパスワードを変更した。
- rootパスワードを再設定
- mysqlサービスをrestart
rootパスワードを再設定
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ここに新しいパスワードを入力';
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以外でサーバに接続
ローカルで遊ぶのに毎回これは面倒
sudo mysql -u root -p
解決方法
1.userをつくる
2.つくったuserにすべての権限を付与
3.ユーザの権限を確認
userをつくる
create user 'hoge'@'localhost' identified by 'hogehoge';
つくったuserにすべての権限を付与
grant all on *.* to 'hoge'@'localhost' identified by 'hogehoge'
ユーザの権限を確認
show grants for 'hoge'@'localhost';
+------------------------------------------------------+
| Grants for hoge@localhost |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hoge'@'localhost' |
+------------------------------------------------------+
文字化け
/etc/my.cnf~/.my.cnfに追記する〜とかゆう記事で溢れているので詰まってた人は感謝してね！
文字化けの原因
初期値が`latin1'になっており日本語が表示されない。
次のファイルに追記すればいい！
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
...
character-set-server=utf8mb4
[clinet]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
なにを変更できる？
[mysqld]
character_set_database
[mysql]
character_set_clinet
character_set_connection
character_set_results
character_set_server
mysqlを再起動して変更を反映
以下、どちらか実行
sudo service mysql reload
/etc/init.d/mysql restart
mysqlの文字コードを確認
show variables like '%char%';
csvをインポート
手順
１．create database データベス名;
２．create table テーブル名(スキーマを定義);
３．csvインポート
※[]は不要
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=[文字コード]
を記述してインポートする。
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);