MySQL8.0 新機能 WITH句、共通テーブル式(CTE)のSQLの書き方教えます！

MySQL8.0にするなら、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

MySQL8 インビジブルインデックス（INVISIBLE INDEX）の勧め

その索引使われていますか？

MySQLで長年運用しているうちに、パフォーマンスチューニングが必要になって、ほとんどが、チューニング効果の高い、索引チューニング（インデックスチューニング）をしていきますよね。そんでインデックスをぺたぺた張っているうちに、インデクスだらけになり、気付いた時には、テーブルの全カラムに対して索引が設定されてたなんてことないですか？（自分だけかも）

その索引、本当に使われてますか？ 使われていない索引が大量に存在すると、データの更新パフォーマンスが低下します。的確な答えではないのでですが、インデックスはある意味、インデックスのデータを含めたテーブルが一個できるといった認識をもってくれれば、使った数だけ、テーブルが増えていくって感覚をもってくれればイメージがつきやすいかな？って思います。

ということで、MySQL8.0で実装された、インビジブルインデックス（INVISIBLE INDEX）の活用方法について書いていきたいと思います。

MySQLでの文字列の日付から日付形式に変換する方法（STR_TO_DATE)

毎回忘れる、MYSQLの文字列->日付変換についてメモしました。例えば、rssのフィード内によくある、publishedとかとか。。これを日付/時刻型に変換するにはどうすればいいか？なんていうのを例につらずらと書いていきます。

<pubDate>Sun, 29 Jul 2018 21:00:00 +0900</pubDate>

python3関連インストールもろもろメモ

あくまでも自分だけのメモですが、これだけは、いや、あくまでも私的な話ですが必須だろうと思うものしか書いてません。 今後他にも色々出てきそうですが、その時はその時で随時追記していくつもりです。

OSは、CENTOS 6x をベースに記載しましたが、まあ、UNIX系だとみんなおんなじなんじゃないかな？って思います。

MySQLの変数をSQLのLIKEで活用する！

どうでも良い話ですが、設定した変数をLIKE検索で使う場合、%%ってどうすればいいの？というのがわからなかったのでメモしました。

少なくともこの書き方ではない模様

SET @ARTIST='Michael' ;
SELECT artist_name FROM artist_master
WHERE
  artist_name LIKE @ARTIST
;

さてさて正解は。。

超便利！！ MySQL 8 window関数でグループ連番をふる

MySQL 8で（やっと）実装された、window関数が超便利です！！ postgreSQLとほぼ同じことができます。

window関数とは

結果レコードを部分的に切り出した領域に集約関数をかけることができる、ものすごい便利な関数です。MySQL8.0で（ようやく）実装されました。 単純なところでは、例えばWindow関数がないMySQLのバージョンでとある規則に乗っ取った連番を振りたいなんていう要求があった場合どうします？

その答えが、以前こちらに掲載したものになるのですが、これがwindow関数で行うとすっごく簡単にかけちゃいます。

hit.hateblo.jp

あ、これは、MySQL 8.0以前のバージョンでの連番手法ですね。

MySQL 8 での連番の振り方

では、MySQL8 + window関数で、どのようにグループ連番をつけるのか、やってみたいと思います 今回試すテーブルは前回と同様です。

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

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

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

[Amazonブランド]Presto! マスク ふつうサイズ 200枚(50枚×4パック) PM2.5対応

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

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

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

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

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

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

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をインストールしたメモを書いていこうと思います。

amazon linux2 にMySQL8をインストールする場合は、こちらをご覧ください！

hit.hateblo.jp

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

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

information_schema.columns

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

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

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

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

索引のチューニング

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

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

MySQL即効クエリチューニング ThinkIT Books

超便利！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みたいな)

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

自分でしたら

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文を発行したい時はどうしましょう？実はそれが簡単にできてしまうのです！！