Database field matching problem

Now there are two tables (mysql)
The fields of table 1 are as follows: province, city, mobile phone number, name, other fields
The fields of table 2 are as follows: province, city, mobile phone number, other fields

The databases of Table 1 and Table 2 are both large, with millions of new rows of data added every day.
Now both Table 1 and Table 2 are divided into tables.
The requirement is: match the data in the two tables based on the mobile phone numbers in the two tables.
The question is:
1. How to store the matched data? Is it stored in the third table, or adding a field to table 1 to keep the ID of the matching row in table 2.
2. How to query unmatched records.

Reply content:

Now there are two tables (mysql)
The fields of table 1 are as follows: province, city, mobile phone number, name, other fields
The fields of table 2 are as follows: province, city, mobile phone number, other fields

The databases of Table 1 and Table 2 are both large, with millions of new rows of data added every day.
Now both Table 1 and Table 2 are divided into tables.
The requirement is: match the data in the two tables based on the mobile phone numbers in the two tables.
The question is:
1. How to store the matched data? Is it stored in the third table, or adding a field to table 1 to keep the ID of the matching row in table 2.
2. How to query unmatched records.

<code class="sql">create table zzz(
id int not null,
xx ...,
primary key(id)
) as (
select id, xx from table where ..
)</code>

http://dev.mysql.com/doc/refm...

For unmatched records, use one table as the left table to associate with another table, and determine whether there is null in the right table
For example

<code class="sql">select * from tb1
left join tb2 on tb1.mobi=tb2.mobi
where tb2.mobi is null</code>

The result is all the records in tb1 that do not appear in tb2

But there is another situation. If there is a record in tb2 that tb1 does not exist, the unmatched record of tb1 cannot be found.
In fact, to check whether the two tables all match, you can use full join to match, but from Judging from the amount of data you have, it is recommended not to use full join. Even if you use it, don’t check all of it.

See this picture for how to use join

Table optimization
1. Create an index on the mobile phone number field

2. It is recommended to divide the tables according to the first few digits of the mobile phone number.

About storage

It is recommended to add fields to Table 1 and Table 2 to store rows in another table. You can skip the processing of fields with values ​​in the next update process.

Unmatched fields are empty.

Also

It is recommended to put the update directly into the record and check whether it exists in Table 2 when adding data to Table 1.

You can query it
select * from t1,t2 where t1.mobile = t2.mobile
After querying it, you can create a new one