英文:
How to replace whole SQL table data frequently?
问题
我有一个运行定时任务的Spring应用程序。每隔几分钟，定时任务从外部API获取新数据。这些数据应该存储在数据库（MySQL）中，取代旧数据（旧数据应该被新数据覆盖）。需要覆盖数据而不是更新数据。该应用程序本身提供REST API，因此客户端能够从数据库获取数据。因此，不应该出现客户端看到空数据或只看到部分数据的情况，因为有数据更新。
目前，我尝试过删除所有旧数据并插入新数据，但存在客户端只获取部分数据的情况。我尝试过使用Spring Data的deleteAll和saveAll方法。
@Override
@Transactional
public List<Country> overrideAll(@NonNull Iterable<Country> countries) {
removeAllAndFlush();
List<CountryEntity> countriesToCreate = stream(countries.spliterator(), false)
.map(CountryEntity::from)
.collect(toList());
List<CountryEntity> createdCountries = repository.saveAll(countriesToCreate);
return createdCountries.stream()
.map(CountryEntity::toCountry)
.collect(toList());
}
private void removeAllAndFlush() {
repository.deleteAll();
repository.flush();
}
我还考虑过使用一个临时表来获取新数据，当数据完整时，将主表替换为临时表。这是一个好主意吗？还有其他的想法吗？
英文:
I have a Spring application that runs a cron on it. The cron every few minutes gets new data from external API. The data should be stored in a database (MySQL), in place of old data (Old data should be overwritten by new data). The data requires to be overwritten instead of updated. The application itself provides REST API so the client is able to get the data from the database. So there should not be situation that client sees an empty or just a part of data from database because there is an data update.
Currently I've tried deleting whole old data and insert new data but there is a place that a client gets just a part of the data. I've tried it via Spring Data deleteAll and saveAll methods.
@Override
@Transactional
public List<Country> overrideAll(@NonNull Iterable<Country> countries) {
removeAllAndFlush();
List<CountryEntity> countriesToCreate = stream(countries.spliterator(), false)
.map(CountryEntity::from)
.collect(toList());
List<CountryEntity> createdCountries = repository.saveAll(countriesToCreate);
return createdCountries.stream()
.map(CountryEntity::toCountry)
.collect(toList());
}
private void removeAllAndFlush() {
repository.deleteAll();
repository.flush();
}
I also thought about having a temporary table that gets new data and when the data is complete just replace main table with temporary table. Is it a good idea? Any other ideas?
答案1
得分: 1
这是个好主意。您可以通过在另一个表上工作来最小化停机时间，直到它准备好，然后通过重命名快速切换表格。这还将改善用户的感知性能，因为不需要像使用UPDATE/DELETE时那样锁定记录。
在MySQL中，如果表上没有触发器，您可以使用RENAME TABLE。它允许一次重命名多个表，并且它是原子操作（即事务 - 如果发生任何错误，不会进行更改）。您可以使用以下示例：
RENAME TABLE countries TO countries_old, countries_new TO countries;
DROP TABLE countries_old;
请参阅此处获取更多详细信息：
https://dev.mysql.com/doc/refman/5.7/en/rename-table.html
英文:
It's a good idea. You can minimize the downtime by working on another table until it's ready and then switch tables quickly by renaming. This will also improve perceived performance by the users because no record needs to be locked like what happens when using UPDATE/DELETE.
In MySQL, you can use RENAME TABLE if you don't have triggers on the table. It allows multiple table renaming at once and it works atomically (i.e. transaction - if any error happens, no change is made). You can use the following for example
RENAME TABLE countries TO countries_old, countries_new TO countries;
DROP TABLE countries_old;
Refer here for more details
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论