Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL8.0にするなら、共通テーブル式を使いましょう!(WITH 〜 AS)

MySQL8.0にするなら、共通テーブル式(CTE: WITH句)を使いましょう!

MySQL8.0を使いたい理由は、CTEに対応したからなんです!!

そもそも、MySQL 8.0を使いたかった理由って、共通テーブル式が実装されてからなんです! 私自身は、 IBM DB2でかなりお世話になりました。やがて年は過ぎ、MySQLしか触らなくなったのですが、WITH句ってどんなRDBMSにも付いているものだとばかり思っていたのですが、無い。。MySQLは無かったんですよね(汗) おかげさまで、サブクエリーのサブクエリをごりごり書く生活に慣れてしまいました。

今回は、このCTEを利用して、どれだけ、SQLが綺麗になるのかを見せたいと思います!

テーブル

今回、CTEとして利用するテーブル定義です

CREATE TABLE `loglog` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `action` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

続きを読む

MySQL mysqdumpで、Error 1412: Table definition has changed

mysqldumpする際にこんなエラーを食らったことはありませんか?

現在、 MySQL5.7のデータベースをMySQL 8.0に絶賛リストア中なのですが、mysqldumpが終わりそうなところでこのエラーがコンソールに出てきて疲弊してきました(汗)

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table table1 at row: 0

続きを読む

MySQL8.0 td-agent mysqlが起動できない場合の対処方法 ibmysqlclient.so.20: cannot open

こんなエラーがでてtd-agentが起動できない現象が、MySQL8.0環境下のサーバで発生しました。

libmysqlclient.so.20: cannot open shared object file: No such file or directory

今回、MySQL5.7がインストールしているサーバをMySQL8.0に入れ替えたのですが、そのタイミングでtd-agentが起動しなくなった備忘録です

続きを読む

MySQL 8.0にアップデート!注意すべき点

MySQL 8.0 バージョンアップしたら、速度が2倍向上!便利な関数が使える!!等、色々なメリットがあります。 ・・が、 通常のサービスでデータベースがメジャーバージョンアップしたからといってサービスを停止して、バージョンアップってしないかなー?って思ってます。自分がやるのであれば、別のサーバ立てて旧バージョンからレプリケーション!可能な限り、ダウンタイムを減らしたいのでこの手法一点で進めます

ただ、今回は、かなり苦戦しています

次回、同じ状態にならないように、備忘録に残しておきます。

続きを読む

MySQL 異なるバージョン間でのmysqldumpおよびリストアについて

異なるバージョン、もしくは、異なる サーバ間で、データベースのdump (mysqldump) および restore リストアを実施した時に、リストア先にデータベースもテーブルもないはずなのに、キー重複(Duplicate entry)が起きたりしたことはありませんか? え?、なにもないはずなのに。。。って。オカルトですよね。

この原因って、dump時の文字コードが原因している場合が多いですって話です。。。

プログラマのための文字コード技術入門 (WEB+DB PRESS plus) (WEB+DB PRESS plusシリーズ)

プログラマのための文字コード技術入門 (WEB+DB PRESS plus) (WEB+DB PRESS plusシリーズ)

続きを読む

MySQL8.0環境でのmy.cnfの設定について

バージョン変わるたびにいつも混乱する。my.cnfの設定、、今回ももれなく混乱しました。 細かいところの説明は抜きにして、これで前バージョンの必要な設定は継承したかな?ってレベルまで作り込んでみました。

f:id:hit10231023:20180309104332j:plain

my.cnf (MySQL 8.0用)

Innodbのチューニングがオートマチックっぽくなったのはうれしいですね。あと、デフォルトがONになっているものも多くなり、わざわざ my.cnfに設定する項目もすくなくなったかな?といった感想です。本当によくはなっているんですよね!! 初回の設定で大変ってだけで。。

てなわけで、コピペ用のmy.cnfつけておきますね

続きを読む

10分でMySQL8.0をamazon linuxにyumでインストールする!!

amazon linuxにMySQL8.0をインストール!

知ってはいたけど、なかなか、触る機会がなく、かつ、インストールすらしていない状態だったので、今回、インストールの手順をメモしました。 MySQL8.0は、2018/4/19 にリリースされ、以下の機能が更新、もしくは新機能として実装されました。

MySQL :: MySQL 8.0: MySQL 5.7よりも最大2倍高速

私個人として。地味なところではありますが、降順(Descending)インデックスの対応や、Window関数関数などが何気にうれしいです。

細かい部分はあとで検証するとして、今回は、amazon linuxにこの、MySQL 8.0をインストールしたメモを書いていこうと思います。

f:id:hit10231023:20180309104332j:plain

続きを読む

MySQL データベース中に特定をカラム名を含むテーブルを検索する方法

例えば、User_Idというカラム名を含むテーブル名に何か変更をかけないといけないという運用が発生した場合、テーブル定義書に書かれているドキュメントを確認したりしますか?いやいや、とっても効率悪いので、

information_schema.columns

を使いましょうよ!って話です

ドラことば心に響くドラえもん名言集

ドラことば心に響くドラえもん名言集

f:id:hit10231023:20180309104332j:plain

続きを読む

MySQL チューニングのど本命!索引チューニング

MySQLのチューニングで一番効果があるものは、パラメータのチューニングでもなく、はたまた、マシンのスペックでもなく、

索引のチューニング

です。私自身、サーバをスケールアウトしてみたり、パラメータをいじってみたりしてみましたが大した効果はないんですよね。やはり、目で見て効果が高いのは、やはり索引を適切に設定することでした。

f:id:hit10231023:20180309104332j:plain

今回、MySQLオプティマイザがぁ。。とか、オンメモリーにするためにはーとかの話は抜きで、何を勘所にして、どのように索引チューニングしていくかというのを中心に書いていきます

続きを読む

超便利!MySQL テーブル定義のデフォルト値で関数をつかっちゃおう

いままでなんで、きずかなかったのだろう。。

例えば、こんなテーブルがあったとして

DROP TABLE IF EXISTS test_tab1 ;

CREATE TABLE `test_tab1` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Numeric_only_date_time` varchar(8) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

idはオートインクリメント値

numeric_only_date_timeは、現在の日付を、数字のみに表したもの(20180511みたいな)

という形式でデータを格納したい場合、みなさまはどうしますか?

f:id:hit10231023:20180309104332j:plain

自分でしたら

INSERT INTO test_tab1
  (numeric_only_date_time) VALUES (DATE_FORMAT(CURRENT_TIMESTAMP,'%Y%m%d')) ;

で、まずINSERTすると思います。

結果は以下のような感じになります

SELECT * FROM test_tab1 ;
+----+---------------------+------------------------+
| Id | CreatedAt           | Numeric_only_date_time |
+----+---------------------+------------------------+
|  1 | 2018-05-11 12:50:16 | 20180511               |
+----+---------------------+------------------------+

今回シンプルなデータなので、あまりメリットは感じないかもしれませんが、仮に、numeric_only_date_timeのデフォルト(現在日付)にしたい場合で、もっとシンプルにINSERT文を発行したい時はどうしましょう?実はそれが簡単にできてしまうのです!!

続きを読む

SQLで変数を使う

他のDBはよくしらないですが、MySQL系のSQLSQL文中にユーザー変数を含めることができます。この手法を理解すると、洗練されたSQLを記述することができます。 SET文を用いる方法と、SELECT ~ INTO を用いる2種類の方法があります

f:id:hit10231023:20180309104332j:plain

続きを読む

MySQL ストアドプロシージャのHANDLERの順番には注意

ストアドプロシージャにて、トランザクションをかける場合、HANDLERの順番には注意しましょう!という話です。

Logicool ロジクール Bluetoothマウス M558

Logicool ロジクール Bluetoothマウス M558

HANDLERだけ書くと、別途MySQLがもっているHANDLER構文も入ってしまいますが、例外処理(DECLARE 〜 HANDLER)の話を今回はします。

f:id:hit10231023:20180309104332j:plain

先に正しい順番を書きますね。

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      SELECT _sqlstate, _errno, _text;
      ROLLBACK;
    END;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;

    DECLARE CONTINUE HANDLER FOR SQLWARNING
    BEGIN
      GET DIAGNOSTICS CONDITION 1 _sqlstate = RETURNED_SQLSTATE, _errno = MYSQL_ERRNO, _text = MESSAGE_TEXT;
    END;

続きを読む

MySQL ストアドプロシージャで処理を途中で抜け出す方法って知ってる?

私が知らなかっただけかもしれせんが、処理を途中で抜けることってストアドプロシージャって出来ないとばかり思っていたのですが、できるんですね汗

今まで、ストアドでこんなIF文のネスティングで書いてました。可読性悪いし、バグを誘発するしで何か良い手はないのかな?って思ってました

今までのダメな書き方

CREATE PROCEDURE `sp_test_proc`(
  IN _mode tinyint, -- (0: Normal 1: force delete 2: Batch )
  INOUT _value varchar(32) ,
  OUT _result_code int(11)
)
BEGIN

    BEGIN

       IF _mode = 1 OR _mode = 2 OR _mode = 3 THEN
          IF _value='test' THEN
              SET _value = CONCAT(_value, '-TEST') ;  
          ELSE            
                SET _result_code = 8888 ;
          END IF ;
       ELSE
           SET _result_code = 9999 ;
       END IF ;

〜以降略
    END ;
END $$

あ。。処理何やっているの?とか、コードの書き方が変とかの指摘はなしで。。なんて、IF のネスティングを延々と書いてました。理由は前述の通り、処理を途中で抜ける方法を知らなかったからです。。

よくよく考えたら、おかしいなって調べていたら当たり前のように、そういうやり方がありました。いやあ、はじめて、BEGIN - END の意味を知った感じでお恥ずかしいです。。

f:id:hit10231023:20180309104332j:plain

続きを読む

たまにしか使わないから忘れるMySQLコマンド集メモ

たまーにしか使わないのに、その必要が急に出てきて、調べる時間が勿体無いので、ざっくりここにMySQLDDLおよびSQLをメモしていきます。

f:id:hit10231023:20180309104332j:plain

  • テーブル名変更
  • プライマリーキー
  • プライマリーキーの削除
  • 索引
  • キー削除
  • カラムの追加
  • カラム名変更
  • カラム定義(属性)変更
  • カラム削除
  • Jsonの要素を仮想カラム化する(MySQL 5.7 - )
  • enum型の定義
  • enum型への値追加の(ALTER TABLE)
  • テーブルの名前変更
  • テーブル定義をコピーする
    • 定義は完全にコピーされるがデータはコピーされてない
    • データを含めて、コピーする
  • 定義のデータも完全にコピーしたい
  • INSERT SELECT JOIN
  • INSERT SELECT JOINで差分のみINSERTする(DIFF INSERT)
  • JOIN UPDATE
  • 集計結果を横倒しする
  • ストアドプロシージャ、ストアドファンクションの雛形
  • mysqlのバックアップ(mysqldump)
  • mysql障害時のリカバリ方法(ロールフォワード回復)
  • レコードの更新日を自動的にセットする
  • グループ毎に連番を振る
続きを読む

SQLでshellを作成するサンプル?

とあるテーブルの内容を元にして、SQLで、shellファイルをつくって実行しちゃおうってのを無理やりつくってみました。

テーブル定義

CREATE TABLE `files` (
  `id` int(11) NOT NULL DEFAULT '0',
  `file_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

primary keyはidで、urlは、例えば、"abcdefg.jpg"ってのが入っていたとします。内容的にはこんな感じです

mysql> SELECT * FROM files LIMIT 3;
+----+-----------+
| id | file_name |
+----+-----------+
|  1 | a.jpg     |
|  2 | b.jpg     |
|  3 | c.jpg     |
+----+-----------+

今回、このfile_nameの中にあるファイル名を利用して、

#!/bin/bash
wget http://test.test.local/download/a.jpg
・
・
exit 0

というようなshellファイルを作成してみたいと思います

f:id:hit10231023:20180309104332j:plain f:id:hit10231023:20180308234003p:plain

続きを読む