mysql - 更安全的执行INSERT INTO TABLE1 SELECT * FROM TABLE2 WHERE...

Question

在mysql的存储过程中执行：

insert into table1
select * from table2
where xxxx=xxxx

table1和table2的结构是一样的

但是select * from table2 where ...的结果有时候是空的
这时插入table1，就会出现Column 'ID' cannot be null的错误

请问有什么更加安全做法，如果查询的结果是空就不执行插入，MySQL有提供相关的支持么？

[补充]
上面是个归档的存过，归档之后还需要执行删除操作：

insert into month_table
select * from day_table
where xxxx=xxxx;

delete from day_table where xxxx=xxxx;

这时候出现了Dead lock found when try get lock; try restarting transaction的错误。
是不是因为insert into select...语句出错但并没有释放锁？

Answer 1

Is there any safer way? If the query result is empty, the insertion will not be performed. Does MySQL provide relevant support?

If I encounter this problem, I won’t bother to think about it insert into table1 select * from table2 where xxxx=xxxx Is there any protection for this syntax in mysql (no data will not be inserted)? If you want this little judgment, why not check it yourself in advance? What about data? Use count, limit 1

But the result of select * from table2 where... is sometimes empty
When inserting table1, the error Column 'ID' cannot be null will appear

Then let’s talk about this. I don’t remember that an error will be reported due to an empty set, so I specially wrote PROCEDURE and tried it. If the query is an empty result set, no error will be reported. The error "Column 'ID' cannot be null" should be The ID is not allowed to be empty, but the one you want to insert is empty, please check

At this time, the error Dead lock found when try get lock; try restarting transaction occurred.
Is it because the insert into select... statement made an error but the lock was not released?

I personally don’t think so. Please insert and correct the above first and see if there is still a lock prompt. Check the mysql connection to see who is locking the table