なにが起きましたか
- とあるDBのテーブルでは,日時データが全て別カラムの文字列で構成されていた.
-
SELECT year, month,..., second from Hoge
みたいなやり方を強いられていた - これはプログラムでも扱いにくいしなんとかしたい.
-
- →MySQLなどでサポートされている
DATETIME
型に変換する.
変換元のHogeテーブルと変換先のPiyoテーブルは以下のような見た目である.
> SHOW CREATE TABLE Hoge;
CREATE TABLE Hoge (id, columns, year VARCHAR(4), month VARCHAR(2)...)
> SHOW CREATE TABLE Piyo
CREATE TABLE Piyo (id, columns, date DATETIME(6))
やったこと
- HogeからPiyoに変換する以下のようなSQLを書いた
convert.sql
insert into Piyo(
id,
columns,
STR_TO_DATE(
CONCAT(
year,'-',month,'-',day,' ',hour,':',minute,':',second,'.',usecond
),
'%Y-%m-%d %H:%i:%s.%f'
)
) select id, columns, from Hoge;
取得したそれぞれを,CONCAT
で結合して,DATETIMEフォーマットに合わせる.それをSTR_TO_DATE
でDATETIME型に変換して,新しいPiyoテーブルにINSERTする.
さて,動くような気もするが,動かない.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) from ...
なんと,SyntaxErrorだと言われた.全く分からん......
解決
全くSQLをわかっていなかった.
INSERTするときINSERT先カラム名を指定しない場合,INSERT先のカラム名と一致している必要がある当たり前だ.
もしINSERT先とINSERT元の名前を違うものにしたかったらINSERT INTO テーブル名 (インサート先カラム名) VALUES (インサート元カラム名)
でやる.
あるいは,SELECTしたときにASでINSERT先の名前として別名をつけてやる必要がある.
結論
SELECT時に別名をつける手法を採用した.
convert.sql
INSERT INTO Piyo(
id,
columns,
date
)
SELECT id,columns,STR_TO_DATE(
CONCAT(
year,'-',month,'-',day,' ',hour,':',minute,':',second,'.',usecond
),'%Y-%m-%d %H:%i:%s.%f'
) as date
from Hoge;
これで1時間以上溶かしてしまった...SQLのエラーがもっっっと親切だったら良かった(ない)