MySQL 表新增/修改 DATE/DATETIME 類型字段的完整實踐 (SQL 與 Java JDBC)

MySQL 表新增/修改 DATE/DATETIME 類型字段的完整實踐 (SQL 與 Java JDBC)

日期時間類型是業務表的核心屬性，正確選型、DDL 變更、應用層寫入讀取、服務端格式配置四環扣一環，缺一不可。

一、類型選型對照表

💡 推薦：業務時間用 DATETIME (無時區歧義)，修改時間用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

二、DDL 變更語句

// 依賴: mysql-connector-java 8.0+
// 連接串建議顯式指定時區
String url = "jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";

try (Connection conn = DriverManager.getConnection(url, "user", "pass");
     PreparedStatement ps = conn.prepareStatement(
       "INSERT INTO orders (biz_date, pay_time) VALUES (?, ?)")) {

    // 1. 寫入 java.time.LocalDate / LocalDateTime (JDBC 4.2+ 直接支援)
    ps.setObject(1, LocalDate.of(2024, 6, 15));           // DATE
    ps.setObject(2, LocalDateTime.of(2024, 6, 15, 14, 30, 45, 123_000_000)); // DATETIME(3)
    ps.executeUpdate();

    // 2. 讀取
    try (ResultSet rs = conn.createStatement().executeQuery("SELECT biz_date, pay_time FROM orders WHERE id=1")) {
        if (rs.next()) {
            LocalDate bizDate = rs.getObject(1, LocalDate.class);
            LocalDateTime payTime = rs.getObject(2, LocalDateTime.class);
            System.out.println(bizDate + " | " + payTime);
        }
    }
}

✅ 棄用 java.sql.Date/Timestamp、SimpleDateFormat，全鏈路使用 java.time.* (不可變、線程安全、ISO-8601 原生)。

四、MySQL 服務端日期格式配置 (如需修改顯示格式)

MySQL 內部儲存為二進制，顯示格式由客戶端決定。若確需調整服務端預設輸出格式 (影響 SELECT 直接輸出文本)：

-- my.cnf / my.ini [mysqld] 區段
[mysqld]
# 8.0 可動態修改
# date_format = '%Y-%m-%d'        -- 預設已是此格式
# datetime_format = '%Y-%m-%d %H:%i:%s'
# time_format = '%H:%i:%s'

-- 會話級臨時修改
SET SESSION date_format = '%Y/%m/%d';
SELECT CURDATE();  -- 輸出 2024/06/15

⚠️ 生產環境強烈不建議修改，應用層統一用 DateTimeFormatter 格式化，避免跨語言/驅動版本兼容性問題。

五、常見坑點清單

從建表選型到 JDBC 參數綁定，再到服務端時區一致性，每一環都要「類型對齊、時區統一、參數化」，才能構建零 Bug 的日期時間體系。

1. 上一篇文章： MySQL 字段增刪改全攻略：ADD、CHANGE、MODIFY、DROP COLUMN 實戰語法與避坑指南
2. 下一篇文章： MySQL 啟動失敗 Error 2013 多種解決方案總覽

MySQL 綜合教學

Zabbix 監控 MySQL 實戰：從部署到關鍵指標採集

Zabbix 作為企業級開源監控標桿，對 MySQL 的深度

MySQL 亂碼問題徹底解決：字符集編碼轉換全流程圖解與實戰修復

MySQL 亂碼根因深度解析：從輸入到存儲再到輸出的三次編碼

MySQL NOT NULL 約束與空值深度解析：空字符串 vs NULL、長度差異、索引影響

現象還原 表列有 NOT NULL 約束，卻能插入「空值」。

MySQL 主從複制架構實戰：從原理到生產環境部署全攻略

MySQL 主從複制架構實戰：從原理到生產環境部署全攻略 M