Cập nhật dấu thời gian trong MySQL

Trong quá trình thử nghiệm di chuyển mới, chúng tôi đã phát hiện ra rằng các dấu thời gian trong bảng kiểm tra đột nhiên được đặt lại về cùng một dấu thời gian (gần như bây giờ). Điều đó vấp phải một số dây thần kinh. Sau một số điều tra, hóa ra MySQL và MariaDB có thể có hành vi mặc định nguy hiểm khi làm việc với các cột loại

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

0. Giả sử bạn khai báo một bảng sự kiện kiểm toán như thế này

CREATE TABLE `Event` (
  `id` varchar(128) NOT NULL,
  `createdOn` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

Khi đọc lại định nghĩa bảng (e. g. thông qua MySQL Workbench), bạn sẽ thấy nó thực sự

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Lưu ý việc tự động thêm

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1 cho cột

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. Điều này tạo ra hai vấn đề

• các giá trị dấu thời gian do mã của bạn đặt có thể không khớp với dấu thời gian được lưu trữ trong cơ sở dữ liệu
• dấu thời gian trên bảng trở nên có thể thay đổi, tôi. e. trong trường hợp di chuyển chạy CẬP NHẬT trên bảng, tất cả các dấu thời gian hiện có sẽ bị ghi đè

Nguyên nhân của hành vi này là Khởi tạo và cập nhật tự động cho TIMESTAMP và DATETIME, được kiểm soát bởi biến cấu hình clear_defaults_for_timestamp và cũng phụ thuộc vào Chế độ SQL đang hoạt động

Để xem cấu hình đang hoạt động của bạn, hãy chạy SQL này trên kết nối đang hoạt động của bạn

SHOW Variables WHERE Variable_name = "explicit_defaults_for_timestamp";
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

Trong trường hợp của chúng tôi,

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3 là

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4, điều này đặc biệt dẫn đến hành vi được quan sát

Cột DẤU THỜI GIAN đầu tiên trong một bảng, nếu không được khai báo rõ ràng bằng thuộc tính NULL hoặc thuộc tính MẶC ĐỊNH hoặc BẬT CẬP NHẬT rõ ràng, sẽ tự động được khai báo bằng các thuộc tính MẶC ĐỊNH CURRENT_TIMESTAMP và BẬT CẬP NHẬT CURRENT_TIMESTAMP

một lưu ý khác. Nhóm hoặc nhà cung cấp cơ sở dữ liệu của bạn có thể chưa cấu hình rõ ràng

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Để thêm sự xúc phạm đến thương tích, giá trị mặc định cho biến này phụ thuộc vào Phiên bản MySQL của bạn

MySQL VersiondefaultMySQL >= 8.0.2

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

0MySQL <= 8.0.1

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1MariaDB >= 10.1.8

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1

Kiểm soát rõ ràng khởi tạo dấu thời gian

Đó là tin xấu khi hành vi của ứng dụng của bạn phụ thuộc vào một biến cấu hình cơ sở dữ liệu nằm ngoài sự kiểm soát trực tiếp của nhóm của bạn. Do đó, chúng tôi phải sửa các bảng của mình ngay sau khi tạo chúng như được đề xuất trong câu trả lời stackoverflow này và được điều chỉnh cho MariaDB

ALTER TABLE `Event` MODIFY COLUMN `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `Event` ALTER COLUMN `createdOn` DROP DEFAULT;

Ghi chú. Những tuyên bố này là idempotent, tôi. e. chúng tôi có thể chạy chúng một cách an toàn ngay cả khi

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3 là

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

0 và bảng của chúng tôi sẽ có trạng thái mong muốn, tôi. e. cột

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

0 không có mệnh đề

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6 và không có mệnh đề

CREATE TABLE `Event` (
  `id` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

7

Chúng tôi cũng đã thêm một thử nghiệm vào bộ thử nghiệm di chuyển của mình để xác minh tất cả các cột dấu thời gian được tạo như dự định và không có hành vi ẩn nào gây rối với các định nghĩa cột của chúng tôi

Làm cách nào để cập nhật DẤU THỜI GIAN hiện tại trong MySQL?

Làm cách nào để cập nhật ngày với TIMESTAMP trong SQL?

Làm cách nào để cập nhật DẤU THỜI GIAN trong db?

Làm cách nào để thêm DẤU THỜI GIAN trong MySQL?