MySQLのデフォルトスキーマ

【現セッションのデフォルトスキーマ

select database(); 

【現セッションのデフォルトスキーマを変更】

use スキーマ名;

【接続時にデフォルトスキーマを設定する方法】
 (mysqlコマンド)
 --database or -Dオプションを利用する

 (MySQL workbench)
 コネクション作成時にDefault Schemaを設定

【現在永続的に設定されているデフォルトスキーマを確認・設定・変更】
調査不足かもしれないが、MySQLには実装されていない模様

【参考リンク】
dev.mysql.com

MySQL即効クエリチューニング ThinkIT Books

MySQL即効クエリチューニング ThinkIT Books

MySQLの消したと思っていたテーブルやビューが残っている

【事象】
MySQLスキーマ(データベース)をリストアする際に、以下の様な事を行っていた。
(DB_BACKUP_FILEは、mysqldumpしたバックアップファイルをgzipで固めただけのファイル)

zcat ${DB_BACKUP_FILE} | mysql -u ${DB_USER} -p${DB_PASS} ${DB_SCHEMA}

しかし、クリアしていたと思っていたテーブルやビューが残っていた。

【原因】
バックアップファイルにはスキーマdropする構文が入っていなかった。
そのため、バックアップファイルに存在するテーブルは置換されているが、存在しないテーブルやビューはそのまま残っていたため。

【対応方法】
1.バックアップファイルを作成し直して、drop databaseされる様にする。
2.リストアする前に事前にデータベースやビューを削除する。

(削除例)

----- テーブル削除
mysql -u ${DB_USER} -p${DB_PASS} ${DB_SCHEMA} -N -e 'show tables' | while read dtable; do mysql -u ${DB_USER} -p${DB_PASS} ${DB_SCHEMA} -N -e "drop table ${dtable}"; done
----- ビュー削除
mysql -u ${DB_USER} -p${DB_PASS} ${DB_SCHEMA} -N -e 'show tables' | while read dview; do mysql -u ${DB_USER} -p${DB_PASS} ${DB_SCHEMA} -N -e "drop view ${dview}"; done

・無精しているので、エラー出しながら削除する方式
 必要であれば、きちんとエラーハンドリングすること
・プロシージャなど他のオブジェクトも必要であれば対応する必要有

(SQLでやる方法)
mebee.info

【参考リンク】
tm.root-n.com

QuickSightから退会できない

【事象】
QuickSightの利用を辞めたいのでサブスクリプションの削除を行おうとした。
しかし、以下の様なエラー画面が発生して削除できなかった。

セカンダリ名前空間(プレビュー)がプロビジョニングされています。
(以下、略) 

【前提事項】
・今はGUIではセカンダリ名前空間は消せないっぽい
・よってAWSコマンドが必要
AWSにアクセスできる環境なら、WindowsCLIでOK
 事前にaws configureを実行して、事前にAWSコマンドの設定しておく必要有
docs.aws.amazon.com

【対応方法】
(名前空間の一覧確認)

aws quicksight list-namespaces --aws-account-id  12XXXXXXXXXX

(名前空間の削除)

aws quicksight delete-namespace --aws-account-id  12XXXXXXXXXX --namespace 削除したいQuickSightのName

非同期的に削除が行われるためかレスポンスコードが202(Accepted)が返ってくる。
そのため、少し経ってからアクセスしないと、確認しても残ったままであった。
(正確には、CreationStatusがDELETINGになっていた)
逆に、少し経ってからアクセスしたら無事削除できていた。

(解約)
QuickSightにアクセス → QuickSightの管理 → アカウント設定 → サブスクリプション解除

【嵌ったこと】
awsコマンドの設定をしている場合としていない場合では、利用できるコマンドが変わるの気がつくの遅かった。。
 (使えるけど怒られるのかと思っていたら、そもそも使えない制御が行われている模様)
aws-account-idをハイフン付きで投げてた
 (これはすぐ気づいた)
awsコマンドがプロキシに阻まれているのが分からなかった
 (とはいえもう散々慣れているので気づいた)

【参考リンク】
list-namespaces — AWS CLI 2.1.4 Command Reference
delete-namespace — AWS CLI 2.1.4 Command Reference

MySQL5.7と8.0における文字コード/照合順序の設定方法

前置き

パラメータ名がハイフン表記か、アンダースコア表記かは常に意識しておく必要有
・初めてMySQLを起動する前に設定すべき
 後でも直せなくはないが、苦行
 また、mysql/performance/sysスキーマをalter文で変更するの怖い(放置している…)

前提

・MySQL5.7/8.0におけるmy.cnf(Linux)/パラメータグループ(AmazonRDS)での設定を考慮
文字コードはutf8mb4、照合順序はutf8mb4_general_ciに設定
・データベースのことをスキーマMySQLサーバーのプロセス全体をサーバーと記述する

文字コード

1.文字コードとは

 ・みんな大好き文字コードのため割愛
 ・MySQL5.7も8.0もutf8mb4が無難
  (cp932で保管しないといけないシステムもあるであろうが…)

2.文字コードに関するパラメータ

2.1.character-set-server

 ・サーバー全体で使用する文字コードを指定
 ・当パラメータを設定する事で以下のパラメータが従属する
  - character_set_server
  - character_set_database

2.1.1.character_set_server
 ・サーバーの文字コード

2.1.2.character_set_database
 ・デフォルトスキーマ文字コード
 ・useコマンドでスキーマを変更する毎に当パラメータで設定した値に戻る
 ・但し、globalパラメータとしての利用は、5.7及び8.0ではdeprecatedなパラメータ

(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_character_set_database より抜粋)

The global character_set_database and collation_database system variables are deprecated; expect them to be removed in a future version of MySQL.

2.2.default-character-set

 ・クライアント(ex. JDBCMySQL Workbench)が接続してきた際の文字コードを指定
 ・当パラメータを設定する事で以下のパラメータが従属する
  - character_set_client
  - character_set_connection
  - character_set_results
 ・後述するcharacter-set-client-handshakeというパラメータにより当設定群を無視する事も可能

2.2.1.character_set_client
 ・クライアントが接続してきた際の文字コードを指定

2.2.2.character_set_connection
 ・MySQLSQLを実行する際にリテラル値の解釈を行う際の文字コード

2.2.3.character_set_results
 ・クライアントに結果を返却する際の文字コードを指定

2.3.character_set_filesystem

 ・MySQLがOS上のファイルにアクセスする際に用いる文字コード
 ・binary(無変換)がデフォルトで変更しない方が無難
 ・よって、MySQL関連のファイルにマルチバイト文字は扱わない方が無難

2.4.character_set_system

 ・MySQLの内部実装の文字コード
 ・utf8でいいし、触るな危険(utf8mb4に設定するパラメータでない)

3.設定方法

3.1.my.cnfの場合

 MySQL5.7の場合:character-set-serverとdefault-character-setに設定する
 (∵MySQL5.7のデフォルト文字コードはutf8のため)
 MySQL8.0の場合:デフォルトのままでOK
 (∵MySQL8.0のデフォルト文字コードはutf8mb4のため)

3.2.パラメータグループの場合

 MySQL5.7の場合:2.1.x~2.2.xのパラメータを設定する
 (∵MySQL5.7のデフォルト文字コードはutf8で、character-set-serverの様なハイフン指定パラメータが存在しないため)
 MySQL8.0の場合:デフォルトパラメータグループのままでOK
 (∵MySQL8.0のデフォルト文字コードはutf8mb4のため)

4.character-set-client-handshakeとskip-character-set-client-handshake

4-1.効能

 クライアントが文字コードをこれでエンコしてねとお願いしてきても跳ね除ける。
 そして、サーバー側に設定しているcharacter_set_client, character_set_connection, character_set_clientを用いてエンコードする。
 なお、当パラメータを設定する事で、character_set_clientなどの値が変更する事は無いと思われる。(未実験)

4-2.character-set-client-handshakeとskip-character-set-client-handshake

 MySQLのマニュアルには、一覧としては両方が記載されている。
 MySQL :: MySQL 8.0 Reference Manual :: 5.1.4 Server Option, System Variable, and Status Variable Reference
 しかし、リンク先に遷移するとどちらも--character-set-client-handshakeの説明に飛ばされる。
 マニュアルには、--character-set-client-handshakeはboolean値との記載はある。
 一方、--skip-character-set-client-handshakeは記載がない。
 (my.cnfとかで定義する場合も当パラメータを定義するだけで値の設定は必要ない)
 しかし、RDSのパラメータグループは、どちらもboolean値なので排他的に設定しないと思わぬ事態が起きるかもしれないので注意が必要。
 (my.cnfでも排他的に定義しないといけないという状況は変わらないが)

4-3.当パラメータを設定する事について

 当パラメータはクライアント側での文字コードが指定されても無視してサーバー側の設定を用いるという設定値である。
 そして、MySQL4.0からの移行用なのであまりお勧めしないなどの記載は良く見かける。
 しかし、MySQL8のドキュメントには非推奨パラメータみたいな記載は発見できない。
 (只のメンテ不足という可能性もあるが…)
 MySQL :: MySQL 8.0 Reference Manual :: 5.1.7 Server Command Options
 MySQL8.0においても、FAQで当パラメータの設定に関する回答は存置である。
 当FAQでは、アジア地域においてはMySQL4.0の設定が望ましい場合もあるという見解である。
 また、推奨していないという文面をマニュアルからは発見できなかった。
 MySQL :: MySQL 8.0 Reference Manual :: A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
 一方、以下の様な脆弱性に関する観点から設定するべきではないという意見もある。
 mysqlでskip-character-set-client-handshakeはもう使わないほうがいいと思われ | へぼい日記
 但し、本番環境のDBに直接SQLを発行する場面は少ないと思われ、アプリ側で制御してしまえばええんではとも。
 結果、非推奨ではないが、効能把握した上で使わないと問題或るパラメータと理解とした。

照合順序

1.照合順序とは

 ・ソートなどを行う際の順番を決定する
 ・また、例えば"あ"と"ア"は同じ文字か違う文字かなどを決定する
 ・aiやciが何なのか?具体的な照合順序の違いなどに関しては、最後の参考リンク先を参考に確認して欲しい

2.照合順序に関するパラメータ

2.1.collation-server

 ・サーバー全体で使用する照合順序を指定
 ・character-set-serverを指定してcollation-serverを指定しない場合は、character-set-serverで指定した文字コードのデフォルト照合順序が設定される
 ・collation_serverとcollation_databaseが従属すると思われる(未実験)

2.1.1.collation_server
 ・character_set_serverを用いて文字コードを設定する際の照合順序
 ・指定が無ければ文字コードに設定されている照合順序が適応されると思われる(未実験)

2.1.2.collation_database
 ・character_set_databaseを用いて文字コードを設定する際の照合順序
 ・指定が無ければ文字コードに設定されている照合順序が適応されると思われる(未実験)
 ・但し、globalパラメータとしての利用は、5.7及び8.0ではdeprecatedなパラメータ

(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_collation_database より抜粋)

The global character_set_database and collation_database system variables are deprecated; expect them to be removed in a future version of MySQL.

2.2.collation-hogehoge

 ・クライアント側の全体を設定する様なパラメータは照合順序には存在しない模様
 ・照合順序に関するクライアントのパラメータ自体が、collation_conectionのみのためと推測

2-2-1.collation_connection
 ・character_set_connectionを用いて文字コードを設定する際の照合順序
 ・指定が無ければ文字コードに設定されている照合順序が適応されると思われる(未実験)

2.3.default_collation_for_utf8mb4

 ・MySQL8.0から用意されているパラメータ
 ・utf8mb4を設定した場合のデフォルト照合順序を決めるためのパラメータ
 ・utf8mb4のMySQL5.7のデフォルトがutf8mb4_general_ci、MySQL8.0のデフォルトがutf8mb4_0900_ai_ciという差を吸収するために用意されたパラメータ

3.設定方法

3.1.my.cnfの場合

 MySQL5.7の場合:未指定でOK
 (∵MySQL5.7のutf8mb4のデフォルト照合順序がutf8mb4_general_ciのため)
 MySQL8.0の場合:collation-server=utf8mb4_general_ci, default_collation_for_utf8mb4=utf8mb4_general_ciを設定
 (∵MySQL8.0のutf8mb4のデフォルト照合順序がutf8mb4_0900_ai_ciのため)

3.2.パラメータグループの場合

 MySQL5.7の場合:未編集でOK
 (∵MySQL5.7のutf8mb4のデフォルト照合順序がutf8mb4_general_ciのため)
 MySQL8.0の場合:以下をutf8mb4_general_ciを設定
  ・default_collation_for_utf8mb4
  ・collation_server
  ・collation_connection
 サーバー起動済の場合は再起動すればcollation_databaseの値もutf8mb4_general_ciに変わった
 (collation_databaseがglobalでは非推奨値なので気にする必要はないやろけど)

4.注意点

4.1.default_collation_for_utf8mb4がデータベースを再起動すると初期化される場合がある

 MySQL8に不具合があり、バージョンによっては再起動するとクリアされる模様
 (ステータスとしては、2020/12/4時点ではclosed)
 MySQL Bugs: #90719: default_collation_for_utf8mb4 can't be set from configuration file

Tips

●現在設定されている文字コードと照合順序を確認したい(要権限)

テーブルやカラムを確認する場合は、絞り込んで実行しないとびっくりする位出力する場合があるため注意

select * from information_schema.schemata; ----- スキーマ
select * from information_schema.tables where table_schema = スキーマ名; ----- テーブル
select * from information_schema.columns where table_schema = スキーマ名 and table_name = テーブル名; ----- カラム
show create schema(database) スキーマ名; ----- スキーマ
show create table スキーマ名.テーブル名; ----- テーブル情報全体

●現在のセッションで有効な文字コードのパラメータを確認したい

show global variables like '%character_set\_%';

●現在のセッションで有効な照合順序のパラメータを確認したい

show global variables like %collation\_%

●利用できる文字コード一覧が知りたい

文字コードにそれぞれデフォルトの照合順序が設定されており、照合順序のパラメータの設定がされていない場合はこの値を用いて設定する

SHOW CHARACTER SET

●利用できる照合順序一覧が知りたい

SHOW COLLATION

スキーマの照合順序を変更したい

----- 照合順序のみ
alter database スキーマ名 collate utf8mb4_general_ci;
----- 文字コードも
alter database スキーマ名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

●テーブルの照合順序を変更したい

時間かかっても問題無い時でないと、データ量などによっては自分の首も羽交い絞めとかでロックされるかも。。

----- 照合順序のみ
alter table テーブル名 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

●カラムの照合順序を変更したい

スキーマやテーブルは設定変更位であるが、カラムは再定義扱いのため、引数にカラムタイプが必要であったり、設定されているデータなどによって上手くいかないなど変更作業は覚悟しておいた方がいい。。

----- 照合順序のみ
alter table テーブル名 MODIFY COLUMN カラム名 COLLATE utf8mb4_general_ci;
----- 文字コードも
alter table テーブル名 MODIFY COLUMN カラム名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

但し、制約条件は存在するが、一括で置換する方法も存在する。

alter table テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

(制約条件の一例)
・元々のDDLにはCHARACTER SETを無記名だったのが必ず設定された状態になる
・意図しない型に強制変換される場合が有 → この場合は個別にやっていくしかない…
・CONVERT TO CHARACTER SET binary~という指定は不可
詳細は、ALTER TABLE構文の最後らへんに記載有。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文
後、ブログに纏めてくれている方も。
MySQLでテーブルとカラムの文字コードを一括変更する - Be an Idealistic Realist

文字コードセットイントロデューサー(Character set introducer)

SELECT _utf8mb4'abc'の様な文字列リテラルの前に記載している文字コードのこと
詳しくは、以下のガイドに記載(あえてMySQL5.6の日本語マニュアル)
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.3.5 文字列リテラルの文字セットおよび照合順序

【参考リンク】
MySQL5.5で携帯の絵文字対応の為に文字コードをutf8mb4にする方法 | カフーブログ
文字コードや照合順序がサーバーのどの辺に効いているのか、文字コードと照合順序の関係性が概念図が記載されていて理解が進んだ

www.slideshare.net
照合順序が異なる事でどの様に検索結果が変化するの中詳細に記載してくれていた

Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター | Amazon Web Services ブログ
MySQLのパラメータの中でも当パートは中盤位から文字コード関連を説明(MySQL5.7前提での記載)

Excelのよく忘れるやつ

本当に雑記(これをやり始めて少しずつEvernoteからの脱却を計っているが、結局書きにくいことはEvernote側に書いてしまうので、完全移行は難しそう…)

【ショートカット】
オートSUM:Shift + Alt + =
オートフィル:(伸ばしたいところまでセル選択した状態で、) Alt → H → F → I → S
置換:Ctrl + H
x行目に移動:Ctrl + G → 参照先の欄に Axと入力し(例:999行目に移動したいならA999)、Enter

80分でマスター! [ガチ速]決算書入門

80分でマスター! [ガチ速]決算書入門

  • 作者:金川 顕教
  • 発売日: 2020/03/26
  • メディア: 単行本(ソフトカバー)
Excel方眼紙だけがExcelの使い方では無い

Colaboratoryのplotlyでpathが無いと怒られる

【事象】
Google Colaboratoryのplotlyでsunburstやtreemapを用いて円グラフやツリーマップを表示しようとすると、以下の様なエラーで怒られる。

TypeError   Traceback (most recent call last)
(中略)
----> px.treemap(zaisei_melt, path=['board', 'variable', 'main_title', 'title'], value='value')
TypeError: treemap() got an unexpected keyword argument 'path'

【原因】
Google Colaboratoryにインストールされているplotlyが古い

!pip show plotly
(2020.11.2 19:00 実行結果)
Name: plotly
Version: 4.4.1

【対応方法】
バージョンアップすれば良い

!pip install -U plotly
(実行後、RESTART RUNTIMEボタンを押下する必要有)
!pip show plotly
(2020.11.2 19:00 実行結果)
Name: plotly
Version: 4.12.0

【参考リンク】
stackoverflow.com

本当に綺麗に記事が纏められており、分かりやすかった。
また、タイムリーなオープンデータが使われており、程よい(※1)データであった。
可視化に関して勉強したいのであれば、下手な参考書やオンライン教材を買うより、このページを"調べながら"写経するのが絶対に良いと思います。

※1 雑音的なデータが混入しており(ex. 数値項目に'-'や'80以上')、それらを除去しながら処理する必要あるので、写経している時にも雑音パターンが増えたりなど実践的であった。
とはいえ、そんなに頭抱えて考えないといけない様な難しいデータ(ex. 郵便番号)ではないので、本当にちょうどよい。

Colaboratoryでデータフレームの値が省略されて表示されるのをどうにかしたい

【事象】
Google ColaboratoryでPandasのデータフレームの値を確認しようとしても、省略して表示されてしまう。
同じ様な内容でstack overflowに記載は合ったが、どうもこの方法では結局縦の表示が省略表示されてしまう。
python - how to see complete rows in google colab - Stack Overflow

【対応方法】
max_rowsやmax_columnsを設定をした上で、printでなくheadやtailで見る

【具体的方法】
>|python|
#設定
pd.set_option('display.max_rows', 999) #縦の表示(件数)
pd.set_option('display.max_columns', 99) #横の表示件数

#出力
df1.head(999) #先頭から999件
df1.tail(999) #最後から999件
df1.head(500).append(df1.tail(499)) #先頭500件と最後の499件
|