What to do if mysql datetime error occurs

Mysql datetime error solution: 1. Change datetime to timestamp timestamp; 2. Upgrade MySQL to a higher version; 3. Execute "ALTER USER 'root'@'localhost' IDENTIFIED BY 'root1' PASSWORD EXPIRE NEVER;" command is enough.

The operating environment of this tutorial: Windows 10 system, MySQL version 8.0, Dell G3 computer.

What should I do if mysql datetime reports an error?

MySQL reports a datetime error when creating a table

Create the stu table in the student database and execute the following table creation statement

CREATE TABLE stu (
  id int(12) NOT NULL AUTO_INCREMENT,
  name varchar(150) CHARACTER SET utf8 DEFAULT NULL,
  age int(20),
  createTime datetime DEFAULT CURRENT_TIMESTAMP,
  updateTime datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Reports an Invalid default value for 'createTime' error , Internet Baidu said that it is only after MySQL5.6 that the datetime setting default value is supported, and then change datetime to timestamp timestamp.

When executed again, the error Incorrect table definition; there can be only oneTIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATEclause is reported. This is because current_timestamp is set for multiple timestamps.

The best solution is to upgrade MySQL to a higher version, such as 5.7 or 8.0. After I downloaded MySQL version 8.0 from the official website, the installation was completed and I used Navicat to connect to MySQL and reported a 2059 error. It is said to be caused by the inconsistent encryption rules between 8.0 and previous versions.

Use the command line to enter the MySQL interface and enter the following command

#注意：root1是连接数据库的密码，可以更改为自己想用的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root1' PASSWORD EXPIRE NEVER; #修改数据库的加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root1'; #更新一下localhost的密码
FLUSH PRIVILEGES; #刷新权限

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of What to do if mysql datetime error occurs. For more information, please follow other related articles on the PHP Chinese website!