38498


**環境**  
・MySQL5.7

* * *

**対象カラム**  
・`hoge`  
・VARCHAR型

・下記のようなデータを格納しています  
・文字列の数字8桁  
・日が分からない場合があるため、下2桁は00の場合があります

[code]

    20161200 
    20170111 
    20141211 
    20150507 
    
[/code]

* * *

**やりたいこと**  
・hogeカラムをdataetime型へ変更  
・内容を下記のように変更したい

[code]

    2016-12-00 00:00:00 
    2017-01-11 00:00:00 
    2014-12-11 00:00:00 
    2015-05-07 00:00:00 
    
[/code]

* * *

**質問1.**  
・[STR_TO_DATE](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-
functions.html#function_str-to-date)で変換できるようなのですが、列全てを変換するにはどうすれば良いでしょうか？

[code]

    STR_TO_DATE('01,5,2013','%d,%m,%Y');
    
    STR_TO_DATE('ここにSELECT結果を格納する？？','%d,%m,%Y');
    
[/code]

* * *

**質問2.**  
・処理の進め方が分からないのですが、一時的なdatetimeカラムを一旦作成して、そこを経由してコピーとかするのでしょうか？  
・それとも、カラムのデータ型と内容は同時に変更できるのでしょうか？


CC BY-SA 3.0
2017-10-06T12:47:27.950
38493
2017-10-07T01:11:54.970
7886
question
mysql
MySQLで指定カラム「VARCHAR型」の内容を、全て「datetime型」へ変更したい。カラムのデータ型自体も「datetime型」へ変更したい


テーブルが piyo のとき カラム hoge VARCHARを datetimeに単純に変換するには以下を実行

[code]

     ALTER TABLE `piyo` CHANGE `hoge` `hoge` DATETIME  NOT NULL;
    
[/code]

USE dbname で操作対象のDB選択もお忘れなく、  
また、バックアップを取ってから、変換しましょう。  
別なデータタイプに間違えて変換するとデータが失われます。


CC BY-SA 3.0
2017-10-06T15:52:41.867
38495
2017-10-06T16:21:53.083
2017-10-06T16:21:53.083
22793
22793
38493
answer


  1. テーブルにカラムを追加
  2. 追加したカラムにデータを変換しつつ挿入
  3. 元のカラムを削除
  4. 追加したカラムを元のカラム名にリネーム

の手順でやるのが良いかと思います。

[code]

    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;
    
[/code]

または、カラムではなくテーブルを追加する方法もあります。この場合データのコピーは

[code]

    INSERT INTO new_table_name (hoge, col1, col2, ...) 
      SELECT STR_TO_DATE(hoge, '%Y%m%d'), col1, col2, ... FROM table_name;
    
[/code]

となります。ただし、AUTOINCREMENTを指定しているカラムがある場合、初期値の再設定が必要になります。一旦カラムからAUTO_INCREMENTを外して、`AUTO_INCREMENT
= 999`などとして再設定してください。

`ALTER TABLE table_name CHANGE hoge hoge
DATETIME`として型を変換する方法もありますが、これは新旧の型の間で自明に型変換が可能な場合のみ使用してください。例えば

  * varchar(50)をvarchar(100)
  * 整数型から浮動小数点型
  * 日付型から日付時刻型

このような場合です。

今回のように文字列から日付時刻型に変換するような(しかも異常値が含まれていることが明らか)場合、変換の成否が確認出来ませんし戻ることもできません。


CC BY-SA 3.0
2017-10-07T01:11:54.970
38498
2017-10-07T01:11:54.970
5793
38493
answer