質問: **環境** ・MySQL5.7 --- **対象カラム** ・`hoge` ・VARCHAR型 ・下記のようなデータを格納しています ・文字列の数字8桁 ・日が分からない場合があるため、下2桁は00の場合があります ``` 20161200 20170111 20141211 20150507 ``` --- **やりたいこと** ・hogeカラムをdataetime型へ変更 ・内容を下記のように変更したい ``` 2016-12-00 00:00:00 2017-01-11 00:00:00 2014-12-11 00:00:00 2015-05-07 00:00:00 ``` --- **質問1.** ・STR_TO_DATEで変換できるようなのですが、列全てを変換するにはどうすれば良いでしょうか? ``` STR_TO_DATE('01,5,2013','%d,%m,%Y'); STR_TO_DATE('ここにSELECT結果を格納する??','%d,%m,%Y'); ``` --- **質問2.** ・処理の進め方が分からないのですが、一時的なdatetimeカラムを一旦作成して、そこを経由してコピーとかするのでしょうか? ・それとも、カラムのデータ型と内容は同時に変更できるのでしょうか?
回答: 1. テーブルにカラムを追加 2. 追加したカラムにデータを変換しつつ挿入 3. 元のカラムを削除 4. 追加したカラムを元のカラム名にリネーム の手順でやるのが良いかと思います。 ``` ALTER TABLE table_name ADD hoge_tmp DATETIME; UPDATE table_name SET hoge_tmp = STR_TO_DATE(hoge, '%Y%m%d'); ALTER TABLE table_name DROP hoge; ALTER TABLE table_name CHANGE hoge_tmp hoge DATETIME; ``` または、カラムではなくテーブルを追加する方法もあります。この場合データのコピーは ``` INSERT INTO new_table_name (hoge, col1, col2, ...) SELECT STR_TO_DATE(hoge, '%Y%m%d'), col1, col2, ... FROM table_name; ``` となります。ただし、AUTOINCREMENTを指定しているカラムがある場合、初期値の再設定が必要になります。一旦カラムからAUTO_INCREMENTを外して、`AUTO_INCREMENT = 999`などとして再設定してください。 `ALTER TABLE table_name CHANGE hoge hoge DATETIME`として型を変換する方法もありますが、これは新旧の型の間で自明に型変換が可能な場合のみ使用してください。例えば * varchar(50)をvarchar(100) * 整数型から浮動小数点型 * 日付型から日付時刻型 このような場合です。 今回のように文字列から日付時刻型に変換するような(しかも異常値が含まれていることが明らか)場合、変換の成否が確認出来ませんし戻ることもできません。