Data truncation: Incorrect datetime value: ‘0000-00-00 00:00:00.000‘ for column ‘createTime‘ at row

这是因为sql_mode中有对datetime类型的非0限制

可以查看自己数据库的sql_mode

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

myqsl数据库一般查询结果为【ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION】

然后设置数据库的sql_mode【查询到的sql_mode中的NO_ZERO_DATE和NO_ZERO_IN_DATE删除】

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

======================================================================

记录一些常用的SQL

查看MYSQL数据库所占用的内存

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables WHERE TABLE_SCHEMA = 'xxxx';

查看某个数据库中表所占用的内存

select TABLE_NAME, 
concat(truncate(data_length/1024/1024,2),'MB') as data_size, 
concat(truncate(index_length/1024/1024,2),'MB') as index_size 
from information_schema.tables where TABLE_SCHEMA = 'xxxx' 
group by TABLE_NAME 
order by data_length desc;