MySQL Datetime 열의 기본값을 어떻게 설정합니까?

MySQL Datetime 열의 기본값을 어떻게 설정합니까?

---

MySQL Datetime 열의 기본값을 어떻게 설정합니까?

SQL Server에서는 getdate(). MySQL과 동등한 것은 무엇입니까? 그것이 요인이라면 MySQL 5.x를 사용하고 있습니다.

---

중요 편집 : 이제 MySQL 5.6.5 부터 DATETIME 필드를 사용하여이 작업을 수행 할 수 있습니다. 아래 의 다른 게시물을 살펴보십시오 .

이전 버전은 DATETIME으로 할 수 없습니다 ...

그러나 TIMESTAMP로 할 수 있습니다.

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

**주의 : CURRENT_TIMESTAMP ON을 기본값으로 사용하여 열을 정의하는 경우 항상이 열에 대한 값을 지정해야합니다. 그렇지 않으면 값이 업데이트시 자동으로 "now ()"로 재설정됩니다. 즉, 값을 변경하지 않으려면 UPDATE 문에 "[사용자 열 이름] = [사용자 열 이름]"(또는 다른 값)이 포함되어야합니다. 그렇지 않으면 값이 "now ()"가됩니다. 이상하지만 사실입니다. 이게 도움이 되길 바란다. 5.5.56-MariaDB를 사용하고 있습니다 **

---

버전 5.6.5에서는 datetime 열에 기본값을 설정하고 행이 업데이트 될 때 업데이트되는 열을 만들 수도 있습니다. 유형 정의 :

CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

참조 : http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

---

MySQL ( 5.6.5 이전 버전 )에서는 기본 DateTime 값에 함수를 사용할 수 없습니다. TIMESTAMP는 이상한 동작으로 인해 적합하지 않으며 입력 데이터로 사용하지 않는 것이 좋습니다. ( MySQL 데이터 유형 기본값 참조 )

즉, Trigger를 생성하여 이를 수행 할 수 있습니다 .

DateTime 유형의 DateCreated 필드가있는 테이블이 있습니다. "Before Insert"및 " SET NEW.DateCreated=NOW()" 테이블에 대한 트리거를 만들었는데 잘 작동합니다.

누군가에게 도움이되기를 바랍니다.

---

저에게는 방아쇠 접근 방식이 가장 잘 작동했지만 접근 방식에서 걸림돌을 발견했습니다. 삽입시 날짜 ​​필드를 현재 시간으로 설정하는 기본 트리거를 고려하십시오.

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = NOW();

이것은 일반적으로 훌륭하지만 다음과 같이 INSERT 문을 통해 수동으로 필드를 설정한다고 가정합니다.

INSERT INTO tblMyTable(name, dateAdded) VALUES('Alice', '2010-01-03 04:30:43');

발생하는 일은 트리거가 필드에 제공된 값을 즉시 덮어 쓰므로 현재가 아닌 시간을 설정하는 유일한 방법은 후속 UPDATE 문입니다. 값이 제공 될 때이 동작을 재정의하려면 IFNULL 연산자를 사용하여 약간 수정 된 트리거를 시도하십시오.

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = IFNULL(NEW.dateAdded, NOW());

이것은 두 세계의 장점을 모두 제공합니다. 날짜 열에 값을 제공하면 시간이 걸리며, 그렇지 않으면 현재 시간이 기본값이됩니다. 테이블 정의에서 DEFAULT GETDATE ()와 같은 깨끗한 것에 상대적인 게토이지만 우리는 점점 가까워지고 있습니다!

---

두 개의 datetime 필드가있는 테이블에서이 alter 문을 사용하여이 문제를 해결할 수있었습니다.

ALTER TABLE `test_table`
  CHANGE COLUMN `created_dt` `created_dt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updated_dt` `updated_dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

This works as you would expect the now() function to work. Inserting nulls or ignoring the created_dt and updated_dt fields results in a perfect timestamp value in both fields. Any update to the row changes the updated_dt. If you insert records via the MySQL query browser you needed one more step, a trigger to handle the created_dt with a new timestamp.

CREATE TRIGGER trig_test_table_insert BEFORE INSERT ON `test_table`
    FOR EACH ROW SET NEW.created_dt = NOW();

The trigger can be whatever you want I just like the naming convention [trig]_[my_table_name]_[insert]

---

You can use triggers to do this type of stuff.

CREATE TABLE `MyTable` (
`MyTable_ID`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
`MyData`  varchar(10) NOT NULL ,
`CreationDate`  datetime NULL ,
`UpdateDate`  datetime NULL ,
PRIMARY KEY (`MyTable_ID`)
)
;

CREATE TRIGGER `MyTable_INSERT` BEFORE INSERT ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the creation date
    SET new.CreationDate = now();

        -- Set the udpate date
    Set new.UpdateDate = now();
END;

CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.UpdateDate = now();
END;

---

For all those who lost heart trying to set a default DATETIME value in MySQL, I know exactly how you feel/felt. So here is is:

ALTER TABLE  `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0

Carefully observe that I haven't added single quotes/double quotes around the 0

I'm literally jumping after solving this one :D

---

MySQL 5.6 has fixed this problem.

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'

---

this is indeed terrible news.here is a long pending bug/feature request for this. that discussion also talks about the limitations of timestamp data type.

I am seriously wondering what is the issue with getting this thing implemented.

---

If you have already created the table then you can use

To change default value to current date time

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

To change default value to '2015-05-11 13:01:01'

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT '2015-05-11 13:01:01';

---

I'm running MySql Server 5.7.11 and this sentence:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

is not working. But the following:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

just works.

As a sidenote, it is mentioned in the mysql docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

even if they also say:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

---

For all who use the TIMESTAMP column as a solution i want to second the following limitation from the manual:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

"The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section. "

So this will obviously break your software in about 28 years.

I believe the only solution on the database side is to use triggers like mentioned in other answers.

---

While defining multi-line triggers one has to change the delimiter as semicolon will be taken by MySQL compiler as end of trigger and generate error. e.g.

DELIMITER //
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.UpdateDate = now();
END//
DELIMITER ;

---

You can use now() to set the value of a datetime column, but keep in mind that you can't use that as a default value.

---

While you can't do this with DATETIME in the default definition, you can simply incorporate a select statement in your insert statement like this:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

Note the lack of quotes around the table.

For MySQL 5.5

---

Here is how to do it on MySQL 5.1:

ALTER TABLE `table_name` CHANGE `column_name` `column_name` 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

I have no clue why you have to enter the column name twice.

---

If you are trying to set default value as NOW(), I don't think MySQL supports that. In MySQL, you cannot use a function or an expression as the default value for any type of column, except for the TIMESTAMP data type column, for which you can specify the CURRENT_TIMESTAMP as the default.

---

CREATE TABLE `testtable` (
    `id` INT(10) NULL DEFAULT NULL,
    `colname` DATETIME NULL DEFAULT '1999-12-12 12:12:12'
)

In the above query to create 'testtable', i used '1999-12-12 12:12:12' as default value for DATETIME column colname

---

Use the following code

DELIMITER $$

    CREATE TRIGGER bu_table1_each BEFORE UPDATE ON table1 FOR EACH ROW
    BEGIN
      SET new.datefield = NOW();
    END $$

    DELIMITER ;

---

I think it simple in mysql since mysql the inbuilt function called now() which gives current time(time of that insert).

So your query should look like similarly

CREATE TABLE defaultforTime(
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME default now()
);

Thank you.

---

If you are trying to set default value as NOW(),MySQL supports that you have to change the type of that column TIMESTAMP instead of DATETIME. TIMESTAMP have current date and time as default..i think it will resolved your problem..

---

Take for instance If I had a table named 'site' with a created_at and an update_at column that were both DATETIME and need the default value of now, I could execute the following sql to achieve this.

ALTER TABLE `site` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `created_at` `created_at` DATETIME  NULL DEFAULT NULL;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` DATETIME NULL DEFAULT  NULL;

The sequence of statements is important because a table can not have two columns of type TIMESTAMP with default values of CUREENT TIMESTAMP

---

This is my trigger example:

/************ ROLE ************/
drop table if exists `role`;
create table `role` (
    `id_role` bigint(20) unsigned not null auto_increment,
    `date_created` datetime,
    `date_deleted` datetime,
    `name` varchar(35) not null,
    `description` text,
    primary key (`id_role`)
) comment='';

drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
    on `role`
    for each row 
    set new.`date_created` = now();

---

You can resolve the default timestamp. First consider which character set you are using for example if u taken utf8 this character set support all languages and if u taken laten1 this character set support only for English. Next setp if you are working under any project you should know client time zone and select you are client zone. This step are mandatory.

참고URL : https://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column