こんなに違うよ MySQLとPostgreSQL /

2024年6月22日に開催された「第14回　関西DB勉強会 」での、
『こんなに違うよ MySQLとPostgreSQL
～MySQLとPostgreSQLのニッチな違いを語る～』
の発表資料です。

https://kansaidbstudy.connpass.com/event/316348/

Transcript

1. こんなに違うよ MySQLとPostgreSQL ～MySQLとPostgreSQLのニッチな違いを語る～ 関西DB勉強会 @ 大阪 2024/06/22 有限会社アートライ 日本MySQLユーザ会 坂井 恵 ( @sakaik )
2. 自己紹介 坂井 恵 @sakaik • 日本MySQLユーザ会副代表 • OSGeo.JP 運営委員 • (有)アートライ代表取締役 最近は「爆速DB powered by PG-Strom」の検証・普及に関わっています。
3. 本が出ました MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ yoku0825，北川健太郎，tom__bo，坂井恵 著 技術評論社 2024年5月22日発売 B5変形判／232ページ 定価3,080円
4. ThinkITさんに 記事が出ました 結構わかりやすいと思うので、 このあたりの情報に興味を持ってい る周りの皆さんにもお勧めしてくだ さい！ 来週のOSC2024北海道で、たっぷり お伝えします。来週札幌でお待ちし ています！
5. MySQLユーザ会の人として 今日お伝えしたいこと • MySQLにも「LTS (Long Term Support)」の概念がやってきまし た！ • 2024年4月にリリースされた「MySQL 8.4.0 LTS」 • MySQL 8.4-LTSシリーズとなり、8年間のサポートです • このシリーズ内では、互換性を損なう修正は加えられないこと になっています（つまり、安心してマイナーバージョンを最新 に上げることができる！！！） • ・・・当たり前の事だと思うでしょ？ MySQL 8.0シリーズとい うのがあってですね....（続きは来週のOSC北海道で） MySQL 発展よん
6. MySQL推しの個人の人として 今日お伝えしたいこと • 野心的な修正や新機能が加わるのは MySQL の Innovation Release • 追っていて面白いのは絶対に Innovation Release • 非互換の変更も含めてわくわくできるのは Innovation Release • 「次のLTS」が見える Innovation Release →みんな、Innovation Releaseを触っていこう！ 2024年7月にMySQL 9.0.0 が出る予定 （その後、10月に 9.1.0、2025年1月に 9.2.0、、、）
7. こんなに違うよ MySQLとPostgreSQL
8. MySQLとPostgreSQLは違うのか？ OSCのブースにて。 来場者「DBMSのブース2つ並んでいるけど何が違うんですか？」 私「だいたい一緒ですよ。」 → とは言うものの、ほんとは結構違うってことを、皆さんは知って いますよね。さて何が違うのでしょう。
9. 今日のおはなし MySQL と PostgreSQL の、主に 動作面から見た違い、 こんなに違いがあるんだよ、 という「役に立たない」トリビア？を 最初に小ネタをいくつか紹介し、 そのあとにただのネタを紹介していきます。
10. 違いポイント1：ポスグレは長い • 原稿を書くときなど、 • PostgreSQL は場所を取る • MySQLはコンパクト 10対5 。 つまりMySQLのほうが2倍効率が良い。 • ただしクライアントになると形勢逆転 • psql (非常にコンパクト) • mysql (こもじごもじ) こんなに違うよ MySQLとPostgreSQL 0 2.5 5 7.5 10 PostgreSQL MySQL 文字数効率 x2 !!
11. 違いポイント2：ポスグレは間違われやすい 写真提供：寺内大輝さん こんなに違うよ MySQLとPostgreSQL 先週の総会にて：
12. 違いポイント2：ポスグレは間違われやすい 写真提供：寺内大輝さん こんなに違うよ MySQLとPostgreSQL 先週の総会にて： 実は初めてではない！ OSC 2019 Niigata → ※ぜひ、間違われにくいMySQLをどうぞ
13. 違いポイント3：「¥コマンド」が便利 • コマンドラインクライアント上での操作 • データベース一覧 の確認 • テーブル一覧 の確認 どうやりますか。 こんなに違うよ MySQLとPostgreSQL • PostgreSQL • ¥l 細かいオプションはすぐ忘れるけど • ¥d とりあえず "+" をつけるともう少し詳細な情報が出る • MySQL • SHOW DATABASES 覚えやすいメリットの半面、慣れると • SHOW TABLES 一旦Postgresに慣れると、こんなコマンド打ちたくない！ → MySQLにも ¥l ¥d 実装したらいいんじゃね？
14. 違いポイント4：スキーマ PostgreSQL こんなに違うよ MySQLとPostgreSQL インスタンス データベース データベース スキーマ (public) 田田田田 スキーマ 田田田 スキーマ (public) 田田田 データベース スキーマ (public) インスタンス データベース(スキーマ) 田田田田 データベース 田田田 データベース MySQL
15. 違いポイント5：演算子 ^ 解説： 1010 XOR 0011 = 1001 こんなに違うよ MySQLとPostgreSQL mysql> SELECT 10^3; +------+ | 10^3 | +------+ | 9 | +------+ db=# SELECT 10^3; ?column? ---------- 1000 お題： 10の3乗を求めたい PostgreSQL MySQL
16. 違いポイント5：演算子 ^ こんなに違うよ MySQLとPostgreSQL mysql> SELECT POW(10,3); +-----------+ | POW(10,3) | +-----------+ | 1000 | +-----------+ お題： 10の3乗を求めたい MySQL mysql> SELECT 10^3; +------+ | 10^3 | +------+ | 9 | +------+
17. 違いポイント6：INSERT文 PostgreSQLは、こんなINSERT文が OK。 と作成したテーブルにINSERT。 → 値が与えられていない列cにはNULLが入る。 ※標準SQLでは、INSERT時に与えるカラムリスト（省略した場合はテーブ ルの全カラム）と値のリストの数は一致しなければならない。 こんなに違うよ MySQLとPostgreSQL CREATE TABLE t1 (a int, b int, c int); INSERT INTO t1 VALUES(4,5); INSERT INTO t1(a,b,c) VALUES(4,5); ←さすがにこれはエラー
18. 違いポイント6：INSERT文 https://twitter.com/sakaik/status/1776085795520368720 こんなに違うよ MySQLとPostgreSQL 2024年4月5日 @sakaik えええっ！？ PostgreSQLってこれOKなの...。驚 いた。エラーにしてほしい。 --- CREATE TABLE t1 (a int, b int, c int); INSERT INTO t1 VALUES(4,5); Twitter(X) →珍しくバズ （約10万インプレ） （私のツイートなんて普段は、 インプレ数百行けば「今回はバズったな(当社比) 」と思うくらいです）
19. 違いポイント6：INSERT文 こんなに違うよ MySQLとPostgreSQL • バズると良いこともある（色々教えてもらえる） https://x.com/nsomeyann/status/1776533281233920271
20. 違いポイント7：部分指定DISTINCT • DISTINCT ON 句 こんなに違うよ MySQLとPostgreSQL db=# SELECT * FROM t2; id | val1 | val2 | val3 ----+------+------+------ 1 | aaa | 10 | 21 2 | bbb | 12 | 22 3 | aaa | 14 | 21 4 | ccc | 16 | 24 5 | bbb | 17 | 25 6 | aaa | 10 | 21 PostgreSQL のみ https://www.postgresql.jp/document/16/html/sql-select.html#SQL-DISTINCT db=# SELECT DISTINCT val1,val2,val3 FROM t2; val1 | val2 | val3 ------+------+------ aaa | 10 | 21 bbb | 17 | 25 ccc | 16 | 24 aaa | 14 | 21 bbb | 12 | 22 db=# SELECT DISTINCT ON (val1) val1,val2,val3 FROM t2; val1 | val2 | val3 ------+------+------ aaa | 10 | 21 bbb | 12 | 22 ccc | 16 | 24 • 指定したカラムの値がユニークになるように良きに計らってくれる機能
21. 違いポイント7：部分指定DISTINCT • 先ほどの例だと意味がわかりませんが、以下 のようなケースで便利 こんなに違うよ MySQLとPostgreSQL PostgreSQL のみ db=# SELECT DISTINCT ON (user_id) -# user_id, name, phone, email -# FROM sales; でも強烈におすすめしません！！！
22. 違いポイント7：部分指定DISTINCT 古のMySQLでは DISTINCT ON と似た動作をするものとして、このように書けました。 (name, phone, email は良きに計らってくれる) こんなに違うよ MySQLとPostgreSQL db=# SELECT DISTINCT ON (user_id) -# user_id, name, phone, email -# FROM sales; db=# SELECT user_id, MAX(name), MAX(phone), MAX(email) -# FROM sales -# GROUP BY user_id; mysql> SELECT user_id, name, phone, email -> FROM sales -> GROUP BY user_id; SQLの動作としては、滅茶苦茶超絶スーパーウルトラ イケてなかったので、 MySQL 5.7 からはデフォルト動作では なくなりました！ ﾔｯﾀｰ (互換性のために SQL_MODE の ONLY_FULL_GROUP_BY でウルトライケてない 動作に変更することはできます） ※余談： イケてない動作の時の「良きに計らいかた」通りの結果を得るために MAX()の代 わりに ANY_VALUE() という集約関数が追加されました こう書こう チョットむかしばなし
23. 違いポイント8：なくなくない？演算子 PostgreSQL : IS NOT DISTINCT FROM MySQL： <=> こんなに違うよ MySQLとPostgreSQL t1 +------+------+ | id | val | +------+------+ | 1 | 12 | | 2 | 13 | | 3 | NULL | | 4 | 14 | +------+------+ mysql> SELECT * FROM t1 WHERE val<>13 OR val IS NULL; +------+------+ | id | val | +------+------+ | 1 | 12 | | 3 | NULL | | 4 | 14 | +------+------+ db=# SELECT * FROM t1 WHERE val<>13 OR val IS NULL; id | val ----+----- 1 | 12 3 | 4 | 14 PostgreSQL MySQL
24. 違いポイント8：なくなくない？演算子 PostgreSQL : IS NOT DISTINCT FROM MySQL: <=> こんなに違うよ MySQLとPostgreSQL t1 +------+------+ | id | val | +------+------+ | 1 | 12 | | 2 | 13 | | 3 | NULL | | 4 | 14 | +------+------+ mysql> SELECT * FROM t1 WHERE !(val<=>13); +------+------+ | id | val | +------+------+ | 1 | 12 | | 3 | NULL | | 4 | 14 | +------+------+ db=# SELECT * FROM t1 WHERE val IS DISTINCT FROM 13; id | val ----+----- 1 | 12 3 | 4 | 14 PostgreSQL MySQL
25. 違いポイント8：なくなくない？演算子 PostgreSQL : IS NOT DISTINCT FROM MySQL: <=> こんなに違うよ MySQLとPostgreSQL t1 +------+------+ | id | val | +------+------+ | 1 | 12 | | 2 | 13 | | 3 | NULL | | 4 | 14 | +------+------+ mysql> SELECT * FROM t1 WHERE val<=>13; +------+------+ | id | val | +------+------+ | 2 | 13 | +------+------+ db=# SELECT * FROM t1 WHERE val IS NOT DISTINCT FROM 13; id | val ----+----- 2 | 13 mysql> SELECT * FROM t1 WHERE val<=>null; +------+------+ | id | val | +------+------+ | 3 | NULL | +------+------+ sakaitest_nvme5=# SELECT * FROM t1 WHERE val IS NOT DISTINCT FROM null; id | val ----+----- 3 |
26. 違わない面白ポイント：ぎゅうぎゅう SELECT (空白) * (空白) FROM ～; じゃないと動かないと思っていませんか。 こんなに違うよ MySQLとPostgreSQL mysql> SELECT*FROM t1; db=# SELECT*FROM t1; mysql> SELECT'test'FROM dual; +------+ | test | +------+ | test | +------+ db=# SELECT'test'FROM t1; ?column? ---------- test test : これはOK！（空白なし） これもOK！（空白なし） ※空白抜きをおすすめするものではありません:-)
27. MySQL PostgreSQL 公式 日本の ユーザ会 違いポイント番外編：マスコット こんなに違うよ MySQLとPostgreSQL
28. これからもよろしくお願いします