興味ない人には、なんのこっちゃわからないタイトル。
仕事で複数人数、複数のマシンで開発を行っているので、それらのマシンを同期しないといけない。
プログラムなんかは github 使って管理しているのだけど、DB 構造なんかの問題もある。
で、mysql (実際には mariadb だけど）のテーブル構造を、ファイル化してある。
このファイルは github で管理し、新しいサーバーを建てるときには、ファイルを mysql コマンドで
流し込めば完了する。
そして、開発中に「変更」になったものについては、alter table を列記したファイルを作る。
github から最新のデータを得たときなどは、このファイルを mysql に流し込めば完了する。
…というつもりで作業していたら、そう単純じゃない問題があったよ、という話。
もともと技術者でないとわからない話なので、ついてこれる人だけ読み進めてほしい。
alter table でテーブル構造の変更はできるが、alter table には IF NOT EXISTS というオプションがある。
「もし、その構造が存在していないなら」新しく追加する、などという動作を行う。
だから、先に書いたように、プログラム更新のたびに、必ずこの「 DB 構造差分データ」を DB に流し込めば、構造がすでにあれば無視され、なければ正しく追加される。
でも、「varchar(4) を tinyint に変えたい」で詰まったんだ。
設計段階ではいろいろあって varchar(4) してあったけど、事実上小さな数値しか入らない、と分かったため。
直接 alter table で変換しようとすると、「文字列を数値に変えられない」とエラーが出て失敗する。
これは、何もデータがない時に、NULL 文字列が入っていたため。
数値になった際も、0 は使わないとわかっている。だから、NULL 文字列は 0 に変換してほしい。
というわけで、事前に
UPDATE [table] SET [col]='0' where [col]=''
という感じで、'' を 0 に変える。それから alter table すればうまくいく。
…というわけでもなかった。
上の UPDATE 文、[col] が varchar の時は動いた。
でも、tinyint になると、エラーになって止まる。
先に書いた通り、このファイルはプログラム更新のたびに読み込まれるのだ。
すでに tinyint の時には、何もせずにスルーしてほしい。
エラーの理由はわかっている。
[col]は数値なので、where 句に書かれた比較は数値で行おうとする。
しかし、NULL 文字列は数値ではないので、「文字列を数値に変えられない」とエラーが出て失敗する。
…最初の問題に戻ってきた。
んぎぎぎぎ…どうすればよいのだ。
仕方がない。IF 文を使ってみよう。
僕は mysql を「単に使っていた」歴は長いのだけど、php や javascript と組み合わせていたことが多いため、sql のみで記述するファイルとして、IF を使ったことがない。
やりたいのは、「[col] の型が varchar の時だけ、各種処理を行う」だ。
[col]の型を調べるには、
show columns [table] like '[col]'
とすればよい。これで、画面上に型が表示される。
…いやいや、それはダメだ。画面上に出ただけでは、IF 文で判断できないじゃないか。
しかし、どうすればこれを基に IF の判断につなげられるのかわからない。
ところで、mysql はデータベースだ。データの扱いに長けている。
どれくらい長けているかというと、「自ら作成するデータベースの構造も、データベースとして格納されている」のだ。
つまり、先ほど show columns で見た内容も、実はデータベースに入っている。
データベース名 INFORMAION_SCHEMA の中に、テーブル名を示すテーブルや、カラム名を示すテーブルがある。
SELECT data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=[table]' AND column_name='[col]'
とすれば、目的のカラムのデータ型を得られる。
…が、これもまだ、表示されるだけだ。
また、サーバー内に複数のデータベースが入っており、同じテーブル・カラムがそれぞれにある場合、全てが表示されてしまう。
そのためさらに現在接続しているデータベース名で絞り込む必要がある。
「表示しかできない」という問題に対しては、show columns は名前の通り本当に表示だけだが、select は sql の中心的な構造なので、素敵なオプションがある。
先のコマンドの冒頭を
SELECT data_type INTO @type FROM ～
と変えてやる。 INTO @type が挟まっただけだ。
しかし、これで「表示」するのではなく、@type という変数に格納されるようになる。
もう一つ、目的外のデータベースも検索対象になることについては、WHERE の直後を変えてやる。
～ WHERE table_schema=database() AND tabel_name～
これで、データベースを絞り込める。
ここで、@type には varchar か、tinyint が入っているはずだ。
目的は、varchar の時だけ、tinyint に alter table することなので、
IF @type='varchar' THEN
UPDATE [table] SET [col]='0' WHERE [col]='';
ALTER TABLE [table] MODIFY [col] tinyint DEFAULT 0 NOT NULL;
END IF;
みたいな感じで処理してやればよい。
ただし、このまま mysql クライアントで流し込むとエラーになる。
; は「mysql サーバー的には」コマンドの区切りなのだが、「mysql クライアント的には」命令の終わりを兼ねるためだ。
DELIMITER で命令の終わりを変えられる。
なので、最初に DELIMITER で // を命令の終わりに変え、全てが終わった後で元の ; に戻してやるといい。
最初の SELECT から含めて全部を書くと、次のような感じになる。
SELECT data_type INTO @type FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema=database() AND table_name=[table]' AND column_name='[col]';
DELIMITER //
IF @type='varchar' THEN
UPDATE [table] SET [col]='0' WHERE [col]='';
ALTER TABLE [table] MODIFY [col] tinyint DEFAULT 0 NOT NULL;
END IF;
//
DELIMITER ;
同じことで悩んでいる人、結構多そうだと思うのだけど、探してもそういう記事を見かけなかった。
ただ、「show columns の結果を変数に入れたい」とか、「文字列から数値のキャストの癖が強すぎる」とか、悩んで解決できていない記事は多数見つけた。
それらがすべて、僕と同じ悩みで書かれたわけではないだろう。
でも、同じ悩みの人の解決の手立てになれれば、幸いである。
同じテーマの日記(最近の一覧)
別年同日の日記
申し訳ありませんが、現在意見投稿をできない状態にしています。 |