在MySQL中创建表时出错

CREATE DATABASE vehicle_system;

USE vehicle_system;

USE vehicle_system; CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(255) NOT NULL, 
  `password_hash` text NOT NULL, 
  `api_key` varchar(32) NOT NULL, 
  `phone` varchar(14) NOT NULL, 
  `status` int(1) NOT NULL DEFAULT '1', 
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  `updated_at` timestamp NOT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `email` (`email`), 
  UNIQUE KEY `phone` (`phone`)
);

CREATE TABLE IF NOT EXISTS `vehicle_model` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `manufacturer_name` varchar(250) NOT NULL,
  `model` varchar(32) NOT NULL,
  `model_no` varchar(32) NOT NULL,
  `type` varchar(32) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `manufacturer_id` (`manufacturer_id`)
);

CREATE TABLE IF NOT EXISTS `user_vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `vehicle_no` varchar(32) NOT NULL,
  `rc_no` text,
  `engine_type` varchar(32),
  `year` int(4),
  `insurance_exp_date` timestamp,
  `pollution_exp_date` timestamp,
  `rc_renew_date` timestamp,
  `insurance_company` varchar(32),
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vehicle_no` (`vehicle_no`),
  KEY `user_id` (`user_id`),
  KEY `manufacturer_id` (`manufacturer_id`)
);

While executing above query I get

执行上面的查询时，我得到了

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

＃1293 - 表定义不正确;在DEFAULT或ON UPDATE子句中只能有一个TIMESTAMP列和CURRENT_TIMESTAMP

But I do not see two default time stamp in the query, Can any one point out what could be the possible error. Is it because current time stamp is present in the users table ? If so how to get rid of this ?

但是我没有在查询中看到两个默认时间戳，任何人都可以指出可能的错误。是因为当前时间戳存在于用户表中吗？如果是这样如何摆脱这个？

1 个解决方案

#1

I think the problem is that you do not have a default for all the fields of type timestamp.

我认为问题是你没有时间戳类型的所有字段的默认值。

Running your query I got the following error:

运行您的查询我收到以下错误：

Error Code: 1067. Invalid default value for 'updated_at'

Then I got:

然后我得到了：

Error Code: 1067. Invalid default value for 'pollution_exp_date'

Everything works fine when all the timestamp field have a default value:

当所有时间戳字段都具有默认值时，一切正常：

CREATE DATABASE vehicle_system;

USE vehicle_system;

USE vehicle_system; CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(255) NOT NULL, 
  `password_hash` text NOT NULL, 
  `api_key` varchar(32) NOT NULL, 
  `phone` varchar(14) NOT NULL, 
  `status` int(1) NOT NULL DEFAULT '1', 
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `email` (`email`), 
  UNIQUE KEY `phone` (`phone`)
);

CREATE TABLE IF NOT EXISTS `vehicle_model` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `manufacturer_name` varchar(250) NOT NULL,
  `model` varchar(32) NOT NULL,
  `model_no` varchar(32) NOT NULL,
 `type` varchar(32) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `manufacturer_id` (`manufacturer_id`)
);

CREATE TABLE IF NOT EXISTS `user_vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `vehicle_no` varchar(32) NOT NULL,
  `rc_no` text,
  `engine_type` varchar(32),
  `year` int(4),
  `insurance_exp_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pollution_exp_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rc_renew_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `insurance_company` varchar(32),
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vehicle_no` (`vehicle_no`),
  KEY `user_id` (`user_id`),
  KEY `manufacturer_id` (`manufacturer_id`)
);

This is on MySql 5.7.10.

这是在MySql 5.7.10上。

There is an interesting discussion on this here:

这里有一个有趣的讨论：

https://bugs.mysql.com/bug.php?id=41137

Creation of timestamp fields is different from any other!

创建时间戳字段与其他任何字段都不同！

Practically up to 5.6.6 you do not have to declare NOT NULL as by definition that field is NOT NULL and with neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

实际上高达5.6.6，您不必声明NOT NULL，因为根据定义，该字段为NOT NULL且既没有DEFAULT也没有ON UPDATE子句，它与DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP相同。

After 5.6.6 you have to explicit defaults. Look also:

在5.6.6之后，您必须使用显式默认值。看看：

https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

and

和

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

Finally, in your case removing NOT NULL is the best approach.

最后，在您的情况下，删除NOT NULL是最好的方法。

Regards

问候

#1