MySQL 5.6 Developer試験対策 1 MySQL アーキテクチャ
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- MySQL ServerとのインターフェースであるMySQL クライアントプログラムを使用して、対話的な方法およびバッチでアクセスする
- SQLモードと、MySQLの動作に対するSQLモードの影響について説明する
- セッションのスコープ(有効範囲)を識別する
MySQL ServerとのインターフェースであるMySQL クライアントプログラムを使用して、対話的な方法およびバッチでアクセスする
dockerでうごかすまで
docker container run --rm -dit \ --name mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql:5.6 docker container exec -it mysql bash
root@c9b2970c51d2:/# mysql -proot mysql -proot 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 1 Server version: 5.6.48 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>
- ヘルプ
mysql> \! mysql --help \! mysql --help mysql Ver 14.14 Distrib 5.6.48, for Linux (x86_64) using EditLine wrapper 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. Usage: mysql [OPTIONS] [database]
サーバーへの接続とサーバーからの切断
root@c9b2970c51d2:/# mysql -h localhost -u root -p
Enter password: (入力)
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.48 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>
- 【所感】rootで入れるんですね
- postgresqlは入れない
- 【補】
-h localhost
はUnixドメインソケットに解決されることに注意する - 前述のコンテナの立ち上げ方で
mysql
だけ打つとAccess denied
root@c9b2970c51d2:/# mysql mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- 環境変数なしで同期で立ち上げてログを見てみる
docker run --rm mysql:5.6
2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.48-1debian9 started. 2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2020-05-21 15:30:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 5.6.48-1debian9 started. 2020-05-21 15:30:51+00:00 [ERROR] [Entrypoint]: Database is uninitialized and password option is not specified You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD
You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD
MYSQL_ALLOW_EMPTY_PASSWORD=true
を設定して立ち上げてみる
docker run --rm -dit \ --name=mysql \ -e MYSQL_ALLOW_EMPTY_PASSWORD=true \ mysql:5.6 docker container exec -it mysql bash
mysql
だけで入れる
root@f973197c71b6:/# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.48 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>
\q
,QUIT
, UnixならCtrl + D
で出る- 【補】
EXIT
可
- 【補】
クエリーの入力
mysql> SELECT VERSION(), CURRENT_DATE; SELECT VERSION(), CURRENT_DATE; +-----------+--------------+ | VERSION() | CURRENT_DATE | +-----------+--------------+ | 5.6.48 | 2020-05-21 | +-----------+--------------+ 1 row in set (0.00 sec)
- 【補】
CURRENT_DATE
にカッコが付かないのは標準SQL準拠 - 計算機にもなる
mysql> SELECT SIN(PI() / 4), (4 + 1) * 5; SELECT SIN(PI() / 4), (4 + 1) * 5; +--------------------+-------------+ | SIN(PI() / 4) | (4 + 1) * 5 | +--------------------+-------------+ | 0.7071067811865475 | 25 | +--------------------+-------------+ 1 row in set (0.00 sec)
- 1行に複数クエリ
mysql> SELECT VERSION(); SELECT NOW(); SELECT VERSION(); SELECT NOW(); +-----------+ | VERSION() | +-----------+ | 5.6.48 | +-----------+ 1 row in set (0.00 sec) +---------------------+ | NOW() | +---------------------+ | 2020-05-21 15:51:13 | +---------------------+ 1 row in set (0.00 sec)
- 逆に1クエリを複数行にまたがって書くこともできる
mysql> SELECT USER() SELECT USER() -> ; ; +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
- やめたくなったら
\c
clear (\c) Clear the current input statement.
mysql> SELECT SELECT -> USER() USER() -> , , -> \c \c mysql>
- コメント
mysql> SELECT /* hoge */ 1; SELECT /* hoge */ 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
データベースの作成と使用
mysql> SHOW DATABASES; SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
- 【補】
information_schema
は標準SQL
mysql> USE information_schema USE information_schema 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; SHOW TABLES; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | ...
データベースの作成と選択
mysql> CREATE DATABASE managerie; CREATE DATABASE managerie; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | managerie | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
mysql> USE managerie; USE managerie; Database changed
- ログイン時にDB指定もできる
- オプション無しの引数
-proot
のように書かないといけない理由
root@f973197c71b6:/# mysql managerie
mysql> SELECT database(); SELECT database(); +------------+ | database() | +------------+ | managerie | +------------+ 1 row in set (0.00 sec)
mysql> SHOW TABLES; SHOW TABLES; Empty set (0.00 sec) mysql> CREATE TABLE tbl (col int); CREATE TABLE tbl (col int); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tbl (col) VALUES (1); INSERT INTO tbl (col) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> TABLE tbl; TABLE tbl; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE tbl' at line 1 mysql> SELECT * FROM tbl; SELECT * FROM tbl; +------+ | col | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
- 【補】TABLE文はない
- 【補】
generate_series
もない- パフォーマンスチューニングの学習とか面倒そう…
テーブルへのデータのロード
LOAD DATA
ステートメントを使う- 【補】PostgreSQLの
COPY FROM
,\copy from
にあたる
- 【補】PostgreSQLの
mysql> \h LOAD DATA
Name: 'LOAD DATA' Description: Syntax: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name [, partition_name] ...)] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var [, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ...] The LOAD DATA statement reads rows from a text file into a table at a very high speed. LOAD DATA is the complement of SELECT ... INTO OUTFILE. (See https://dev.mysql.com/doc/refman/5.6/en/select-into.html.) To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA. The syntax of the FIELDS and LINES clauses is the same for both statements. You can also load data files by using the mysqlimport utility; see https://dev.mysql.com/doc/refman/5.6/en/mysqlimport.html. mysqlimport operates by sending a LOAD DATA statement to the server. For more information about the efficiency of INSERT versus LOAD DATA and speeding up LOAD DATA, see https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html. URL: https://dev.mysql.com/doc/refman/5.6/en/load-data.html
- 【補】
COPY TO
にあたるものはSELECT ... INTO OUTFILE
- 【補】
LOCAL
: クライアント側のファイルをロードすることを意味する- つけないとサーバ側
- やってみる
- 準備
mysql> DROP TABLE tbl; DROP TABLE tbl; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE tbl (id int, name varchar(20)); CREATE TABLE tbl (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM tbl; SELECT * FROM tbl; Empty set (0.00 sec)
- 投入データ作る
- タブ区切り
- NULLは
\N
mysql> \! echo '1\thoge' > data.txt mysql> \! echo '2\tpiyo' >> data.txt mysql> \! echo '3\t\\N' >> data.txt mysql> \! cat /data.txt 1 hoge 2 piyo 3 \N
LOAD DATA
ステートメントを実行するもエラー
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl; LOAD DATA INFILE 'data.txt' INTO TABLE tbl; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
--secure-file-priv
オプションがついてるから駄目らしい- スコープ: グローバル
- 動的: no
- my.cnfで設定を行う
- どこ?
mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
- 本docker環境では
/etc/mysql/my.cnf
にあった
/docker:mysql:/etc/mysql $ ls
conf.d my.cnf my.cnf.fallback mysql.cnf mysql.conf.d
/etc/mysql/my.cnf
# * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/
- どこかの
[mysqld]
設定に追記
[mysqld] secure-file-priv = ""
- 書き換えるだけでは駄目
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl; LOAD DATA INFILE 'data.txt' INTO TABLE tbl; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- SIGHUPしてみる
kill -s SIGHUP 1
- 駄目
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl; LOAD DATA INFILE 'data.txt' INTO TABLE tbl; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- restartが必要
docker container restart mysql
- あらためて
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl; LOAD DATA INFILE 'data.txt' INTO TABLE tbl; ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/managerie/data.txt' (Errcode: 2 - No such file or directory)
--secure-file-priv
のエラーは解消した- 「そんなファイルねえぞ」と言われる
- 絶対パスで指定し直す
mysql> LOAD DATA INFILE '/data.txt' INTO TABLE tbl; LOAD DATA INFILE '/data.txt' INTO TABLE tbl; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
- OK
mysql> SELECT * FROM tbl; SELECT * FROM tbl; +------+------+ | id | name | +------+------+ | 1 | hoge | | 2 | piyo | | 3 | NULL | +------+------+ 3 rows in set (0.00 sec)
ならべかえ
- 【補】
ORDER BY
にNULLS {FIRST|LAST}
とかはない
日付の計算
- 2つのtimepointの差
mysql> SELECT CURDATE(), SELECT CURDATE(), -> TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()), TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()), -> TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()), TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()), -> TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE()); TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE()); +------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+ | CURDATE() | TIMESTAMPDIFF(YEAR, '2020-04-04', CURDATE()) | TIMESTAMPDIFF(MONTH, '2020-04-04', CURDATE()) | TIMESTAMPDIFF(DAY, '2020-04-04', CURDATE()) | +------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+ | 2020-05-21 | 0 | 1 | 47 | +------------+----------------------------------------------+-----------------------------------------------+---------------------------------------------+ 1 row in set (0.00 sec)
- timepoint + interval
mysql> SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH); SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 MONTH); +------------+---------------------------------------+ | CURDATE() | DATE_ADD(CURDATE(), INTERVAL 1 MONTH) | +------------+---------------------------------------+ | 2020-05-21 | 2020-06-21 | +------------+---------------------------------------+ 1 row in set (0.00 sec)
- 抽出
mysql> SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE()); SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE()); +-----------------+------------------+----------------+ | YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | +-----------------+------------------+----------------+ | 2020 | 5 | 21 | +-----------------+------------------+----------------+ 1 row in set (0.00 sec)
データベースとテーブルに関する情報の取得
mysql> SELECT VERSION(); SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.6.48 | +-----------+ 1 row in set (0.00 sec)
mysql> DESCRIBE tbl; DESCRIBE tbl; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
- cf. CREATE TABLE文の取得
mysql> SHOW CREATE TABLE tbl; SHOW CREATE TABLE tbl; +-------+----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------+ | tbl | CREATE TABLE `tbl` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
- index見る
- まだ貼ってない
mysql> SHOW INDEX FROM tbl; SHOW INDEX FROM tbl; Empty set (0.00 sec)
バッチモードでのMySQLの使用
cat sample.sql
SELECT 1;
mysql < sample.sql
- 出力は簡潔
1 1
- cf. インタラクティブモード
+---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
- バッチモードでもインタラクティブモードと同じ表示形式(table format)を用いるには、
-t, --table
オプションをつける
-t, --table Output in table format.
mysql -t < sample.sql
+---+ | 1 | +---+ | 1 | +---+
- 実行したコマンドをstdoutに出力するには
-vvv
mysql -t -vvv < sample.sql
-------------- SELECT 1 -------------- +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Bye
SQLモードと、MySQLの動作に対するSQLモードの影響について説明する
MySQL Server は異なる SQL モードで動作でき、
sql_mode システム変数の値に応じて異なるクライアントにこれらの異なるモードを適用できます。 DBA はサイトサーバーの動作要件に一致するグローバル SQL モードを設定でき、
各アプリケーションはアプリケーションのセッション SQL モードをアプリケーション独自の要件に設定できます。
- デフォルト値について
MySQL 5.6.6 以降でのデフォルトの SQL モードは
NO_ENGINE_SUBSTITUTION
で、 MySQL 5.6.5 以前では、これは空白です (モードの設定なし)。
- 確認
mysql> SELECT VERSION(); SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.6.48 | +-----------+ 1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode; SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode; +------------------------+------------------------+ | @@GLOBAL.sql_mode | @@SESSION.sql_mode | +------------------------+------------------------+ | NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION | +------------------------+------------------------+ 1 row in set (0.00 sec)
- 注意
- パーティショニングしたテーブルの作成・データ投入後にSQLモードを変えないほうが良い
- レプリケーション時はマスターとスレーブで揃える
- 【補】8.0のドキュメントでも「slave」と言っている
- PostgreSQLは10を境にsecondary, standbyなどと呼ぶようになった
- 【補】8.0のドキュメントでも「slave」と言っている
- どんなのがあるの
- 他RDBMSに寄り添う組み合わせのプリセットが用意されていたりする
- ANSI
- MSSQL
- ORACLE
- POSTGRESQL
- ...
セッションのスコープ(有効範囲)を識別する
- オプションファイル(my.cnf)でサーバー起動時に設定できる
- サーバーの実行中にSETステートメントで動的に変更できる
mysql> SELECT @@GLOBAL.sort_buffer_size; SELECT @@GLOBAL.sort_buffer_size; +---------------------------+ | @@GLOBAL.sort_buffer_size | +---------------------------+ | 262144 | +---------------------------+ 1 row in set (0.00 sec)
- 見るからにパフォーマンスチューニングに使いそうなサーバーシステム変数
- globalで2倍にしてみる
mysql> SELECT 262144 * 2; SELECT 262144 * 2; +------------+ | 262144 * 2 | +------------+ | 524288 | +------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL sort_buffer_size=524288; SET GLOBAL sort_buffer_size=524288; Query OK, 0 rows affected (0.00 sec)
- 確認
mysql> SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size; SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size; +---------------------------+----------------------------+ | @@GLOBAL.sort_buffer_size | @@SESSION.sort_buffer_size | +---------------------------+----------------------------+ | 524288 | 262144 | +---------------------------+----------------------------+ 1 row in set (0.00 sec)
- セッションの値は変わらない
- グローバルの値は変わった
- ログアウトして再度ログイン
mysql> SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size; SELECT @@GLOBAL.sort_buffer_size, @@SESSION.sort_buffer_size; +---------------------------+----------------------------+ | @@GLOBAL.sort_buffer_size | @@SESSION.sort_buffer_size | +---------------------------+----------------------------+ | 524288 | 524288 | +---------------------------+----------------------------+ 1 row in set (0.00 sec)
- 新しいセッションではグローバルの値が読まれる
- 【所感】SIGHUPやreloadによるサーバ無停止オプションファイル再読込がない代わりに、PostgreSQLでいうところの
ALTER SYSTEM SET
がある感じ - グローバルスコープのみのサーバーシステム変数を
@@SESSION
で読もうとするとエラー
mysql> SELECT @@SESSION.secure_file_priv; SELECT @@SESSION.secure_file_priv; ERROR 1238 (HY000): Variable 'secure_file_priv' is a GLOBAL variable
- 動的でないものを書き換えようとするとエラー
mysql> SET GLOBAL secure_file_priv = 'true'; SET GLOBAL secure_file_priv = 'true'; ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable