How to set default value for datetime type in MySQL

How to set default value for datetime type in MySQL

I encountered a problem when modifying the default datetime value through the navicat client.

The database table field type datetime originally defaults to NULL. When the default value is set to the current time through the interface, a prompt "1067-Invalid default value for 'CREATE_TM' " appears. However, this problem does not occur when creating a table. For example, the table creation statement:

CREATE TABLE `app_info1` ( 
 `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID', 
 `app_id` varchar(30) DEFAULT NULL COMMENT 'Application code', 
 `app_name` varchar(30) DEFAULT NULL COMMENT 'Application name', 
 `package_name` varchar(100) DEFAULT NULL COMMENT 'Package name', 
 `version` int(11) DEFAULT NULL COMMENT 'Version number', 
 `version_name` varchar(20) DEFAULT NULL COMMENT 'Version name', 
 `icon_url` varchar(512) DEFAULT NULL COMMENT 'Icon address', 
 `download_url` varchar(512) DEFAULT NULL COMMENT 'Download URL', 
 `summary` varchar(512) DEFAULT NULL COMMENT 'Summary', 
 `desc` varchar(512) DEFAULT NULL COMMENT 'Description', 
 `app_status` int(4) DEFAULT '0' COMMENT 'Status 0: Available, 1: Deleted', 
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', 
 `create_user` varchar(30) DEFAULT NULL COMMENT 'creator', 
 `modify_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification time', 
 `modify_user` varchar(30) DEFAULT NULL COMMENT 'Modifier', 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Then I tried to modify the field directly through the script, which was also OK, as follows:

alter table ts_role 
MODIFY column create_tm datetime default CURRENT_TIMESTAMP COMMENT 'Creation time'

Of course, the MySQL I installed is 5.6 or above, and I haven't tried other versions.

The above method of setting the default value of the datetime type in MySQL is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Summary of the use of Datetime and Timestamp in MySQL
  • The difference and choice between datetime and timestamp in MySQL
  • A brief discussion on the datetime format when exporting table data from MySQL to Excel
  • The difference and usage of datetime and timestamp in MySQL
  • How to create a datetime type in a MySQL database

<<:  Detailed explanation of how to find the location of the nginx configuration file

>>:  Detailed explanation of how Node.js middleware works

Recommend

JS, CSS and HTML to implement the registration page

A registration page template implemented with HTM...

MySQL 5.5 installation and configuration graphic tutorial

Organize the MySQL 5.5 installation and configura...

Summary of English names of Chinese fonts

When using the font-family property in CSS to ref...

CSS implements Google Material Design text input box style (recommended)

Hello everyone, today I want to share with you ho...

Vue large screen data display example

In order to efficiently meet requirements and avo...

JavaScript modularity explained

Table of contents Preface: 1. Concept 2. The bene...

VMware Workstation Pro 16 License Key with Usage Tutorial

VMware Workstation is a powerful desktop virtual ...

8 commands to effectively manage processes in Linux

Preface The role of process management: Determine...

Introduction to JavaScript array deduplication and flattening functions

Table of contents 1. Array flattening (also known...

JavaScript to implement click to switch verification code and verification

This article shares the specific code of JavaScri...

JavaScript design pattern learning proxy pattern

Table of contents Overview Implementation Protect...

A brief discussion on MySQL event planning tasks

1. Check whether event is enabled show variables ...

HTML Basics Must-Read - Comprehensive Understanding of CSS Style Sheets

CSS (Cascading Style Sheet) is used to beautify H...