キムラデービーブログ

オープンソースデータベースを加速する「キムラデービー」のブログです。カレー日記を兼ねてます。なお著者は2010-06-01より日本オラクルに在籍していますが、本サイト(ブログ、またはウェブサイト)において示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。
データベースの分離レベルをめぐる冒険(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) | - | - |
                MySQL Utilities 1.6
                0
                  本エントリはMySQL Casual Advent Calendar 2015 の24日目です。

                  MySQL Utilitiesというpythonの便利なスクリプト集があります。1.0のときに梶山さんのブログエントリで紹介されました。その後1.1, 1.2とバージョンを重ね、1.3の時には以下のエントリにて21ユティリティとなり、その主要なツールについて説明してあります。MySQL Utilities特有のコマンドラインとか、おおまかな動作イメージは今でも以下のエントリが参考になると思います。

                  Pythonで作られた便利なコマンドラインツール MySQL Utilities(Think IT)

                  MySQL Utilities 1.4.2で二つのユティリティが追加され、合計23ツールになりました。

                  5.15 mysqlrplms : マルチソースレプリケーションの設定と開始(@do_akiさん対抗?)
                  5.19 mysqlrplsync : レプリケーション同期チェッカー

                  MySQL Utilities 1.4くらいになると参考になる記事もちらほら。以下はPerconaによるmysqlrpmadminとmysqlfailoverの記事。

                  MySQL Utilitiesでフェイルオーバ - mysqlrpladmin
                  Failover with the MySQL Utilities: Part 2 – mysqlfailover



                  現在MySQL Utilitiesは1.5がGAを向かえ、現在1.6が開発中です。
                  1.5では上記紹介した記事などを参考にして、一度使ってみてください。

                  現在開発中の1.6(2015-12-24時点でMySQL Utilities 1.6.2 beta)ではいくつかツールが追加されています。

                  1.6.0では次の二つのユティリティが追加され、合計25ツールになりました。

                  5.3 mysqlbinlogmove : バイナリログ再配置ユティリティ
                  5.14 mysqlgrants : オブジェクト毎のGRANTS表示

                  1.6.1では次の三つのユティリティが追加され、合計28ツールになりました。

                  5.4 mysqlbinlogpurge : バイナリログパージユティリティ
                  5.5 mysqlbinlogrotate : バイナリログローテートユティリティ
                  5.26 mysqlslavetrx : スレーブトランザクションスキップユティリティ

                  1.6の目玉はmysqlslavetrxではないでしょうか? これはGTIDを利用したレプリケーションで、スレーブのエラーをスキップするために、空トランザクションを複数打つかわりに利用できるものです。詳しくはこちらをご参照ください。

                  Fixing errant transactions with mysqlslavetrx prior to a GTID failover(Percona)

                  Firebird Advent Calendar 2015でへろへろな私です。なんか結局各バージョンの紹介とリンクを紹介しただけで終わってしまいました。申し訳ねぇっす。

                  明日はみんなも大好きヒゲもじゃギークのサンタクロース、@nippondanjiさんのエントリです!

                  JUGEMテーマ:コンピュータ
                  | meijik | MySQL | 00:24 | comments(0) | trackbacks(0) | - | - |
                  ISO8610形式のUTC日付時刻型をとんちでロード
                  0
                    ISO8610形式の日付時刻表記があります。イメージこんな感じのフォーマットです。

                    20150224T1045Z

                    詳しくはwikipediaなどでチェックしてみてください。

                    ISO 8601(wikipedia)

                    MySQLでは直接扱えないので、心得のあるひとはMySQLに突っ込む前にLL(Light Weigh Language)などで変換すると思うのですが、ある程度限定された書式であれば、比較的簡単にLOAD DATAで変換しながら、つっこめます。
                    本エントリは、そのとんちについてのメモです。対象データは以下のようなやつで/tmp/iso8610.txtとして置きます。

                    20150224T10Z
                    20150224T1045Z
                    20150224T104623Z
                    20150224T104623.283576Z

                    条件はこんな感じ
                    (1) すべてUTC
                    (2) 分や秒の省略あり
                    (3) 秒より小さい値は切り捨て
                    (4) +0900とかはなし

                    1.DATETIMEカラムの場合。
                     TZ(タイムゾーン)は意識しないので、単純に9時間足して突っ込む。足し算のもとになる値は、
                    ISO8610形式からTとZを省いて
                    分・秒省略を補うため'0000'を追加して、左から14文字(つまり20140224104623)をとる。

                    CREATE TABLE `iso_datetime` (
                    `v1` varchar(30) DEFAULT NULL,
                    `d2` datetime DEFAULT NULL
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

                    -- loading data without tz (datetime)
                    load data infile '/tmp/iso8610.txt' into table iso_datetime (@var) set v1=@var, d2 = addtime(left(replace(replace(@var,'T',''),'Z','0000'),14),'09:00:00');

                    2.TIMESTAMPカラムの場合。
                     TZ(タイムゾーン)は意識するので、LOAD DATAを実行するまえにUTC(つまりセッションのTIME_ZONEを'+00:00')に
                    設定して、値をつっこむ。元となる値の作成方法はDATETIMEカラムの場合と同じ。

                    CREATE TABLE `iso_timestamp` (
                    `v1` varchar(30) DEFAULT NULL,
                    `t2` timestamp DEFAULT '20150101000000'
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

                    -- loading data with tz (timestamp)
                    SET @OLD_TIME_ZONE=@@TIME_ZONE;
                    SET TIME_ZONE='+00:00';
                    load data infile '/tmp/iso8610.txt' into table iso_timestamp (@var) set v1=@var, t2 = left(replace(replace(@var,'T',''),'Z','0000'),14);
                    SET TIME_ZONE=@OLD_TIME_ZONE;

                    結果はこんな感じです。

                    mysql> select * from iso_datetime;
                    +-------------------------+---------------------+
                    | v1 | d2 |
                    +-------------------------+---------------------+
                    | 20150224T10Z | 2015-02-24 19:00:00 |
                    | 20150224T1045Z | 2015-02-24 19:45:00 |
                    | 20150224T104623Z | 2015-02-24 19:46:23 |
                    | 20150224T104623.283576Z | 2015-02-24 19:46:23 |
                    +-------------------------+---------------------+
                    4 rows in set (0.00 sec)

                    mysql> select * from iso_timestamp;
                    +-------------------------+---------------------+
                    | v1 | t2 |
                    +-------------------------+---------------------+
                    | 20150224T10Z | 2015-02-24 19:00:00 |
                    | 20150224T1045Z | 2015-02-24 19:45:00 |
                    | 20150224T104623Z | 2015-02-24 19:46:23 |
                    | 20150224T104623.283576Z | 2015-02-24 19:46:23 |
                    +-------------------------+---------------------+
                    4 rows in set (0.00 sec)

                    mysql> SET @OLD_TIME_ZONE=@@TIME_ZONE;
                    Query OK, 0 rows affected (0.00 sec)

                    mysql> SET TIME_ZONE='+00:00';
                    Query OK, 0 rows affected (0.00 sec)

                    mysql> select * from iso_datetime;
                    +-------------------------+---------------------+
                    | v1 | d2 |
                    +-------------------------+---------------------+
                    | 20150224T10Z | 2015-02-24 19:00:00 |
                    | 20150224T1045Z | 2015-02-24 19:45:00 |
                    | 20150224T104623Z | 2015-02-24 19:46:23 |
                    | 20150224T104623.283576Z | 2015-02-24 19:46:23 |
                    +-------------------------+---------------------+
                    4 rows in set (0.00 sec)

                    mysql> select * from iso_timestamp;
                    +-------------------------+---------------------+
                    | v1 | t2 |
                    +-------------------------+---------------------+
                    | 20150224T10Z | 2015-02-24 10:00:00 |
                    | 20150224T1045Z | 2015-02-24 10:45:00 |
                    | 20150224T104623Z | 2015-02-24 10:46:23 |
                    | 20150224T104623.283576Z | 2015-02-24 10:46:23 |
                    +-------------------------+---------------------+
                    4 rows in set (0.00 sec)

                    もっといろいろな条件に対応するためには、関数を作ったりする必要がありますが、
                    とんちでまにあう場合には、便利ですLOAD DATA INFILE。関数の例は以下。

                    mjc / iso8601_to_datetime.sql

                    JUGEMテーマ:コンピュータ


                    | meijik | MySQL | 23:05 | comments(0) | trackbacks(0) | - | - |
                    MySQLのGIS拡張2014
                    0
                      本エントリはMySQL Casual Advent Calendarの第11日目です。

                      MySQLの地理情報システム(GIS:Geographic Information System)拡張は4.1の時代から存在します。
                      以前4.1/5.0のMBR*について以下のブログを書きました。

                      MySQLのGIS拡張

                      そして、5.1のBetaについて以下のブログを書きました。

                      MySQLのGIS拡張(2) 5.1の拡張

                      しかし、残念ながら5.1でのGIS拡張は本体にはマージされませんでした。

                      MySQL 5.6からはちゃんと動作するContainsがST_Containsとして実装されています。
                      以前のブログエントリと同様のコマンドをST_*について実行するとST_Contains(@g1,@g3)が0を返すことがわかります。

                      [実行コマンド]
                      SET @g1 = GeomFromText('Polygon((0 2,1 0,2 2,0 2))');
                      SET @g2 = GeomFromText('Point(1 1)');
                      SET @g3 = GeomFromText('Point(2 1)');
                      SELECT MBRContains(@g1,@g2);
                      SELECT MBRContains(@g1,@g3);
                      SELECT Contains(@g1,@g2);
                      SELECT Contains(@g1,@g3);
                      SELECT ST_Contains(@g1,@g2);
                      SELECT ST_Contains(@g1,@g3);

                      [実行結果]
                      mysql> SET @g1 = GeomFromText('Polygon((0 2,1 0,2 2,0 2))')
                      Query OK, 0 rows affected (0.00 sec)

                      mysql> SET @g2 = GeomFromText('Point(1 1)');
                      Query OK, 0 rows affected (0.01 sec)

                      mysql> SET @g3 = GeomFromText('Point(2 1)');
                      Query OK, 0 rows affected (0.00 sec)

                      mysql> SELECT MBRContains(@g1,@g2);
                      +----------------------+
                      | MBRContains(@g1,@g2) |
                      +----------------------+
                      | 1 |
                      +----------------------+
                      1 row in set (0.00 sec)

                      mysql> SELECT MBRContains(@g1,@g3);
                      +----------------------+
                      | MBRContains(@g1,@g3) |
                      +----------------------+
                      | 1 |
                      +----------------------+
                      1 row in set (0.00 sec)

                      mysql> SELECT Contains(@g1,@g2);
                      +-------------------+
                      | Contains(@g1,@g2) |
                      +-------------------+
                      | 1 |
                      +-------------------+
                      1 row in set (0.00 sec)

                      mysql> SELECT Contains(@g1,@g3);
                      +-------------------+
                      | Contains(@g1,@g3) |
                      +-------------------+
                      | 1 |
                      +-------------------+
                      1 row in set (0.00 sec)

                      mysql> SELECT ST_Contains(@g1,@g2);
                      +----------------------+
                      | ST_Contains(@g1,@g2) |
                      +----------------------+
                      | 1 |
                      +----------------------+
                      1 row in set (0.00 sec)

                      mysql> SELECT ST_Contains(@g1,@g3);
                      +----------------------+
                      | ST_Contains(@g1,@g3) |
                      +----------------------+
                      | 0 |
                      +----------------------+
                      1 row in set (0.00 sec)

                      MySQL 5.7からはさらに拡張されています。が、ここでちと時間切れ。
                      続きは後ほど本エントリを更新するか、別エントリにて記述することにします。どうもすみません。。。。

                      JUGEMテーマ:コンピュータ
                      | meijik | MySQL | 05:07 | comments(0) | trackbacks(0) | - | - |
                      1234567
                      891011121314
                      15161718192021
                      22232425262728
                      2930     
                      << September 2019 >>
                      + 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
                      • [ミュージシャンに聴かせたい・弾かせたい!] (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)
                      • Windows上でのMySQLビルド方法
                        kinoyasu (03/31)
                      + RECENT TRACKBACK
                      + CATEGORIES
                      + ARCHIVES
                      + MOBILE
                      qrcode
                      + LINKS
                      + PROFILE