在 MySQL 中將日期轉換為日期時間

我應該在日期欄位中添加時間。我們將 MySQL 與 python3、alembic 和 sqlalchemy 一起使用。現在我寫了一個轉換程序。這是我的第一個此類程序。我不確定它將如何工作，如何將數據遷移到新欄位，或者是否是不同的場景。

我不確定？downgrade ，如果是降級怎麼辦？在範例中，我查看了幾乎所有人都執行 ADD TABLE 的範例，因此在降級時，這些函式只需刪除他們創建的內容。但是，如果降級（可能不會發生），我如何在降級功能中回滾 ALTER 聲明呢？

還有什麼我應該考慮的嗎？

def upgrade(**args):
   db_update_context = mnet_get_required_argument("db_update_context", **args)

   if not mnet_column_exists(table_name, column_name, **args):
       sql = textwrap.dedent("""\
               ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                 """.format(table_name, column_name))
       mnet_execute(db_update_context, sql)

       sql = textwrap.dedent("""\
               ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                 """.format(table_name, column_name2))
       mnet_execute(db_update_context, sql)

def downgrade(**args): # TODO: What is supposed to happen here
   columns_to_drop = [column_name]
   for column_to_drop in columns_to_drop:
       medinet_drop_column(table_name, column_to_drop, **args)

創建和填充：

mysql> CREATE TABLE grade(d DATE NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO grade (d) VALUES (NOW()), (CURDATE() - INTERVAL 1 DAY);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
+------------+
| d          |
+------------+
| 2017-11-22 |
| 2017-11-21 |
+------------+
2 rows in set (0.00 sec)

升級：

mysql> ALTER TABLE grade MODIFY COLUMN d DATETIME NOT NULL;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM grade;
+---------------------+
| d                   |
+---------------------+
| 2017-11-22 00:00:00 |
| 2017-11-21 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO grade (d) VALUES (NOW()), (CURDATE() - INTERVAL 10 DAY);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM grade;
+---------------------+
| d                   |
+---------------------+
| 2017-11-22 00:00:00 |
| 2017-11-21 00:00:00 |
| 2017-11-22 13:56:37 |
| 2017-11-12 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

降級：

mysql> ALTER TABLE grade MODIFY COLUMN d DATE NOT NULL;
Query OK, 4 rows affected, 1 warning (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 3 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
+------------+
| d          |
+------------+
| 2017-11-22 |
| 2017-11-21 |
| 2017-11-22 |
| 2017-11-12 |
+------------+
4 rows in set (0.00 sec)

mysql> 

我故意包括嘗試將 aDATETIME放入DATE列中以顯示發生了什麼，並顯示發生的警告。

引用自：https://dba.stackexchange.com/questions/191496