MySQL8でCHAR関数がドキュメントどおりになってない
MySQL8でCHAR関数がドキュメントどおりになってない
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_char
を読むと、こう書いてある。
- CHAR(N,... [USING charset_name])
CHAR() は各 N 引数を整数として解釈し、それらの整数のコード値で指定された文字を構成している文字列を返します。NULL 値はスキップされます。
mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77,77.3,'77.3'); -> 'MMM'
MySQL5.7.29の場合
mysql [localhost:5729] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
ドキュメント通りですね。
MySQL8.0.19の場合
mysql [localhost:8019] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76'); +--------------------------------------------------+ | CHAR(77,121,83,81,'76') | +--------------------------------------------------+ | 0x4D7953514C | +--------------------------------------------------+ 1 row in set (0.01 sec)
あるぇ？おかしい。。。
これ、バグっぽいな
バグレポを見る。
これはわりと関係しそうだけど直接的ではないんだよなー。
ただ、ドキュメントと違うというだけで、雰囲気的には8のそれも最初からそれならアリな感じはするんだよな。
そして
atsuizoさんからコメントを頂く。
なるほど、8.0.14で検証したら大丈夫。 ということはどこかのバージョンか、環境のせいでおかしいのかも？
MySQL8.0.19の場合 by docker
mysql> SELECT CHAR(77,121,83,81,'76'); +--------------------------------------------------+ | CHAR(77,121,83,81,'76') | +--------------------------------------------------+ | 0x4D7953514C | +--------------------------------------------------+ 1 row in set (0.00 sec)
同じだ。
MySQL8.0.18の場合 by docker
mysql> SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
あ！今までの見え方になった・・・。
おそらくバージョンのせいっぽいぞ。
バグレポ
既に木村さんの方で出してもらってたようです。ありがたや。
解決方法
やはり木村さんに教えてもらいました。
MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options
試してみます
# mysql -uroot -p --skip-binary-as-hex Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, 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> SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
ほんとだ、元の見え方になりました！
リリースノートを見る
8.0.19のリリースノートはこちら
When the mysql client operates in interactive mode, the --binary-as-hex option now is enabled by default.
mysqlクライアントが対話モードで動作する とき、--binary-as-hexオプションはデフォルトで有効になりました。
To disable hexadecimal notation, use --skip-binary-as-hex (Bug #24432545)
16進表記を無効にするには、--skip-binary-as-hex （バグ＃24432545）を使用します
ここのことか！（分かりづらい…）
バグ対応で正しくしたのならドキュメントの方直すべきだべ…。
感想
今回は木村さんに全てやってもらって、僕は記事を挙げただけですｗ（木村さんありがとうございます）
そして、atsuizoさんに指摘してもらって8.0.19からの変更だと気づけましたし、そのあとのコメントの通り、これ誰が嬉しいのかよくわからない変更ですよね（苦笑）
マイナーながらもなかなかナニコレ的なものなので、こいつがいつか誰かの役に立てれば幸いです。
MySQL Technology Cafe #7 で『MySQL Shellを使ってみよう』を聞いてからのLinux(Ubuntu)での導入部分
前回Windowsだったので、今回はUbuntuで引き続き初心者向けにライトな感じで導入部分を書いてみました。
1. MySQL APT Repositoryの取得
ここからダウンロード(インストール済の人はスキップ)
2020/1/25現在の最新版はmysql-apt-config_0.8.14-1_all.deb
2. インストール＆アップデート
$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb $ sudo apt-get update
実行例
$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb 以前に未選択のパッケージ mysql-apt-config を選択しています。 (データベースを読み込んでいます ... 現在 200765 個のファイルとディレクトリがインストールされています。) mysql-apt-config_0.8.14-1_all.deb を展開する準備をしています ... mysql-apt-config (0.8.14-1) を展開しています... mysql-apt-config (0.8.14-1) を設定しています ... Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config) OK $ sudo apt-get update 取得:1 http://repo.mysql.com/apt/ubuntu bionic InRelease [19.4 kB] ヒット:2 http://archive.ubuntulinux.jp/ubuntu bionic InRelease ヒット:3 http://jp.archive.ubuntu.com/ubuntu bionic InRelease ヒット:4 http://archive.ubuntulinux.jp/ubuntu-ja-non-free bionic InRelease 取得:5 https://download.docker.com/linux/ubuntu bionic InRelease [64.4 kB] 取得:6 http://jp.archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB] 取得:7 http://jp.archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB] 無視:8 http://dl.google.com/linux/chrome/deb stable InRelease ヒット:9 http://packages.microsoft.com/repos/vscode stable InRelease ヒット:11 http://dl.google.com/linux/chrome/deb stable Release 取得:12 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB] ヒット:10 https://packages.cloud.google.com/apt kubernetes-xenial InRelease ヒット:13 https://packagecloud.io/slacktechnologies/slack/debian jessie InRelease ヒット:14 http://apt.postgresql.org/pub/repos/apt bionic-pgdg InRelease 取得:16 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 Sources [928 B] 取得:17 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config i386 Packages [566 B] 取得:18 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config amd64 Packages [566 B] 取得:19 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages [5,667 B] 取得:20 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 i386 Packages [5,657 B] 取得:21 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 Packages [4,294 B] 取得:22 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools i386 Packages [3,694 B] 357 kB を 6秒 で取得しました (61.4 kB/s) パッケージリストを読み込んでいます... 完了
3. MySQL Shellのインストール
$ sudo apt-get install mysql-shell
実行例
$ sudo apt-get install mysql-shell パッケージリストを読み込んでいます... 完了 依存関係ツリーを作成しています 状態情報を読み取っています... 完了 以下のパッケージが自動でインストールされましたが、もう必要とされていません: libllvm8 postgresql-client-11 python3-flask-htmlmin python3-htmlmin これを削除するには 'sudo apt autoremove' を利用してください。 以下のパッケージが新たにインストールされます: mysql-shell アップグレード: 0 個、新規インストール: 1 個、削除: 0 個、保留: 14 個。 13.0 MB のアーカイブを取得する必要があります。 この操作後に追加で 72.3 MB のディスク容量が消費されます。 取得:1 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 mysql-shell amd64 8.0.19-1ubuntu18.04 [13.0 MB] 13.0 MB を 2秒 で取得しました (7,678 kB/s) 以前に未選択のパッケージ mysql-shell:amd64 を選択しています。 (データベースを読み込んでいます ... 現在 200770 個のファイルとディレクトリがインストールされています。) .../mysql-shell_8.0.19-1ubuntu18.04_amd64.deb を展開する準備をしています ... mysql-shell:amd64 (8.0.19-1ubuntu18.04) を展開しています... mysql-shell:amd64 (8.0.19-1ubuntu18.04) を設定しています ...
4. 確認
mysqlshのHELP（オプション確認）
$ mysqlsh MySQL Shell 8.0.19 Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
MySQL JS > \help
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: \? <pattern>
The <pattern> argument should be the name of a category or a topic.
The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:
- ? matches any single character.
- * matches any character sequence.
The following are the main help categories:
- AdminAPI The AdminAPI is an API that enables configuring and managing
InnoDB clusters and replicasets, among other things.
- Shell Commands Provides details about the available built-in shell commands.
- ShellAPI Contains information about the shell and util global objects
as well as the mysql module that enables executing SQL on
MySQL Servers.
- SQL Syntax Entry point to retrieve syntax help on SQL statements.
- X DevAPI Details the mysqlx module as well as the capabilities of the
X DevAPI which enable working with MySQL as a Document Store
The available topics include:
- The dba global object and the classes available at the AdminAPI.
- The mysqlx module and the classes available at the X DevAPI.
- The mysql module and the global objects and classes available at the
ShellAPI.
- The functions and properties of the classes exposed by the APIs.
- The available shell commands.
- Any word that is part of an SQL statement.
- Command Line - invoking built-in shell functions without entering interactive
mode.
SHELL COMMANDS
The shell commands allow executing specific operations including updating the
shell configuration.
The following shell commands are available:
- \ Start multi-line input when in SQL mode.
- \connect (\c) Connects the shell to a MySQL server and assigns the
global session.
- \edit (\e) Launch a system editor to edit a command to be executed.
- \exit Exits the MySQL Shell, same as \quit.
- \help (\?,\h) Prints help information about a specific topic.
- \history View and edit command line history.
- \js Switches to JavaScript processing mode.
- \nopager Disables the current pager.
- \nowarnings (\w) Don't show warnings after every statement.
- \option Allows working with the available shell options.
- \pager (\P) Sets the current pager.
- \py Switches to Python processing mode.
- \quit (\q) Exits the MySQL Shell.
- \reconnect Reconnects the global session.
- \rehash Refresh the autocompletion cache.
- \show Executes the given report with provided options and
arguments.
- \source (\.) Loads and executes a script from a file.
- \sql Executes SQL statement or switches to SQL processing
mode when no statement is given.
- \status (\s) Print information about the current global session.
- \system (\!) Execute a system shell command.
- \use (\u) Sets the active schema.
- \warnings (\W) Show warnings after every statement.
- \watch Executes the given report with provided options and
arguments in a loop.
GLOBAL OBJECTS
The following modules and objects are ready for use when the shell starts:
- dba Used for InnoDB cluster administration.
- mysql Support for connecting to MySQL servers using the classic MySQL
protocol.
- mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
- os Gives access to functions which allow to interact with the operating
system.
- shell Gives access to general purpose functions and properties.
- sys Gives access to system specific parameters.
- util Global object that groups miscellaneous tools like upgrade checker
and JSON import.
For additional information on these global objects use: <object>.help()
EXAMPLES
\? AdminAPI
Displays information about the AdminAPI.
\? \connect
Displays usage details for the \connect command.
\? checkInstanceConfiguration
Displays usage details for the dba.checkInstanceConfiguration function.
\? sql syntax
Displays the main SQL help categories.
\?または\helpを最初に書いて、その後オプションを書く。
MySQL SQL > \? \connect
NAME
\connect - Connects the shell to a MySQL server and assigns the global
session.
SYNTAX
\connect [<TYPE>] <URI>
\c [<TYPE>] <URI>
DESCRIPTION
TYPE is an optional parameter to specify the session type. Accepts the
following values:
- --mc, --mysql: create a classic MySQL protocol session (default port
3306)
- --mx, --mysqlx: create an X protocol session (default port 33060)
If TYPE is omitted, automatic protocol detection is done, unless the
protocol is given in the URI.
URI format is: [user[:password]@]hostname[:port]
EXAMPLE
\connect --mx root@localhost
Creates a global session using the X protocol to the indicated URI.
DB接続
mysqlのクライアントとしてDBに接続するときはこんな感じで書くということらしい。
MySQL SQL > \connect --mc user:password@127.0.0.1:3306
or
MySQL SQL > \connect --mysql user:password@127.0.0.1:3306
実行例
MySQL SQL > \connect --mysql root:xxxxxx@127.0.0.1:3306 Creating a Classic session to 'root@127.0.0.1:3306' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 4410 Server version: 8.0.19 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:3306 ssl SQL >
スキーマ変更は\use <schema>
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \use performance_schema Default schema set to `performance_schema`. Fetching table and column names from `performance_schema` for auto-completion... Press ^C to stop. MySQL 127.0.0.1:3306 ssl performance_schema SQL >
セッションの再接続
セッション切れたっぽいぞ、と思ったら\reconnect
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \reconnect Attempting to reconnect to 'mysql://root@127.0.0.1:3306/performance_schema'.. The global session was successfully reconnected.
接続先DBサーバの状態確認
繋いでいるDBサーバの状態を見るのは\status
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \status MySQL Shell version 8.0.19 Session type: Classic Connection Id: 4430 Current schema: performance_schema Current user: root@172.xxx.xxx.1 SSL: Cipher in use: ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 Using delimiter: ; Server version: 8.0.19 MySQL Community Server - GPL Protocol version: classic 10 Client library: 8.0.19 Connection: 127.0.0.1 via TCP/IP TCP port: 3306 Server characterset: utf8mb4 Schema characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 Compression: Disabled Uptime: 23 hours 35 min 19.0000 sec Threads: 6 Questions: 391129 Slow queries: 0 Opens: 542 Flush tables: 3 Open tables: 462 Queries per second avg: 4.605
別のMySQLサーバ（5.7）の環境に繋いで\status実行してみる
MySQL 127.0.0.1:3306 ssl performance_schema SQL > \connect --mc root:xxxxx@127.0.0.1:3307 Creating a Classic session to 'root@127.0.0.1:3307' Fetching schema names for autocompletion... Press ^C to stop. Closing old connection... Your MySQL connection id is 4440 Server version: 5.7.26 MySQL Community Server (GPL) No default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:3307 ssl SQL > \use information_schema Default schema set to `information_schema`. Fetching table and column names from `information_schema` for auto-completion... Press ^C to stop. MySQL 127.0.0.1:3307 ssl information_schema SQL > \status MySQL Shell version 8.0.19 Session type: Classic Connection Id: 4440 Current schema: information_schema Current user: root@172.xxx.xxx.1 SSL: Cipher in use: DHE-RSA-AES256-SHA TLSv1.1 Using delimiter: ; Server version: 5.7.26 MySQL Community Server (GPL) Protocol version: classic 10 Client library: 8.0.19 Connection: 127.0.0.1 via TCP/IP TCP port: 3307 Server characterset: latin1 Schema characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 Compression: Disabled Uptime: 23 hours 36 min 19.0000 sec Threads: 5 Questions: 388499 Slow queries: 0 Opens: 204626 Flush tables: 1 Open tables: 2000 Queries per second avg: 4.571
コマンド履歴の確認
今のセッションのコマンド履歴を見たいときは\history
MySQL SQL > \history
1 \history
2 \status
但し、その番号のコマンドが実行できるわけではなさそう
MySQL SQL > \? \history
NAME
\history - View and edit command line history.
SYNTAX
\history [options].
DESCRIPTION
The operation done by this command depends on the given options. Valid
options are:
- del range Deletes entry/entries from history.
- clear Clear history.
- save Save history to file.
If no options are given the command will display the history entries.
Range in the delete operation can be given in one of the following forms:
- num single number identifying entry to delete.
- num-num numbers specifying lower and upper bounds of the range.
- num- range from num till the end of history.
- -num last num entries.
NOTE: The history.autoSave shell option must be set to true to
automatically save the contents of the command history when MySQL Shell
exits.
EXAMPLES
\history
Displays the entire history.
\history del 123
Deletes entry number 123 from the history.
\history del 10-20
Deletes range of entries from number 10 to 20 from the history.
\history del 10-
Deletes entries from number 10 and ahead from the history.
\history del -5
Deletes last 5 entries from the history.
ページャーの設定と解除
先頭１０行で区切る
\pager more -10
うう、改行されちゃったものも一行とみなすのね・・・。
MySQL 127.0.0.1:3306 ssl information_schema SQL > select * from TABLES;
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA
_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT
|
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| def | mysql | innodb_table_stats | BASE TABLE | InnoDB | 10 | Dynamic | 67 | 244 | 16384 |
--More--
ページャーの解除は\nopager
\nopager
抜けるときは\qか\quitか\exitで
MySQL 127.0.0.1:3306 ssl information_schema SQL > \q Bye! $
最後に
\js や\pyでJavaScriptやPythonに切り替えられますし、Replica SetやMySQL InnoDB Clusterを使うならそっちなので、今度はそっちの話を書こうかと思います。
MySQL Technology Cafe #7 で『MySQL Shellを使ってみよう』を聞いてからのWindowsでの導入部分
MySQL Shell をWindowsに入れてみる
先日参加してきたMySQL Technology Cafe #7でMySQL Shellって何？どんなことできるの？ といった初心者向けの話がされるというので聞いて来ました。
今回はそこから学んだこと、およびハマってしまったWindowsでの設定について軽く触れておきます。
（普段LinuxなのでWindowsはすっかりわからんちんになってた…）
セッションで使われた資料
資料と当日の講義から感じた思いとしては
- MySQL ShellってInnoDB Clusterの管理をするのに必要なものってイメージで、それを使わないなら必要ないと思われてる
- でも、そもそものコンセプトとしてはMySQL Utilitiesから派生してできたもので、色々なことができるもの
- それなのにMySQL大好きな人たちにすら前述の認知しかされてないのでもったいない！
もっと知ってもらいたい！
- JavaScript,Python,SQLどんな形でも簡単にDBにつないで作業できる
- MySQLのバージョンアップグレードチェッカー機能がある
- XdevAPIでスキーマレスJSONドキュメントの操作ができる
- パラレルデータインポートができる
- JSONデータのインポートができる
- データの出力形式を色々選べる
- バッチとして使える
- pluginを追加すればここにない機能が追加できる！
- 最新機能のReplica Setの管理にも使える（構築・手動HAとか）
- イメージとしてはこちらを参照
今回の講義では、来ているメンバーが濃い目（笑）と思ったことからプラグイン部分とRepluca Setの部分に焦点をあてたらしい。
（実際はその濃い目の人たちもMySQL Shellって普段全然触らないから何それおいしいの？って感じで温度差があったのですが）
インストール前のおススメ
https://insidemysql.com/mysql-shell-8-0-19/ https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring-options.html
最新のMySQL Shellを使う！
最新を使う理由としては新しいほうがより便利になっていっていることから。
実行履歴を保存しよう！
shell.options.setPersist を使って構成ファイルへ設定を保存することができる。
実行履歴を保存するのであれば、
MySQL JS > shell.options.set('history.maxSize', 5000)
MySQL JS > shell.options.setPersist('useWizards', 'true')
MySQL JS > shell.options.setPersist('history.autoSave', 1)
- MySQL Shellコード履歴に保存するエントリの最大数を5000にセットし、
- ウィザードモードを有効にして、構成ファイルへ設定を保存
- MySQL Shellを終了するときに、MySQL Shellコード履歴のエントリを保存（true）するように構成ファイルへ設定を保存
とやるらしい。
デフォルトのモードを設定しよう（defaultMode）
（後述のMySQL Shellのセットアップ終わってから戻ってきて試してみてください）
まず、現状を確認してみよう。
mysqlshと打ってMySQL Shellを起動。
shell.と打って、そのあとTabを二回たたくと候補が表示される
MySQL JS > shell. addExtensionObjectMember() options connect() parseUri() createExtensionObject() prompt() deleteAllCredentials() reconnect() deleteCredential() registerGlobal() disablePager() registerReport() dumpRows() reports enablePager() setCurrentSchema() getSession() setSession() help() status() listCredentialHelpers() storeCredential() listCredentials() unparseUri() log()
この中からとりあえずオプション(option)を選択して同様に二回Tabをタタンとたたく。
MySQL JS > shell.options. autocomplete.nameCache interactive batchContinueOnError logLevel credentialStore.excludeFilters oci.configFile credentialStore.helper oci.profile credentialStore.savePasswords outputFormat dba.gtidWaitTimeout pager dba.logSql passwordsFromStdin defaultCompress resultFormat defaultMode sandboxDir devapi.dbObjectHandles showColumnTypeInfo history.autoSave showWarnings history.maxSize useWizards history.sql.ignorePattern verbose
じゃあ、defaultModeを見てみよう。
MySQL JS > shell.options.defaultMode none
今は何も設定されていないらしいのでSQLモードにしてみる。
MySQL JS > shell.options.setPersist('defaultMode', 'SQL')
MySQL JS > shell.options.defaultMode
sql
一回抜けて入りなおしてみる。
MySQL JS > \q Bye! C:\Users\takayuki>mysqlsh MySQL Shell 8.0.19 Copyright (c) 2016, 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 '\?' for help; '\quit' to exit. MySQL SQL >
確かにSQLモードになった。
こんな感じで設定変更が色々できそうだ。
MySQLシェルオプション
他にも色々な設定変更ができるので以下参照。
| optionName | DefaultValue | Type | Effect |
|---|---|---|---|
| autocomplete.nameCache | TRUE | ブール値 | 自動補完のデータベース名のキャッシュを有効にします。 |
| batchContinueOnError | FALSE | ブール値（読み取り専用） | SQLバッチモードでは、エラーが見つかった場合、処理を強制的に続行します。 |
| dba.gtidWaitTimeout | 60 | 0より大きい整数 | AdminAPI操作で必要な場合に、GTIDトランザクションが適用されるのを待機する秒単位のタイムアウト（InnoDBクラスターの操作を参照 ） |
| dba.logSql | 0 | 0から2の範囲の整数 | AdminAPI操作によって実行されるSQLステートメントをログに記録します（第8章「 MySQLシェルのロギングとデバッグ」を参照）。 |
| defaultCompress | FALSE | ブール値 | すべてのグローバルセッションで可能であれば、クライアントとサーバー間で送信される情報の圧縮を有効にします（クラシックMySQLプロトコル接続のみ）。 |
| defaultMode | None | 文字列（sql、js、またはpy） | MySQLシェルの起動時に使用するモード（SQL、Javascript、またはPython）。 |
| devapi.dbObjectHandles | TRUE | ブール値 | DevAPI dbオブジェクトのテーブル名とコレクション名のハンドルを有効にします。 |
| history.autoSave | FALSE | ブール値 | MySQL Shellを終了するときに、MySQL Shellコード履歴のエントリを保存（true）またはクリア（false）します（ セクション5.5「コード履歴」を参照）。 |
| history.maxSize | 1000 | 整数 | MySQL Shellコード履歴に保存するエントリの最大数。 |
| history.sql.ignorePattern | IDENTIFIED : PASSWORD | 文字列 | これらのパターンに一致する文字列は、MySQL Shellコード履歴に追加されません。 |
| interactive | TRUE | ブール値（読み取り専用） | 対話モードを有効にします。 |
| logLevel | Requires a value | 1から8の範囲の整数 | アプリケーションログのログレベルを設定します（第8章「 MySQLシェルのログとデバッグ」を参照）。 |
| pager | None | 文字列 | 指定された外部ポケットベルツールを使用して、テキストと結果を表示します。ツールのコマンドライン引数を追加できます（セクション4.6「ページャーの使用」を参照 ）。 |
| passwordsFromStdin | FALSE | ブール値 | stdin端末の代わりにパスワードを読み取ります。 |
| resultFormat | table | 文字列（table, 水平タブ区切り, 垂直タブ区切り, json | json/pretty, ndjson | json/raw, json/array）|結果セットを印刷するためのデフォルトの出力形式（ セクション5.7「出力形式」を参照）。 |
| sandboxDir | プラットフォームに依存 | 文字列 | サンドボックスディレクトリ。デフォルトはWindowsでは C:\Users\MyUser\MySQL\mysql-sandboxes であり、Unixシステムでは $HOME/mysql-sandboxes です。 |
| showColumnTypeInfo | FALSE | ブール値 | SQLモードでは、結果セットの列メタデータを表示します。 |
| showWarnings | TRUE | ブール値 | SQLモードでは、SQL警告があれば自動的に表示します。 |
| useWizards | TRUE | ブール値 | ウィザードモードを有効にします。 |
| verbose | 1 | 0から4の範囲の整数 | コンソールへの詳細出力を有効にし、詳細レベルを設定します（第8章「 MySQLシェルのロギングとデバッグ」を参照）。 |
参照：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring-options.html
Windows でのMySQL Shellのセットアップ
さて、現場でちょっとハマったセットアップの話。
自分が使ったのはWindowsマシンだったのでその方法を以下記載。
1. インストーラをダウンロード
- MySQL Community Downloads MySQL Shellからダウンロードします。
- mysql-shell-8.0.xx-windows-x86-64bit.zipファイルを解凍します。
- mysql-shell-8.0.xx-windows-x86-64bit\binフォルダ下のmysqlsh.exeファイルを環境変数PATHに追加します。
ハマったのは以下のポイント
- プラグインを試したい場合、%AppData%
\MySQL\mysqlsh\pluginsフォルダを作ってそこに入れる- 資料のほうが%AppData% とMySQLの間の\が抜けててそれに最初気づけなかった
- あ、俺のPC、今Git入れてないわ…。（ZIP方式に切り替え）
2. 確認
3. 起動について（講義にはない。補足）
- 起動スクリプトというものを用意することでJavaScriptとPythonモードで起動した場合は実行環境をカスタマイズできる（9.1 Working With Startup Scripts）
- 起動スクリプトは以下のどこかに、Pythonなら
mysqlshrc.py、JavaScriptならmysqlshrc.jsという名前で置く（以下Windowsでの話にしているが、Unix/Linuxも同様）- 標準のグローバル構成パス：Windowsの場合： %PROGRAMDATA%\MySQL\mysqlsh\mysqlshrc.[js|py]
- MYSQLSH_HOMEを作った場合：Windowsの場合： %MYSQLSH_HOME%\share\mysqlsh\mysqlshrc.[js|py]
- mysqlshバイナリを含むフォルダー内：Windowsの場合： （mysqlsh binary path）\mysqlshrc.[js|py]
- MYSQLSH_USER_CONFIG_HOMEを作った場合：Windowsの場合： %MYSQLSH_USER_CONFIG_HOME%\mysqlshrc.[js|py]
- 標準ユーザー設定パス：Windowsの場合： %APPDATA%\MySQL\mysqlsh\mysqlshrc.[js|py]
- 但し、あとで読み込まれたほうが優先されるので置き場所は注意！
- 例えば、以下のようにして起動スクリプトのモジュール検索パスを追加できる
Python（mysqlshrc.py）
# Import the sys module import sys # Append the additional module paths sys.path.append('~/custom/python') sys.path.append('~/other/custom/modules')
JavaScript（mysqlshrc.js）
// Append the additional module paths sys.path = [...sys.path, '~/custom/js']; sys.path = [...sys.path, '~/other/custom/modules'];
9.2.2 Module Search Path Variable in Startup Scripts参照
次の段階
下準備はできたので、ここから以下を少しずつ進めてみたい。
- 公式ドキュメントを一通り読んで触ってみる
- 講義でやってたpluginを試す（起動スクリプトが置かれているフォルダの下にpluginsというフォルダを作ってそこに置く）
- 同様に、Replica Setも触ってみる
- 書籍を読む⇒Charles BellのIntroducing MySQL Shell
- （余裕があれば）pluginを作る
Percona Serverのマスキング試してみた
MySQLのマスキングをPerconaで使ってみる
MySQLのマスキングはEnterpriseEditionでしか使えないが、PerconaServerはそれを無償で使えるように！
やるじゃん、ぺるこな！
でも使えるのは8.0.17-18バージョンからなので、ごめんね5.7…。
とりあえず、この記事 をなぞりつつ、MySQLの公式ドキュメントを見ながら試してみた。
マスキング関数
- ビューでマスキング済の結果を返す
- ビューにマスキング関数を利用する
- 今のところは関数を見る限りあまり日本向けではない
マスキング関数の種類
マスキング関数の種類以下の3パターンに分かれている。
(ネーミングは公式の英語からこんな感じで勝手につけたので正しくないかも)
- データマスキング関数
- ランダムデータ生成関数
- ランダムデータディクショナリベース関数
１．データマスキング関数（Data Masking Functions＝DMF）
- 文字列引数に対してマスキング操作を実行し、マスクされた結果を返します。
２．ランダムデータ生成関数（Random Data Generation Functions＝RDGF）
- さまざまなタイプのデータに対してランダムな値を生成します。
- 生成された値には、可能であれば、正当なデータと間違われることを避けるために、デモンストレーションまたはテスト値用に予約された特性があります。
３．ランダムデータディクショナリベース関数（Random Data Dictionary-Based Functions＝RDDBF）
- 用語の辞書を操作し、それらに基づいて生成およびマスキング操作を実行します。
マスキング関数の一覧(MySQL8.0.19時点)
| 関数名 | 関数タイプ | 説明 | 構文 | 引数 |
|---|---|---|---|---|
| mask_inner | DMF | 文字列の内部部分をマスクし、両端をそのままにして、結果を返します。 オプションのマスキング文字を指定できます。 マスキング文字はシングルバイト文字でなければなりません。 マルチバイト文字を使用しようとすると、エラーが発生します |
mask_inner(str, margin1, margin2 [, mask_char]) | str：マスクする文字列。 margin1：マスクされないままにする文字列の左端の文字数を指定する負でない整数。値が0の場合、マスクされていない左端の文字はありません。 margin2：マスクされないままにする文字列の右端の文字数を指定する負でない整数。 値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。もし mask_charが指定されていない場合 デフォルトは 'X'です。 |
| mask_outer | DMF | 文字列の左端と右端をマスクし、内部をマスクせずに、結果を返します。 オプションのマスキング文字を指定できます。 マスキング文字はシングルバイト文字でなければなりません。 マルチバイト文字を使用しようとすると、エラーが発生します |
mask_outer(str, margin1, margin2 [, mask_char]) | str：マスクする文字列。 margin1：マスクする文字列の左端の文字数を指定する非負の整数。 値が0の場合、左端の文字はマスクされません。 margin2：マスクする文字列の右端の文字数を指定する非負の整数。 値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。 もし mask_charが指定されていない場合 デフォルトは 'X'です。 |
| mask_pan | DMF | 支払いカードのプライマリアカウント番号をマスクし、最後の4桁以外をすべて'X'文字で置き換えた番号を返します 。 | mask_pan(str) | str：マスクする文字列。 文字列はプライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。 |
| mask_pan_relaxed | DMF | 支払いカードのプライマリアカウント番号をマスクし、最初の6桁と最後の4桁を除くすべての'X'文字を文字に置き換えた番号を返します。 最初の6桁は、支払いカード発行者を示します。 |
mask_pan_relaxed(str) | str：マスクする文字列。文字列は、プライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。 |
| mask_ssn | DMF | 米国の社会保障番号をマスクし、最後の4桁以外のすべてを'X'文字に置き換えた番号を返します 。 | mask_ssn(str) | |
| gen_range | RDGF | 指定された範囲から選択された乱数を生成します。 | gen_range(lower, upper) | lower：範囲の下限を指定する整数。 upper：範囲の上限を指定する整数。下限より小さくてはいけません。 |
| gen_rnd_email | RDGF | example.comドメイン 内のランダムな電子メールアドレスを生成します。 | gen_rnd_email() | なし |
| gen_rnd_pan | RDGF | ランダムな支払いカードのプライマリアカウント番号を生成します。 (※割り当てられない可能性が0じゃないので公開向きではないと公式には記載されている) |
gen_rnd_pan(size) | size：（オプション）結果のサイズを指定する整数。指定しない場合デフォルトは16です。指定する場合12〜19の範囲の整数である必要があります。 |
| gen_rnd_ssn | RDGF | ランダムな米国社会保障番号をAAA-BB-CCCCAAABB形式で生成します。 | gen_rnd_ssn() | なし |
| gen_rnd_us_phone | RDGF | ランダムな米国の電話番号を1-555-AAA-BBBB形式で生成します。 | gen_rnd_us_phone() | なし |
| gen_blacklist | RDDBF | 1つの辞書に存在する用語を2番目の辞書の用語に置き換え、置換する用語を返します。 これにより、置換によって元の用語がマスクされます |
gen_blacklist(str, dictionary_name, replacement_dictionary_name) | str：置換する用語を示す文字列。 dictionary_name：置換する用語を含む辞書に名前を付ける文字列。 replacement_dictionary_name：置換語を選択する辞書を指定する文字列。 |
| gen_dictionary | RDDBF | 辞書からランダムな用語を返します。 | gen_dictionary(dictionary_name) | dictionary_name：用語を選択する辞書を指定する文字列。 |
| gen_dictionary_drop | RDDBF | 辞書を辞書レジストリから削除します。 | gen_dictionary_drop(dictionary_name) | dictionary_name：辞書レジストリから削除する辞書に名前を付ける文字列。 |
| gen_dictionary_load | RDDBF | ファイルを辞書レジストリにロードし、辞書名の引数を必要とする他の関数で使用される名前を辞書に割り当てます。 この機能にはSUPER特権が必要です。 辞書をリロードするには、まずgen_dictionary_drop()で辞書をドロップし 、次にgen_dictionary_load()で再度ロードします。 |
gen_dictionary_load(dictionary_path, dictionary_name) | dictionary_path：辞書ファイルのパス名を指定する文字列。 dictionary_name：辞書の名前を提供する文字列。 |
環境構築
では、遊んで見るために環境を構築してみるか！
Percona8 インストール in AWS EC2
Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS参照
今回は作業時のPercona Serverの最新Verである8.0.18-9 を入れました。
[ec2-user@ip-xxx ~]$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
percona-release-latest.noarch.rpm | 17 kB 00:00:00
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm を調べています: percona-release-1.0-13.noarch
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:1.0-13 を インストール
--> 依存性解決を終了しました。
amzn2-core/2/x86_64 | 2.4 kB 00:00:00
amzn2extra-docker/2/x86_64 | 1.3 kB 00:00:00
依存性を解決しました
============================================================================================================================================================================================================
Package アーキテクチャー バージョン リポジトリー 容量
============================================================================================================================================================================================================
インストール中:
percona-release noarch 1.0-13 /percona-release-latest.noarch 20 k
トランザクションの要約
============================================================================================================================================================================================================
インストール 1 パッケージ
合計容量: 20 k
インストール容量: 20 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
インストール中 : percona-release-1.0-13.noarch 1/1
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
https://www.percona.com/doc/percona-repo-config/percona-release.html
検証中 : percona-release-1.0-13.noarch 1/1
インストール:
percona-release.noarch 0:1.0-13
完了しました!
[ec2-user@ip-xxx ~]$ sudo percona-release setup ps80
* Disabling all Percona Repositories
* Enabling the Percona Server 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
[ec2-user@ip-xxx ~]$ sudo yum install percona-server-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
ps-80-release-noarch | 2.9 kB 00:00:00
ps-80-release-x86_64 | 2.9 kB 00:00:00
tools-release-noarch | 2.9 kB 00:00:00
tools-release-x86_64 | 2.9 kB 00:00:00
(1/4): tools-release-noarch/2/primary_db | 1.1 kB 00:00:00
(2/4): ps-80-release-noarch/2/primary_db | 1.1 kB 00:00:00
(3/4): ps-80-release-x86_64/2/primary_db | 63 kB 00:00:00
(4/4): tools-release-x86_64/2/primary_db | 54 kB 00:00:00
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-server.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: percona-server-client のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-client.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: libncurses.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: libtinfo.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
---> パッケージ percona-server-shared.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared-compat のパッケージ: percona-server-shared-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ mariadb-libs.x86_64 1:5.5.64-1.amzn2 を 不要
---> パッケージ ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 を インストール
---> パッケージ percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7 を 非推奨
--> 依存性解決を終了しました。
依存性を解決しました
============================================================================================================================================================================================================
Package アーキテクチャー バージョン リポジトリー 容量
============================================================================================================================================================================================================
インストール中:
percona-server-server x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 53 M
percona-server-shared-compat x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 1.2 M
mariadb-libs.x86_64 1:5.5.64-1.amzn2 を入れ替えます
依存性関連でのインストールをします:
ncurses-compat-libs x86_64 6.0-8.20170212.amzn2.1.3 amzn2-core 308 k
percona-server-client x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 12 M
percona-server-shared x86_64 8.0.18-9.1.el7 ps-80-release-x86_64 1.3 M
トランザクションの要約
============================================================================================================================================================================================================
インストール 2 パッケージ (+3 個の依存関係のパッケージ)
総ダウンロード容量: 68 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64.rpm | 308 kB 00:00:00
warning: /var/cache/yum/x86_64/2/ps-80-release-x86_64/packages/percona-server-client-8.0.18-9.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY ] 3.6 MB/s | 24 MB 00:00:12 ETA
percona-server-client-8.0.18-9.1.el7.x86_64.rpm の公開鍵がインストールされていません
(2/5): percona-server-client-8.0.18-9.1.el7.x86_64.rpm | 12 MB 00:00:04
(3/5): percona-server-shared-8.0.18-9.1.el7.x86_64.rpm | 1.3 MB 00:00:00
(4/5): percona-server-shared-compat-8.0.18-9.1.el7.x86_64.rpm | 1.2 MB 00:00:01
(5/5): percona-server-server-8.0.18-9.1.el7.x86_64.rpm | 53 MB 00:00:18
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
合計 3.7 MB/s | 68 MB 00:00:18
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
Userid : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
Package : percona-release-1.0-13.noarch (installed)
From : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか？ [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
インストール中 : percona-server-shared-compat-8.0.18-9.1.el7.x86_64 1/6
インストール中 : percona-server-shared-8.0.18-9.1.el7.x86_64 2/6
インストール中 : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64 3/6
インストール中 : percona-server-client-8.0.18-9.1.el7.x86_64 4/6
インストール中 : percona-server-server-8.0.18-9.1.el7.x86_64 5/6
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/8.0/management/udf_percona_toolkit.html for more details
削除中 : 1:mariadb-libs-5.5.64-1.amzn2.x86_64 6/6
検証中 : percona-server-client-8.0.18-9.1.el7.x86_64 1/6
検証中 : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64 2/6
検証中 : percona-server-server-8.0.18-9.1.el7.x86_64 3/6
検証中 : percona-server-shared-8.0.18-9.1.el7.x86_64 4/6
検証中 : percona-server-shared-compat-8.0.18-9.1.el7.x86_64 5/6
検証中 : 1:mariadb-libs-5.5.64-1.amzn2.x86_64 6/6
インストール:
percona-server-server.x86_64 0:8.0.18-9.1.el7 percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7
依存性関連をインストールしました:
ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 percona-server-client.x86_64 0:8.0.18-9.1.el7 percona-server-shared.x86_64 0:8.0.18-9.1.el7
置換:
mariadb-libs.x86_64 1:5.5.64-1.amzn2
完了しました!
Percona Server起動
[ec2-user@ip-xxx ~]$ sudo service mysql start
Redirecting to /bin/systemctl start mysql.service
[ec2-user@ip-xxx ~]$ sudo service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 水 2020-01-15 02:26:06 UTC; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3294 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 3376 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─3376 /usr/sbin/mysqld
1月 15 02:26:00 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Starting MySQL Server...
1月 15 02:26:06 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Started MySQL Server.
Percona Server ログイン
[ec2-user@ip-xxx ~]$ sudo cat /var/log/mysqld.log |grep generated 2020-01-15T02:26:03.644352Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xxxxxx [ec2-user@ip-xxx ~]$ mysql -uroot -pxxxxxx mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18-9 Copyright (c) 2009-2019 Percona LLC and/or its affiliates 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>
root ユーザのパスワード変更
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
が出るのでパスワードを変更する
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'passxxxxxx'; Query OK, 0 rows affected (0.00 sec)
マスキング設定
mysql> INSTALL PLUGIN data_masking SONAME 'data_masking.so'; Query OK, 0 rows affected (0.00 sec)
辞書登録
gen_dictionaryで行う。
辞書と呼ばれるものにマスキング用文字列登録します。(再起動すると読み直しが必要)
というわけで、お試しファイルを落としてくる
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
--2020-01-15 02:50:00-- https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1289773 (1.2M) [text/plain]
`first_names.all.txt' に保存中
100%[==================================================================================================================================================================>] 1,289,773 --.-K/s 時間 0.03s
2020-01-15 02:50:01 (39.1 MB/s) - `first_names.all.txt' へ保存完了 [1289773/1289773]
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
--2020-01-15 02:50:45-- https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 786535 (768K) [text/plain]
`last_names.all.txt' に保存中
100%[==================================================================================================================================================================>] 786,535 --.-K/s 時間 0.04s
2020-01-15 02:50:46 (18.1 MB/s) - `last_names.all.txt' へ保存完了 [786535/786535]
[ec2-user@ip-xxx ~]$ ll
合計 2032
-rw-rw-r-- 1 ec2-user ec2-user 1289773 1月 15 02:50 first_names.all.txt
-rw-rw-r-- 1 ec2-user ec2-user 786535 1月 15 02:50 last_names.all.txt
[ec2-user@ip-xxx ~]$ mv *.txt /tmp/
[ec2-user@ip-xxx ~]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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> select gen_dictionary_load('/tmp/first_names.all.txt', 'first_names');
+----------------------------------------------------------------+
| gen_dictionary_load('/tmp/first_names.all.txt', 'first_names') |
+----------------------------------------------------------------+
| Dictionary load success |
+----------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select gen_dictionary_load('/tmp/last_names.all.txt', 'last_names');
+--------------------------------------------------------------+
| gen_dictionary_load('/tmp/last_names.all.txt', 'last_names') |
+--------------------------------------------------------------+
| Dictionary load success |
+--------------------------------------------------------------+
1 row in set (0.01 sec)
テストテーブルの用意
[ec2-user@ip-xxx ~]$ wget https://github.com/datacharmer/test_db/archive/master.zip
--2020-01-15 03:00:43-- https://github.com/datacharmer/test_db/archive/master.zip
github.com (github.com) をDNSに問いあわせています... 52.69.186.44
github.com (github.com)|52.69.186.44|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: https://codeload.github.com/datacharmer/test_db/zip/master [続く]
--2020-01-15 03:00:44-- https://codeload.github.com/datacharmer/test_db/zip/master
codeload.github.com (codeload.github.com) をDNSに問いあわせています... 52.68.31.213
codeload.github.com (codeload.github.com)|52.68.31.213|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 特定できません [application/zip]
`master.zip' に保存中
[ <=> ] 36,687,757 3.15MB/s 時間 11s
2020-01-15 03:00:55 (3.15 MB/s) - `master.zip' へ保存終了 [36687757]
[ec2-user@ip-xxx ~]$ ll
合計 35828
-rw-rw-r-- 1 ec2-user ec2-user 36687757 1月 15 03:00 master.zip
[ec2-user@ip-xxx ~]$ unzip master.zip
Archive: master.zip
0b66c2338736779e3b150c7d125b1012d95a961f
creating: test_db-master/
inflating: test_db-master/Changelog
inflating: test_db-master/README.md
inflating: test_db-master/employees.sql
inflating: test_db-master/employees_partitioned.sql
inflating: test_db-master/employees_partitioned_5.1.sql
creating: test_db-master/images/
inflating: test_db-master/images/employees.gif
inflating: test_db-master/images/employees.jpg
inflating: test_db-master/images/employees.png
inflating: test_db-master/load_departments.dump
inflating: test_db-master/load_dept_emp.dump
inflating: test_db-master/load_dept_manager.dump
inflating: test_db-master/load_employees.dump
inflating: test_db-master/load_salaries1.dump
inflating: test_db-master/load_salaries2.dump
inflating: test_db-master/load_salaries3.dump
inflating: test_db-master/load_titles.dump
inflating: test_db-master/objects.sql
creating: test_db-master/sakila/
inflating: test_db-master/sakila/README.md
inflating: test_db-master/sakila/sakila-mv-data.sql
inflating: test_db-master/sakila/sakila-mv-schema.sql
inflating: test_db-master/show_elapsed.sql
inflating: test_db-master/sql_test.sh
inflating: test_db-master/test_employees_md5.sql
inflating: test_db-master/test_employees_sha.sql
[ec2-user@ip-xxx ~]$ ll
合計 35832
-rw-rw-r-- 1 ec2-user ec2-user 36687757 1月 15 03:00 master.zip
drwxrwxr-x 4 ec2-user ec2-user 4096 4月 9 2019 test_db-master
[ec2-user@ip-xxx ~]$ cd test_db-master/
[ec2-user@ip-xxx test_db-master]$ ll
合計 168336
-rw-rw-r-- 1 ec2-user ec2-user 964 4月 9 2019 Changelog
-rw-rw-r-- 1 ec2-user ec2-user 4325 4月 9 2019 README.md
-rw-rw-r-- 1 ec2-user ec2-user 4193 4月 9 2019 employees.sql
-rw-rw-r-- 1 ec2-user ec2-user 6276 4月 9 2019 employees_partitioned.sql
-rw-rw-r-- 1 ec2-user ec2-user 7948 4月 9 2019 employees_partitioned_5.1.sql
drwxrwxr-x 2 ec2-user ec2-user 69 4月 9 2019 images
-rw-rw-r-- 1 ec2-user ec2-user 250 4月 9 2019 load_departments.dump
-rw-rw-r-- 1 ec2-user ec2-user 14159880 4月 9 2019 load_dept_emp.dump
-rw-rw-r-- 1 ec2-user ec2-user 1090 4月 9 2019 load_dept_manager.dump
-rw-rw-r-- 1 ec2-user ec2-user 17722832 4月 9 2019 load_employees.dump
-rw-rw-r-- 1 ec2-user ec2-user 39806034 4月 9 2019 load_salaries1.dump
-rw-rw-r-- 1 ec2-user ec2-user 39805981 4月 9 2019 load_salaries2.dump
-rw-rw-r-- 1 ec2-user ec2-user 39080916 4月 9 2019 load_salaries3.dump
-rw-rw-r-- 1 ec2-user ec2-user 21708736 4月 9 2019 load_titles.dump
-rw-rw-r-- 1 ec2-user ec2-user 4568 4月 9 2019 objects.sql
drwxrwxr-x 2 ec2-user ec2-user 77 4月 9 2019 sakila
-rw-rw-r-- 1 ec2-user ec2-user 272 4月 9 2019 show_elapsed.sql
-rwxr-xr-x 1 ec2-user ec2-user 1800 4月 9 2019 sql_test.sh
-rw-rw-r-- 1 ec2-user ec2-user 4878 4月 9 2019 test_employees_md5.sql
-rw-rw-r-- 1 ec2-user ec2-user 4882 4月 9 2019 test_employees_sha.sql
[ec2-user@ip-xxx test_db-master]$ view employees.sql
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:51
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
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> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql> show columns from employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
マスキングビュー作成(GEN_RANGE,GEN_DICTIONARY)
mysql> CREATE VIEW deidentified_employees -> AS -> SELECT -> gen_range(900000000, 999999999) as emp_no, -> makedate(year(birth_date), 1) as birth_date, -> gen_dictionary('first_names') as first_name, -> gen_dictionary('last_names') as last_name, -> gender, -> makedate(year(hire_date), 1) as hire_date -> FROM employees; ERROR 1305 (42000): FUNCTION employees. gen_range does not exist
どうやらPerconaの記事のそれをそのまま使うとエラーになるみたい。(どっかに変な文字コードでも入ってるのかな？)
書き換える
mysql> CREATE VIEW deidentified_employees -> AS -> SELECT -> GEN_RANGE(900000000, 999999999) AS emp_no, -> MAKEDATE(YEAR(birth_date), 1) AS birth_date, -> GEN_DICTIONARY('first_names') AS first_name, -> GEN_DICTIONARY('last_names') AS last_name, -> gender, -> MAKEDATE(YEAR(hire_date), 1) AS hire_date -> FROM -> employees; Query OK, 0 rows affected (0.01 sec)
マスキング結果確認
ここでマスキング関数でマスキングされるのはemp_no,first_name,last_name、
birth_dateとhire_dateはMAKEDATE関数で置換表示してます。
元テーブル
mysql> SELECT * FROM employees LIMIT 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
マスキングビュー
mysql> SELECT * FROM deidentified_employees LIMIT 10; +-----------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +-----------+------------+------------+-------------+--------+------------+ | 912474868 | 1953-01-01 | sumio | greff | M | 1986-01-01 | | 998170886 | 1964-01-01 | haralambie | rauhuff | F | 1985-01-01 | | 958103369 | 1959-01-01 | filippina | angilletta | M | 1986-01-01 | | 967871226 | 1954-01-01 | margarine | munoz | M | 1986-01-01 | | 907286604 | 1955-01-01 | ajahnay | agel | M | 1989-01-01 | | 914673574 | 1953-01-01 | kalibo | manganiello | F | 1989-01-01 | | 906652311 | 1957-01-01 | shantai | mitchael | F | 1989-01-01 | | 998921306 | 1958-01-01 | rachela | selbe | M | 1994-01-01 | | 918607185 | 1952-01-01 | bradlay | ehlke | F | 1985-01-01 | | 904967645 | 1963-01-01 | shadiyah | partyka | F | 1989-01-01 | +-----------+------------+------------+-------------+--------+------------+ 10 rows in set (0.00 sec)
マスキングビュー作成(MASK_INNER/MASK_OUTER)
文字列の中もしくは外側をマスキングする関数ですね
mysql> CREATE VIEW deidentified_salaries -> AS -> SELECT -> gen_range(900000000, 999999999) as emp_no, -> gen_range(40000, 80000) as salary, -> mask_inner(date_format(from_date, '%Y-%m-%d'), 4, 0) as from_date, -> mask_outer(date_format(to_date, '%Y-%m-%d'), 4, 2, '0') as to_date -> FROM salaries; ERROR 1123 (HY000): Can't initialize function 'mask_outer'; Wrong argument type: MASK_OUTER(string, int, int, [char])
えー、こっちもそのままだと動かん…。(4つ目の引数を外してみる)
mysql> CREATE VIEW deidentified_salaries AS -> SELECT -> GEN_RANGE(900000000, 999999999) AS emp_no, -> GEN_RANGE(40000, 80000) AS salary, -> MASK_INNER(DATE_FORMAT(from_date,'%Y-%m-%d'),4,0) AS from_date, -> MASK_OUTER(DATE_FORMAT(to_date, '%Y-%m-%d'),4,2) AS to_date -> FROM -> salaries; Query OK, 0 rows affected (0.01 sec)
通った。
マスキング結果確認
from_dateでは前から4文字を除いてマスキング、to_dateでは前から4文字目の次の文字から2文字(-は含まない模様)を除きマスキング
元テーブル
mysql> SELECT * FROM salaries LIMIT 10; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10001 | 60117 | 1986-06-26 | 1987-06-26 | | 10001 | 62102 | 1987-06-26 | 1988-06-25 | | 10001 | 66074 | 1988-06-25 | 1989-06-25 | | 10001 | 66596 | 1989-06-25 | 1990-06-25 | | 10001 | 66961 | 1990-06-25 | 1991-06-25 | | 10001 | 71046 | 1991-06-25 | 1992-06-24 | | 10001 | 74333 | 1992-06-24 | 1993-06-24 | | 10001 | 75286 | 1993-06-24 | 1994-06-24 | | 10001 | 75994 | 1994-06-24 | 1995-06-24 | | 10001 | 76884 | 1995-06-24 | 1996-06-23 | +--------+--------+------------+------------+ 10 rows in set (0.00 sec)
マスキングビュー
mysql> SELECT * FROM deidentified_salaries LIMIT 10; +-----------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +-----------+--------+------------+------------+ | 950823834 | 70949 | 1986XXXXXX | XXXX-06-XX | | 987110465 | 62877 | 1987XXXXXX | XXXX-06-XX | | 964944005 | 43451 | 1988XXXXXX | XXXX-06-XX | | 990093942 | 65625 | 1989XXXXXX | XXXX-06-XX | | 997508813 | 53442 | 1990XXXXXX | XXXX-06-XX | | 935495362 | 65082 | 1991XXXXXX | XXXX-06-XX | | 991618027 | 65280 | 1992XXXXXX | XXXX-06-XX | | 961487920 | 60056 | 1993XXXXXX | XXXX-06-XX | | 947195247 | 45183 | 1994XXXXXX | XXXX-06-XX | | 976597378 | 62853 | 1995XXXXXX | XXXX-06-XX | +-----------+--------+------------+------------+ 10 rows in set (0.00 sec)
ちなみに
- MySQL 8.0.19の時点で戻り値がlatin1になっている(それ以前はbinary)
- 確認方法は
SELECT CHARSET(gen_rnd_email()); - 文字セットを特定のものにしたい場合はCONVERT関数を使う（
CONVERT(gen_rnd_email() USING utf8mb4);）
- 確認方法は
バグもしくはMySQL EEとは違ってるところ
- mask_outer(a*****,a#####ってなるんじゃないの？)
mysql> SELECT mask_outer('abcdef', 0, 1, '*'), mask_outer('abcdef',0, 1, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 1, '*') | mask_outer('abcdef',0, 1, "#") | +---------------------------------+--------------------------------+ | abcde* | abcde# | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT mask_outer('abcdef', 0, 2, '*'), mask_outer('abcdef',0, 2, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 2, '*') | mask_outer('abcdef',0, 2, "#") | +---------------------------------+--------------------------------+ | abcd** | abcd## | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT mask_outer('abcdef', 0, 5, '*'), mask_outer('abcdef',0, 5, "#"); +---------------------------------+--------------------------------+ | mask_outer('abcdef', 0, 5, '*') | mask_outer('abcdef',0, 5, "#") | +---------------------------------+--------------------------------+ | abcdef | abcdef | +---------------------------------+--------------------------------+ 1 row in set (0.00 sec)
- gen_rnd_panに数字が入れられない
mysql> SELECT mask_pan(gen_rnd_pan(19)); ERROR 1123 (HY000): Can't initialize function 'gen_rnd_pan'; Wrong argument list: gen_rnd_pan()
- gen_rangeがマイナス値入れるとバグる
mysql> SELECT gen_range(100, 200), gen_range(-1000, -800); +---------------------+------------------------+ | gen_range(100, 200) | gen_range(-1000, -800) | +---------------------+------------------------+ | 159 | 4294966311 | +---------------------+------------------------+ 1 row in set (0.00 sec)
感想
- マスキング便利(バグっぽいところあるけど、無料でこれらが気軽に使えるのは良い)
- でも、2バイト文字は使えない…。
- 早く2バイト対応版欲しい！
mysql> SELECT mask_inner('41からはじめたんですよね?', 1, 2); +----------------------------------------------------------+ | mask_inner('41からはじめたんですよね?', 1, 2) | +----------------------------------------------------------+ | 4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX�? | +----------------------------------------------------------+ 1 row in set (0.00 sec)
- ビューで同じデータを加工した形で見せることができるので色々な使い方ができそう
- 一方でPerconaServer AND 8.0なのでそうじゃない環境の場合どうするか？
MySQLも含めてマスキングがまだまだ使い勝手が微妙なところはあるものの、気軽に元データに手を入れずにマスキングできるのは本当に便利。 無いよりはあったほうが絶対に良いので、なんとか使う方法を模索してみるのもありでは。
【番外編】北海道（函館近辺）のおすすめポイントを語ってみる
今回は技術的なことはもちろん、MySQLのMの字も出ない番外編です
北海道（函館近辺）のおすすめポイントを語ってみる
- 嫁の実家があるので年末年始で行ってきた
- 今までにも何度も行ってるんで、函館近辺で良かったとことか雑に紹介してみる
遊
函館の街並み
- 懐かしいレトロな感じが残っている
- 明治～大正くらいの建物が普通に残ってる
- 外人墓地なんかもあって長崎とかと似ている
- いつも「るろうに剣心っぽい」なと感じる街並みである（行ってみたらわかると思う）
夜景
https://www.hakobura.jp/nightview/
雪遊び
- パウダースノーなので転んでも痛くない
- 手につかむとすぐ溶ける
- 雪の結晶が裸眼で見えるくらい細やか
- 自分でウェアとか用意すればかかるのはリフト代くらいなので安くつく
温泉
- 北海道はあちこちに温泉が湧いているが、函館近辺も多い
- 大沼だとスキーしたあとそのままホテルのお風呂が使えるので良い
- 寒い中で温泉はいると幸せな気分になる
- 大衆浴場的なお風呂屋さんも函館市内に多く点在しており、どれも綺麗で大きい
蔦屋書店
- 超巨大なTSUTAYA
- 無い本はほとんどないのでは？と思うような書籍量
- 休むところも多く、地元民は図書館のように使っている
- もちろんレンタルやゲームやカード等の販売もやってる
- 駐車場もバカでかい
食
ラッキーピエロ
- 函館近辺でやってるファーストフード（ご当地なんちゃら）
- 主にハンバーガーを扱っているが、オムライスやカレーライス、カツ丼なんかも食べられる
- 価格はハンバーガーはさほど安くないがオリジナリティはある
- オムライスやカレー、カツ丼あたりは量のわりには安い
- ソフトクリーム、パフェはコスパ高い
- 最近は認知度が上がったため、外国人観光客も多く、激混みで店によっては40分～1h待ちもありうる
- 15歳から75歳までバイトを募集しているらしい
寿司🍣（魚介類）
- いうまでもなく函館に行ったら食べるべきものの一つ
- スーパーの寿司ですら東京のそれと比べ物にならないが、ぜひ回転寿司に行くべき
- 一人3000円出せば「暫く寿司は食べなくてもいいや」という満足した気持ちになれる
- 東京で同じ味を同じ量食べようと思うと諭吉が必要
- 海岸通り沿いにあるところは混んでいるのでお昼過ぎくらいまでに行かないとネタが夜にはかなり無くなっている
大沼だんご🍡
- 保存料とか使ってないので、24h以内に食べないとどんどん固くなるだんご
- 買ってすぐに食べるのが吉。柔らかくてうまい
- だんごといっても串にささっているわけではない
- 結構な量が入っている
- 水曜どうでしょうでも甘いものが苦手なミスターが苦しめられていたｗ
http://www.hakonavi.ne.jp/oonuma/numanoya.html
セイコーマート
- 函館にはセブンイレブンが結構あるが、それ以上にあるのがセイコーマートというコンビニ
- コンビニ業界ではかなりホワイト企業として知られている
- 品物は他のコンビニで見ないようなオリジナル商品が多いが、特にドリンクとHOT CHEFと呼ばれる店内で作られるお弁当やおにぎり、フライドポテトなんかがかなり美味いのでおすすめ
https://www.seicomart.co.jp/instore/lineup.html
交通
飛行機
- 函館空港があり、羽田から約1hで着くんだから驚き
- 羽田まで行くのがそこまで大変じゃなければおすすめ
- 子供が小さければ膝に乗せられる年齢なら子供分が無料
- 本数が少ないJALとANN足して1日8便程度なのは注意（まあ、地方としては多いほうだけど）
新幹線
- 4～5hかかるけどずっと座って行けるのは良いし、何より連休シーズンは飛行機より安い
- 普段できない動画やラジオをまとめて聞ける
- 途中下車も可能だし
- ただし、東北新幹線は基本自由席という概念がない（一部あるけど）ので注意
路面電車
車
- 車の運転に自信があるならレンタカーが吉
- 車があればどこでも行ける（函館はそんなに大きくないが、車がないといけないところは多い）
- 効率よく楽しむのであれば車はサイコー
ロードヒーティング
- 高速道路的なところは大体雪が溶けるように道路が温かい
- 大雪の日でも安心して走れる
- さすがに住宅街とか近辺の道路には無い
その他
- 水曜どうでしょう最新作が最初に見られる（これは北海道だから）
ネガティブな話も少し書いてみる
- 函館市内と新函館北斗駅（新幹線の駅）は結構離れてて、電車で約30分ほどかかる
- 函館市内と函館空港も同様
- 嫁の実家にはインターネット環境がない
- テレビも山に近いので映らなくなること多い
- 嫁の実家には犬と猫がいるのだが、子供がアレルギー持ちで全身湿疹だらけになる（でも動物は好き）
- 病院が遠い（車なしでは行けない）
まとめ
- 函館だけでも十分に北海道を堪能できる
- 夏も冬も北海道は楽しいし、おいしい
- 函館をスタート地点にして北海道旅行始めてみません？
" _ " or " % " - Which is faster? (Like predicate in MySQL's sql)
MySQL Advent Calendar 2019 の22日目です。
MySQLのあいまい検索時のLIKEで使える「_」と「%」について調べてみました。
あいまい検索時に使う「_」と「%」
会社でlike 検索した際に「_」と「%」で速度差ってあるのかな？という話になり、
こういうの調べたことある人って世の中に結構いるんじゃない？と調べてみたんですが、なかなか出てこないので自分で調べてみました
検証について
- カラムはとりあえず文字列としました
- セカンダリインデックスの有無とNULLの有無で比較
- NULLABLEなカラムのほうは全体の1割程度NULLにしてみた(根拠は無い)
- 作業環境はdockerに立てたMySQL8.0.18です(HDD上に結構小さいスペックで立ててます)
テーブル用意
CREATE TABLE `like_test` ( `like_test_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'LIKE文の検証ID', `uuid_key_ari_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_nashi_nn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `uuid_key_ari_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, `uuid_key_nashi_dn` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin default NULL, PRIMARY KEY (`like_test_id`), KEY `idx_uuid_key_ari_nn` (`uuid_key_ari_nn`), KEY `idx_uuid_key_ari_dn` (`uuid_key_ari_dn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='LIKE文の検証' ;
| カラム名 | カラムの役割 | インデックス有無 | NOT NULL |
|---|---|---|---|
| like_test_id | PK | 〇 | 〇 |
| uuid_key_ari_nn | UUID値をNOT NULLの形で持つ | 〇 | 〇 |
| uuid_key_nashi_nn | UUID値をNOT NULLの形で持つ | × | 〇 |
| uuid_key_ari_dn | UUID値をNULLABLEの形で持つ | 〇 | × |
| uuid_key_nashi_dn | UUID値をNULLABLEの形で持つ | × | × |
データ用意
INSERT INTO `test`.`like_test` (`like_test_id`, `uuid_key_ari_nn`, `uuid_key_nashi_nn`, `uuid_key_ari_dn`, `uuid_key_nashi_dn`) VALUES (null, uuid(), uuid(), uuid(), uuid());
pythonで書いてuuid v4で入れるか？とも考えたけど、とりあえずいーやってことでMySQLのUUID関数使う。
上のINSERTを適当にぶん回したりアレコレして480000件作った
> select count(1) from like_test; +------------+ | count(1) | |------------| | 480000 | +------------+ 1 row in set Time: 0.059s
ちょっとデータをいじって、４つのカラムがすべて同じ値かつテーブルで一意な値を用意した。
> select * from like_test where like_test_id = 14100; +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ | like_test_id | uuid_key_ari_nn | uuid_key_nashi_nn | uuid_key_ari_dn | uuid_key_nashi_dn | |----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------| | 14100 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | z9y8x7v6-99xx-99yy-99zz-zzzzzz123456 | +----------------+--------------------------------------+--------------------------------------+--------------------------------------+--------------------------------------+ 1 row in set Time: 0.010s
このレコードを使って検証してみる。
検証詳細
- mysqlslapを使う
- 並列数は1、1000回クエリを実行して平均、最小、最大時間を見る
- where句に
uuid_key_ari_nn,uuid_key_nashi_nn,uuid_key_ari_dn,uuid_key_nashi_dnの4カラムでLIKE文を作る - where 句の条件は以下のような感じ
| パターンNo | 条件パターン | WHERE句 |
|---|---|---|
| 1 | _も%も使わないかつlikeじゃなくて= |
= 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 2 | _も%も使わない |
like 'z9y8x7v6-99xx-99yy-99zz-zzzzzz123456' |
| 3 | _で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-____________' |
| 4 | %で前方一致検索 |
like 'z9y8x7v6-99xx-99yy-99zz-%' |
| 5 | _で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-______________-zzzzzzz123456' |
| 6 | %で中間があいまいな検索(一応前方一致) |
like 'z9y8x7v6-%-zzzzzz123456' |
| 7 | _で後方一致検索 |
like '________-99xx-99yy-99zz-zzzzzz123456' |
| 8 | %で後方一致検索 |
like '%-99xx-99yy-99zz-zzzzzz123456' |
| 9 | _で中間一致検索 |
like '-99xx-99yy-99zz-____' |
| 10 | %で中間一致検索 |
like '%-99xx-99yy-99zz-%' |
ちなみに、後方一致と中間一致(7～10のパターンですね)はインデックス張っててもフルテーブルスキャンになります。
結果
分かりやすい結果が出ました。
- 検索条件値が曖昧じゃない場合は＝のほうがLIKEよりほんのり速い気がする(けど気にしなくてもいいかなってレベル)
- インデックス使えてたら気にしなくていいレベル
- フルテーブルスキャンの場合は前方一致の場合と中間一致・後方一致のばあいだと前方一致検索のほうが速い
- フルテーブルスキャン・前方一致の場合だと「_」と「%」で差が無い
- 一方、フルテーブルスキャン・中間一致/後方一致だと「_」のほうが効率がいいのが明らか
まあ、文字列探索とかしたことある人はイメージしやすいですよね。(インデックスの素晴らしさも改めて実感しましたｗ)
それぞれの速度結果
uuid_key_ari_nn(インデックスあり・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.012 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.013 |
| 7 | 0.160 | 0.154 | 0.176 |
| 8 | 0.248 | 0.240 | 0.263 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.241 | 0.269 |
uuid_key_nashi_nn(インデックス無し・NOT NULL)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.143 | 0.137 | 0.156 |
| 2 | 0.147 | 0.141 | 0.162 |
| 3 | 0.147 | 0.140 | 0.175 |
| 4 | 0.147 | 0.141 | 0.160 |
| 5 | 0.147 | 0.140 | 0.159 |
| 6 | 0.147 | 0.141 | 0.162 |
| 7 | 0.160 | 0.153 | 0.177 |
| 8 | 0.248 | 0.241 | 0.286 |
| 9 | 0.160 | 0.154 | 0.182 |
| 10 | 0.248 | 0.240 | 0.269 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.005 | 0.003 | 0.013 |
| 2 | 0.005 | 0.003 | 0.017 |
| 3 | 0.005 | 0.003 | 0.011 |
| 4 | 0.005 | 0.003 | 0.013 |
| 5 | 0.005 | 0.003 | 0.011 |
| 6 | 0.005 | 0.003 | 0.015 |
| 7 | 0.158 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.255 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.234 | 0.227 | 0.253 |
uuid_key_ari_dn(インデックスあり・NULLABLE)
| パターンNo | 平均時間(秒) | 最小時間(秒) | 最大時間(秒) |
|---|---|---|---|
| 1 | 0.142 | 0.136 | 0.152 |
| 2 | 0.146 | 0.139 | 0.166 |
| 3 | 0.145 | 0.140 | 0.158 |
| 4 | 0.145 | 0.140 | 0.160 |
| 5 | 0.146 | 0.140 | 0.158 |
| 6 | 0.146 | 0.140 | 0.160 |
| 7 | 0.157 | 0.151 | 0.171 |
| 8 | 0.234 | 0.227 | 0.254 |
| 9 | 0.157 | 0.151 | 0.172 |
| 10 | 0.233 | 0.227 | 0.250 |
結論
- 中間一致検索、後方一致検索で「%」使うとパフォーマンス下がる可能性があるので、そういう場合は「_」に置き換えましょう
- そもそもそんな検索しないのがいいんですが・・・
明日は@asmrt_dsさんの「はじめてMySQLを使ってみた話」です。
COUNT に「OR NULL」入れるの、正しい結果返すのわかったけど、心がザワザワしてたけど、今はスッキリした。(スゲーッ爽やかな気分だぜ)
タイトルにある話はつまりこう
ここに都市名を入れたテーブルがあったとします。
mysql root@127.0.0.1:test> select * from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+--------------+------------------------| | 1 | 北海道 | 0 | <null> | <null> | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | +-----------------+-------------------+-----------+--------------+------------------------+ 10 rows in set
実際には47都道府県分入っています。 city_idが0のものが都道府県を表しているレコードです。 (あ、このテーブル正規化されてなくてイケてないというツッコミはご勘弁をｗ あえてイメージしやすいようにしてるので)
定義はこちらです。
mysql root@127.0.0.1:test> desc city; +-----------------+--------------+--------+-------+-----------+---------+ | Field | Type | Null | Key | Default | Extra | |-----------------+--------------+--------+-------+-----------+---------| | prefecture_id | tinyint(2) | NO | PRI | <null> | | | prefecture_name | varchar(10) | YES | | <null> | | | city_id | smallint(3) | NO | PRI | <null> | | | city_name | varchar(600) | YES | | <null> | | | city_kana | varchar(216) | YES | | <null> | | +-----------------+--------------+--------+-------+-----------+---------+ 5 rows in set
件数は市区町村名まで入ってるレコードと47都道府県の分で1987件あるとします。
mysql root@127.0.0.1:test> select count(1) from city; +------------+ | count(1) | |------------| | 1987 | +------------+ 1 row in set mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set mysql root@127.0.0.1:test> select * from city where city_id = 0; +-----------------+-------------------+-----------+-------------+-------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | |-----------------+-------------------+-----------+-------------+-------------| | 1 | 北海道 | 0 | <null> | <null> | | 2 | 青森県 | 0 | <null> | <null> | | 3 | 岩手県 | 0 | <null> | <null> | | 4 | 宮城県 | 0 | <null> | <null> | | 5 | 秋田県 | 0 | <null> | <null> | | 6 | 山形県 | 0 | <null> | <null> | | 7 | 福島県 | 0 | <null> | <null> | | 8 | 茨城県 | 0 | <null> | <null> | | 9 | 栃木県 | 0 | <null> | <null> | | 10 | 群馬県 | 0 | <null> | <null> | | 11 | 埼玉県 | 0 | <null> | <null> | | 12 | 千葉県 | 0 | <null> | <null> | | 13 | 東京都 | 0 | <null> | <null> | | 14 | 神奈川県 | 0 | <null> | <null> | | 15 | 新潟県 | 0 | <null> | <null> | | 16 | 富山県 | 0 | <null> | <null> | | 17 | 石川県 | 0 | <null> | <null> | | 18 | 福井県 | 0 | <null> | <null> | | 19 | 山梨県 | 0 | <null> | <null> | | 20 | 長野県 | 0 | <null> | <null> | | 21 | 岐阜県 | 0 | <null> | <null> | | 22 | 静岡県 | 0 | <null> | <null> | | 23 | 愛知県 | 0 | <null> | <null> | | 24 | 三重県 | 0 | <null> | <null> | | 25 | 滋賀県 | 0 | <null> | <null> | | 26 | 京都府 | 0 | <null> | <null> | | 27 | 大阪府 | 0 | <null> | <null> | | 28 | 兵庫県 | 0 | <null> | <null> | | 29 | 奈良県 | 0 | <null> | <null> | | 30 | 和歌山県 | 0 | <null> | <null> | | 31 | 鳥取県 | 0 | <null> | <null> | | 32 | 島根県 | 0 | <null> | <null> | | 33 | 岡山県 | 0 | <null> | <null> | | 34 | 広島県 | 0 | <null> | <null> | | 35 | 山口県 | 0 | <null> | <null> | | 36 | 徳島県 | 0 | <null> | <null> | | 37 | 香川県 | 0 | <null> | <null> | | 38 | 愛媛県 | 0 | <null> | <null> | | 39 | 高知県 | 0 | <null> | <null> | | 40 | 福岡県 | 0 | <null> | <null> | | 41 | 佐賀県 | 0 | <null> | <null> | | 42 | 長崎県 | 0 | <null> | <null> | | 43 | 熊本県 | 0 | <null> | <null> | | 44 | 大分県 | 0 | <null> | <null> | | 45 | 宮崎県 | 0 | <null> | <null> | | 46 | 鹿児島県 | 0 | <null> | <null> | | 47 | 沖縄県 | 0 | <null> | <null> | +-----------------+-------------------+-----------+-------------+-------------+ 47 rows in set
さて問題
select count(1) from city where city_name is null;を実行した場合にかえる数値は？
当然、47です。
mysql root@127.0.0.1:test> select count(1) from city where city_name is null; +------------+ | count(1) | |------------| | 47 | +------------+ 1 row in set
では、select count(city_name is null) from city; を実行した場合に返る数値は？
この場合、全件がかえります。
mysql root@127.0.0.1:test> select count(city_name is null) from city; +----------------------------+ | count(city_name is null) | |----------------------------| | 1987 | +----------------------------+ 1 row in set
ここでの city_name is null はtrueの意味の1とfalseの意味の0を返してます。
mysql root@127.0.0.1:test> select *,city_name is null from city limit 10; +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ | prefecture_id | prefecture_name | city_id | city_name | city_kana | city_name is null | |-----------------+-------------------+-----------+--------------+------------------------+---------------------| | 1 | 北海道 | 0 | <null> | <null> | 1 | | 1 | 北海道 | 100 | 札幌市 | サッポロシ | 0 | | 1 | 北海道 | 101 | 札幌市中央区 | サッポロシチュウオウク | 0 | | 1 | 北海道 | 102 | 札幌市北区 | サッポロシキタク | 0 | | 1 | 北海道 | 103 | 札幌市東区 | サッポロシヒガシク | 0 | | 1 | 北海道 | 104 | 札幌市白石区 | サッポロシシロイシク | 0 | | 1 | 北海道 | 105 | 札幌市豊平区 | サッポロシトヨヒラク | 0 | | 1 | 北海道 | 106 | 札幌市南区 | サッポロシミナミク | 0 | | 1 | 北海道 | 107 | 札幌市西区 | サッポロシニシク | 0 | | 1 | 北海道 | 108 | 札幌市厚別区 | サッポロシアツベツク | 0 | +-----------------+-------------------+-----------+--------------+------------------------+---------------------+ 10 rows in set
where 条件と同じ結果をSELECT 句のCOUNT内で表現するにはどうすれば良いでしょう？
OR NULLを足します
mysql root@127.0.0.1:test> select count(city_name is null or null) from city; +------------------------------------+ | count(city_name is null or null) | |------------------------------------| | 47 | +------------------------------------+ 1 row in set
ちなみに、MySQLでCOUNT内のカラムにNULLが含まれている場合は、NULLのカラムはカウント対象から外れます。 これはマニュアルにも載ってますし、Oracle等でも同じなので結構おなじみな話だと思います。
mysql root@127.0.0.1:test> select count(city_name) from city; +--------------------+ | count(city_name) | |--------------------| | 1940 | +--------------------+ 1 row in set
OR NULLを入れるとなぜ正しい結果がかえるのか？
- count(city_name is null) -> TRUEもFALSEもかえす
- count(city_name is null or null) -> 「OR NULL」を付けることで前の構文に当てはまればTRUE、それ以外はNULL -> NULLはカウント非対象 -> 正しい結果がかえる
何に心がザワつくのか？
- 「OR NULL」付けたら条件として見るというのがイマイチ納得いかない。（じゃあ、IS NULLは何なの？）
- 「OR NULL」付けたら0をNULLに置き換えてるの？
基本的にはWHERE句でやるべきことをCOUNTとかでやろうとするとこのように書くということで、本当は使わないほうがいいんだけど HAVING句で使いたい場合にはこれが出てくるだろうし、なんか納得する説明が欲しいんだけど誰か教えてチョモランマ。
と言ったら、神様仏様、いや、『兄貴ィ』降臨です！
- const OR NULLは
- constが真の時に短絡評価されて真
- constが偽の時に "偽とNULLの和集合" と評価されて不定(= NULL)
真 IS NULLは偽偽 IS NULLは偽NULL IS NULLは真
おふざけが入りましたが、本当にスッキリしました。 ありがとうございました！
