MySQL 4.0에서 생성 및 마지막 업데이트 타임 스탬프 열 모두

MySQL 4.0에서 생성 및 마지막 업데이트 타임 스탬프 열 모두

---

다음과 같은 테이블 스키마가 있습니다.

CREATE TABLE `db1`.`sms_queue` (
  `Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error',
  `CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None',
  `Phone` VARCHAR(14) DEFAULT NULL,
  `Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `TriesLeft` tinyint NOT NULL DEFAULT 3,
  PRIMARY KEY (`Id`)
)
ENGINE = InnoDB;

다음 오류와 함께 실패합니다.

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

내 질문은, 그 두 분야를 모두 가질 수 있습니까? 또는 각 트랜잭션 중에 LastUpdated 필드를 수동으로 설정해야합니까?

---

보내는 사람 의 MySQL 5.5 문서 :

테이블에서 하나의 TIMESTAMP 열은 열을 초기화하기위한 기본값, 자동 업데이트 값 또는 둘 다로 현재 시간 소인을 가질 수 있습니다. 현재 타임 스탬프를 한 열의 기본값으로 설정하고 다른 열의 자동 업데이트 값으로 설정할 수 없습니다.

MySQL 5.6.5의 변경 사항 :

이전에는 테이블 당 최대 하나의 TIMESTAMP 열을 자동으로 초기화하거나 현재 날짜 및 시간으로 업데이트 할 수있었습니다. 이 제한이 해제되었습니다. TIMESTAMP 컬럼 정의는 DEFAULT CURRENT_TIMESTAMP 및 ON UPDATE CURRENT_TIMESTAMP 절의 조합을 가질 수 있습니다. 또한이 절은 DATETIME 열 정의와 함께 사용할 수 있습니다. 자세한 내용은 TIMESTAMP 및 DATETIME 자동 초기화 및 업데이트를 참조하십시오.

---

두 개의 타임 스탬프를 모두 갖는 트릭 이 있지만 약간의 제한이 있습니다.

한 테이블에서 하나의 정의 만 사용할 수 있습니다. 다음과 같이 두 타임 스탬프 열을 만듭니다.

create table test_table( 
  id integer not null auto_increment primary key, 
  stamp_created timestamp default '0000-00-00 00:00:00', 
  stamp_updated timestamp default now() on update now() 
); 

다음 null동안 두 열을 모두 입력해야합니다 insert.

mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)

mysql> select * from test_table; 
+----+---------------------+---------------------+ 
| id | stamp_created       | stamp_updated       |
+----+---------------------+---------------------+
|  2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)  

mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1  Changed: 1  Warnings: 0  

mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created       | stamp_updated       | 
+----+---------------------+---------------------+ 
|  3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec)  

---

You can have them both, just take off the "CURRENT_TIMESTAMP" flag on the created field. Whenever you create a new record in the table, just use "NOW()" for a value.

Or.

On the contrary, remove the 'ON UPDATE CURRENT_TIMESTAMP' flag and send the NOW() for that field. That way actually makes more sense.

---

If you do decide to have MySQL handle the update of timestamps, you can set up a trigger to update the field on insert.

CREATE TRIGGER <trigger_name> BEFORE INSERT ON <table_name> FOR EACH ROW SET NEW.<timestamp_field> = CURRENT_TIMESTAMP;

MySQL Reference: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

---

This is how can you have automatic & flexible createDate/lastModified fields using triggers:

First define them like this:

CREATE TABLE `entity` (
  `entityid` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `name` varchar(255) DEFAULT NULL,
  `comment` text,
  PRIMARY KEY (`entityid`),
)

Then add these triggers:

DELIMITER ;;
CREATE trigger entityinsert BEFORE INSERT ON entity FOR EACH ROW BEGIN SET NEW.createDate=IF(ISNULL(NEW.createDate) OR NEW.createDate='0000-00-00 00:00:00', CURRENT_TIMESTAMP, IF(NEW.createDate<CURRENT_TIMESTAMP, NEW.createDate, CURRENT_TIMESTAMP));SET NEW.lastModified=NEW.createDate; END;;
DELIMITER ;
CREATE trigger entityupdate BEFORE UPDATE ON entity FOR EACH ROW SET NEW.lastModified=IF(NEW.lastModified<OLD.lastModified, OLD.lastModified, CURRENT_TIMESTAMP);

• If you insert without specifying createDate or lastModified, they will be equal and set to the current timestamp.
• If you update them without specifying createDate or lastModified, the lastModified will be set to the current timestamp.

But here's the nice part:

• If you insert, you can specify a createDate older than the current timestamp, allowing imports from older times to work well (lastModified will be equal to createDate).
• If you update, you can specify a lastModified older than the previous value ('0000-00-00 00:00:00' works well), allowing to update an entry if you're doing cosmetic changes (fixing a typo in a comment) and you want to keep the old lastModified date. This will not modify the lastModified date.

---

As of MySQL 5.6 its easy-peasy... give it a try:

create table tweet ( 
    id integer not null auto_increment primary key, 
    stamp_created timestamp default now(), 
    stamp_updated timestamp default now() on update now(),
    message varchar(163)
)

---

This issue seemed to have been resolved in MySQL 5.6. I have noticed this until MySQL 5.5; here is an example code:

DROP TABLE IF EXISTS `provider_org_group` ;
CREATE TABLE IF NOT EXISTS `provider_org_group` (
  `id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `type` VARCHAR(100) NULL,
  `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `insert_src_ver_id` INT NULL,
  `updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_src_ver_id` INT NULL,
  `version` INT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB;

Running this on MySQL 5.5 gives:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Running this on MySQL 5.6

0 row(s) affected   0.093 sec

---

create table test_table( 
id integer not null auto_increment primary key, 
stamp_created timestamp default '0000-00-00 00:00:00', 
stamp_updated timestamp default now() on update now() 
); 

source: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/

---

i think this is the better query for stamp_created and stamp_updated

CREATE TABLE test_table( 
    id integer not null auto_increment primary key, 
    stamp_created TIMESTAMP DEFAULT now(), 
    stamp_updated TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE now() 
); 

because when the record created, stamp_created should be filled by now() and stamp_updated should be filled by '0000-00-00 00:00:00'

---

For mysql 5.7.21 I use the following and works fine:

CREATE TABLE Posts ( modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP )

---

My web host is stuck on version 5.1 of mysql so anyone like me that doesn't have the option of upgrading can follow these directions:

http://joegornick.com/2009/12/30/mysql-created-and-modified-date-fields/

참고URL : https://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0