MySQL 5.6 Developer試験対策 1 MySQL アーキテクチャ

MySQLRDBSQL勉強メモ

MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]


MySQL ServerとのインターフェースであるMySQL クライアントプログラムを使用して、対話的な方法およびバッチでアクセスする

[https://dev.mysql.com/doc/refman/5.6/ja/tutorial.html:embed:cite]

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 にあたる
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のエラーは解消した
  • 「そんなファイルねえぞ」と言われる

    • カレントディレクトリ = /にdata.txtを作った
    • LOAD DATA INFILEで相対パスを指定すると、データベースディレクトリ /var/lib/mysql/managerie/ からの相対パスになる
  • 絶対パスで指定し直す
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 BYNULLS {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の使用

  • mysqlコマンドにstdinでSQLファイルを流し込める
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モードの影響について説明する

[https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html:embed:cite]

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などと呼ぶようになった
  • どんなのがあるの

    • NO_ZERO_DATE
    • 0000-00-00を拒否するやつ
    • ONLY_FULL_GROUP_BY
    • SQL AntipatternsのAmbiguous Groupsなクエリを拒否するやつ
    • 他、識別子のクォートや文字列concat演算子なども変わる
  • 他RDBMSに寄り添う組み合わせのプリセットが用意されていたりする

    • ANSI
    • MSSQL
    • ORACLE
    • POSTGRESQL

セッションのスコープ(有効範囲)を識別する

[https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html:embed:cite]

  • オプションファイル(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