大家好，
我的表中有 2 列（Start Date& End Date），它們實際上是文本。我想將它們轉換為時間戳格式，但我不知道該怎么做。下面的查詢是我嘗試過但不起作用的：
ALTER TABLE mytable
MODIFY COLUMN STR_TO_DATE(`Start Date`,"%m/%d/%Y %H:%i") TIMESTAMP,
MODIFY COLUMN STR_TO_DATE(`End Date`,"%m/%d/%Y %H:%i") TIMESTAMP;
我可以知道如何更改表中這 2 列的資料型別嗎？任何幫助或建議將不勝感激！
uj5u.com熱心網友回復：
假設這是我們的初始列定義狀態：
mysql> SHOW CREATE TABLE mytable;
--------- ------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------- ------------------------------------------------------------------------------------------------------------------------------------
| mytable | CREATE TABLE `mytable` (
`Start_Date` text,
`End_Date` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
--------- ------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
首先你需要使用更新：
UPDATE mytable SET End_Date = STR_TO_DATE(End_Date,'%m/%d/%Y %H:%i');
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM mytable;
--------------------- ---------------------
| Start_Date | End_Date |
--------------------- ---------------------
| 2022-10-16 10:35:00 | 2022-10-16 10:40:00 |
| 2022-10-16 09:18:00 | 2022-10-16 09:25:00 |
--------------------- ---------------------
2 rows in set (0.00 sec)
然后更改表以將列資料型別更改為“時間戳”：
mysql> ALTER TABLE mytable MODIFY COLUMN Start_Date TIMESTAMP, MODIFY COLUMN End_Date TIMESTAMP;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
如果我們測驗一下，
mysql> SHOW CREATE TABLE mytable;
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| mytable | CREATE TABLE `mytable` (
`Start_Date` timestamp NULL DEFAULT NULL,
`End_Date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
--------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/533158.html