如何复制新数据但跳过 MySQL 中 2 个表中的旧数据 [英] How to copy new data but skip old data from 2 tables in MySQL

问题描述

我对 SQL 非常(就像今天的新手一样)，我有一个问题.我正在尝试将数据从一个表复制到另一个表中.我知道如何从互联网上做到这一点，但我的问题是，我需要跳过任何已经存在的数据.这是我的场景.我们有监控软件，每次遇到新用户时都会记录新用户.我们有一个包含数千个用户的旧数据库用作我们的数据库，但由于服务器中断，我们不得不在短时间内切换到不同的数据库.这意味着我们有一些用户使用新数据库，但大多数用户使用旧数据库.也有重复，因为当软件遇到不在新数据库中的新用户"时，所有登录旧数据库的人都必须重新登录新数据库.

I am very new to SQL (like today new) and I have a question. I'm trying to copy data from one table into another table. I know how to do this from the internet but my issue is, I need to skip any data that is already there. Here's my scenario. We have monitoring software that logs new users every time it encounters one. We have an old database with thousands of users that we use as our database, but we had to switch to a different database for a short time due to a server outage. This means that we have some users on the new database, but most on the old. There are also duplicates since everyone that was logged in the old had to be re-logged in the new when the software encountered a 'new user' that wasn't in the new database.

我想要做的是将新数据库中的所有新用户复制到旧数据库中，但跳过旧数据库中已有的所有用户.

这是怎么做到的?抱歉，冗长的令人困惑的段落.请记住，我对 SQL 很陌生，不知道深蹲.提前致谢.

How can this be done? Sorry for the long confusing paragraph. Please remember I'm very new to SQL and don't know squat. Thanks in advance.

更多信息:这是我的桌子的设置方式.我有 2 个数据库bat"和bat2".bat2 是新的，bat 是旧的.

more info: Here is how my tables are set up. I have 2 databases 'bat' and 'bat2'. bat2 is the new one, bat is the old one.

+---------------+
| Tables_in_bat |
+---------------+
| BAT_ban       |
| BAT_kick      |
| BAT_mute      |
| BAT_players   |
| BAT_web       |
| bat_ban       |
| bat_comments  |
| bat_kick      |
| bat_mute      |
| bat_players   |
| bat_web       |
+---------------+

+----------------+
| Tables_in_bat2 |
+----------------+
| BAT_ban        |
| BAT_kick       |
| BAT_mute       |
| BAT_players    |
| bat2_comments  |
+----------------+

我需要将新的 BAT_players 合并到旧的 BAT_players 中.小写表格是另一回事，因此请忽略它们.

I will need to merge BAT_players from the new one into BAT_players in the old one. the lowercase tables are a different story so ignore those.

BAT_players 表的结构如下所示:

The structure of the BAT_players table looks like this:

+------------------+----------------------------------+-----------------+---------------------+---------------------+
| BAT_player       | UUID                             | lastip          | firstlogin          | lastlogin           |
+------------------+----------------------------------+-----------------+---------------------+---------------------+
| 00meow9          | 21826e64a2974911a0fe542bf11e3901 | 73.163.22.154   | 2021-05-06 10:49:49 | 2021-05-06 10:49:49 |
| 0800Nummer       | bb7bd784c7804834b2df56a6c76d53da | 85.250.79.8     | 2021-05-04 13:43:28 | 2021-05-08 08:04:33 |
| 1angelfish       | 6c7ebb3197d04d71a7976bd830abe452 | 68.131.116.38   | 2021-05-03 20:46:36 | 2021-05-07 17:32:15 |

UUID 将在 2 个文件中保持一致.

The UUID is something that will stay consistent throughout the 2 files.

推荐答案

我想要做的是将新数据库中的所有新用户复制到旧数据库中，但跳过旧数据库中已有的所有用户.

What I want to do is copy all the new users in the new database over to the old database, but skip any users that are already in the old database.

您需要一列或多列来指定用户"的内容.是.如果您有 user_id，一种方法是:

You need one or more columns to specify what a "user" is. One method if you have a user_id is:

insert into old ( . . . )      -- list the columns here
    select . . .
    from new n
    where not exists (select 1 from old o where o.user_id = n.user_id);

MySQL 具有方便的构造，如果您对 user_id(或定义用户的任何列)有唯一索引或约束:

MySQL has convenient construct, if you have a unique index or constraint on user_id (or whatever columns define the user):

insert into old ( . . . )
    select . . .
    from new n
    on duplicate key update set user_id = values(user_id);

最新版本已弃用 VALUES()，您可能会收到警告.

The most recent versions have deprecated VALUES() to you might get a warning.

这篇关于如何复制新数据但跳过 MySQL 中 2 个表中的旧数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！