今度のプロジェクトのDB構成を考えていて、当初からMySQLという選択肢が頭を離れない。

これまでのプロジェクトを見ると、PostgreSQLで9割を占める。Oracleもあったけど、ライセンス高いから省略。一方、MySQLは触ったことがあるという程度。チューニングやSQL方言など、慣れないことが多いだけに、いつもその採用には二の足を踏んできた。

ただ、mixiやはてなといった大規模サイトでのMySQLの実績を横目に見て、それなりの規模のアプリケーションならMySQLを真剣に考える必要がありそう。こんだけ流行しているわけだから、ちゃんと触ってノウハウ蓄積したいという思いもあるし。

PostgreSQLに比べて、MySQLが良いなと思っているところ。

• レプリケーション機能が標準で付いている。
• Federated ストレージエンジンが使える。

日本語対応とか、スレッドとプロセスの違いとか、細かいところで違いはあるんだけど、そこはさておき。要は、スケーラビリティにおいてMySQLが優れているように思えて、気になっている。

以前は両者の性能差がもっとあったので、そんなに悩むことは無かったけど、もはやエンタープライズだからPostgreSQL、WebサービスだからMySQLっていう安易な選択もしちゃいけないんだと思う。なんなら両方が合体しちゃえば良いのに、とかトンチンカンなことを言ってしまいそうだ。

こんなわけでMySQLに後ろ髪ひかれる思いなんだけど、きっと今回はPostgreSQLになるんだろう。いまの自分の状況では、拡張性の要件を除いて、まだPostgreSQLに優位性がある。地道に検証を続けて、近いうちにMySQLで組めるようにもっていけるといいな。

postgresqlでスキーマを変更しようと、SQLを流したらエラー続出。

ERROR:  invalid byte sequence for encoding "UTF8": 0xba
ERROR:  invalid byte sequence for encoding "UTF8": 0xb4
ERROR:  invalid byte sequence for encoding "UTF8": 0xbc

調べてみると、このSQLインジェクションの脆弱性修正の影響のようだ。無効なマルチバイトは蹴られる、と。

ということで、PGCLIENTENCODINGを変更。

$ export PGCLIENTENCODING=SQL_ASCII

これでエラー無く流れた。おー。

普段はPGAdminなどのツールを使っているので気付かないけど、大量のDBのスキーマ変更しようと夜間バッチ流したりすると、こういうステキな問題に直面する。ぐあー、リハーサルで良かった。

SQLでカラムを超えて文字列を結合する場合は、|| 演算子を使えば良いけれども、レコードを超えて文字列を結合する場合はどうしたもんか。

イメージとしては、数値をSUMするような感じで、文字列を縦結合していく感じ。

しかし、sum(integer)はあっても、sum(text)は見当たらない。ということで、自分で作ってみた。初めてのオリジナル集約関数。わーい。(PostgreSQL限定)

続きを読む "SQLで文字列をsumするには"

PostgreSQL で文字列を連結させるには、|| 演算子を使うのだけど、これが一癖あって、連結するカラムの値が null だと、連結結果も null になってしまう。今日はじめて知った。

 sei | mei
-----+-----
 姓  | 名
 姓  | null


# select sei || mei as name from table;
 name 
------
 姓名 
 null 

null にしないためには、COALESCE で空文字を連結するように書いてあげれば大丈夫。

# select sei || COALESCE(mei, '') as name from table;
 name 
------
 姓名 
 姓 

こういう動きを見ると、|| 演算子が単純に文字を連結するだけの演算子のように思えないんだけど、SQL92の仕様なのか、それともPostgreSQLの方言なのかなあ。

だいぶ MySQL に苦戦中。

コマンドラインでの操作が、全然覚えられない。というか、気付くと「psql」と打ってしまっている自分にガッカリ。染み付きすぎ。欧米か。(言いたかっただけ)

MySQLでデータベースを切替える方法メモ。

(新しいDBを作成)
# mysqladmin -u root create new_db
(古いDBのダンプ)
# mysqldump -u root old_db >old_db.dump
(新しいDBにリストア)
# mysql -u root new_db < old_db.dump
(古いDBを削除)
# mysqladmin -u root drop old_db

ちなみに、バックアップを取りたい場合は、直接gzipにかませて圧縮すれば容量節約で良い感じ。mysqldumpのオプションでそういうのがあるわけじゃないのかあ…。

# mysqldump -u root db | gzip > db.dump

MySQLのお作法を習得するには、まだ時間がかかりそうだなあ。うーむ。

急遽 MySQL を使うことになったので、ざっくりインストール。

普段は PostgreSQL 使いで、MySQL は3回くらいしか触ったことがない。とにかく慣れないにもんで、わけも分からないまま、Google 先生に教えを請いながら何とか動作するに至る。人生いろいろ、DBもいろいろである。ふぅ。

今回は MySQL4.1系を採用。5.0系の方が良いんじゃないのかなーと思ったけど、前任SEの推奨環境が MySQL4.1系になっていたので、そこは長いものに巻かれていきます。基本、流れ重視です。

ということで、手順をメモメモ。

続きを読む "MySQLのインストール"

プロジェクトも終盤だというのに、ここに来てテーブル設計に凡ミスを発見。何とかその穴を埋めようと、ビューを定義したのだけれども、やけにパフォーマンスが悪い。なんでじゃ。

ビューの功罪
注意を要するケースがあります。それは、ビュー定義で集約操作を行なっているときです。具体的には、次のような演算が含まれる場合です。

AVG、COUNT、MAX などの集約関数
集約演算子（ UNION、INTERSECT、MINUS 等 )

おー、バッチリ嬉々として UNION ALL とかやっちゃってましたよ。縦結合の代わりに横結合するようにしたら、劇的に軽くなった。ビューが単なるSQLクエリの束であることを考えれば、当然なんだけど、盲点でした。

ちなみに、ビューはSQLクエリでしかないということで、元となる実テーブルのインデックスが使えます。さっき試してみて驚いたけど、よく考えたら当たり前じゃないか。ううう。

SQLでテーブルのデータに順位付けするのって、どうやるんだろう。ということで、ちょっとばかし考えてみた。

例として、次のような商品テーブルがあって、その価格が高い方から順に、1位、2位…と順位付けするケースを考えてみる。サンプルに深い意味はないです。

順位付けには、次のようなSQLを考えてみた。

SELECT 
    COALESCE(rank, 1) AS rank,
    name,
    price
FROM
    product
LEFT OUTER JOIN (
    SELECT
       (count(*) + 1) AS rank,
       p1.id
    FROM product AS p1
    CROSS JOIN product AS p2
    WHERE p2.price>p1.price
    GROUP BY p1.id
) AS rank_table
USING (id)
ORDER BY rank

もし価格が同じ商品があった場合は、順位タイということで、同じ順番が振られる。なので、ユニークな順位が求められるケースでは、ちょいと工夫が必要です。

できるにはできたんだけど、使うことは無さそうだなあ。相当数あるときは、CROSS JOIN でパフォーマンスが落ちそうだし、JavaなりPHPなり別言語でやった方がラクチンだし。もっと効率の良い方法があれば、嬉しいんだけど。

いつまでたっても覚えられない「COALESCE」の読み方。

社内でも、明確に発音を知っている人がいないようで、話題が出るたびに「コー何とかを使ってさー」となる。結構な頻度で使うし、口にも出すくせに、「コー何とか」である。読めずともDBには通じるし、いっそ「コー何とか」を社内言語にしてしまうのも手かと思ったけど、横着にもほどがあるので調べた。

coalesce

【自動】 合体｛がったい｝する、連合｛れんごう｝する、融合｛ゆうごう｝する
【発音！】ko`uэle's、【＠】コウアレス

via. スペースアルク

ということで、正解は「コウアレス」とのこと。どうか忘れませんように。

ORDER BY句と聞くと、整然と並べられたデータを取得できるイメージばかりが先行してしまう。でも、「ソート」という考え方を応用すると、実はぐちゃぐちゃな順列で取得することも可能。

// postgresql の場合
SELECT * FROM table ORDER BY random();


// MySQL の場合
SELECT * FROM table ORDER BY rand()

乱数でソートできるというのが、イカス！

でも、データをそんなブサイクに並べてどうするのか。たとえば、テーブルから任意のレコードを抽出したい場合に、この方法が使える。統計で無作為抽出するときなんかは、有用なんじゃないでしょうか。