MySQL8.0.4 RCがリリースされたので、その中でJSON関連の関数JSON_TABLEがリリースされていたので、合わせて挙動を確認してみました。
MySQLにはJSON関数が30弱ありますが、便利なJSON関数が増えてきているので、これまで以上にNOSQL関連データを扱う処理の選択肢として活用出来そうです。

MySQL 8.0.4 Release Candidateの変更点
参照:https://mysqlserverteam.com/the-mysql-8-0-4-release-candidate-is-available/

多くの変更があるので、上記ブログを確認頂いた方が良さそうです。
一部抜粋:
Security:
caching_sha2_password default authentication mechanism
Dynamic Linking of OpenSSL in MySQL Server(yaSSL->OpenSSL)
JSON Functions:
JSON Table Functions
RLIKE/REGEXP:
ICU library to handle RLIKE/REGEXP
SQL DIGEST:
STATEMENT_DIGEST() and STATEMENT_DIGEST_TEXT()
Tablespaces:
ibd and system tablespace files can be moved from one location to another while the server is offline.

and so on ….. Pleases check mysqlserverteam blog.

JSON_TABLEの検証

JSON_TABLE詳細:
WL#8867: Add JSON table functions
https://dev.mysql.com/worklog/task/?id=8867

MySQL8.0.4をDockerにて起動

docker run --name mysql84 -v /docker/docker84:/var/lib/mysql -v /docker/option84:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0



[root@DockerHost docker]# docker run --name mysql84 -v /docker/docker84:/var/lib/mysql -v /docker/option84:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
f314f67d547db50b89be395555fccf2c54dc1d390932a9666c7bfb40da25de1d
[root@DockerHost docker]# 

JSONデータを作成して、JSON_TABLE関数を利用してデータを抽出。
SONデータをそのまま、通常のリレーショナルなテーブルから列を抽出するように扱う事が出来る。JSONデータにWHERE句を利用した抽出も可能。おそらく、JSONデータと生成列を活用してINDEXを利用して参照した方が早いとは思いますが、使いどころは色々とあるかと思います。

[root@DockerHost docker]# docker exec -it mysql84 mysql --default-character-set=utf8mb4 -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.13 sec)

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.01 sec)

mysql> 



mysql> CREATE TABLE `T_JSON` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `body` json DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":1,"name":"washing machine","price":10000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":2,"name":"TV","price":30000,"Conditions":["USED",2013]}');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":3,"name":"refrigerator","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.02 sec)


mysql> select * from T_JSON;
+----+-----------------------------------------------------------------------------------+
| id | body                                                                              |
+----+-----------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "washing machine", "price": 10000, "Conditions": ["NEW", 2015]} |
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]}             |
|  3 | {"id": 3, "name": "refrigerator", "price": 50000, "Conditions": ["NEW", 2015]}    |
+----+-----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 


mysql> select * from T_JSON, JSON_TABLE(body,"$" COLUMNS
    -> ( 
    ->  product_name varchar(20) PATH "$.name",
    ->  product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+----+-----------------------------------------------------------------------+--------------+---------------+
| id | body                                                                  | product_name | product_price |
+----+-----------------------------------------------------------------------+--------------+---------------+
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]} | TV           | 30000         |
+----+-----------------------------------------------------------------------+--------------+---------------+
1 row in set (0.00 sec)

mysql> 


mysql> select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+--------------+---------------+
| product_name | product_price |
+--------------+---------------+
| TV           | 30000         |
+--------------+---------------+
1 row in set (0.00 sec)


mysql> select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> id for ordinality,
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+------+--------------+---------------+
| id   | product_name | product_price |
+------+--------------+---------------+
|    1 | TV           | 30000         |
+------+--------------+---------------+
1 row in set (0.01 sec)

mysql> 

mysql> explain select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                       |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | T_JSON   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    2 |   100.00 | NULL                                        |
|  1 | SIMPLE      | products | NULL       | ref  | <auto_key0>   | <auto_key0> | 83      | const |    1 |   100.00 | Table function: json_table; Using temporary |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>  

WORKLOG抜粋:
It’s used to extract data from a JSON document and form a relational table, that could be processed further using SQL. It’s a virtual table,
in this sense it’s like a derived table, with only difference in how result data is obtained. Just like a derived table, it’s specified in the FROM
clause, uses tmp table to store its result, and is able to provide generated indexes for ref access.

JSON_TABLEマニュアル
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Please check additional details for MySQL8.0
http://mysqlserverteam.com/


5.6.1で既に実装されていてDefaultでONになっているので,5.6や5.7では普段殆ど気にしてませんでしたが、質問頂いたのでindex_condition_pushdownの条件を再確認。
DefaultはONになっています。あえて、OFFにするメリットはあまり無いかと思います。

Index Condition Pushdown(ICP): ストレージエンジンからフェッチしたレコードをMySQLが評価してWHERE区の条件による絞り込みを行っていたが、
インデックスが貼られたカラムを用いた評価については、ストレージエンジンへ条件式を渡し(プッシュダウン)、ストレージエンジン側で評価を行わせることによってオーバーヘッドの低減させる。

ICPの目標は、完全なレコードの読み取りの回数を減らし、それによって I/O 操作を減らすことです。InnoDB のクラスタ化されたインデックスの場合、完全なレコードはすでに InnoDB バッファーに読み込まれています。この場合に ICP を使用しても IO は削減されません。 その為、ICP はセカンダリインデックスにのみ使用されます。

インデックス条件をプッシュダウンしようとするのは以下の条件の場合:
0. テーブルには選択条件がある。
1. ストレージエンジンがICPをサポートする。
2. index_condition_pushdownスイッチがオンで、ICPの使用がNO_ICPヒントによって無効にされていない。
3. クエリーは、複数表の更新または削除ステートメントでは無い。この要件の理由は、選択/結合と更新の両方に同じハンドラが使用されるためです。プッシュされたインデックス条件は、更新部分を実行するときにストレージエンジンによって適用され、誤ったレコードを更新または更新するためのレコードを見つけられない結果になることがあります。
4. JOIN_TABは、”NULLキーをフル・スキャン”実行中にオンまたはオフにできる条件を保護しているサブクエリの一部ではありません。

       @see Item_in_optimizer :: val_int()
       @see subselect_single_select_engine :: exec()
       @see TABLE_REF :: cond_guards
       @see setup_join_buffering

5.結合タイプはCONSTまたはSYSTEMではありません。これらの結合タイプを除外する理由は、これらの結合タイプが、ストレージエンジンから一度だけレコードを読み取り、後でそれを再利用するように最適化されているためです。プッシュされたインデックス条件が結合シーケンスの前のテーブルからのフィールドを評価する結合では、プッシュされた条件は、レコード値が最初に必要とされたときにのみ評価されます。
6.インデックスはクラスタ化インデックスではありません。クラスタ化されたキーでインデックス条件をプッシュすると、クラスタ化されていないキーよりも大幅に低くなります。WL#6061が実装されている場合、この制限は再評価する必要があります。
7.仮想生成列の索引は、ICPではサポートされていません。

Ref Source:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_select.cc#L1723

index_condition_pushdown=offを意図的にOFFにした場合

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 605   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

root@localhost [world]> 

Defaultの挙動 (index_condition_pushdown=on)

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 440   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

参照:
http://nippondanji.blogspot.jp/2011/04/mysql-56.html
https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/


MySQL Enterprise Edition on official docker image

商用版のライセンスを利用者が別途準備する事で(Bring Your Own License)商用版のMySQLをDockerで利用する事が可能です。
https://blogs.oracle.com/mysql/mysql-enterprise-edition-now-in-docker-store

MySQLライセンスについては此方を参照下さい。
https://www.mysql.com/jp/products/
物理サーバーライセンスなので、Dockerを複数立ち上げるような開発環境ではコストメリットが大きいかと思います。

STEP1: Dockerストアにアクセス
https://store.docker.com
https://store.docker.com/images/mysql-enterprise-server 

STEP2:Checkoutに進むと以下のように入力が画面が出てくるので、必要な入力項目を入れて下さい。

STEP3: Checkoutするとインストール方法とイメージのPULL用URLが表示されます。

STEP4: 利用可能パッケージの確認
自分のアカウントで利用可能な、パッケージが以下のURLにアクセスする事が出来ます。

https://store.docker.com/profiles/{DockerID}/content 

STEP5: ここからは実際にイメージをダウンロードして初期設定後に環境の確認をしてみます。

[root@DockerHost oracle]# docker login
Username: myaccount
Password: 
Email: my.private@variable.jp
WARNING: login credentials saved in /root/.docker/config.json
Login Succeeded
[root@DockerHost oracle]# docker pull store/oracle/mysql-enterprise-server:5.7
5.7: Pulling from store/oracle/mysql-enterprise-server
0a8af4fbe73a: Pull complete 
f8726cc27fe2: Pull complete 
fae37bbdd736: Pull complete 
39a334372a33: Pull complete 
f9c82196334c: Pull complete 
ef578d7130a0: Pull complete 
847fdfc5f5d7: Pull complete 
8e21b593de13: Pull complete 
5b0a6ab7d9e4: Pull complete 
99091fe3cf31: Pull complete 
483de1c4dc82: Pull complete 
f7d4675ff63d: Pull complete 
c41819234bbd: Pull complete 
Digest: sha256:09a6201fe690055c450f7e17a94efc4f10ae38b2607242abae191e3b283698f5
Status: Downloaded newer image for store/oracle/mysql-enterprise-server:5.7
[root@DockerHost oracle]# 

あとで、設定を適宜変更したいのでデータやオプションファイルのパスを指定してインスタンスの初期化を行っています。

[root@DockerHost oracle]# docker images
REPOSITORY                             TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
store/oracle/mysql-enterprise-server   5.7                 c41819234bbd        13 days ago         246.9 MB
mysql/mysql-server                     8.0                 270395aafb1e        3 months ago        295.3 MB
mysql                                  5.7.15              3dd6dfe65426        15 months ago       383.4 MB
mysql/mysql-server                     5.7.15              de24da03ab76        15 months ago       369.1 MB
[root@DockerHost oracle]# 

[root@DockerHost docker57ee]# docker run --name=mysql57ee -v /docker/option57ee/my.cnf:/etc/my.cnf -v /docker/docker57ee:/var/lib/mysql -e MYSQL_RANDOM_ROOT_PASSWORD=true -e MYSQL_ONETIME_PASSWORD=true -d store/oracle/mysql-enterprise-server:5.7
9ea25f5c1df4a6045197d3adac2e123faa404b538919775cc6269e0d0556a921
[root@DockerHost docker57ee]# docker logs mysql57ee
[Entrypoint] MySQL Docker Image 5.7.20-1.1.2
<SNIP>
[Entrypoint] GENERATED ROOT PASSWORD: hUmv@v+EgFopmUcZIr-yqegLOn

[root@DockerHost docker57ee]# docker ps -a
CONTAINER ID        IMAGE                                      COMMAND                  CREATED             STATUS                      PORTS                 NAMES
38347f04b6b7        store/oracle/mysql-enterprise-server:5.7   "/entrypoint.sh mysql"   4 minutes ago       Up 4 minutes                3306/tcp, 33060/tcp   mysql57ee
ecd2156cdd36        mysql/mysql-server:8.0                     "/entrypoint.sh mysql"   12 weeks ago        Exited (0) 19 minutes ago                         mysql83
d92d218ffff6        mysql/mysql-server:5.7.15                  "/entrypoint.sh mysql"   15 months ago       Exited (0) 8 months ago                           multi_docker05
7e9de9a905ad        mysql/mysql-server:5.7.15                  "/entrypoint.sh mysql"   15 months ago       Exited (0) 15 months ago                          multi_docker04
fb1c8aaf8de7        mysql/mysql-server:5.7.15                  "/entrypoint.sh mysql"   15 months ago       Exited (0) 14 months ago                          multi_docker03
[root@DockerHost docker57ee]# 

ログインして、初期パスワードを変更後にバージョンとプラグインを確認。
現状ではプラグインを設定してないのでロードはされてませんが、モジュールが存在するところまで確認してあります。


[root@DockerHost docker57ee]# docker exec -it mysql57ee mysql --default-character-set=utf8mb4 -uroot -phUmv@v+EgFopmUcZIr-yqegLOn
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 4
Server version: 5.7.20-enterprise-commercial-advanced-log

Copyright (c) 2000, 2017, 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.

root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| healthchecker | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

root@localhost [(none)]> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'plugin%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)

root@localhost [(none)]> system ls -l /usr/lib64/mysql/plugin/
total 4484
-rwxr-xr-x 1 root root   22336 Sep 13 17:12 adt_null.so
-rwxr-xr-x 1 root root  299944 Sep 13 17:12 audit_log.so
-rwxr-xr-x 1 root root    7392 Sep 13 17:12 auth_socket.so
-rwxr-xr-x 1 root root   40712 Sep 13 17:12 authentication_ldap_sasl_client.so
-rwxr-xr-x 1 root root   19928 Sep 13 17:12 authentication_pam.so
-rwxr-xr-x 1 root root   50216 Sep 13 17:12 connection_control.so
-rwxr-xr-x 1 root root  100064 Sep 13 17:12 firewall.so
-rwxr-xr-x 1 root root 1240104 Sep 13 17:12 group_replication.so
-rwxr-xr-x 1 root root  116624 Sep 13 17:12 innodb_engine.so
-rwxr-xr-x 1 root root   86792 Sep 13 17:12 keyring_file.so
-rwxr-xr-x 1 root root  287256 Sep 13 17:12 keyring_okv.so
-rwxr-xr-x 1 root root   19968 Sep 13 17:12 keyring_udf.so
-rwxr-xr-x 1 root root  192696 Sep 13 17:12 libmemcached.so
-rwxr-xr-x 1 root root   10920 Sep 13 17:12 locking_service.so
-rwxr-xr-x 1 root root   11928 Sep 13 17:12 mypluglib.so
-rwxr-xr-x 1 root root    7352 Sep 13 17:12 mysql_no_login.so
-rwxr-xr-x 1 root root 1714912 Sep 13 17:12 mysqlx.so
-rwxr-xr-x 1 root root   49368 Sep 13 17:12 openssl_udf.so
-rwxr-xr-x 1 root root    7480 Sep 13 17:12 rewrite_example.so
-rwxr-xr-x 1 root root   53992 Sep 13 17:12 rewriter.so
-rwxr-xr-x 1 root root   63312 Sep 13 17:12 semisync_master.so
-rwxr-xr-x 1 root root   16112 Sep 13 17:12 semisync_slave.so
-rwxr-xr-x 1 root root   61312 Sep 13 17:12 thread_pool.so
-rwxr-xr-x 1 root root   29352 Sep 13 17:12 validate_password.so
-rwxr-xr-x 1 root root   33016 Sep 13 17:12 version_token.so
root@localhost [(none)]> 

本日の説明はここまで。Docker環境でもMySQLのEnterprise Editionの機能を利用して開発や検証したい方にはお勧め。

Please Enjoy it.


MySQL8.0のヒント句が便利になっていたので基本動作確認。
バッチ処理前にSETコマンドで動的に設定していたセッション変数を実行するクエリーにヒントとして追加する事が出来るようです。

WL#681: Hint to temporarily set session variable for current statement
https://dev.mysql.com/worklog/task/?id=681

Syntax of the SET_VAR hint is:


 /*+ SET_VAR( = ) */

大量のデータをOrder by, Group by等でソート処理していて、Sort_merge_passesが多発している場合にソートバッファーの不足をヒント句で回避してディスクI/Oの発生を抑える事が出来る。
大量データのソートが多いバッチ処理などに組み込んでおくと、処理が早く終わらせる事が出来る。
(例)


mysql> SELECT /*+ SET_VAR(sort_buffer_size = 4M) */ name FROM city ORDER BY name;


実際にデータを準備してsort_buffer_sizeヒント句を検証した結果
Duration: 12.71 sec → 6.45 sec
sort_merge_passes: 216 → 0


mysql> select count(*) from ( select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (12.71 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM ( SELE ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 12.71 s
      max_latency: 12.71 s
      avg_latency: 12.71 s
     lock_latency: 1.47 ms
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 216
           digest: 1e941ce49e8f810963a8468995dc2eaf
       first_seen: 2017-09-28 20:29:46.768069
        last_seen: 2017-09-28 20:29:46.768069
1 row in set (0.01 sec)

mysql> 


mysql> CALL sys.ps_truncate_all_tables(FALSE);
+---------------------+
| summary             |
+---------------------+
| Truncated 49 tables |
+---------------------+
1 row in set (0.17 sec)

Query OK, 0 rows affected (0.18 sec)

mysql> select  /*+ SET_VAR(sort_buffer_size = 16M) */ count(*) from (select v.name, count(*) from t_group g join tbnode n on g.groupid = n.groupid join t_virual v on n.nodeid = v.nodeid  group by v.name with rollup) t;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (6.45 sec)

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT /*+ SET_VAR ( `sort_buf ... v` . `name` WITH ROLLUP ) `t` 
               db: PERF
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 9.94 s
      max_latency: 9.94 s
      avg_latency: 9.94 s
     lock_latency: 406.00 us
        rows_sent: 1
    rows_sent_avg: 1
    rows_examined: 3102001
rows_examined_avg: 3102001
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 2
  tmp_disk_tables: 0
      rows_sorted: 1000000
sort_merge_passes: 0
           digest: bdf5d04f4574930fef467d6c359c49bf
       first_seen: 2017-09-28 20:37:42.923895
        last_seen: 2017-09-28 20:37:42.923895
1 row in set (0.01 sec)

使いどころが難しいが、以下のようにauto_increment_increment等の値もヒント句で変更出来るようです。


mysql> CREATE TABLE `T_SET_VAR` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `memo` varchar(100) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test');
Query OK, 1 row affected (0.08 sec)

mysql> insert into T_SET_VAR(memo) values('auto_increment_increment test2');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                                  
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from T_SET_VAR;                                             
+----+--------------------------------+
| id | memo                           |
+----+--------------------------------+
|  1 | auto_increment_increment test  |
|  2 | auto_increment_increment test2 |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql> insert /*+ SET_VAR(auto_increment_increment = 10) */ into T_SET_VAR(memo) values('auto_increment_increment test3?');
Query OK, 1 row affected (0.03 sec)

mysql> select * from T_SET_VAR;                                                                                            
+----+---------------------------------+
| id | memo                            |
+----+---------------------------------+
|  1 | auto_increment_increment test   |
|  2 | auto_increment_increment test2  |
| 11 | auto_increment_increment test3? |
+----+---------------------------------+
3 rows in set (0.00 sec)

その他、Worklogによると以下のように、色々なセッション変数をSETコマンドを利用しないでヒント句で指定出来るようです。

List of settable variables:
———————————————————————————-
auto_increment_increment
auto_increment_offset
big_tables
bulk_insert_buffer_size
default_tmp_storage_engine
div_precision_increment
end_markers_in_json
eq_range_index_dive_limit
foreign_key_checks
group_concat_max_len
insert_id
internal_tmp_mem_storage_engine
join_buffer_size
lock_wait_timeout
max_error_count
max_execution_time
max_heap_table_size
max_join_size
max_length_for_sort_data
max_points_in_geometry
max_seeks_for_key
max_sort_length
optimizer_prune_level
optimizer_search_depth variables
optimizer_switch
range_alloc_block_size
range_optimizer_max_mem_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
sql_auto_is_null
sql_big_selects
sql_buffer_result
sql_mode
sql_safe_updates
sql_select_limit
timestamp
tmp_table_size
updatable_views_with_limit
unique_checks
windowing_use_high_precision
———————————————————————————-

参照: The MySQL 8.0.3 Release Candidate is available


MySQL8.0 RCのDockerイメージがリリースされていたので、今後の検証やデモ用に設定しました。
手軽に検証出来るので、軽く検証するにはお勧めです。

Docker Image: https://github.com/mysql/mysql-docker


[root@DockerHost oracle]# docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
323fb8f65502: Pull complete 
b2a15600aac3: Pull complete 
a1116f4203e9: Pull complete 
8be6f234356c: Pull complete 
a09590e34bdc: Pull complete 
554cdb588e9e: Pull complete 
851fce189663: Pull complete 
ca60670c6cb3: Pull complete 
98a8195f4fc5: Pull complete 
ec8c0ade6c51: Pull complete 
73919c529833: Pull complete 
285b77036a3a: Pull complete 
270395aafb1e: Pull complete 
Digest: sha256:183772d6f5a1decd1eb0252e542d338a5ef8c02fe4cc2cc909b58788f8728c58
Status: Downloaded newer image for mysql/mysql-server:8.0
[root@DockerHost oracle]#

[root@DockerHost oracle]# docker run --name mysql83 -v /docker/docker83:/var/lib/mysql -v /docker/option83:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
ecd2156cdd36d735b5d01f6d7b89ea24cc7d499cbc59e1014bc42ba92c764365
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker exec -it mysql83 mysql --default-character-set=utf8mb4 -uroot -pmysql
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 7
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.3-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)


メモ:CTEの確認

mysql> WITH RECURSIVE  
    -> emp_ext (id, name, path) AS ( 
    ->    SELECT id, name, CAST(id AS CHAR(200)) 
    ->    FROM employees WHERE manager_id IS NULL 
    ->  UNION ALL 
    ->    SELECT s.id, s.name,CONCAT(m.path, ",", s.id) 
    ->    FROM emp_ext m JOIN  employees s ON m.id=s.manager_id )
    -> SELECT * FROM emp_ext ORDER BY path; 
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

mysql> 

メモ:Windows Functionの確認


mysql> select employee,date,sale,SUM(sale)
    -> OVER (PARTITION BY employee) AS sum FROM sales;
+----------+------------+------+------+
| employee | date       | sale | sum  |
+----------+------------+------+------+
| A        | 2017-03-01 |  200 |  900 |
| A        | 2017-04-01 |  300 |  900 |
| A        | 2017-05-01 |  400 |  900 |
| B        | 2017-03-01 |  400 | 1200 |
| B        | 2017-04-01 |  300 | 1200 |
| B        | 2017-05-01 |  500 | 1200 |
| C        | 2017-03-01 |  100 | 1000 |
| C        | 2017-04-01 |  600 | 1000 |
| C        | 2017-05-01 |  300 | 1000 |
+----------+------------+------+------+
9 rows in set (0.00 sec)

mysql> 

MySQL8.0には管理者と開発者にとって使い易い機能や関数も増えているので、
色々な場面で活用する事が出来るかと思います。

詳細情報:
http://mysqlserverteam.com/

バグ報告:
https://bugs.mysql.com/

ブログにはRC1と書いてあったけど、RC2とかもリリース予定なのかな?
Please enjoy it.


MySQL8.0がリリース候補版になりました。

Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)
リリースノート:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html

【主な変更点】
Histograms, Force Index, Hints, Invisible Indexes
Common Table Expressions, Windows Functions, Character Sets,
JSON, GIS, Resource Groups, Performance Schema, Security,
Protocol, Service Infrastructure, X Protocol / X Plugin,
Performance, Tablespaces, DDL, Replication, Group Replication,
Data Dictionary, MTR Tests, Library Upgrade, Changes to Defaults

詳細:http://mysqlserverteam.com/the-mysql-8-0-3-release-candidate-is-available/

全てをカバーしてませんが、先日のセミナー資料で概要を紹介しています。


DB Tech Showcase 2017にてMySQL InnoDB Clusterの概要説明とデモをさせて頂きました。
プレゼン資料をスライドシェアにアップロードしたので、2017年4月にリリースされたMySQL標準の高可用性構成に興味あるある方、若しくは高可用性構成を検討の方はご覧ください。

余談ですが、WP Social Bookmarking Lightプラグインをアップグレードしたら古いPHPで動かなかったので、PHP5.6のバージョンにアップグレードしました。もし、WordPressのアップグレードのタイミングでwp-social-bookmarking-light.phpがエラーになったら、PHPを5.5以上にアップグレードしてみて下さい。


MySQL Group Replicationは、グループで一つのIDを持つ為、通常のシングルインスタンスと同じようにレプリケーションを組む事が出来ます。4月のInnoDB Clusterリリース以降、MySQLを利用されているお客様から、幾つか質問を受けていたので念の為に挙動を再確認。

環境
複数サーバーを準備出来なかったので,シングサーバーにポートを変更して、2グループ(6サーバー)で先ずはGROUP REPLICATIONを準備。

mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

グループレプリケーション間のレプリケーションの設定・開始
マスター側のGroup Replicationにスレーブ接続用のアカウントを作成し、スレーブにデータをコピーしたので、レプリケーションをスレーブ側のグループレプリケーションで開始。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=3310,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected (3.11 sec)

MySQL Enterprise Monitorで確認

マスター側のGROUP REPLICATIONの状態

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 199e19cb-5326-11e7-947d-080027d65c57 | replications |        3330 | ONLINE       |
| group_replication_applier | d9e318f0-5325-11e7-8762-080027d65c57 | replications |        3310 | ONLINE       |
| group_replication_applier | fab196ae-5325-11e7-8edb-080027d65c57 | replications |        3320 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> 

スレーブ側のGROUP REPLICATIONの状態


mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | bc653b5a-3b8b-11e7-94cd-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | c68819f0-3b8b-11e7-958b-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | d0a3d2c8-3b8b-11e7-97ef-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'remote-dc01'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: replications-bin.000003
          Read_Master_Log_Pos: 15217
               Relay_Log_File: replications-relay-bin-remote@002ddc01.000003
                Relay_Log_Pos: 676
        Relay_Master_Log_File: replications-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 15217
              Relay_Log_Space: 16007
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1097457189
                  Master_UUID: d9e318f0-5325-11e7-8762-080027d65c57
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
            Executed_Gtid_Set: 00000000-1111-2222-3333-123456789abc:1-6,
33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: remote-dc01
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> 

マスター側でテーブルとデータを作成してレプリケーションの確認


mysql> CREATE TABLE `T_GR` (
    ->   `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `string1` char(1) DEFAULT NULL,
    ->   `string2` char(1) DEFAULT NULL,
    ->   `string1_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
    ->   `string2_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
    ->   `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
    ->   PRIMARY KEY (`pid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into T_GR(string1,string2) values('A','a');
Query OK, 1 row affected (0.19 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.00 sec)

mysql> 

スレーブ側でデータの確認

mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;
+-----------------------+
| Tables_in_GR_GR_REPLI |
+-----------------------+
| T_GR                  |
+-----------------------+
1 row in set (0.01 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.30 sec)

mysql> 


スレーブ側の全てのグループメンバーでデータがレプリケートされている事を確認

-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql1/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql2/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql3/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ 

グループレプリケーション稼働中はRESET MASTER出来なかったり、パラメータ周りやレプリケーションの設定でいくつか考慮が必要なので、慣れるまでは十分に手順の確認と検証して下さい。
若しくは、ミッションクリティカルな環境では、設計の段階でサポートを受けると安心かと思います。 
サポート: https://www.mysql.com/jp/support/

参考までに、Defaultでシングルマスターモードでもauto_incrementの値が7になっているため、group_replication_auto_increment_increment=1等で設定を変更すると良いかと思います。
変更しない場合は、以下のように値がIncrementされていきます。

補足:
双方向にレプリケーションも可能ですが、やはり事前に確認が必要ですので事前のPOCをお願いします。
以下の様に、逆方向にもレプリケーションを張る事は可能です。但し、グループレプリケーションとグループレプリケーションの間は非同期でレプリケーションを張っています。その為、両方で同じデータを同時に変更しないようにする必要があります。またInnoDB Clusterの場合は、グループレプリケーションだけで構成した以外にも考慮が必要です。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=63301,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected (0.19 sec)



InnoDB Clusterを構築すると、MySQL Routerの設定ファイルが自動的に作成されます。基本的にはTTLによって構成はキャッシュされますが、metadata cache componentが常にMySQLに接続していて、Group Replicationの状況をモニタリングしています。それにより、構成変更や障害発生時には自動的に検知してアプリケーションを適切なデータベースに振り分けてくれます。

【以下、マニュアル抜粋】
MySQL Router keeps a cached list of the online MySQL servers, or the topology and state of the configured InnoDB cluster. Initially, the list is loaded from Router’s configuration file when Router is started. This list was generated with InnoDB cluster servers when Router was bootstrapped using the –bootstrap option.

To keep the cache updated, the metadata cache component keeps an open connection to one of the InnoDB cluster servers that contains metadata. It does so by querying the metadata database and live state information from MySQL’s performance schema. The cluster metadata is changed whenever the InnoDB cluster is modified, such as adding or removing a MySQL server using the MySQL Shell, and the performance_schema tables are updated in real-time by the MySQL server’s Group Replication plugin whenever a cluster state change is detected. For example, if one of the MySQL servers unexpectedly exits.

When Router detects that a connected MySQL server crashes, for example because the metadata cache has lost its connection and can not connect again, it attempts to connect to a different MySQL server to fetch metadata and InnoDB cluster state from the new MySQL server.

Application connections to a MySQL server that crashes are automatically closed. They must then reconnect to Router, which redirects them to an online MySQL server.

参照: https://dev.mysql.com/doc/mysql-router/2.1/en/mysql-router-general-metadata.html

MySQL Router2.1から, MySQLへの常時接続の確認 (mysql_router5_212qj4063dmw)


mysql> show processlist;
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User                       | Host            | db    | Command | Time | State                                                  | Info             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
| 11 | system user                |                 | NULL  | Connect | 4600 | executing                                              | NULL             |
| 14 | system user                |                 | NULL  | Connect | 4600 | Slave has read all relay log; waiting for more updates | NULL             |
| 42 | root                       | localhost       | mysql | Query   |    0 | System lock                                            | show processlist |
| 43 | mysql_router5_212qj4063dmw | localhost:48721 | NULL  | Sleep   |   66 |                                                        | NULL             |
+----+----------------------------+-----------------+-------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.01 sec)

mysql> select * from general_log;
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| event_time                 | user_host                    | thread_id | server_id | command_type | argument                               |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
| 2017-06-09 14:34:32.995672 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
| 2017-06-09 14:34:37.233924 | root[root] @ localhost []    |        42 | 944686911 | Query        | show processlist                       |
| 2017-06-09 14:34:41.252563 | [root] @ localhost [::1]     |        46 | 944686911 | Connect      | root@localhost on  using SSL/TLS       |
| 2017-06-09 14:34:41.253181 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select @@version_comment limit 1       |
| 2017-06-09 14:34:41.253872 | root[root] @ localhost [::1] |        46 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port |
| 2017-06-09 14:34:41.257997 | root[root] @ localhost [::1] |        46 | 944686911 | Quit         |                                        |
| 2017-06-09 14:34:44.344733 | root[root] @ localhost []    |        42 | 944686911 | Query        | select * from general_log              |
+----------------------------+------------------------------+-----------+-----------+--------------+----------------------------------------+
7 rows in set (0.00 sec)


PRIMARY(書き込み)は固定され、SECONDARYは参照専用でラウンドロビンされている

-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 


SECONDARYの一台を停止してみる


mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.killSandboxInstance(3320)
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be killed


Killing MySQL instance...

Instance localhost:3320 successfully killed.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 


MySQL Router2.1がトポロジーの変更を認識し、以下のコマンドをmysql_router5_212qj4063dmw@localhostから実行している事が確認出来る

mysql> select * from general_log;
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time                 | user_host                                                                      | thread_id | server_id | command_type | argument                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2017-06-09 14:34:57.700152 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| 2017-06-09 14:35:39.294567 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.294983 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Query        | SELECT @@GLOBAL.gtid_executed                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 2017-06-09 14:35:39.295129 | _gr_user[_gr_user] @ localhost []                                              |        48 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.299868 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Connect      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:39.300055 | _gr_user[_gr_user] @ localhost []                                              |        49 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:41.755412 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021036 | [root] @ localhost [::1]                                                       |        50 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.021898 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:54.022239 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:54.025850 | root[root] @ localhost [::1]                                                   |        50 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:54.939557 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        43 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:35:55.225193 | [mysql_router5_212qj4063dmw] @ localhost [127.0.0.1]                           |        51 | 944686911 | Connect      | mysql_router5_212qj4063dmw@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.227370 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster' |
| 2017-06-09 14:35:55.229489 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | show status like 'group_replication_primary_member'                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 2017-06-09 14:35:55.232809 | mysql_router5_212qj4063dmw[mysql_router5_212qj4063dmw] @ localhost [127.0.0.1] |        51 | 944686911 | Query        | SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'                                                                                                                                                                                                                                                                                          |
| 2017-06-09 14:35:55.764665 | [root] @ localhost [::1]                                                       |        52 | 944686911 | Connect      | root@localhost on  using SSL/TLS                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.765502 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select @@version_comment limit 1                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:35:55.767062 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Query        | select 'R-Port:6446',@@hostname,@@port                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 2017-06-09 14:35:55.770798 | root[root] @ localhost [::1]                                                   |        52 | 944686911 | Quit         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2017-06-09 14:36:00.277880 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | show processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 2017-06-09 14:36:04.168023 | root[root] @ localhost []                                                      |        42 | 944686911 | Query        | select * from general_log                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+--------------------------------------------------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33 rows in set (0.00 sec)

MySQL Router 2.1が実行しているコマンド
このコマンドで、PRIMARYと現状のGroup Replicationの構成を認識している。

mysql> show status like 'group_replication_primary_member';      
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 1c008db5-4cca-11e7-be2d-080027d65c57 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT R.replicaset_name, I.mysql_server_uuid, I.role, I.weight, I.version_token, H.location, I.addresses->>'$.mysqlClassic', I.addresses->>'$.mysqlX' FROM mysql_innodb_cluster_metadata.clusters AS F JOIN mysql_innodb_cluster_metadata.replicasets AS R ON F.cluster_id = R.cluster_id JOIN mysql_innodb_cluster_metadata.instances AS I ON R.replicaset_id = I.replicaset_id JOIN mysql_innodb_cluster_metadata.hosts AS H ON I.host_id = H.host_id WHERE F.cluster_name = 'testCluster';
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| replicaset_name | mysql_server_uuid                    | role | weight | version_token | location | I.addresses->>'$.mysqlClassic' | I.addresses->>'$.mysqlX' |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
| default         | 1c008db5-4cca-11e7-be2d-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3310                 | localhost:33100          |
| default         | 4acfab6c-4cca-11e7-848b-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3320                 | localhost:33200          |
| default         | 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | HA   |   NULL |          NULL |          | localhost:3330                 | localhost:33300          |
+-----------------+--------------------------------------+------+--------+---------------+----------+--------------------------------+--------------------------+
3 rows in set (0.01 sec)

mysql> SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier';
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| member_id                            | member_host  | member_port | member_state | @@group_replication_single_primary_mode |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
| 1c008db5-4cca-11e7-be2d-080027d65c57 | replications |        3310 | ONLINE       |                                       1 |
| 7b6cb3c3-4cca-11e7-8af6-080027d65c57 | replications |        3330 | ONLINE       |                                       1 |
+--------------------------------------+--------------+-------------+--------------+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> 

ノード3320は停止しているので、MySQL Router2.1が稼働しているノードに適切に割り振っている

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ 

停止していたインスタンスを再稼働させる


-bash-4.2$ mysqlsh
Welcome to MySQL Shell 1.0.9

Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.

Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> shell.connect('root@localhost:3310');
Please provide the password for 'root@localhost:3310': 
Creating a Session to 'root@localhost:3310'
Classic Session successfully established. No default schema selected.
mysql-js> dba.startSandboxInstance(3320);
The MySQL sandbox instance on this host in 
/home/mysql/mysql-sandboxes/3320 will be started


Starting MySQL instance...

Instance localhost:3320 successfully started.

mysql-js> var cluster = dba.getCluster("testCluster")
mysql-js> cluster.rejoinInstance('localhost:3320')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'root@localhost:3320': 
Rejoining instance to the cluster ...

The instance 'root@localhost:3320' was successfully rejoined on the cluster.

The instance 'localhost:3320' was successfully added to the MySQL Cluster.
mysql-js> cluster.status()
{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }
}
mysql-js> 

MySQL Routerも認識して再度バランシングに追加されている事が確認出来る


mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3330 |
+-------------+--------------+--------+
-bash-4.2$ ./mysql_status_router.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6446 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6446 | replications |   3310 |
+-------------+--------------+--------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+--------------+--------+
| R-Port:6447 | @@hostname   | @@port |
+-------------+--------------+--------+
| R-Port:6447 | replications |   3320 |
+-------------+--------------+--------+
-bash-4.2$ 

詳細は、此方を確認下さい
https://dev.mysql.com/doc/mysql-router/2.1/en/

サポート (HA & RouterはEEにてサポート)
https://www.mysql.com/jp/support/


MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()

MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/

まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。

確認バージョン


mysql> select now(),@@version;     
+---------------------+-----------+
| now()               | @@version |
+---------------------+-----------+
| 2017-05-31 19:50:46 | 8.0.1-dmr |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> 

WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987

Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.

mysql> select body from T_JSON_DOC where id in (1,2);              
+---------------------------------------------------------------------------------+
| body                                                                            |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id,body from T_JSON_DOC where id in(1,2);            
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body)                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,body from T_JSON_DOC;                                                
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
|  3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}              |
|  4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}             |
|  5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}   |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price
": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri
ce": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)

mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)

mysql>
 

WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name “pretty”:


mysql> select body from T_JSON_DOC where id = 1\G             
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)

mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
  "id": 1,
  "name": "",
  "price": 10000,
  "Conditions": [
    "NEW",
    2015,
    "Excellent"
  ]
}
1 row in set (0.00 sec)

mysql> 

その他のJSON関数(運用向け)

JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.

JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.


mysql> SELECT 
    ->     jcol, 
    ->     JSON_STORAGE_SIZE(jcol) AS Size, 
    ->     JSON_STORAGE_FREE(jcol) AS Free 
    -> FROM jtable;

JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.


SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, 
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;