All geek questions in one place

Database schema, default value is "NOW" ()

I have a database schema for users. Looks like...

CREATE TABLE `users` ( `id` int( 8 ) unsigned AUTO_INCREMENT, `username` varchar( 255 ), `password` varchar( 40 ), `level` tinyint( 1 ) unsigned DEFAULT 1, `time` datetime DEFAULT NOW(), `email` varchar( 255 ), PRIMARY KEY (`id`) ) ENGINE=InnoDB;

There are six fields: id, username, password, level, time, email, but I want to insert only three of them - when the user logs in: username, password and email address. The rest of them will have default values.

The problem is that MySQL throws an error: # 1067 - Invalid default value for 'time' . Any ideas guys?

+4
source share
4 answers

Use CURRENT_TIMESTAMP and change the column to a timestamp.

It does the same, but works (as long as you can live with a timestamp) - this is a limitation.

Discussions:

So your creation becomes

 CREATE TABLE `users` ( `id` int( 8 ) unsigned AUTO_INCREMENT, `username` varchar( 255 ), `password` varchar( 40 ), `level` tinyint( 1 ) unsigned DEFAULT 1, `time` timestamp DEFAULT CURRENT_TIMESTAMP, `email` varchar( 255 ), PRIMARY KEY (`id`) ) ENGINE=InnoDB;
+5
source

You need to use TimeStamp not DateTime

 `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
0
source

In general, MySQL does not support functions as defaults. I know only one exception: you can assign CURRENT_TIMESTAMP TIMESTAMP column. Therefore, you need to either change the type of the column (realizing that TIMESTAMP not as strong as DATETIME ), or rely on a trigger to fill the value.

0
source

we cannot do "DEFAULT CURRENT_TIMESTAMP" when my attribute is "datetime", so we will change our attribute to "TIMESTAMP", for example:

time * datetime * DEFAULT CURRENT_TIMESTAMP,

 should be...

time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

0
source

Source: https://habr.com/ru/post/1343890/

More articles:


All Articles