blocks|key|4419401|text|尝试运行以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419402|ALTER+TABLE+`investments`+
MODIFY+created_at+TIMESTAMP+
DEFAULT+CURRENT_TIMESTAMP+
NOT+NULL;|code-block|syntax|javascript|4419403|和|4419404|ALTER+TABLE+`investments`+
MODIFY+updated_at+TIMESTAMP+
DEFAULT+CURRENT_TIMESTAMP+
NOT+NULL;|4419405|出现此错误的原因是您没有为created_at和updated_at字段设置默认值。MySQL不接受您的命令，因为这些列的值不能为null。|offset|length|style|CODE|4419406|entityMap^0|0|0|0|0|D|A|O|A|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Try and run the following command:
<pre><code>ALTER TABLE `investments` 
MODIFY created_at TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP 
NOT NULL;
</code></pre>
and
<pre><code>ALTER TABLE `investments` 
MODIFY updated_at TIMESTAMP 
DEFAULT CURRENT_TIMESTAMP 
NOT NULL;
</code></pre>
The reason you are getting this error is because you are not setting a default value for the <code>created_at</code> and <code>updated_at</code> fields. MySQL is not accepting your command since the values for these columns cannot be null.

blocks|key|4419626|text|我在尝试安装第三方数据库时遇到了同样的错误。我尝试了所提出的解决方案，但没有成功。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419627|SET+sql_mode+=+'';|offset|length|style|CODE|4419628|然后，我尝试了下面的命令，该命令可以安装数据库|4419629|SET+GLOBAL+sql_mode+=+'';|4419630|entityMap^0|0|0|I|0|0|0|P|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

I came across the same error while trying to install a third party database. 
I tried the solution proposed unsuccessfully i.e. 
<code>SET sql_mode = '';</code> 

Then I tried the command below which worked allowing the database to be installed 
<code>SET GLOBAL sql_mode = '';</code>

blocks|key|4419559|text|在我的例子中，我有一个要导入的文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419560|所以我只需在文件的开头添加SET+sql_mode+=+'';，它就可以工作了！|offset|length|style|CODE|4419561|entityMap^0|0|D|I|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

In my case, I have a file to import. 
So I simply added <code>SET sql_mode = '';</code> at the beginning of the file and it works!

blocks|key|4419537|text|运行以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419538|SET+SQL_MODE+=+"NO_AUTO_VALUE_ON_ZERO";
SET+time_zone+=+"%2B00:00";|code-block|syntax|javascript|4419539|对我来说很管用|4419540|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Run this query:

<pre><code>SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
</code></pre>

it works for me

blocks|key|4419610|text|对于Mysql5.7，登录mysql命令行并运行命令|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419611|mysql>+show+variables+like+'sql_mode'+;|code-block|syntax|javascript|4419612|它将在sql_mode中显示NO_ZERO_IN_DATE、NO_ZERO_DATE。|4419613|​|4419614|📷|atomic|offset|length|4419615|4419616|尝试在mysql+conf文件的mysqld下添加一行以删除两个选项，我的(+Ubuntu+16上的mysql+5.7+)是/etc/mysql/mysql.conf.d/mysqld.cnf|4419617|4419618|4419619|4419620|现在重启mysql。它起作用了!|4419621|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/0b378b492b44ed47bc6a6f57e047f7d9.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/333e6973a1219a4485cbc5d455350eef.png^0|0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1A|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1B|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1C|8|@]|9|@$N|1D|O|1E|1|1F]]|A|$]]|$1|P|3|J|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|J|5|6|7|1I|8|@]|9|@]|A|$]]|$1|T|3|L|5|M|7|1J|8|@]|9|@$N|1K|O|1L|1|1M]]|A|$]]|$1|U|3|J|5|6|7|1N|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1O|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|Y|$Z|$5|10|11|12|A|$13|14|15|-4]]|16|$5|10|11|12|A|$13|17|15|-4]]]]

For Mysql5.7, login in mysql command line and run the command,
<pre><code>mysql&gt; show variables like 'sql_mode' ;
</code></pre>
It will show that NO_ZERO_IN_DATE,NO_ZERO_DATE in sql_mode.
<a href="https://i.stack.imgur.com/dJwws.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dJwws.png" alt="enter image description here" /></a>
Try to add a line below [mysqld] in your mysql conf file to remove the two option, mine(mysql 5.7 on Ubuntu 16) is /etc/mysql/mysql.conf.d/mysqld.cnf
<a href="https://i.stack.imgur.com/4E22a.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4E22a.png" alt="enter image description here" /></a>
Now restart mysql. It works!

blocks|key|4419772|text|对于Mysql8.0.18：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419773|CURRENT_TIMESTAMP([fsp])|code-block|syntax|javascript|4419774|删除"(fsp)"，解决了我的问题。|4419775|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For Mysql8.0.18:

<pre><code>CURRENT_TIMESTAMP([fsp])
</code></pre>

Remove "([fsp])", resolved my problem.

blocks|key|4419654|text|最简单的方法是向default+value添加current+timestamp。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4419655|或|4419656|通过添加这个，通过sql+=+"...+DEFAULT+CURRENT_TIMESTAMP;"|4419657|entityMap^0|8|D|N|H|0|0|F|W|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|K|$]]

the simplest way is by adding <code>current timestamp</code> to <code>default value</code>.
or
by add this by sql = <code>&quot;... DEFAULT CURRENT_TIMESTAMP;&quot;</code>

blocks|key|4419874|text|这个问题太宽泛了，无法回答。关于这些问题有很多问题，当基于MySQL的不同引擎的不兼容性是臭名昭著的时候，问题就更多了。对我来说，最好的选择是使用--opt+(mysqldump+--opt)选项了解备份时变量的状态，如果备份不像往常一样有它，或者因为原始备份我没有它，或者因为发生在我们身上的备份不正确，就将它应用于我们的备份。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4419875|如果备份不包含进行备份时使用的设置，我们将不得不开始调查，但基本上我们可以这样做。|4419876|将SETtings添加到备份标头|4419877|echo+'
/*!40101+SET+@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT+*/;
/*!40101+SET+@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS+*/;
/*!40101+SET+@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION+*/;
/*!50503+SET+NAMES+utf8mb4+*/;
/*!40103+SET+@OLD_TIME_ZONE=@@TIME_ZONE+*/;
/*!40103+SET+TIME_ZONE='%2B00:00'+*/;
/*!50606+SET+@OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC+*/;
/*!50606+SET+GLOBAL+INNODB_STATS_AUTO_RECALC=OFF+*/;
/*!40014+SET+@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,+UNIQUE_CHECKS=0+*/;
/*!40014+SET+@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,+FOREIGN_KEY_CHECKS=0+*/;
/*!40101+SET+@OLD_SQL_MODE=@@SQL_MODE,+SQL_MODE='NO_AUTO_VALUE_ON_ZERO'+*/;
/*!40111+SET+@OLD_SQL_NOTES=@@SQL_NOTES,+SQL_NOTES=0+*/;'+%7C+cat+-+mybackup.sql+>+temp+&&+mv+temp++mybackup.sql+|code-block|syntax|javascript|4419878|将恢复SETtings添加到末尾|4419879|/*!40103+SET+TIME_ZONE=@OLD_TIME_ZONE+*/;
/*!50606+SET+GLOBAL+INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC+*/;

/*!40101+SET+SQL_MODE=@OLD_SQL_MODE+*/;
/*!40014+SET+FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS+*/;
/*!40014+SET+UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS+*/;
/*!40101+SET+CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT+*/;
/*!40101+SET+CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS+*/;
/*!40101+SET+COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION+*/;
/*!40111+SET+SQL_NOTES=@OLD_SQL_NOTES+*/;'+>>+mybackup.sql+|4419880|如果您没有这些设置，您可以在原始服务器上使用--opt选项进行mysqldump备份，以便获得这些设置。|4419881|如果你没有，你可以一点一点地进行，在开始和退出时都设置必要的设置。|4419882|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

The question is too broad for the answer. There are many problems regarding these questions, even more so when the incompatibility of the different MySQL-based engines is notorious.
For me the best option is to know the state of variables at the time of making the backup with the option --opt (mysqldump --opt) and apply it to our backup if it does not have it as usual, either because the original backup I did not have it, or because the one that has happened to us is incorrect.
If the backup does not contain the settings with which it was made, we will have to start investigating, but basically we can do it like this.
<h3>Add SETtings to header of backup</h3>
<pre><code>echo '
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;' | cat - mybackup.sql &gt; temp &amp;&amp; mv temp mybackup.sql 
</code></pre>
<h3>Add restore SETtings to end</h3>
<pre><code>/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;' &gt;&gt; mybackup.sql 
</code></pre>
If you don't have those settings you can make a mysqldump backup with the --opt option on the original server, in order to get them.
If you don't have it, you can go little by little, setting the necessary settings, both at the start and at the exit.

When I tried to alter the table it showed the error:

<pre><code>ERROR 1067 (42000): Invalid default value for 'created_at'
</code></pre>

I googled for this error but all I found was as if they tried to alter the timestamp so it occurred. However here I am trying to add a new column and I am getting this error:

<pre><code>mysql&gt; ALTER TABLE investments ADD bank TEXT;
ERROR 1067 (42000): Invalid default value for 'created_at'
</code></pre>

and my table's last two columns are <code>created_at</code> and <code>updated_at</code>.

Here is my table structure:

<a href="https://i.stack.imgur.com/BGZon.png" rel="noreferrer"><img src="https://i.stack.imgur.com/BGZon.png" alt="enter image description here"></a>

ERROR 1067 (42000): Invalid default value for 'created_at'

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

当我试图修改该表时，它显示了错误：ERROR 1067 (42000): Invalid default value for 'created_at'我在谷歌上搜索了这个错误，但我发现的所有错误似乎是他们试图更改时间戳，所以它发生了。然而，在这里我试图添加一个新的列，我得到了这个错误：mysql> ALTER TABLE investments ADD bank TEXT;ERROR 1067 (

问错误1067 (42000)：'created_at‘的默认值无效
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误1067 (42000)：'created_at‘的默认值无效EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误1067 (42000)：'created_at‘的默认值无效
EN