キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
MySQL 8.0.19が来た! その2: mysqlコマンドラインツールのbinary-as-hexがデフォルトでオンに。
0
    MySQL 8.0.19が来た! その2: mysqlコマンドラインツールのbinary-as-hexがデフォルトでオンに。

    2020-01-13にMySQL 8.0.xのメンテナンスリリースである8.0.19が出ました。

    多くの機能が追加されましたが、本ブログではあまり他の人が取り上げなさそうなものをご紹介します。
    今回はmysqlコマンドラインツールのbinary-as-hexについて。

    いきなり顛末はこちら。

    MySQL8でCHAR関数がドキュメントどおりになってない(41から始めました)

    # 私は40くらいからMySQLはじめてたみたいです。

    ちなみにドキュメントではもともと「デフォルトでは、CHAR() はバイナリ文字列を返します」なので、出力フォーマットの問題です。

    今回の現象の原因はMySQL 8.0.19のmysqlコマンドラインツールのbinary-as-hexオプションがデフォルトでオンになったことです。binary-as-hexオプション自体はMySQL 5.6以降、以下のバージョンで用意されました。それぞれ2017-07-17のリリースと結構古いものです。

    MySQL 5.6.37
    MySQL 5.7.19
    MySQL 8.0.2

    そして今回MySQL 8.0.19のみデフォルトがONになりました。

    しかしながら、5.6, 5.7ではデフォルトはOFFのままですし、MySQL Shellの8.0.19でも、動作は旧来のままですので、今後しばらくは混乱しそうです。

    C:¥Program Files¥MySQL¥MySQL Shell 8.0¥bin>mysqlsh --sql -uユーザ名 -pパスワード
    MySQL Shell 8.0.19-commercial

    Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
    Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
    Other names may be trademarks of their respective owners.

    Type '¥help' or '¥?' for help; '¥quit' to exit.
    WARNING: Using a password on the command line interface can be insecure.
    Creating a session to 'root@localhost'
    Fetching schema names for autocompletion... Press ^C to stop.
    Your MySQL connection id is 24 (X protocol)
    Server version: 8.0.19-commercial MySQL Enterprise Server - Commercial
    No default schema selected; type ¥use to set one.
    MySQL localhost:33060+ ssl SQL > SELECT CHAR(77,121,83,81,'76');
    +-------------------------+
    | CHAR(77,121,83,81,'76') |
    +-------------------------+
    | MySQL |
    +-------------------------+
    1 row in set (0.0003 sec)

    [回避策]

    (1) mysqlコマンドラインツール起動時に--skip-binary-as-hexを指定する。
    (2) CHAR()関数に明示的に文字セットを指定する

    なお、今回のようにあるオプションがどのリビジョンで設定された、変更された、かを確認するには
    リリースノートのインデックスが便利です。(今回はじめて気付きました)それぞれのバージョンのインデックスは以下のとおり。

    MySQL 5.6 Release Notes / Index
    MySQL 5.7 Release Notes / Index
    MySQL 8.0 Release Notes / Index

    [2020-05-02 追記] 未定義のユーザ変数にも影響があるそうです。詳しくは以下。

    MySQLで未定義のユーザ変数の表示がおかしい(8.0.20) | sakaikの日々雑感〜(T)編

    JUGEMテーマ:コンピュータ



    | meijik | MySQL | 08:50 | comments(0) | - | - | - |
    MySQL 8.0.19が来た! その1: 明示表(Explicit Table)とTVC(Table Value Constructor)
    0
      今週頭(2020-01-13)にMySQL 8.0.xのメンテナンスリリースである8.0.19が出ました。

      多くの機能が追加されましたが、本ブログではあまり他の人が取り上げなさそうなものをご紹介します。
      そのうちの一つ明示表(Explicit Table)について。

      リリースノートには以下の記述があります。

      Important Change: MySQL now supports explicit table clauses and table value constructors according to the SQL standard.

      VALUESについては以前以下の発表の中で触れました。Db2とPostgreSQLがサポートしている形式までいっきょにMySQL 8.0.19がサポートしたことになります。これはSQL標準でTable Value Constructor(TVC)と呼ばれるもの(F641)で、訳語としてはJISが表値構成子、Microsoftがテーブル値コンストラクターを使っています。

      INSERTには便利だが、まさかの展開: INSERT INTO ... VALUES(値1),(値2),...,(値n)

      また、Modern SQLでも以下の記事で紹介されています。

      select without from

      明示表(Explicit Table)については、ちょっと調べたところではCockrochDBがテーブル句(Table Clause)としてサポートしているだけのように見えました。

      SQL標準(JIS)では以下の部分となります。

      329X 3005-2:2015 (ISO/IEC 9075-2:2011) p.328, p.332

      5) <明示表>
      TABLE <表名又は問合せ名>は,次の<問合せ指定>と等価とする。
      SELECT * FROM <表名又は問合せ名>

      # Explicit Tableの確認とJISの訳語については土田正士さんに教えを請いました。ありがとうございました。

      また、Mimer SQL Validatorを使って確認する方法を以下のブログに追記しました。

      SQL標準の確認方法

      [2020-02-11 追記] その後いくつかのブログ記事で、明示表とTVCとりあげられました。

      values — Create Rows out of Nothing(modern SQL)
      MySQL 8.0.19 で追加された TABLE と VALUES(@tmtms のメモ)
      MySQL8.0.19で加わった VALUES を試してみる(sakaikの日々雑感〜(T)編)

      JUGEMテーマ:コンピュータ

      | meijik | MySQL | 08:17 | comments(0) | - | - | - |
      MySQLクライアントでの非同期処理概略
      0
        MySQLクライアントでの非同期処理概略

        本エントリはMySQL Advent Calendarの二日目です。

        MySQLの従来のプロトコルを利用するConnectorでは非同期の対応はありませんでしたが、
        MySQL 8.0.16でサポートされました。

        The MySQL C API now supports asynchronous functions for nonblocking communication with the MySQL server

        これらはもともとあるAPI関数の名称末尾に_nonblockingをつけた以下の六関数です。

        ・mysql_real_connect_nonblocking()
        ・mysql_real_query_nonblocking()
        ・mysql_store_result_nonblocking()
        ・mysql_next_result_nonblocking()
        ・mysql_fetch_row_nonblocking()
        ・mysql_free_result_nonblocking()

        ひとつのクエリを実行するときはmysql_next_result以外の5関数を使います。
        # 複数のクエリを実行する例(これは同期(blocking)の例)はこちらを参照してください。

        非同期のSELECTの例はこちらのマニュアルをご参照ください。。

        上記マニュアルにありますが、非同期処理の流れは以下のようなものになります。

        status = 非同期関数の呼び出し(args);
        while (status == NET_ASYNC_NOT_READY) {
        /* 処理が終了するまで他の処理呼び出し */
        other_processing ();
        /* 同じ引数で同じ関数をinvoke */
        status = 非同期関数の呼び出し(args);
        }
        if (status == NET_ASYNC_ERROR) {
        /* 非同期呼び出しエラー */
        } else {
        /* 非同期呼び出し成功。次の処理へ */
        }

        さて、C言語では非同期処理が実装されましたが、他の言語ではどうでしょうか?
        実はX ProtocolでそれをサポートするConnectorでは非同期処理が可能です。
        以下のマニュアルではNode.js, C#, Javaの例が確認できます。

        3.3 Synchronous versus Asynchronous Execution



        X Protocolでなく、従来のプロトコルでの各言語の対応は以下のとおりです。

        (1) Connector/J (Java) JDBCの仕様として無いため下記のような取り組みがあります。
        Asynchronous Database Access(ADBA) 

        (2) Connector/ODBCは未対応
        ODBCとしての仕様とMicrosoftの実装(SQL Server)にはある

        (3) Connector/Net (C#)
        Connector/Net自体はネイティブな非同期処理を持ちませんが、
        .Netが提供するTAP(タスク ベースの非同期パターン)ベースの非同期メソッドはあります。

        5.11 Asynchronous Methods

        タスク ベースの非同期パターン (TAP)

        Connector/Net自体はネイティブな非同期処理を持たないため以下で議論されたり、サードパーティのドライバや
        実装の試みがなされています。

        C# MySql Driver - Async Operations

        C#の高速なMySQLのドライバを書こうかという話、或いはパフォーマンス向上のためのアプローチについて

        明日はhmatsu47さんです!

        JUGEMテーマ:コンピュータ



        | meijik | MySQL | 08:41 | comments(0) | trackbacks(0) | - | - |
        データベースの分離レベルをめぐる冒険(I.イントロダクションとII.MySQLの場合)
        0
          とある原稿を書いていて、トランザクションまわりをチト調べている私です。

          あまり深みに入らなければ、4つのトランザクション分離レベルと、それぞれのアノーマリを説明して、はい、終わり、ということにしたいのですが、私には以前気づいてしまった以下の記事があり「あれ、四つ多くない?」を細かく調べることなく放置していたのでした。

          Web開発者が知っておくべき八つの分離レベル

          しかし、ここで調べなければもやもやは晴れないですし、
          神林さんがシリアライズでもパフォーマンスに問題のないRDBMSの実装が終わり、
          世の中でがんがん使われるようになるまでは、いましばらくかかるため、手元の道具(RDBMS)が
          どのようにデータベースの分離レベルを実装しているか知って使ったほうがいいだろう、ということで
          ここでしばらく時間をとって、整理したり、まとめたりします。

          I.イントロダクション

          日本語翻訳の原文はこちら。

          Eight Isolation Levels Every Web Developer Should Know

          要は「八つのトランザクション分離レベルがあるので、知っとけ」ということなのですが。。。。

          1.Read Uncommitted
          2.Read Committed
          3.Monotonic View
          4.Snapshot Reads
          5.Cursor Stability
          6.Repeatable Read
          7.Snapshot Isolation
          8.Serializability

          なんか見慣れぬものがごろごろと。。。。実は2.から5.はRC(Read Committed)と、その拡張です。

          Overwritesを禁止(SELECT ... FOR UPDATE)ということで、少なくともOracleとMySQL, Firebirdの
          Read Committedは、ここでいう6.Cursor Stabilityの実装のようです。

          うーん、なんかこう定義みたいなものはないのかなぁ、ということで見つけたドキュメントが以下。

          Weak Consistency: A Generalized Theory and OptimisticImplementations for Distributed Transactions by Atul Adya

          これを順に読んでいけばなんとかなるかなぁ。

          II.MySQLの場合

          ちなみにMySQLでは、以下の5つとなります。

          ----------------------+-----------------------
          この記事での分離レベル MySQLでの分離レベル
          ----------------------+-----------------------
          1.Read Uncommitted   READ UNCOMMITTED
          5.Cursor Stability     READ COMMITTED
          6.Repeatable Read    REPEATABLE READ
          7.Snapshot Isolation   REPEATABLE READ WITH CONSISTENT SNAPSHOT
          8.Serializability      SERIALIZABLE
          ----------------------+-----------------------

          「あれ、四つじゃないの?」と思った人は鋭い! (いや、別の意味では鋭くない)
          実はmysqldumpにて、InnoDB型のテーブル、すなわちトランザクションが利用できる環境で、
          一貫性をもったバックアップを取得するため(--single-transaction)に用意されているものです。

          この分離レベルでSTART TRANSACTIONした場合は、RRのように、一度オープンしたテーブルの内容を
          繰り返し取得できるのではなく、START TRANSACTIONした際のテーブルの内容を
          その後のトランザクション内で参照できる、というバックアップに適した(==そのために)
          実装されている分離レベルとなります。

          13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文


          (次回以降に続く(予定))

          JUGEMテーマ:コンピュータ

          | meijik | MySQL | 09:40 | comments(0) | trackbacks(0) | - | - |
          [便乗企画] 「SQL大量発行処理をいかにして高速化するか」for MySQL
          0
            ツイッターでこういうのがあったので、便乗してMySQLで考えてみるエントリ。




            高速化手法として#1-#10の十個がリストアップされています。

            #1 SQL発行数の削減 その
            Oracle11gの新機能「クライアント結果キャッシュ」は、当然MySQLにはありませんが、
            クエリと結果をサーバ側にキャッシュする「クエリキャッシュ」機能がMySQL 5.7以前では利用できます。

            # 木村はクライアント結果キャッシュをアプリ(ミドルウエア)で実装したことがあります。
            # たとえば数秒間に同じクエリを死ぬほど発行するようなアホアプリには、データベースへの問い合わせをスキップして、同じ結果を返す、など。

            #2, #3はそのまま使えそう。

            #4のinsert allはOracleのみの方言です。この例のように複数のテーブルへのinsertや単一テーブルへの複数の値の組のinsertをまとめて実行することができます。OracleとFirebird以外のRDBMSでは、単一テーブルへの複数の値の組のinsertは以下の記法でバルクインサートできます。

            【Before】
            insert into TBL_A(ID) values(1002);
            insert into TBL_A(ID) values(1003);
            insert into TBL_A(ID) values(1004);

            【After】
            insert into TBL_A(ID) values(1002),(1003),(1004);

            この記法は元の例のような複数のテーブルへのインサートにはつかえません。

            #5のようにPL/SQLでまとめて発行するためには、無名プロシジャ(Anonymous procedure)のサポートが必要ですが、MySQLではサポートしていないため使えません。(一時的に名前をつけたプロシジャを作成して、それを削除するということでエミュレートは可能ですが、作成・削除に時間がかかり、本来のよさがなくなってしまいます)

            以下のWLにはあがっていますが、まだ実装されていません。(「動的複合ステートメント」としてリストアップされています)

            WL#3696: Dynamic Compound Statements

            MySQLからフォークしたMariaDBでは、最近Oracle DBに対する互換性をあげてきており、10.1.1から対応しています。

            MariaDB 10.1.1: Compound statements

            Oracleでの詳細は以下のブログ記事が参考になります。

            PL/SQLの理解と利用に関するシリーズ記事のパート1

            #6,#7,#8 のBLOB,CLOBの最適化、高速化はMySQLでは当てはまるものがないです。(もともと普通のデータ型ちとして、通常のBinary, Characterと同様なアクセスができるので)

            #9はMySQLでも同様にあてはまります。autocommit=1ではなく、0にして、ある程度の処理(1万行など)ごとにコミットするようにしてください。

            #10も同様にあてはまります。

            ということで、わりとあっさりと終わります。。。。

            JUGEMテーマ:コンピュータ



            | meijik | MySQL | 09:57 | comments(0) | trackbacks(0) | - | - |
            [勝手に対応]MySQL8.0の分析関数の2(Lag関数と累計と移動累計)
            0
              [勝手に対応]MySQL8.0の分析関数の2(Lag関数と累計と移動累計)

              本エントリはMySQL Casual Advent Calendar 2018の11日目です。

              MySQLでは8.0からウインドウ関数に対応しました。これにより商用RDBMS御三家+PostgreSQLが対応していた
              多くの分析関数・ウインドウ関数が利用できるようになりました。

              本エントリは以下のエントリの続きです。

              [勝手に対応]MySQL8.0の分析関数の1(モードとメジアン)


              1. 部分的に不一致なキーの検索

              PostgreSQL 8.4でエラーになるクエリはMySQL 8.0でもエラーになります。(PostgreSQL 11 でも同じ)

              ERROR 1235 (42000): This version of MySQL doesn't yet support '(DISTINCT ..)'

              これはWindow関数内でDISTINCT を扱えない、実装上の問題です。

              window関数で書き換えたSQL1,2は動作しますが、
              window関数で書き換えたSQL3,4はPostgreSQL特有の配列を使ったものですので、PostgreSQL以外は動作しません。

              [To be contined]


              JUGEMテーマ:コンピュータ



              | meijik | MySQL | 23:58 | comments(0) | trackbacks(0) | - | - |
              [勝手に対応]MySQL8.0の分析関数の1(モードとメジアン)
              0
                本エントリはMySQL Casual Advent Calendar 2018の二日目です。今年は参加者少なく継続募集中です。

                MySQLでは8.0からウインドウ関数に対応しました。これにより商用RDBMS御三家+PostgreSQLが対応していた
                多くの分析関数・ウインドウ関数が利用できるようになりました。

                分析関数については、山岸さんが以下の時系列の流れで詳細に解説してくださってます。

                1.分析関数の衝撃(1-6) 商用RDBMSでの説明。主にOracle 10.2を利用。
                2.MySQLで分析関数を模倣(1-5) 分析関数未実装のRDBMSでの説明。主にMySQL 5.1を利用。
                3.PostgreSQLの分析関数の衝撃(1-8) PostgreSQL8.4での説明

                これらの記事で、分析関数を利用していないクエリについては、主にミックさんがオンラインで掲載した
                達人に学ぶSQL
                を参照しています。

                これは以下の本にまとまってます。

                達人に学ぶSQL徹底指南書 第2版

                このような経緯のため、MySQL 8.0を利用する人は「PostgreSQLの分析関数の衝撃」を参照し、
                ウインドウ関数でわからないところがあれば「分析関数の衝撃」を参照、
                ウインドウ関数と等価のクエリでわからないところがあれば「MySQLで分析関数を模倣」
                これらが参照しているクエリでわからないところがあればミックさんのオンライン記事か、書籍を参照すると
                きっともれなくわかると思います。

                今回は「PostgreSQLの分析関数の衝撃1」を参照してみます。

                [勝手に対応]MySQL8.0の分析関数の1(モードとメジアン)

                以下の記事のMySQL8.0対応版です。

                PostgreSQLの分析関数の衝撃1(モードとメジアン)


                この連載は無償のユーザ登録して、CodeZineダウンロード規約に同意すれば、
                ソースコードがダウンロードできますので実際にためしてみたい方は上記ページからダウンロードしてください。

                1. 歯抜けを探す


                 これはDDL, DMLともにそのまま動作します。これはMySQL 8.0ではROW_NUMBERがサポートされたからです。

                ROW_NUMBER(MySQL 8.0 Manual)

                ソースコードを利用する人で注意点は(本質ではないのですが)一部コメントを示す--の後に空白がないため
                エラーになります。MySQLの方言でコメントの--の後には空白(ホワイトスペース)が必要です。

                2. 最頻値(モード)を求める

                MySQLにはtext型のデータ型がないので、Graduatesテーブルのnameカラムのデータ型をtextからvarchar(10)などに変換すると動作します。
                window関数を使わない版のクエリはすべてのMySQLで、window関数を使う版のクエリはMySQL 8.0以降で利用できます。

                最頻値(モード)を利用する際の注意点は、一般的に利用されるmode()関数とは戻り値が違う場合があることです。
                モード(最頻値)が複数ある場合でも、当該ページにあるクエリやプログラマのためのSQL「31.SQLにおける記述統計」にある方法で求めると複数のモード(最頻値)が結果として戻りますが、mode()関数 (OracleやPostgreSQL, MariaDBで実装)では、複数のモードがある場合は一番小さいもの一つが戻ります。これを模倣するためには、incomeでorder byして、一行取り出せばいいことになります。

                select income,cnt
                from (select income,count(*) as cnt,
                max(count(*)) over() as maxCnt
                from Graduates
                group by income
                order by income) a
                where cnt = maxCnt
                limit 1;

                ORDER BYが必要になるのは、MySQL 8.0ではGROUP BYの暗黙のソートがされなくなるためです。(ハッシュによるGROUP BYへの布石?)

                MySQL 8.0では「GROUP BYによる暗黙のソート」がされなくなるよ

                3. 中央値(メジアン)を求める

                これは、どちらも動作します。MySQL(そしてFirebirdも)以外のプラットフォームでは、SQL標準のPERCENTILE_CONT(0.5)を使ったり方言のmedian()関数を利用して求めることもできます。

                 MySQLは8.0時点でmedian()もpercentile_cont()も実装されていません。以下のようにFR(Feature Request)を出しました。

                Bug #93234 Support percentile_cont (SQL Standard) for median.

                 上記から参照しているBugで過去にmedianサポートのFRも出ています。

                明日は(というよりもう今日ですね)@hmatsu47です。

                JUGEMテーマ:コンピュータ



                | meijik | MySQL | 23:02 | comments(0) | trackbacks(0) | - | - |
                MySQL 8.0でカジュアルにウインドウ関数(rank, dense_rank)
                0
                  MySQL 8.0でカジュアルにウインドウ関数(rank, dense_rank)

                  本エントリはMySQL Casual Advent Calendar 2017の3日目です。

                  MySQL 8.0ではウインドウ関数がサポートされる予定である最新の8.0.3 RCで実際に動作させることが可能です。

                  12.19.1 Window Function Descriptions

                  高度な使い方の代表例としては、以下のように自己結合の代替する、というものですが、

                  津島博士のパフォーマンス講座 第47回 自己結合と分析ファンクションについて
                  Window関数(Let's PostgreSQL)

                  今回はカジュアル! ということで、誰でも考えなく使えそうなrank, dense_rankを説明します。
                  (これまでは、下記のような遅い方法か、これこれのようにユーザ変数を利用した方法しか使えませんでした)

                  オープンソースデータベースでの順位(dense_rank, rank)の取得(キムラデービー)

                  上記の方法をウインドウ関数を使ってリプレイ!

                  create table emp(sal int);
                  insert into emp values(800);
                  insert into emp values(1600);
                  insert into emp values(1250);
                  insert into emp values(2975);
                  insert into emp values(1250);
                  insert into emp values(2850);
                  insert into emp values(2450);
                  insert into emp values(3000);
                  insert into emp values(5000);
                  insert into emp values(1500);
                  insert into emp values(1100);
                  insert into emp values(950);
                  insert into emp values(3000);
                  insert into emp values(1300);

                  -- dense_rank()
                  select dense_rank() over(order by sal) rnk, sal from emp;

                  +-----+------+
                  | rnk | sal |
                  +-----+------+
                  | 1 | 800 |
                  | 2 | 950 |
                  | 3 | 1100 |
                  | 4 | 1250 |
                  | 4 | 1250 |
                  | 5 | 1300 |
                  | 6 | 1500 |
                  | 7 | 1600 |
                  | 8 | 2450 |
                  | 9 | 2850 |
                  | 10 | 2975 |
                  | 11 | 3000 |
                  | 11 | 3000 |
                  | 12 | 5000 |
                  +-----+------+
                  14 rows in set (0.00 sec)

                  -- rank()
                  select rank() over(order by sal) rnk, sal from emp;

                  +-----+------+
                  | rnk | sal |
                  +-----+------+
                  | 1 | 800 |
                  | 2 | 950 |
                  | 3 | 1100 |
                  | 4 | 1250 |
                  | 4 | 1250 |
                  | 6 | 1300 |
                  | 7 | 1500 |
                  | 8 | 1600 |
                  | 9 | 2450 |
                  | 10 | 2850 |
                  | 11 | 2975 |
                  | 12 | 3000 |
                  | 12 | 3000 |
                  | 14 | 5000 |
                  +-----+------+
                  14 rows in set (0.00 sec)

                  MySQL 8.0からはウインドウ関数に加えてCTE(Common Table Expression)も利用できます。
                  MySQL 8.0での実装はSQL標準のSELECTのみならず、更新(UPDATE/DELETE)もできますが、
                  (MariaDBやFirebirdの実装はSELECTのみ)
                  現在ウインドウ関数の実装の制限により、以下のようなケースでは更新不可です。。。。
                  (元ネタはMyNAメーリングリストの明智さんの投稿から)

                  CTE with window function don't work with update/delete as expected.



                  [追伸]
                  ググると以下のようなエントリがすでにありましたorz

                  MySQL 8.0.2 DMRでウィンドウ関数がサポートされたので、RANK関数を試してみる(Qiita)

                  JUGEMテーマ:コンピュータ



                  | meijik | MySQL | 11:39 | comments(0) | trackbacks(0) | - | - |
                  よく使うストアドプロシジャ。とりあえず件数。
                  0
                    こんなん使ってます。

                    -- [前提となるテーブル]
                    DROP TABLE IF EXISTS t1;
                    CREATE TABLE t1(i1 int not null primary key, v2 varchar(20)) engine = innodb;

                    -- [利用するストアドプロシジャ2: 1万件ごとにコミット処理あり]
                    drop procedure if exists test_insert_commit;
                    delimiter $
                    create procedure test_insert_commit(v_max int)
                    begin
                    declare v_id int default 0;
                    repeat
                    set v_id = v_id + 1;
                    insert into t1 values(v_id, v_id);
                    if (mod(v_id,10000) = 0) then commit;
                    end if;
                    until v_id >= v_max
                    end repeat;
                    end$
                    delimiter ;

                    JUGEMテーマ:コンピュータ



                    | meijik | MySQL | 09:45 | comments(0) | trackbacks(0) | - | - |
                    MySQL 8.0 Lab版でCTE(Common Table Expression)
                    0
                      本エントリはMySQL Casual Advent Calendar の9日目です。

                      MySQL Lab坂の8.0 (Optimizer)では、CTEが実装されています。詳しくは次のエントリをどうぞ。

                      MySQL 8.0 Lab版: MySQLの (再帰)共通テーブル式(CTE)

                      まあじっくり攻めるなら上記エントリでいいのですが、ここは簡単にざっくり動かしてみます。

                      まずlab坂の当該パッケージはRHEL7のtarballとソースしか配布されていません。
                      このtarballをCentOS7のmysql sandboxで動かします。

                      [2016-12-27 追記] MySQL Sandboxは3.2.xからMySQL 8.0に対応しており、現時点での最新3.2.04では
                      8.0.0としてインストールもユーザテーブル作成も大丈夫です。私が試したのは3.1.xでその際には
                      以下の代替作業が必要でした。

                      //->ここから代替作業
                      解凍して8.0.0にディレクトリ名を8.0.0とすると動きませんので、まぁ一番近い5.7の、
                      まぁ使わないバージョンとして5.7.1としてインストールして、make_sandbox 5.7.1とします。

                      ユーザテーブルがうまく作成されないみたいなので、my.sandbox.cnfを以下のようにします。

                      [client]
                      #user = msandbox
                      user = root
                      #password = msandbox

                      ./start
                      ./use

                      で起動、ログイン可能です。データベースtestをつくり、

                      create database test;
                      //<-ここまで代替作業

                      ここ十年くらい使っている以下のサンプルを作成します。

                      階層構造を隣接リストで表現

                      こんな感じで、テーブル、データをつくり、

                      use test;
                      CREATE TABLE shain(empid INT PRIMARY KEY, name VARCHAR(20), mgrid INTEGER) charset=utf8;
                      INSERT INTO shain VALUES ( 1, 'みやはら', NULL);
                      INSERT INTO shain VALUES ( 2, 'きむら', 1);
                      INSERT INTO shain VALUES ( 3, 'かまたき', 1);
                      INSERT INTO shain VALUES ( 4, '加藤', 2);
                      INSERT INTO shain VALUES ( 5, '林', 2);
                      INSERT INTO shain VALUES ( 6, '三浦', 5);

                      以下のクエリを実行します。

                      mysql > WITH RECURSIVE n AS
                      -> (
                      -> SELECT empid, name FROM shain WHERE name = 'きむら'
                      -> UNION ALL
                      -> SELECT nnext.empid, nnext.name FROM shain as nnext, n WHERE n.empid= nnext.mgrid
                      -> )
                      -> SELECT * FROM n;
                      +-------+-----------+
                      | empid | name |
                      +-------+-----------+
                      | 2 | きむら |
                      | 4 | 加藤 |
                      | 5 | 林 |
                      | 6 | 三浦 |
                      +-------+-----------+
                      4 rows in set (0.00 sec)

                      キモは上記のように初期集合を求めるSELECTと再帰集合を求めるSELECTをUNION ALLし、
                      それに共通表名をつけ、SELECTするところです。

                      WITH RECURSIVE 共通表名 AS
                      (
                      初期集合を求めるSELECT
                      UNION ALL
                      再帰集合を求めるSELECT
                      )
                      SELECT * FROM 共通表名

                      ではでは。
                      | meijik | MySQL | 20:59 | comments(0) | trackbacks(0) | - | - |
                         1234
                      567891011
                      12131415161718
                      19202122232425
                      262728293031 
                      << July 2020 >>
                      + RECOMMEND
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊
                      100人のプロが選んだソフトウェア開発の名著 君のために選んだ1冊 (JUGEMレビュー »)

                      100人のうちの一人としてミックさんの本を推薦しています。
                      + RECOMMEND
                      MySQL 5.1 Plugin Development
                      MySQL 5.1 Plugin Development (JUGEMレビュー »)
                      Andrew Hutchings,Sergei Golubchik
                      MySQL 5.1 のプラグインを作るならこれ!
                      + RECOMMEND
                      AWKを256倍使うための本 (Ascii 256倍)
                      AWKを256倍使うための本 (Ascii 256倍) (JUGEMレビュー »)
                      志村 拓,鷲北 賢,西村 克信
                      ここにきてまさかの復刊。15〜16年前の本ですが、いまでも十分役立ちますよ!!
                      + RECOMMEND
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION)
                      Linux-DB システム構築/運用入門 (DB Magazine SELECTION) (JUGEMレビュー »)
                      松信 嘉範
                      Linux-DB システム構築の新スタンダード。DB Magazine人気連載、待望の書籍化です!!
                      + RECOMMEND
                      + RECOMMEND
                      超・極める!MySQL
                      超・極める!MySQL (JUGEMレビュー »)
                      坂井 恵,志村 和彦,ひろせ まさあき,松信 嘉範
                      内容は少々古くなりましたが、内部説明があり、いろいろ面白いです。
                      + RECOMMEND
                      The Root of .NET Framework
                      The Root of .NET Framework (JUGEMレビュー »)
                      荒井 省三
                      .NETの根っこをつかむ、か?
                      + RECOMMEND
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
                      エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド (JUGEMレビュー »)
                      奥野 幹也
                      これまでにない構築、そして構築後のMySQLのための本。
                      + RECOMMEND
                      Database Management Systems
                      Database Management Systems (JUGEMレビュー »)
                      Raghu Ramakrishnan,Johannes Gehrke
                      ミックさん連載の参考書籍です。
                      + RECOMMEND
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series)
                      Advanced Programming in the UNIX Environment: Paperback Edition (2nd Edition) (Addison-Wesley Professional Computing Series) (JUGEMレビュー »)
                      W. Richard Stevens,Stephen A. Rago
                      必読! といいつつ私が読んだのは初版の日本語訳です。差分はチェックしないといけませんね。
                      + RECOMMEND
                      + RECOMMEND
                      + RECOMMEND
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE
                      Western Digital Scorpio Blue 2.5inch 5400rpm 320GB 8MB PATA WD3200BEVE (JUGEMレビュー »)

                      DELL SX260の換装用に購入。今はコチラのほうが安いですね。
                      + RECOMMEND
                      情熱プログラマー ソフトウェア開発者の幸せな生き方
                      情熱プログラマー ソフトウェア開発者の幸せな生き方 (JUGEMレビュー »)
                      Chad Fowler
                      OSC2010神戸に出展されたジュンク堂で買いました。
                      + RECOMMEND
                      Software Design 総集編 【2000~2009】(DVD付)
                      Software Design 総集編 【2000~2009】(DVD付) (JUGEMレビュー »)

                      メガトン級の過去記事。売り切れる前にお店に急げ!
                      + RECOMMEND
                      + RECOMMEND
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2
                      玄人志向 2.5型HDDケース SATA対応USB2.0接続 GW2.5AI-SU2 (JUGEMレビュー »)

                      Aspire 1410 HDD換装後、昔のHDDはこれにいれます。
                      + RECOMMEND
                      + RECOMMEND
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22
                      acer Aspire Timeline AS1410 11.6型ノートPC Windows7搭載 250GB ブラック AS1410-KK22 (JUGEMレビュー »)

                      色違い黒(他に青と白があります): メモリは最大4GBとあるが、がんばれば8GBいけるらしい。
                      + RECOMMEND
                      Firebird 徹底入門
                      Firebird 徹底入門 (JUGEMレビュー »)
                      木村 明治,はやし つとむ,坂井 恵
                      Firebird日本ユーザ会のはやしさん、そしてMyNAから拝借(?) してきた坂井さんとともに書きました。まだ誰も知らないFirebird 2.5や、いままでドキュメントのなかったツール類についてもソースを確認してとりあげた本です。是非ご活用ください。
                      + SELECTED ENTRIES
                      + RECENT COMMENTS
                      • たまたま「プリントヘッドの種類が違います」エラーから復活@MG6230
                        meijik (11/20)
                      • [ミュージシャンに聴かせたい・弾かせたい!] (4) 山田元気様
                        meijik (07/27)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (03/03)
                      • 2019年の木村明治
                        田中尚 (01/08)
                      • 使い慣れたSQLに潜む実装依存: Firebirdの場合(6) 文字列との結合演算子
                        noname (12/10)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        Moriaki_ (01/05)
                      • 2015年版 InterBaseの歩み
                        nakagami (12/06)
                      • 毎月日経SYSTEMSが届くたびに?が増え続ける: 日経SYSTEMSのコラム「ITアーキテクチャーの真髄」と「第7正規化」
                        meijik (11/22)
                      • 分散DB本読書会第46回メモ「STOP AFTER 10」
                        masudahidehiko (09/01)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE