MySQL数据插入：1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'start_time' at row 1, Time: 0.003000s

问题

执行如何sql语句：

INSERT INTO `airkeynote` (
	`company_id`,
	`subject`,
	`start_time`,
	`end_time`,
	`contacts`,
	`phone`,
	`email`,
	`cover_img`,
	`detail`,
	`ppt_name`,
	`ppt_url`,
	`state`,
	`create_time`,
	`follow_count`,
	`watch_count`,
	`play_count`,
	`channel`,
	`is_close`,
	`replay_url`,
	`chatroom_id`,
	`resourceid`,
	`sid`,
	`type`,
	`school_code`,
	`show_hide`,
	`air_type` 
)
VALUES
	(
		10,
		'测试',
		'0000-00-00 00:00:00',
		'0000-00-00 00:00:00',
		'',
		'',
		'',
		'http://localhost:8888/Uploads/banner/19878bf3a109bfa5dba3f664dcec7c81.jpg',
		'<p>测试</p>',
		'省级+校级.jpg',
		'http://localhost:8888/Uploads/file/c45c1c8ab49c5d6f56f91a2106fbe176.jpg',
		0,
		'2021-09-11 09:42:10.019',
		0,
		0,
		0,
		'02affc3c549afb71d4eaa869671d8061',
		0,
		'',
		'',
		'',
		'',
		0,
		11765,
	1,
	1)

执行结果：

1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'start_time' at row 1, Time: 0.003000s

从上面的描述我们可以看出原因是：给类型是datetime的xxx字段赋值0000-00-00 00:00:00是不被支持的，赋值包含两种情况：情况一：直接提供的值就是0000-00-00 00:00:00 情况二：xxx字段我们没有提供值，默认值是0000-00-00 00:00:00

解决办法

原因是数据库不支持datetime为0的情况，这是一个模式的设置。

查看现有模式sql_mode

# 查看当前模式
select  @@sql_mode;
# 查看全局模式
SELECT @@global.sql_mode;

执行结果：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

修改sql_mode(将上述查询到的sql_mode中的NO_ZERO_DATE和NO_ZERO_IN_DATE删除即可)

# 修改全局
set @@global.sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
# 修改当前
set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

遗留问题

修改之后，插入还是出现异常：

NSERT INTO `un2co_airkeynote` (
	`company_id`,
	`subject`,
	`start_time`,
	`end_time`,
	`contacts`,
	`phone`,
	`email`,
	`cover_img`,
	`detail`,
	`ppt_name`,
	`ppt_url`,
	`state`,
	`create_time`,
	`follow_count`,
	`watch_count`,
	`play_count`,
	`channel`,
	`is_close`,
	`replay_url`,
	`chatroom_id`,
	`resourceid`,
	`sid`,
	`type`,
	`school_code`,
	`show_hide`,
	`air_type` 
)
VALUES
	(
		8,
		'测试',
		'0000-00-00 00:00:00',
		'0000-00-00 00:00:00',
		'',
		'',
		'',
		'http://localhost:8888/Uploads/banner/3d4c99ae0ca09a36d337052525864a37.jpg',
		'<p>测试</p>',
		'',
		'',
		0,
		'2021-09-11 10:10:56.829',
		0,
		0,
		0,
		'e3ef119672e5fbfbd7446405640d11b9',
		0,
		'',
		'',
		'',
		'',
		0,
		10467,
	1,
	1)

问题：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NSERT INTO `un2co_airkeynote` (
	`company_id`,
	`subject`,
	`start_time`,
	`end_' at line 1, Time: 0.004000s

我的最终解决方案：设置一个默认值，如2006-01-02 15:04:05。